asked on

How to make an update command for sql

When clicking the drsc button i can't find how to set up the update command to use. Im only familiar with using the ole adapter not the sql ones. Can't find what im looking for online.

Public Class datarepair
    Public mainform As GCPOPTSselector
    Dim datapartset As DataSet
    Dim datapart As SqlDataAdapter
    Private Sub SHOW_DATA(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles datarepairshowdatabutton.Click
        Try
            drsc.Enabled = True
            datapartset = New DataSet
            datapart = New SqlDataAdapter("Select * from data where DropOff between @sdate and @edate", mainform.conn)
            datapart.SelectCommand.Parameters.Add("@sdate", SqlDbType.Date).Value = drsd.Value.Date
            datapart.SelectCommand.Parameters.Add("@edate", SqlDbType.Date).Value = dred.Value.Date
            datapart.Fill(datapartset)
            datapartgrid.DataSource = datapartset.Tables(0)
            mainform.RecordUserAction("Data Repair", "Filling grid with data table from " & drsd.Value.Date & " to " & dred.Value.Date)
        Catch ex As Exception
            mainform.errorwrite("Data Repair fill wiht data info " & ex.ToString)
        End Try
    End Sub
    Private Sub ShowDataHIstory(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndatarepairshowdatahistory.Click
        Try
            drsc.Enabled = True
            datapartset = New DataSet
            datapart = New SqlDataAdapter("Select * from history where DropOff between @sdate and @edate", mainform.conn)
            datapart.SelectCommand.Parameters.Add("@sdate", SqlDbType.Date).Value = drsd.Value.Date
            datapart.SelectCommand.Parameters.Add("@edate", SqlDbType.Date).Value = dred.Value.Date
            datapart.Fill(datapartset)
            datapartgrid.DataSource = datapartset.Tables(0)
            mainform.RecordUserAction("Data Repair", "Filling grid with history table from " & drsd.Value.Date & " to " & dred.Value.Date)
        Catch ex As Exception
            mainform.errorwrite("Data Repair fill with history info " & ex.ToString)
        End Try
    End Sub
    Private Sub drsc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles drsc.Click
        If datapartset.HasChanges() Then
            Try

                ''Dim adptcmb As SqlCommand = New SqlCommand(datapart)
                datapart.Update(datapartset)
                MsgBox("Saved Changes to data")
            Catch ex As Exception
                mainform.errorwrite(ex.ToString & " DRSC CLICK")
            End Try
        End If
    End Sub

    Private Sub DataRepair_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        drsc.Enabled = False
    End Sub
End Class

Open in new window

ASKER CERTIFIED SOLUTION

John (Yiannis) Toutountzoglou

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Millkind

ASKER

So i found the line i was missing, i added
Dim cmdBuilder As New SqlCommandBuilder(datapart)
But it is throwing an error saying that "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information"
I already have a rowid column how to i tell the commandbuilder to use the rowid as key column information?

Public Class datarepair
    Public mainform As GCPOPTSselector
    Dim datapartset As DataSet
    Dim datapart As SqlDataAdapter
    Private Sub SHOW_DATA(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles datarepairshowdatabutton.Click
        Try
            drsc.Enabled = True
            datapartset = New DataSet
            datapart = New SqlDataAdapter("Select * from data where DropOff between @sdate and @edate order by rowid", mainform.conn)
            datapart.SelectCommand.Parameters.Add("@sdate", SqlDbType.Date).Value = drsd.Value.Date
            datapart.SelectCommand.Parameters.Add("@edate", SqlDbType.Date).Value = dred.Value.Date
            datapart.Fill(datapartset)
            datapartgrid.DataSource = datapartset.Tables(0)
            mainform.RecordUserAction("Data Repair", "Filling grid with data table from " & drsd.Value.Date & " to " & dred.Value.Date)
        Catch ex As Exception
            mainform.errorwrite("Data Repair fill wiht data info " & ex.ToString)
        End Try
    End Sub
    Private Sub ShowDataHIstory(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndatarepairshowdatahistory.Click
        Try
            drsc.Enabled = True
            datapartset = New DataSet
            datapart = New SqlDataAdapter("Select * from history where DropOff between @sdate and @edate order by rowid", mainform.conn)
            datapart.SelectCommand.Parameters.Add("@sdate", SqlDbType.Date).Value = drsd.Value.Date
            datapart.SelectCommand.Parameters.Add("@edate", SqlDbType.Date).Value = dred.Value.Date
            datapart.Fill(datapartset)
            datapartgrid.DataSource = datapartset.Tables(0)
            mainform.RecordUserAction("Data Repair", "Filling grid with history table from " & drsd.Value.Date & " to " & dred.Value.Date)
        Catch ex As Exception
            mainform.errorwrite("Data Repair fill with history info " & ex.ToString)
        End Try
    End Sub
    Private Sub drsc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles drsc.Click
        If datapartset.HasChanges() Then
            Try
                Dim cmdBuilder As New SqlCommandBuilder(datapart)
                ''Dim adptcmb As SqlCommand = New SqlCommand(datapart)
                datapart.Update(datapartset)
                MsgBox("Saved Changes to data")
            Catch ex As Exception
                mainform.errorwrite(ex.ToString & " DRSC CLICK")
            End Try
        End If
    End Sub

    Private Sub DataRepair_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        drsc.Enabled = False
    End Sub
End Class

Open in new window

Millkind

ASKER

Forgot to declare PRIMARY keys when creating tables. Great Link