Delete using composite key

Question

3.00/5 (2 votes)

See more:

I have a table which has purpose and dateofpayment as it composite key. I am trying
to use my form to delete a row from the table. The code is running but it is not deleting the row from the table
code behind the form

C#

private void btnDelete5_Click(object sender, EventArgs e)
{
            clsStaffsalarypayment person5 = new clsStaffsalarypayment();

            if (MessageBox.Show("Are you really sure of the deletion", "Deletion", MessageBoxButtons.YesNo) == DialogResult.No)
            {
                return;
            }
            else
            {
                person5.ID5 = this.cboStaffid5.Text;
                //person.savehistory();a
                person5.removeRegister5();
            }
}

code in class

C#

public void removeRegister5()
{
            //remove the data in memory


            SqlCommand cmd = new SqlCommand();
            string dat = null;
            dat = dateofpayment.Year + "-" + dateofpayment.Month + "-" + dateofpayment.Day;

            string sqlQuery = null;
            sqlQuery = "delete from tblstaffsalarypaymentdetails where purpose = '" + purpose + "' and dateofpayment ='" + dat + "'";
            cmd.Connection = conn;
            cmd.CommandText = sqlQuery;
            cmd.CommandType = System.Data.CommandType.Text;
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
            cmd.Dispose();
}

Posted 4-Jun-12 7:29am

mikeoabban

Updated 4-Jun-12 7:33am

Shahin Khorshidnia

v2

Add a Solution

2 solutions

Solution 1

Put a breakpoint on the line that builds the query string i.e.

sqlQuery = "delete from tblstaffsalarypaymentdetails where purpose = '" + purpose + "' and dateofpayment ='" + dat + "'";

.
Once the program stops execution, copy this query into sql server management studio and run it (after replacing the parameters).
Check the result - it should most likely help you get to the solution.

Posted 4-Jun-12 7:43am

Abhinav S

Comments

Shahin Khorshidnia 4-Jun-12 13:50pm

Good solution +5

Abhinav S 4-Jun-12 22:02pm

Thank you Shahin.

VJ Reddy 4-Jun-12 19:48pm

Good suggestion. 5!

Abhinav S 4-Jun-12 22:02pm

Thank you VJ.

mikeoabban 5-Jun-12 10:34am

when i run this in sql environment it worked perfect but does not work from my c# form even if all condition are true delete from tblstaffsalarypaymentdetails where staffid ='001' and purpose = 'Allowance' and dateofpayment ='2012-06-04'

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shahin Khorshidnia · Accepted Answer · 2012-06-04T07:49:00

Hello

It doesn't delete because the condition returns false. Check the purpose if it's correct then you must check the dat. If all of these are correct then try this:

C#

sqlQuery = "delete from tblstaffsalarypaymentdetails where purpose = N'" + purpose + "' and dateofpayment ='" + dat + "'";

Some advises:
1. Do not use conn.Close() after a query because if there be an exception before than conn.Close then the connection never be closed.
Use it in a finally block. or use using

2. The code dosn't need else:

C#

if (MessageBox.Show("Are you really sure of the deletion", "Deletion", MessageBoxButtons.YesNo) == DialogResult.No)
{
    return;
}
//else
//{
person5.ID5 = this.cboStaffid5.Text;
//person.savehistory();a
person5.removeRegister5();
//}