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();
                }

            }
        }
adamtraskAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lokesh B RDeveloperCommented:
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

0
awking00Commented:
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.
0
adamtraskAuthor Commented:
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();
        }
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Lokesh B RDeveloperCommented:
Hi,

Check whether you are getting the ID value.

what is the ID value?
0
adamtraskAuthor Commented:
Thank you... I corrected it but still getting same error:

String or binary data would be truncated.
The statement has been terminated.
0
Lokesh B RDeveloperCommented:
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.
0
adamtraskAuthor Commented:
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.
0
Lokesh B RDeveloperCommented:
Hi,

did you keep break point and see in which line it is breaking?
0
adamtraskAuthor Commented:
It looks like it only break at the execution of the query line:
 comm.ExecuteNonQuery();
0
adamtraskAuthor Commented:
By the way, I am getting the ID value
0
Duy PhamFreelance IT ConsultantCommented:
Do you have any trigger setup on delete of that Distribution table? It seems like there was another statement executed as the consequence of the DELETE statement.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
adamtraskAuthor Commented:
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?
0
adamtraskAuthor Commented:
Very good
0
Duy PhamFreelance IT ConsultantCommented:
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.
0
adamtraskAuthor Commented:
OK... I guess if I can't I'll post another questing with the code used.... Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.