PLS HLP!! Update Access DB from VB.Net

[juice_0032]

I am attempting to use VB2008 Express to Display, Edit, and Append an Access database. I can display information fine, but for some reason updating the database is posing an issue. The code below runs fine and doesn't throw any exceptions. However the Access database itself does not update. When I physically open the table in Access, the field I am attempting to change remains empty. Furthermore, when I try to display the changed field in a label (the very last two lines) I get a discrepency between what is in the data set and my dbrow object despite filling the data set and setting the dbrow = data set. For some reason label30 displays the information that I want while label13 remains blank.

Any assistance will be greatly appreciated, as I have spent the last two days in forums just to get this far. I am an intermediate vb user (though new to .net), I am a novice with db applications, and new to this forum so please be gentle. Thank you in advance.

'Update record.
        Try
            Con.Open()
            Adapter = New OleDbDataAdapter("Select * from Machines", Con)
            Builder = New OleDbCommandBuilder(Adapter)
            Adapter.Fill(DS)
            DS.Tables("Machines").Rows.Item(clickedindex).Item(56) = UserName
            DS.AcceptChanges()
            Builder.GetUpdateCommand()
            Adapter.Update(DS, "Machines")
            Adapter.Dispose()
            DS.Dispose()
            Con.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

'Retrieve updated record and display.
        Try
            Con.Open()
            Adapter = New OleDbDataAdapter("Select * from Machines", Con)
            Adapter.Fill(DS)
            dbRow = DS.Tables("Machines").Rows.Item(SearchResults(clickedindex))
            Adapter.Dispose()
            Con.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

        frmMachDisplay.Label13.Text = DS.Tables("Machines").Rows.Item(clickedindex).Item(56).ToString
        frmMachDisplay.Label30.Text = dbRow(56).ToString

[TomW]

When you call a datasets AcceptChanges method it automatically sets all rows to unmodified, removing and new, updated or deleted work you did to the dataset... So by the time you call update a few lines later, theres nothing to update

Also when you use a dataadapter, you dont have to explicitly open & close the db connection, the dataadapter will automatically do it for you.

Not sure why your disposing of your dataset after updating but then filling it right after that. Also I dont know what values are in your dataset or what your SearchResults function is doing to help much further about your label problem.

[juice_0032]

Thanks for the reply TomW. I disposed of the dataset to verify that the changes that I made were happening. My thoughts here was to open the DB, make changes, update, then close. Then open the DB and disply the info. If I dispose the DS then refill it, what I display from the dbrow and the dataset should be equal. From what you mentioned, I the discrepency must have been due to my improper use of the AcceptChanges method.

The SearchResults function is not a function but an array of inegers. The clickedindex is the index of that array that corresponds to the record I want to view/edit. It's a little conveluded, which is why I intended on removing it before posting. Sorry for the confusion.

I have updated my code per your suggestions and I am now throwing the exeption: Syntax error (missing operator) in query expression '((Sales Order = ?) AND ((? = 1 AND Drawing Number IS NULL) OR (Drawing Number = ?)) AND ((? = 1 AND Router IS NULL) OR (Router = ?)) AND ((? = 1 AND Model IS NULL) OR (Model = ?)) AND ((? = 1 AND Customer IS NULL) OR (Customer = ?)) AND ((? = 1 AND Countr'.

I have been down this road before, this is where I started. Once I used the AcceptChanges method, the exception went away so I thought I was doing something right. I have researched this and not found anything that effectively fixes it. Here is my code as it exist right now (which gives me the above mentioned exeption):

Try
            Adapter = New OleDbDataAdapter("Select * from Machines", Con)
            Builder = New OleDbCommandBuilder(Adapter)
            Adapter.Fill(DS)
            DS.Tables("Machines").Rows.Item(i).Item(56) = UserName
            Builder.GetUpdateCommand()
            Adapter.Update(DS, "Machines")
            Adapter.Dispose()
            DS.Dispose()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

[TomW]

Wow the command builder is creating that statement? It has a parameter (?) where a field name should be and its leaving spaces between multi word field names without placing brackets around it. Where is the begining of the statement, Update Table / Insert etc?

To get a more detailed description of your query statement, add a line of code between the GetUpdateCommand and Adapter.Update lines, msgbox(Adapter.UpdateCommand.ToString)

I think it might be easier to dump the command builder (but I have a bias to command builders, always problematic) and just add your own Insert & Update statements to the data adapter. I can show you how if you need help.

Ok its a bit difficult to offer suggestions since I dont know what the rest of your program is doing but from the code snippet shown, your retrieving all records in a table just to update a single record? If thats the case, can we just execute an update statement directly to the database without even the need to return all records first?

Can you give me a summary of what the form is doing overall and the usuage of this update? Is it a one time update or something done multiple times or throughtout multiple records etc?

