We help IT Professionals succeed at work.

Update sql table

b001 asked
Hi Experts
I am trying to update table stock filed free where it is less thamn 0
I am using the following code, it displays them in the grid but when I click Button1 to  update it gives me an error...  
Sytem.InvalidOperationException: Dynamic SQL generation for the Update Command is not supported against  a Select Command that does not return any column information.

Please help
my Code.

Imports System.Data.SqlClient
Imports System.Data.DataSet
Imports System.Data.OleDb
Imports System.Console
Imports System.Data
Imports System.Data.Common
Public Class ClearNegativeQty
    Private DA As New SqlDataAdapter(" select part,free,min,max FROM stock WHERE free < 0", CS)

    Private DS As New DataSet
    Private dt As New DataTable
 Private Sub ClearNegativeQty_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        DA.Fill(DS, "stock")
        dt = DS.Tables("stock")
        DataGridView1.DataSource = dt
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        For Each row As DataRow In dt.Rows
            If row("free") < 0 Then
                row("free") = 0
            End If

        Dim cmdBuilder As SqlCommandBuilder
        cmdBuilder = New SqlCommandBuilder(DA)


            cmdBuilder = New SqlCommandBuilder(DA)
            DA.Update(DS, "stock")
            MsgBox("Changes Updated")
        Catch ex As Exception
        End Try

    End Sub
End Class
Watch Question

maybe use the .haschanges property or method to check whether any changes are made to the dataset before update is run, see link below

Most Valuable Expert 2012
Top Expert 2014
Are you using SQL Server? Include the primary key field in your select statement.