RichardKline
asked on
How do I use SqlDataAdapter.Update to work properly
THis is my first attempt to use the Update method of SqlDataAdapter. I've found several examples on the web but am unable to get my code to work. The current version is, probably, a bad combination of examples.
No error is generated. But the SQL Server table is never updated.
The code does retrieve the needed information. The for loop works fine. The SendNotification() function returns true.
Any thought on what might be wrong?
Thank you.
No error is generated. But the SQL Server table is never updated.
The code does retrieve the needed information. The for loop works fine. The SendNotification() function returns true.
Any thought on what might be wrong?
Thank you.
Dim sqlSelect As String = "SELECT [uid] " & _
" ,[epk] " & _
" ,[user_id] " & _
" ,[password] " & _
" ,[firstName] " & _
" ,[lastName] " & _
" ,[email] " & _
" ,[genDate] " & _
" ,[userOrigin] " & _
" ,[sendNotification] " & _
" ,[emailTemplate] " & _
" ,[snapShotGuid] " & _
" ,[NotificationSentOn] " & _
" FROM [bbprocessing].[dbo].[UrNewUsers] " & _
" WHERE [snapShotGuid] = @snapShotGuid" & _
" AND [sendNotification]=1 " & _
" AND [NotificationSentOn] < @NotificationSentOn"
Dim sqlUpdate As String = "UPDATE [bbprocessing].[dbo].[UrNewUsers] " & _
" SET [sendNotification] = 0 " & _
" ,[NotificationSentOn] = @NotificationSentOn " & _
" WHERE [uid] = @uid "
Dim cn As New SqlConnection
cn = clsMakeCn.newProc
Dim da As New SqlDataAdapter(sqlSelect, cn)
With da.SelectCommand.Parameters
.Clear()
.AddWithValue("@snapShotGuid", GuidToProcess)
.AddWithValue("@NotificationSentOn", New DateTime(2000, 1, 1))
End With
Dim updateCmd As New SqlCommand(sqlUpdate, cn)
da.UpdateCommand = updateCmd
da.AcceptChangesDuringUpdate = True
Dim oDataSet As New DataSet
Try
da.Fill(oDataSet, "Notifications")
Catch ex As Exception
Dim emsg As String = ex.Message
End Try
For Each dr As DataRow In oDataSet.Tables("Notifications").Rows
If SendNotification(dr) Then
Try
With da.UpdateCommand.Parameters
.Clear()
.AddWithValue("@NotificationSentOn", Now)
.AddWithValue("@SendNotification", 0)
.AddWithValue("@uid", dr("uid"))
End With
da.UpdateCommand.ExecuteNonQuery()
Catch ex As Exception
Dim eMsg As String = ex.Message
End Try
Else
Dim eMsg As String = "Unable to Send Notification"
End If
Next
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you add a breakpoint to the da.Update line, and check the value of dr.RowState. The Update method will only execute the Update command on rows that have a RowState of Modified.
ASKER
Found it. Silly omission. Neglected to se the new values before updating.
dr("SendNotification") = 0
dr("NotificationSentOn") = Now
The complete and working new code is below.
dr("SendNotification") = 0
dr("NotificationSentOn") = Now
The complete and working new code is below.
Dim sqlSelect As String = "SELECT [uid] " & _
" ,[epk] " & _
" ,[user_id] " & _
" ,[password] " & _
" ,[firstName] " & _
" ,[lastName] " & _
" ,[email] " & _
" ,[genDate] " & _
" ,[userOrigin] " & _
" ,[sendNotification] " & _
" ,[emailTemplate] " & _
" ,[snapShotGuid] " & _
" ,[NotificationSentOn] " & _
" FROM [bbprocessing].[dbo].[UrNewUsers] " & _
" WHERE [snapShotGuid] = @snapShotGuid" & _
" AND [sendNotification]=1 " & _
" AND [NotificationSentOn] < @NotificationSentOn"
Dim sqlUpdate As String = "UPDATE [bbprocessing].[dbo].[UrNewUsers] " & _
" SET [sendNotification] = 0 " & _
" ,[NotificationSentOn] = @NotificationSentOn " & _
" WHERE [uid] = @uid "
Dim cn As New SqlConnection
cn = clsMakeCn.newProc
Dim da As New SqlDataAdapter(sqlSelect, cn)
With da.SelectCommand.Parameters
.Clear()
.AddWithValue("@snapShotGuid", GuidToProcess)
.AddWithValue("@NotificationSentOn", New DateTime(2000, 1, 1))
End With
Dim updateCmd As New SqlCommand(sqlUpdate, cn)
With updateCmd.Parameters
.Clear()
.Add("NotificationSentOn", SqlDbType.DateTime).SourceColumn = "NotificationSentOn"
.Add("uid", SqlDbType.Int).SourceColumn = "uid"
End With
da.UpdateCommand = updateCmd
da.AcceptChangesDuringUpdate = True
Dim oDataSet As New DataSet
Try
da.Fill(oDataSet, "Notifications")
Catch ex As Exception
Dim emsg As String = ex.Message
End Try
For Each dr As DataRow In oDataSet.Tables("Notifications").Rows
If SendNotification(dr) Then
dr("SendNotification") = 0
dr("NotificationSentOn") = Now
Try
Dim changed As Integer = da.Update(oDataSet, "Notifications")
Catch ex As Exception
Dim eMsg As String = ex.Message
End Try
Else
Dim eMsg As String = "Unable to Send Notification"
End If
Next
ASKER
Also neglected to say "Thanks!" I appreciate the Excellent help.
ASKER
I had to make a couple of small changes to avoid errors with UpdateCmd.Parameters add lines and Dim changed As Integer = da.Update(oDataSet, "Notifications")
So the code operates without error
but there is still no change in the SQL table values.
Open in new window