none
How can I create stored procedure to delete a row in SQL server 2005 Express database table

    Question

  • Hello everyone,

    I have a C# application such that I can execute stored procedures to insert multiple text box text (for example: Surname, First name, Birth date etc)  into an SQL server 2005 Express database table's rows and columns.

    I can also search for data in the database table by typing the primary key text (Surname in this case) in a 'SEARCH' text box if this exist in the database table, the data adapter displays the result (Surname, First name, Birth date etc) inside a datagridview.

    The Surname searched for is highlighted in first column of the first row in the datagridview (in case the database table contained multiple similar Surnames)

    I wish to create a stored procedure(?) or a C# code that will enable me DELETE an highlighted surname and its entire row (First name, Birth date etc) in the datagridview. I can create a stored procedure accepting a parameter as it's best primary key(Surname), but then I will in effect be deleting all the data containing same primary key(Surname) as numerous data table rows may contain same primary key.

    How do I overcome this problem please.

    Many thanks for your anticipated response.

    Alex-Victor.


    Saturday, April 16, 2011 11:21 PM

Answers

  • Hi Chirag,

    Thanks for your response. First, I don't know how to interpret the VB code in C#.

    All I want to do is this:

    How do I write the ADO.net  code in C# to  call DELETE stored procedure?

     I am not sure why you are trying to remove LicenseNumber parameter from parameter collection the i.e.  cmd2.Parameters.Remove("@LicenceNumber"); that's why code is complaning about The SqlParameterCollection only accepts non-null type objects, not string objects:

    instead you need to use

    private void btnDeleteDisplayed_Click(object sender, EventArgs e)
      {
       MessageBox.Show("The Displayed Record Will be Permanently Deleted - THIS DELETE CANNOT BE UNDONE! ", "DELETE DISPLAYED RECORD", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Exclamation);
    
       // 1. Create a command object identifying the Stored Procedure
       SqlCommand cmd2 = new SqlCommand("RiderDataDeleteProcedure", conn);
    
       // 2. Set the command object so it knows to execute a Stored Procedure
       cmd2.CommandType = CommandType.StoredProcedure;
    
       cmd2.Parameters.AddWithValue("@LicenceNumber", txtboxLicenceNumber.Text);
    
       // Execute Query
       cmd2.ExecuteNonQuery();
      }
    
    

     

    • Marked as answer by KJian_ Friday, April 22, 2011 7:15 AM
    Sunday, April 17, 2011 10:22 PM

