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

[cpeterson36]

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:

        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:

        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.



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.