Results 1 to 3 of 3

Thread: [RESOLVED] Help deleting line in data table and passing to stored procedure to delete from table

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2012
    Location
    Omaha
    Posts
    17

    Resolved [RESOLVED] Help deleting line in data table and passing to stored procedure to delete from table

    By policy, we cannot change SQL Server tables except through stored procedures. As such, we end up doing much more work than we need to do. VB.Net would work just fine but they want the logging in the SQL server environment.

    I load the table to a datatable as follows:

    Code:
            Dim mySQL As String = "SELECT Change_PIN, Current_PIN, Badge_ID, Employee_ID, Employee_Name, Usr_ID , Last_PIN_Change_Date, Permission, Normal_Shift, Last_PIN, Next_2_Last_PIN FROM dbo.XP_PZ_Data_Capture_PINs ORDER BY Employee_Name"
            Try
                Using cmd As New SqlCommand(mySQL, New SqlConnection(myConnectionString))
                    cmd.CommandType = CommandType.Text
                    cmd.CommandText = mySQL
                    cmd.Connection = myConn
                    cmd.CommandTimeout = 0
                    Using da As New SqlDataAdapter(cmd)
                        da.Fill(dtPINView)
                    End Using
                End Using
                bs_PINView.DataSource = dtPINView
                dgvEngPINUpdate.DataSource = bs_PINView
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Could not connect to database to display table.", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    This loads and works great. I can change data in the data table and then save the changes and it updates my table through the stored procedure. However, if I delete a row it blows up. This is my code to update the table through the stored procedure:

    Code:
            Try
                Dim strSQL As String = "usp_XP_PZ_Data_Capture_PIN_Update"
                Dim myCommand As New SqlClient.SqlCommand
                For Each dr As DataRow In dtPINView.Rows
                    myCommand = New SqlCommand("usp_XP_PZ_Data_Capture_Data_PIN_Update", myConn)
                    myCommand.Parameters.AddWithValue("@Badge_ID", dr("Badge_ID"))
                    myCommand.Parameters.AddWithValue("@Current_PIN", dr("Current_PIN"))
                    myCommand.Parameters.AddWithValue("@Employee_ID", dr("Employee_ID"))
                    myCommand.Parameters.AddWithValue("@Employee_Name", dr("Employee_Name"))
                    myCommand.Parameters.AddWithValue("@Usr_ID", dr("Usr_ID"))
                    myCommand.Parameters.AddWithValue("@Normal_Shift", dr("Normal_Shift"))
                    myCommand.Parameters.AddWithValue("@Permission", dr("Permission"))
                    myCommand.Parameters.AddWithValue("@Last_Pin_Change_Date", dr("Last_PIN_Change_Date"))
                    myCommand.Parameters.AddWithValue("@Last_PIN", dr("Last_PIN"))
                    myCommand.Parameters.AddWithValue("@Next_2_Last_PIN", dr("Next_2_Last_PIN"))
                    myCommand.Parameters.AddWithValue("@Change_PIN", dr("Change_PIN"))
                    With myCommand
                        .CommandType = CommandType.StoredProcedure
                        .CommandText = strSQL
                        .Connection = myConn
                        .CommandTimeout = 0
                    End With
                    myConn.Open()
                    myCommand.ExecuteNonQuery()
                    myConn.Close()
                Next
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Could not Load data into database.", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    The error message I get is this.

    Name:  Error Msg.png
Views: 384
Size:  6.7 KB

    Thanks for your help! I really appreciate it. I am going to have to do this stuff for a while at this new job so I better get it right.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,296

    Re: Help deleting line in data table and passing to stored procedure to delete from t

    No, no, no! You should NOT be using ExecuteNonQuery if you have a DataTable. You used a data adapter to populate the DataTable in the first place. Use the same data adapter to save the changes. Follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data. It includes an example that uses a data adapter and explicitly creates its InsertCommand, UpdateCommand and DeleteCommand. Follow that example but, instead of assigning SQL code to the CommandText of each command, assign the name of the appropriate sproc and then set the CommandType to StoredProcedure.

    By the way, it also demonstrates a much less verbose method of creating the data adapter in the first place.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Nov 2012
    Location
    Omaha
    Posts
    17

    Re: Help deleting line in data table and passing to stored procedure to delete from t

    Thank you! That code worked like a charm!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width