[juice_0032]

I hope you had a great weekend TomW. Thanks again for your speedy response. What I am trying to do seems simple enough, but I am just having a heck of a time getting it to work. I have an access database with one table. That table contains machine information (options, dates, customer, ect). I am trying to create a front end that will allow a user to search information, view that information, edit it if necessary, and update the database. The user enters information they want to search on. The program returns a listbox with all of the machines that fit that search criteria. The user double clicks the machine they wish to view/edit. The program returns a new form with all of the fields displayed in a combination of textboxes, comboboxes, listboxes, and checkboxes.

This is where I am at. The code I submitted is in the double click event mentioned above. I am attempting to automatically change a field from that record (column 56) that is blank to whatever the user's username is. This way if another user tries to open that same record, I can display the information but also put up a message saying it is locked for editing by the first user. When the first user is done with the information (the form will be closed by either a cancel or save) I will reset that field containing the username to nothing and any other user would be able to edit the information in that record again.

So yes, at this stage, I am merely trying to update one field in the record. But if the user wants to update that record, I will potentially have to update all 56 columns.

I updated the code with the messagebox as you suggested. The results are peculiar. The messagebox does not display, instead a different exception is thrown: Object reference not set to an instance of an object. When I remove the try block, the line with the messagebox throws the exeption (listed above).

I saw one of your posts from a different thread. I tried some different code in accordance with that post, which was also unsuccessful. The following code throws two exceptions. First: Missing semicolon at end of SQL statement. Second: ExecuteReader requires the command to have a transaction when the connection assigned to the command is pending local transaction. The transaction property of the command has not been initialized.

Now I have tried adding a semicolon to the end of my sql statement, but it comes up with the same exception saying there isn't one. Here is the code:

Dim sql As String
        Dim cmd As OleDbCommand
        Dim so As Double
        Dim myTransaction As OleDbTransaction

        Try
            Con.Open()
            Adapter = New OleDbDataAdapter("Select * from Machines", Con)
            Adapter.Fill(DS)
            dbRow = DS.Tables("Machines").Rows.Item(SearchResults(clickedindex))
            so = CInt(dbRow(0).ToString)
            sql = "UPDATE [Machines] SET LockedForEdit = @UserName WHERE Sales Order = @so;"
            cmd = Con.CreateCommand
            myTransaction = Con.BeginTransaction
            cmd.Transaction = myTransaction
            cmd.CommandType = CommandType.Text
            cmd.CommandText = sql
            cmd.Parameters.AddWithValue("@UserName", UserName)
            cmd.Parameters.AddWithValue("so", so)
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            Adapter.Dispose()
            Con.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

[juice_0032]

I found a problem with my own code. I love it when that happens. I was missing the @ sign in my second cmd parameter "so". I've corrected it, and the second exception mentioned in my last post has disappeared. However the first exception about missing a semicolon remains...

[TomW]

I dont think this is your problem but your use of transactions is incorrect. In order for a transaction to work you either have to commit the changes or roll them back if it errors which I dont see being done.

The only other mistake I see is "Sales Order" if you have a space in your column name words, you much enclose the column name in brackets. [Sales Order]

[juice_0032]

I have simplified (or so I thought) my code to the following and now it throws the exception: No value given for one or more parameters. What am I missing?

Dim cmdUpdate As New OleDbCommand
        Dim dblSO As Double = 118043

        Con.Open()
        Try
            cmdUpdate.CommandText = "UPDATE Machines SET LockedForEdit = '" & UserName & "' Where SalesOrder = " & dblSO
            cmdUpdate.CommandType = CommandType.Text
            cmdUpdate.Connection = Con
            MsgBox(cmdUpdate.CommandText)
            cmdUpdate.ExecuteNonQuery()
            cmdUpdate.Dispose()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        Con.Close()

[TomW]

What is UserName and where is it coming from?

[juice_0032]

It's a string containing the windows username of the user.

UserIDInfo = System.Security.Principal.WindowsIdentity.GetCurrent
        UserName = UserIDInfo.Name

[TomW]

Well in that update statement alone, the only think I can think of is UserName as being null when not expected, Did you change the column name Sales Orders in your database to be a single word? This has to match exactly? And I can picture an order number having a decimal point, are your sure the datatype in your database is the same? Lastly LockedForEdit is a string datatype in the db?

[TomW]

Is this something you can upload including the db, so I can take a look at it?

[juice_0032]

Tom, in your infinite wisdom, you have solved the issue I have been struggling with for almost a week. Kudos. The column name is LockedForEditBy not LockedForEdit. I feel like a fool. I just ran the code and it executed perfectly. Thank you.

[TomW]

Glad it worked. You should have gotten a more clear descriptive error msg.... :(