none
Update Delete and Insert Problem RRS feed

  • Question

  • I am new to ADO.net but a seasoned dBase programmer.

    I have the following code that works fine Updating, Deleting, and Inserting the FirstName, LastName, EmailAddress, and Number fields.  My problem is when I add the StartDate text box and field. Now I get the error: "Must declare the scalar variable "@StartDate" when trying to Update; "Update requires a valid DeleteCommand when passed DataRow collection with deleted row" when trying to Delete; and "Update requires a valid InsertCommand when passed DataRow collection with new row" when trying to Insert.  I did not receive any of these errors prior to adding the StartDate.

     

     Dim ds As New DataSet()

        Dim cnSQL As SqlConnection

        Dim da As New SqlDataAdapter()

        Dim intCurrentIndex As Integer = 0

     

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

     

            cnSQL = New SqlConnection()

     

            cnSQL.ConnectionString = "Data Source=SQLServer\Dynamics;Initial Catalog=LIMS;User Id=XXXXXXX;Password=XXXXXXXXXXXX"

     

            'specify the SELECT command

            da.SelectCommand = New SqlCommand("SELECT * FROM Email")

            da.SelectCommand.Connection = cnSQL

     

            da.Fill(ds)                                             'fills the data adapter

     

            'open form ready to append with next incremental number

            If ds.Tables(0).Rows.Count > 0 Then

                intCurrentIndex = ds.Tables(0).Rows.Count - 1       'ds.Tables(0).Rows.Count - 1 is the index for the last row

                txtNumber.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Number").ToString() + 1

            End If

     

            'specify the UPDATE command - editing existing record, therefore, number remains unchanged

            da.UpdateCommand = New SqlCommand("UPDATE Email SET FirstName = @FirstName, LastName = @LastName, EmailAddress = @EmailAddress, Service = @Service, StartDate = @StartDate WHERE Number = @Number")

            da.UpdateCommand.Connection = cnSQL

            da.UpdateCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50, "FirstName")

            da.UpdateCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 50, "LastName")

            da.UpdateCommand.Parameters.Add("@EmailAddress", SqlDbType.NVarChar, 50, "EmailAddress")

            da.UpdateCommand.Parameters.Add("@Number", SqlDbType.Int, 5, "Number")

            da.UpdateCommand.Parameters.Add("@Service", SqlDbType.Int, 5, "Service")

            da.UpdateCommand.Parameters.Add("@StartDate", SqlDbType.DateTime, "StartDate")

     

            'specify the DELETE command - keyed on number

            da.DeleteCommand = New SqlCommand("DELETE FROM Email WHERE Number = @Number")

            da.DeleteCommand.Connection = cnSQL

            da.DeleteCommand.Parameters.Add("@Number", SqlDbType.Int, 5, "Number")

     

            'specify the INSERT command - number set to increment by 1 when insert button fires

            da.InsertCommand = New SqlCommand("INSERT INTO Email (FirstName, LastName, EmailAddress, Service, StartDate) VALUES (@FirstName, @LastName, @EmailAddress, @Service, @StartDate)")

            da.InsertCommand.Connection = cnSQL

            da.InsertCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50, "FirstName")

            da.InsertCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 50, "LastName")

            da.InsertCommand.Parameters.Add("@EmailAddress", SqlDbType.NVarChar, 50, "EmailAddress")

            'No need to insert this one since it is Incremental... da.InsertCommand.Parameters.Add("@Number", SqlDbType.Int, 5, "Number")

            da.InsertCommand.Parameters.Add("@Service", SqlDbType.Int, 5, "Service")

            da.InsertCommand.Parameters.Add("@StartDate", SqlDbType.DateTime, "StartDate")

     

        End Sub

     

     

     

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

     

            Dim dr As DataRow

            Dim ctl As Control

            dr = ds.Tables(0).Rows(intCurrentIndex)     'this gets a reference to the row currently being edited

            dr.BeginEdit()                              'edit the row

            dr("FirstName") = txtFirstName.Text

            dr("LastName") = txtLastName.Text

            dr("EmailAddress") = txtEmailAddress.Text

            dr("Number") = txtNumber.Text

            dr("Service") = txtService.Text

            dr("StartDate") = txtStartDate.Text

            dr.EndEdit()

     

            cnSQL.Open()

            da.Update(ds)

            cnSQL.Close()

     

            'ask the data adapter to call the UpdateCommand and update the database

     

            ds.AcceptChanges()                          'commits the change to the dataset

     

            For Each ctl In Controls

                If TypeOf ctl Is TextBox Then ctl.Text = "" 'clear form's textboxes

            Next ctl

     

            intCurrentIndex = ds.Tables(0).Rows.Count - 1           'ds.Tables(0).Rows.Count - 1 is the index for the last row

            txtNumber.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Number").ToString() + 1 'display form with next incremental number

     

        End Sub

     

        Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

     

            Dim dr As DataRow

            dr = ds.Tables(0).Rows(intCurrentIndex)     'this gets a reference to the row currently being deleted

            dr.Delete()                                 'delete the row

     

            cnSQL.Open()

            da.Update(ds)                               'ask the data adapter to call the UpdateCommand and update the database

            cnSQL.Close()

            ds.AcceptChanges()                          'commits the change to the dataset

     

            For Each ctl In Controls

                If TypeOf ctl Is TextBox Then ctl.Text = "" 'clear form's textboxes

            Next ctl

     

            intCurrentIndex = ds.Tables(0).Rows.Count - 1       'ds.Tables(0).Rows.Count - 1 is the index for the last row

            txtNumber.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Number").ToString() + 1   'display form with next incremental number

     

        End Sub

     

        Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click

     

            Dim dr As DataRow

            dr = ds.Tables(0).NewRow()                    'gets a reference to a new row

            dr("FirstName") = txtFirstName.Text

            dr("LastName") = txtLastName.Text

            dr("EmailAddress") = txtEmailAddress.Text

            dr("Number") = txtNumber.Text

            dr("Service") = txtService.Text

            dr("StartDate") = txtStartDate.Text

     

            ds.Tables(0).Rows.Add(dr)

            cnSQL.Open()

            da.Update(ds)                                 'ask the data adapter to call the UpdateCommand and update the database

            cnSQL.Close()

            ds.AcceptChanges()                            'commits the change to the dataset

     

            For Each ctl In Controls

                If TypeOf ctl Is TextBox Then ctl.Text = "" 'clear form's textboxes

            Next ctl

     

            '   intCurrentIndex = ds.Tables(0).Rows.Count - 1       'ds.Tables(0).Rows.Count - 1 is the index for the last row

            '   txtNumber.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Number").ToString() + 1   'display form with next incremental number

     

        End Sub

     

        Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click

     

            'since 0 is the first row

            txtFirstName.Text = ds.Tables(0).Rows(0).Item("FirstName").ToString()

            txtLastName.Text = ds.Tables(0).Rows(0).Item("LastName").ToString()

            txtEmailAddress.Text = ds.Tables(0).Rows(0).Item("EmailAddress").ToString()

            txtNumber.Text = ds.Tables(0).Rows(0).Item("Number").ToString()

            txtService.Text = ds.Tables(0).Rows(0).Item("Service").ToString()

            txtStartDate.Text = ds.Tables(0).Rows(0).Item("StartDate").ToString()

     

        End Sub

     

        Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click

     

            If intCurrentIndex > 0 Then                      'we move back only if we are not at the first row

                intCurrentIndex = intCurrentIndex - 1         'subtract one from the current intex

     

                txtFirstName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("FirstName").ToString()

                txtLastName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("LastName").ToString()

                txtEmailAddress.Text = ds.Tables(0).Rows(intCurrentIndex).Item("EmailAddress").ToString()

                txtNumber.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Number").ToString()

                txtService.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Service").ToString()

                txtStartDate.Text = ds.Tables(0).Rows(intCurrentIndex).Item("StartDate").ToString()

            Else

                MessageBox.Show("You are already at the first record")

            End If

        End Sub

     

        Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click

     

            If intCurrentIndex < ds.Tables(0).Rows.Count - 1 Then       'ds.Tables(0).Row.Count - 1 is the index for the last row

                intCurrentIndex = intCurrentIndex + 1       'add one to the current index

     

                txtFirstName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("FirstName").ToString()

                txtLastName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("LastName").ToString()

                txtEmailAddress.Text = ds.Tables(0).Rows(intCurrentIndex).Item("EmailAddress").ToString()

                txtNumber.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Number").ToString()

                txtService.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Service").ToString()

                txtStartDate.Text = ds.Tables(0).Rows(intCurrentIndex).Item("StartDate").ToString()

            Else

                MessageBox.Show("You are already at the last record")

            End If

     

        End Sub

     

        Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click

     

            intCurrentIndex = ds.Tables(0).Rows.Count - 1       'ds.Tables(0).Rows.Count - 1 is the index for the last row

            txtFirstName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("FirstName").ToString()

            txtLastName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("LastName").ToString()

            txtEmailAddress.Text = ds.Tables(0).Rows(intCurrentIndex).Item("EmailAddress").ToString()

            txtNumber.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Number").ToString()

            txtService.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Service").ToString()

            txtStartDate.Text = ds.Tables(0).Rows(intCurrentIndex).Item("StartDate").ToString()

     

        End Sub

     

     

    End Class

    Thursday, May 20, 2010 9:48 PM

