We help IT Professionals succeed at work.

Update sql table

b001
b001 asked
on
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

     CS.Open()
        DA.Fill(DS, "stock")
        CS.Close()
        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
        Next

        Dim cmdBuilder As SqlCommandBuilder
        cmdBuilder = New SqlCommandBuilder(DA)

        Try
            CS.Open()

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


    End Sub
End Class
Comment
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

http://msdn.microsoft.com/en-us/library/yy29cch4.aspx
Most Valuable Expert 2012
Top Expert 2014
Commented:
Are you using SQL Server? Include the primary key field in your select statement.