VB.NET DataSet Update

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 SqlComma开发者_开发技巧ndBuilder = 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

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]

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.

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!

--MENU--
Dim login As New LoginClass
login.ShowDialog()

--CONEXION--
Private conec As SqlConnection
Dim stringCon As String = "Data Source= ;Initial Catalog=;Persist Security Info=True;User ID=;Password="
Public ReadOnly Property prConec() As Object
    Get
        Return conec
    End Get
End Property
Public Sub Conectar()
    Try
        conec = New SqlConnection(stringCon)
        If conec.State <> ConnectionState.Open Then
            conec.Open()
        End If
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

--BUSCAR--
funciones.Conectar()
Dim coman As New SqlCommand("sp_cliente", funciones.prConec)
Dim dt As New DataTable
coman.CommandType = CommandType.StoredProcedure
coman.Parameters.Add("@i_operacion", SqlDbType.Char, 1, ParameterDirection.Input).Value = "B"
dt.Load(coman.ExecuteReader())
grdClientes.DataSource = dt

--INSERTAR--
funciones.Conectar()
Dim coman As New SqlCommand("sp_articulo", funciones.prConec)
coman.CommandType = CommandType.StoredProcedure
coman.Parameters.Add("@i_operacion", SqlDbType.Char, 1, ParameterDirection.Input).Value = "I"
coman.ExecuteNonQuery()
Buscar()
Limpiar()

--COMBO--
Dim dt As New DataTable
dt.Columns.Add("Codigo")
dt.Columns.Add("Descripcion")
Dim dr1 As DataRow = dt.NewRow
dr1.Item("Codigo") = "A"
dr1.Item("Descripcion") = "Activo"
dt.Rows.Add(dr1)
Dim dr2 As DataRow = dt.NewRow
dr2.Item("Codigo") = "I"
dr2.Item("Descripcion") = "Inactivo"
dt.Rows.Add(dr2)
cmbEstado.DataSource = dt
cmbEstado.ValueMember = "Codigo"
cmbEstado.DisplayMember = "Descripcion"

--GRIDVIEW--
--1--
Dim grdFila As DataGridViewRow = grdClientes.CurrentRow
txtCedula.Text = grdFila.Cells(0).Value
--2--
If DataGridProductos.CurrentCell.ColumnIndex = 0 Then
    Dim FLstArticulos As New FLstArticulos
    FLstArticulos.ShowDialog()
    DataGridProductos.CurrentRow.Cells(0).Value = FLstArticulos.PrIdArticulo
End If

--GRIDVIEW.CELLENDEDIT--
If DataGridProductos.CurrentCell.ColumnIndex = 3 Then
    Dim precio As New Double
    Dim cantidad As New Double
    precio = CDbl(grdRow.Cells(2).Value)
    cantidad = CDbl(grdRow.Cells(3).Value)
    DataGridProductos.CurrentRow.Cells(4).Value = PLTotalFilaItem(cantidad, precio)
    PLCargaTotales()
End If

Sub PLCargaTotales()
    Dim subTotal As Double
    Dim iva As Double
    For Each grd As DataGridViewRow In DataGridProductos.Rows
        If Not String.IsNullOrEmpty(grd.Cells(4).Value) Then
            subTotal = subTotal + CDbl(grd.Cells(4).Value)
        End If
    Next grd
    txtSubtotal.Text = subTotal.ToString
    iva = Decimal.Round(subTotal`enter code here` * 0.12)
    txtIva.Text = iva.ToString
    txtTotalPagar.Text = (subTotal + iva).ToString
End Sub