Solved

How do I use SqlDataAdapter.Update to work properly

Posted on 2009-04-10
5
189 Views
Last Modified: 2013-11-08
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.
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

Open in new window

0
Comment
Question by:RichardKline
  • 3
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
oobayly earned 500 total points
ID: 24115206
You shouldn't be calling ExecuteNonQuery on the UpdateCommand, what you need to call is the Update method on the DataAdapter.

You also haven't mapped any of the SqlParameters in the update command to the dataset:
' After declaring the Update command

' I've made some assumption about the datatypes used

Dim updateCmd As New SqlCommand(sqlUpdate, cn)

updateCmd.Parameters.Add("NotificationSentOn", DbType.DateTime).SourceColumn = "NotificationSentOn";

updateCmd.Parameters.Add("uid", DbType.Int).SourceColumn = "uid";
 

' Continue with your code

da.UpdateCommand = updateCmd

da.AcceptChangesDuringUpdate = True
 

'With da.UpdateCommand.Parameters

'  .Clear()

'  .AddWithValue("@NotificationSentOn", Now)

'  .AddWithValue("@SendNotification", 0)

'  .AddWithValue("@uid", dr("uid"))

'End With

'da.UpdateCommand.ExecuteNonQuery()

Dim changed As Integer = da.Update(oDataSet)

Open in new window

0
 
LVL 1

Author Comment

by:RichardKline
ID: 24115341
Thank you.

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.



        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

            .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

                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

Open in new window

0
 
LVL 15

Expert Comment

by:oobayly
ID: 24115394
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.
0
 
LVL 1

Author Comment

by:RichardKline
ID: 24115417
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.
        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

Open in new window

0
 
LVL 1

Author Comment

by:RichardKline
ID: 24115568
Also neglected to say "Thanks!"   I appreciate the Excellent help.  
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now