Answers

  • Not 100% sure, but it looks like you've got a problem with DataTypes ... it appears that perhaps the StartDate in your DataTable is of type string rather than DateTime? I'm basing that guess on this line in your btnUpdate_Click() method:

    dr("StartDate") = txtStartDate.Text

    BTW, I think you'd be better off using DataBinding rather than manually getting/setting the value of your TextBoxes:

    In your Load() method, do this:

    txtFirstName.DataBindings.Add("Text", ds.Tables(0), "FirstName")
    txtLastName.DataBindings.Add("Text", ds.Tables(0), "LastName")
    txtEmailAddress.DataBindings.Add("Text", ds.Tables(0), "EmailAddress")
    txtNumber.DataBindings.Add("Text", ds.Tables(0), "Number")
    txtService.DataBindings.Add("Text", ds.Tables(0), "Service")
    txtStartDate.DataBindings.Add("Text", ds.Tables(0), "StartDate")
    

    The DataBinding handles it all automatically.

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, May 22, 2010 11:03 PM
  •         da.UpdateCommand.Parameters.Add("@Service", SqlDbType.Int, 5, "Service")

            da.UpdateCommand.Parameters.Add("@StartDate", SqlDbType.DateTime, "StartDate")

    Hi LHendren,

    It seems there is something wrong with adding @StartDate parameter. The third parameter of SqlParameterCollection.Add method (String, SqlDbType, Int32) is of type Int32 which you assign a "StartDate" to. Please have a check.
    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.add.aspx

    Best regards,
    Alex Liang 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, May 24, 2010 9:39 AM
    Moderator

