VB.NET DataSet Update

Question

Why my set of codes didn't update in DataSet? Then it goes to Error. Please anyone check this code and point me out where I am missing. Thanks in advance!

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

    Dim conxMain As New SqlConnection("Data Source=SERVER;Initial Catalog=DBTest;Persist Security Info=True;User ID=username;Password=pwds")

    Dim dadPurchaseInfo As New SqlDataAdapter
    Dim dsPurchaseInfo As New DataSet1
    Try
        Dim dRow As DataRow

        conxMain.Open()

        Dim cmdSelectCommand As SqlCommand = New SqlCommand("SELECT * FROM Stock", conxMain)
        cmdSelectCommand.CommandTimeout = 30

        dadPurchaseInfo.SelectCommand = cmdSelectCommand
        Dim builder As SqlCommandBuilder = New SqlCommandBuilder(dadPurchaseInfo)

        dadPurchaseInfo.Fill(dsPurchaseInfo, "Stock")


        For Each dRow In dsPurchaseInfo.Tables("Stock").Rows
            If CInt(dRow.Item("StockID").ToString()) = 2 Then
                dRow.Item("StockCode") = "Re-Fashion[G]"
            End If

        Next
        dadPurchaseInfo.Update(dsPurchaseInfo, "Stock")

    Catch ex As Exception
        MsgBox("Error : ")
    Finally
        If dadPurchaseInfo IsNot Nothing Then
            dadPurchaseInfo.Dispose()
        End If

        If dsPurchaseInfo IsNot Nothing Then
            dsPurchaseInfo.Dispose()
        End If

        If conxMain IsNot Nothing Then
            conxMain.Close()
            conxMain.Dispose()
        End If
    End Try
End Sub

Answer 1

Does your dataAdapter has update command ?

(it looks like it doesn't - so it doesn't know what do to with update....)

Here is an Update Command example:(for an employee table with 3 columns - as listed below:

UPDATE [Employee] SET [name] = @name, [manager] = @manager WHERE (([id] = @Original_id) AND ((@IsNull_name = 1 AND [name] IS NULL) OR ([name] = @Original_name)) AND ((@IsNull_manager = 1 AND [manager] IS NULL) OR ([manager] = @Original_manager)));

SELECT id, name, manager FROM Employee WHERE (id = @id)

You can see it is a general update that can handle changes in any field.

Answer 2

Does your condition in the loop get executed (set a break point!)? Where is the error thrown? What error?

Also, why does it use ToString at all? This seems redundant.

If CInt(dRow.Item("StockID")) = 2 Then

Should be enough.

Finally, you’re performing redundant cleanup:

If conxMain IsNot Nothing Then
    conxMain.Close()
    conxMain.Dispose()
End If

Dispose implies Close – no need to perform both operations:

Close and Dispose are functionally equivalent.

[Source: MSDN]

Answer 3

I got it from the error correction of my program by Konard Rudolph!

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

    Dim conxMain As New SqlConnection("Data Source=SERVER;Initial Catalog=DBTest;Persist Security Info=True;User ID=username;Password=pwds")

    Dim dadPurchaseInfo As New SqlDataAdapter
    Dim dsPurchaseInfo As New DataSet1
       Try
            Dim dRow As DataRow

            conxMain.Open()

            dadPurchaseInfo.SelectCommand = New SqlCommand("SELECT * FROM Stock", conxMain)
            Dim builder As SqlCommandBuilder = New SqlCommandBuilder(dadPurchaseInfo)


            dadPurchaseInfo.Fill(dsPurchaseInfo, "Stock")

            For Each dRow In dsPurchaseInfo.Tables("Stock").Rows
                If CInt(dRow.Item("StockID")) = 2 Then
                    dRow.Item("StockCode") = "Re-Fashion(H)"
                End If

            Next
            dadPurchaseInfo.Update(dsPurchaseInfo, "Stock")
        Catch ex As Exception
            MsgBox("Error : " & vbCrLf & ex.Message)
        Finally
            If dadPurchaseInfo IsNot Nothing Then
                dadPurchaseInfo.Dispose()
            End If

            If dsPurchaseInfo IsNot Nothing Then
                dsPurchaseInfo.Dispose()
            End If

            If conxMain IsNot Nothing Then
                conxMain.Dispose()
            End If
        End Try
  End Sub

The above set of code work to update with DataSet! Thanks to stackoverflow community and who answered my question.

Here is ref:

• How To Update a SQL Server Database by Using the SqlDataAdapter Object in Visual Basic .NET
• How to update a database from a DataSet object by using Visual Basic .NET

p.s: Like o.k.w said : The Table must have primary key. Thanks o.k.w!