All replies

  • Hi,

    Keeping Surname only as a primary key is not a good idea. I would rather keep PersonID or employeeID (an integer) as a Primary key for that table. ( or create a composite on primary key on LastName and FirstName)

    Also,  when you bind your data to DataGridView (assuming you are a WPF or winform application in C#)

    you need to bind Employeeid/PersonID (or whatever tableID) and hide it

     

    DataGridView1.Columns("TableID").Visible = False 
    
    
    
    

    and on GridView currentrow changed event

    use the following code to find TableID and pass it to your stored procedure

    Dim r As Integer = DataGridView1.CurrentCell.RowIndex 
    
    
    
    Dim eid As Integer = CType(DataGridView1.Rows[r].Cells["TableID"].Value.ToString(), Integer) 
    
    
    
    

     P.S. I posted code in VB.net please convert it to C#



    • Edited by Chirag Shah Sunday, April 17, 2011 12:09 AM EDIT
    Saturday, April 16, 2011 11:58 PM
  • data table rows may contain same primary key.

    I agree with Chirag Shah... but I didn't understand that how would a data table contain same primary key? Data table have a primary key?
    Make everything as simple as possible, but not simpler.
    Sunday, April 17, 2011 12:36 AM
  • Hi Chirag,

    Thanks for your response. First, I don't know how to interpret the VB code in C#.

    All I want to do is this:

    How do I write the ADO.net  code in C# to  call DELETE stored procedure?

    To call INSERT stored procedure I used the ADO.net code below successfully:

     

    // 1. Create a command object identifying the Stored Procedure
    SqlCommand cmd1 = new SqlCommand("RiderDataUpdateProcedure", conn);
    
    // 2. Set the command object so it knows to execute a Stored Procedure
    cmd1.CommandType = CommandType.StoredProcedure;
    
    // 3.Update Database with Surname
    cmd1.Parameters.AddWithValue("@Surname", Surname.Text);
    
    // 4.Update Database with First Name
    cmd1.Parameters.AddWithValue("@FirstName", FirstName.Text);
    
    // 5.Execute Query
    cmd1.ExecuteNonQuery();
    
    

     

    I can search for the records in the database table and display the results in a datagridview by typing text inside a text box using the code below:

     

    private void btnSearch_Click(object sender, EventArgs e)
        {
          if (radioBtnSurname.Checked == true)
          {
            btnNewSearch.Show();
            
            // Init SqlDataAdapter with select command and connection
            RiderDataDataAdapter = new SqlDataAdapter("select Surname, FirstName, MiddleName, Birthdate, LicenceNumber, ExpirationDate, DateIssued from RiderData where Surname = @Surname", conn);
    
            // Define parameters used in command object.
            SqlCommand SelectCommand = new SqlCommand();
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@Surname";
            param.Value = txtboxSurname.Text;
    
            // Add new parameter to command object
            RiderDataDataAdapter.SelectCommand.Parameters.AddWithValue("@Surname", txtboxSurname.Text);
    
            // Instantiate a new Data table
            DataTable RiderDataDataTable = new DataTable();
    
            // Display the data table in the DataGridView 
            dataGridView1.DataSource = RiderDataDataTable;
    
            // Fill, insert, update, and delete commands
            RiderDataBldr = new SqlCommandBuilder(RiderDataDataAdapter);
    
            // Fill the datable with SqlDataAdapter
            RiderDataDataAdapter.Fill(RiderDataDataTable);
    
          if (RiderDataDataTable.Rows.Count != 0)
            {
              dataGridView1.Show();
            }
    
          else
            {
              MessageBox.Show("The Surname is not found in Database", "RECORD NOT FOUND!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); 
            }
           }
            
          else if (radioBtnLicenceNumber.Checked == true)
          {
            btnNewSearch.Show();
            
            // Init SqlDataAdapter with select command and connection
            RiderDataDataAdapter = new SqlDataAdapter("select Surname, FirstName, MiddleName, Birthdate, LicenceNumber, ExpirationDate, DateIssued from RiderData where LicenceNumber = @LicenceNumber", conn);
    
            // Define parameters used in command object.
            SqlCommand SelectCommand = new SqlCommand();
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@LicenceNumber";
            param.Value = txtboxLicenceNumber.Text;
    
            // Add new parameter to command object
            RiderDataDataAdapter.SelectCommand.Parameters.AddWithValue("@LicenceNumber", txtboxLicenceNumber.Text);
    
            // Instantiate a new Data table
            DataTable RiderDataDataTable = new DataTable();
    
            // Display the data table in the DataGridView 
            dataGridView1.DataSource = RiderDataDataTable;
    
            // Fill, insert, update, and delete commands
            RiderDataBldr = new SqlCommandBuilder(RiderDataDataAdapter);
    
            // Fill the data table with SqlDataAdapter
            RiderDataDataAdapter.Fill(RiderDataDataTable);
    
          if (RiderDataDataTable.Rows.Count != 0)
            {
              dataGridView1.Show();
            }
    
          else
            {
              MessageBox.Show("The Licence Number is not found in Database", "RECORD NOT FOUND!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); 
            }
          }
        }
    

    The next thing I wish to do is to be able to DELETE row from the database table. The row to be deleted will first be searched for in the database table by typing the primary key in a text box and result displayed in a datagridview. So far is, below my code to do this, but I get an unhandled exception: The SqlParameterCollection only accepts non-null type objects, not string objects:

     private void btnDeleteDisplayed_Click(object sender, EventArgs e)
        {
          MessageBox.Show("The Displayed Record Will be Permanently Deleted - THIS DELETE CANNOT BE UNDONE! ", "DELETE DISPLAYED RECORD", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Exclamation);
    
          // 1. Create a command object identifying the Stored Procedure
          SqlCommand cmd2 = new SqlCommand("RiderDataDeleteProcedure", conn);
    
          // 2. Set the command object so it knows to execute a Stored Procedure
          cmd2.CommandType = CommandType.StoredProcedure;
    
          cmd2.Parameters.Remove("@LicenceNumber");
    
          // Execute Query
          cmd2.ExecuteNonQuery();
        }
    

    Can someone please help re-write this code?

    Many thanks in advance.

    Alex-Victor.

     

     

     

    Sunday, April 17, 2011 10:02 PM
  • Hi Chirag,

    Thanks for your response. First, I don't know how to interpret the VB code in C#.

    All I want to do is this:

    How do I write the ADO.net  code in C# to  call DELETE stored procedure?

     I am not sure why you are trying to remove LicenseNumber parameter from parameter collection the i.e.  cmd2.Parameters.Remove("@LicenceNumber"); that's why code is complaning about The SqlParameterCollection only accepts non-null type objects, not string objects:

    instead you need to use

    private void btnDeleteDisplayed_Click(object sender, EventArgs e)
      {
       MessageBox.Show("The Displayed Record Will be Permanently Deleted - THIS DELETE CANNOT BE UNDONE! ", "DELETE DISPLAYED RECORD", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Exclamation);
    
       // 1. Create a command object identifying the Stored Procedure
       SqlCommand cmd2 = new SqlCommand("RiderDataDeleteProcedure", conn);
    
       // 2. Set the command object so it knows to execute a Stored Procedure
       cmd2.CommandType = CommandType.StoredProcedure;
    
       cmd2.Parameters.AddWithValue("@LicenceNumber", txtboxLicenceNumber.Text);
    
       // Execute Query
       cmd2.ExecuteNonQuery();
      }
    
    

     

    • Marked as answer by KJian_ Friday, April 22, 2011 7:15 AM
    Sunday, April 17, 2011 10:22 PM