All replies

  • Not 100% sure, but it looks like you've got a problem with DataTypes ... it appears that perhaps the StartDate in your DataTable is of type string rather than DateTime? I'm basing that guess on this line in your btnUpdate_Click() method:

    dr("StartDate") = txtStartDate.Text

    BTW, I think you'd be better off using DataBinding rather than manually getting/setting the value of your TextBoxes:

    In your Load() method, do this:

    txtFirstName.DataBindings.Add("Text", ds.Tables(0), "FirstName")
    txtLastName.DataBindings.Add("Text", ds.Tables(0), "LastName")
    txtEmailAddress.DataBindings.Add("Text", ds.Tables(0), "EmailAddress")
    txtNumber.DataBindings.Add("Text", ds.Tables(0), "Number")
    txtService.DataBindings.Add("Text", ds.Tables(0), "Service")
    txtStartDate.DataBindings.Add("Text", ds.Tables(0), "StartDate")
    

    The DataBinding handles it all automatically.

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, May 22, 2010 11:03 PM
  •         da.UpdateCommand.Parameters.Add("@Service", SqlDbType.Int, 5, "Service")

            da.UpdateCommand.Parameters.Add("@StartDate", SqlDbType.DateTime, "StartDate")

    Hi LHendren,

    It seems there is something wrong with adding @StartDate parameter. The third parameter of SqlParameterCollection.Add method (String, SqlDbType, Int32) is of type Int32 which you assign a "StartDate" to. Please have a check.
    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.add.aspx

    Best regards,
    Alex Liang 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, May 24, 2010 9:39 AM
    Moderator