How do I Update SQL Server from DataGridView

TadSter
TadSter used Ask the Experts™
on
I am creating a DataGridView in Visual Studio 2005. I created a form and dropped the grid onto it. My fill statement has a parameter to get only the data I want. So far, so good. The data displays on the grid no problem.

Now I expect the user to edit the data and next I want to save it back to SQL Server. How do I do that? All the usual advice is to use the Update method of the TableAdapter. Unfortunately, that's missing. Now what? How do I get changes back to SQL Server?
Try
    Me.Validate()
    Me.TrackingBindingSource.EndEdit()

   'this gives me error "Update is not a member of ...
   Me.TrackingTableAdapter.update(Me.DsFolderTrack.Tracking)

    MsgBox("Update successful")
Catch ex As Exception
    MsgBox("Update failed")
End Try

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Ignore the error it should be an UpdateCommand in your TableAdapter:

http://www.actiontraffic.net/JK/GenerateDBDirectMethodText.jpg

Author

Commented:
OK that's great, but what should my Update statement look like?
Is it correct to use parameters like this?
In the WHERE line, how does it know what line to update? I have a composite primary key composed of ClientNum and DateStamp. However, ClientNum = ClientNum doesn't make sense. What should it look like?

btw. I'm using OleDB to connect with SQL Server.
UPDATE Tracking
SET ClientNum = ?, DateStamp = ?, Location = ?, Status = ?, Computer = ?, [User] = ?
WHERE (ClientNum = ClientNum) AND (DateStamp = DateStamp)

Open in new window

Author

Commented:
Here are my parameters:
List of parameters
Now I get a DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. Why am I getting this? No other user is accessing the database at all so no other changes.

See my UPDATE statement below.

UPDATE    Tracking
SET              ClientNum = ?, DateStamp = ?, Location = ?, Status = ?, Computer = ?, [User] = ?
WHERE     (ClientNum = ?) AND (DateStamp = ?)

Open in new window

Author

Commented:
I think it must be a date issue from SQL Server through OleDB to VB.net on DataGridView.
Then back again to SQL Server.

Author

Commented:
Your answer pointed me in the right direction but still left me pulling my hair trying to figure it out.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial