All what I wanted to do is to Update a Table in DB using Dataset (Visual Studio 2005 & MS SQL Server 2000)

Hello all,

I'am trying to update a modified Dataset back into the DB. I looked all over and surely the answer is out there but haven't yet to find it. When I run this I get zero result. Also, the proc is not being called at all when check in the profiler. Any help or thoght would be really appreciated.

Regards...

Here is the code:

Public Sub UpdateDB()

' //executing the stored proc

Dim da As New SqlDataAdapter

Dim cmd As New SqlCommand

cmd = New SqlCommand("proc_UpdateUser", Me.DataConnection())

cmd.Parameters.Add("@DeliveredOn", SqlDbType.DateTime, 8, itemsDS.Tables(1).Columns("delivery_date").DateTimeMode)

'cmd.Parameters.Item("@DeliveredOn").Value = Now ' itemsDS.Tables(1).Rows.Item(2)

cmd.Parameters.Add("@UserID", SqlDbType.Int, 4, itemsDS.Tables(1).Columns("user_id").ColumnName)

' cmd.Parameters.Item("@UserID").Value = itemsDS.Tables(1).Columns("")

cmd.Parameters.Add("@ProdD", SqlDbType.Int, 4, itemsDS.Tables(1).Columns("prod_id").ColumnName)

'cmd.Parameters.Item("@ProdD").Value = itemsDS.Tables(1).Rows.Item(0)

' da = New SqlDataAdapter(cmd)

' da.SelectCommand = cmd

Dim i As Integer

Try

If (itemsDS.HasChanges()) Then

'i = da.Fill(itemsDS)

'Dim d As DataRow

'For Each d In itemsDS.Tables(1).Rows

'Next

'Dim dsTest As DataSet

'dsTest = itemsDS.GetChanges()

'i = da.Update(dsTest)

'dsTest.Tables(0).AcceptChanges()

End If

Catch ex As Exception

End Try

End Sub

And here is the proc:

ALTER proc [dbo].[proc_UpdateUser]

@DeliveredOn datetime,

@UserID int,

@prodD int

update delivered_date

set delivered_on=@DeliveredOn

where user_id=@UserID

and prod_id=@prodID

Hello Paul and thanks for replaying...

1- itemsDS.HasChanges() returns true.

2- When I modify my code and replace UpdateCommand with SelectCommand it acutally connects and I see the SP call in profiler. But just to be in the safe side, would you suggest to verify if the conn is open

3- The DataSet has changes and it reutrns true. I'm not sure what's ment by the last part "see if the changes will require the Update command to be executed..." , can you please elaberate on this one

Again, thanks a lot for replaying...

Regards...