asked on

How to delete a record from an SQL Server table

Good morning,

I use the following code to create a datatable which becomes the data source for a dataGridView in a C# Windows Form.
The datable is created using an SQL command - shown below - which retrieve data from two tables (Staff and Distribution).
Now I need an SQL command to delete the part of the record created in Distribution table without affecting the staff table.
Please advise. Thank you

public DataTable DataForDestination()
{
SqlConnection con = null;

try
{
con = new SqlConnection("Server=SQL-Server2;;Database=SupportProject;integrated security=True");
SqlCommand comm = new SqlCommand("Select Staff.fName + ' ' + Staff.lName As Name,Staff.extention,Staff.office as Office,Staff.Department,Staff.Email,Distribution.ID ,Distribution.sSubject as Subject,Distribution.compName as Computer,Distribution.ticketContent as Ticket, Distribution.desDepartment from Staff join Distribution on Staff.ID= Distribution.userID", con);
SqlDataAdapter dataAdapter2 = new SqlDataAdapter(comm);
DataSet ds1 = new DataSet();
dataAdapter2.Fill(ds1, "Staff");
return ds1.Tables["Staff"];
//...
}
finally
{
// release any resoutrces
if (con != null)
{
con.Close();
}

}
}

Lokesh B R

Hi,

Add this method

public DataTable DeleteData()
        {
            try
            {
                using (SqlConnection con = new SqlConnection("Server=SQL-Server2;;Database=SupportProject;integrated security=True"))
                {
                    SqlCommand cmd = new SqlCommand("DELETE FROM Distribution WHERE ID=@ID", con);
                    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = 123; // pass the ID to be deleted

                    con.Open();
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Deleted Successfully...!!!");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception: " + ex.Message);
            }
        }

Open in new window

awking00

Can you post some sample data for the staff and distribution tables and what your current sql command would produce from that data plus what you would like it to be? An explanation of the "part of the record" to be deleted would also help.

adamtrask

ASKER

Below is the code I use for deleting a record - I get the following error when I run it:

String or binary data would be truncated.
The statement has been terminated.

The part of the record that needs to be deleted is the one retrieved from the Distribution table:
Distribution.ID ,Distribution.sSubject as Subject,Distribution.compName as Computer,Distribution.ticketContent as Ticket,

public void deleteTicket(int selectedID)
{
SqlConnection con = null;
con = new SqlConnection("Server=SQL-Server2;;Database=SupportProject;integrated security=True");
SqlCommand comm = new SqlCommand("delete Distribution where ID=@ID",con);
comm.Parameters.AddWithValue("@ID", System.Data.SqlDbType.Int);
comm.Parameters["@ID"].Value = selectedID;
con.Open();
comm.ExecuteNonQuery();
con.Close();
}

Lokesh B R

Hi,

Check whether you are getting the ID value.

what is the ID value?

adamtrask

ASKER

Thank you... I corrected it but still getting same error:

String or binary data would be truncated.
The statement has been terminated.

Lokesh B R

Hi,

String or binary data would be truncated.

You get this error when you try to insert data to a field which is greater than field length.

Keep break point and see in which line you are getting error.

adamtrask

ASKER

But I get the error when I try to delete, not insert, data.

I have no problem retrieving the data and displaying it in the gridview. Only when I use the posted delete code to delete one of the records.

Lokesh B R

Hi,

did you keep break point and see in which line it is breaking?

adamtrask

ASKER

It looks like it only break at the execution of the query line:
comm.ExecuteNonQuery();

adamtrask

ASKER

By the way, I am getting the ID value

ASKER CERTIFIED SOLUTION

Duy Pham

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

adamtrask

ASKER

Duy Pham: Thank you very much.... yes I did have an after delete trigger and as soon as i disabled everything worked.

Thanks a lot... but before I close this I need to ask you, is there a way to build the trigger without causing that problem?

adamtrask

ASKER

Very good

Duy Pham

Trigger is not a problem, but the statement/query inside it is the problem. So I think you'd better check out where the error happens in the trigger and solve it rather than just disable the trigger.

adamtrask

ASKER

OK... I guess if I can't I'll post another questing with the code used.... Thank you