Building a dynamic SQL INSERT query with Where Clause C#

Question

I am using SQL Server 2012 & VS 2013. I have table schema like this

[dbo].[Accounts] ([AccntName], [AccntCrVal], [AccntDrVal])

I want to build a dynamic sql query depending on condition. User will select the AccoutnName and enter a Balance then choose balance type Credit or Debit. Now I want if user choose Credit values goes to credit column and vice versa. User will only select AccountName which already entered in the accounts table. Currently I am doing this like this

using (SqlConnection conn = new SqlConnection(conStr))
{
    insrtcmd = new SqlCommand();
    insrtcmd.Connection = conn;
    if (comBoxBalType.Text == "Cr")
    {
        insrtcmd.CommandText = @"INSERT INTO Accoutns(AccntCrVal) VALUES (@bal) Where(AccntName=@acntName)";
        insrtcmd.Connection = conn;
        insrtcmd.Parameters.AddWithValue("@acntName", acntName);
        insrtcmd.Parameters.AddWithValue("@bal", bal);
        conn.Open();
        insrtcmd.ExecuteNonQuery();
    }
    else if (comBoxBalType.Text == "Dr")
    {
        insrtcmd.CommandText = @"INSERT INTO Accoutns(AccntDrVal)  VALUES (@AccntDrVal) Where (AccntName=@prmSlctAcntName)";
        insrtcmd.Connection = conn;
        insrtcmd.Parameters.AddWithValue("@prmSlctAcntName", comBoxSlctAcnt.Text);
        insrtcmd.Parameters.AddWithValue("@AccntDrVal", textBoxBal);
        conn.Open();
        insrtcmd.ExecuteNonQuery();
    }
    else
    {
        MessageBox.Show("Please Enter Values and Select the Balance Type [ Dr | Cr ] ", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

But I don't want to do like this. Thanks

INSERT doesn't have a WHERE clause - if you want to insert - then execute the INSERT, otherwise just don't execute it .... — marc_s
– marc_s, Commented Dec 13, 2015 at 21:15

Arghya C · Accepted Answer · 2015-12-13 21:16:31Z

3

As you already the data in the table, you need to Update the record. With your current setup, you can do something like this

using (SqlConnection conn = new SqlConnection(conStr))
{
    var accountName = comBoxSlctAcnt.Text;
    var balance = textBoxBal.Text;
    var balanceType = comBoxBalType.Text;

    if (balanceType == "Cr" || balanceType == "Dr")
    {
        insrtcmd = new SqlCommand();
        insrtcmd.CommandText = string.Format(
                                     @"UPDATE Accoutns SET {0} = @bal Where AccntName = @acntName", 
                                     balanceType == "Cr" ? "AccntCrVal" : "AccntDrVal");
        insrtcmd.Connection = conn;
        insrtcmd.Parameters.AddWithValue("@acntName", accountName);
        insrtcmd.Parameters.AddWithValue("@bal", balance);
        conn.Open();
        insrtcmd.ExecuteNonQuery();
    }
    else
    {
        MessageBox.Show("Please Enter Values and Select the Balance Type [ Dr | Cr ] ", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

But, you should rather use a CheckBox or DropDown for selecting the transaction type, and also validate all the values before using them. Catch the exceptions, if any. Alternatively you can write a small stored proc and move the conditional logic from C# to SQL.

edited Dec 13, 2015 at 21:16

answered Dec 13, 2015 at 21:08

Arghya C

10.1k4 gold badges51 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mehmet Over a year ago

if it solved your problem. you can mark as an answer.

Collectives™ on Stack Overflow

Building a dynamic SQL INSERT query with Where Clause C#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related