CODEWITHSUNDEEP
c#sql.netstored-proceduresado.net
Jobin
Jobin

Reputation: 91

error executing procedure in the loop

I'm facing problem while executing procedure inside loop. My requirment is to insert data to table. first column is alphabet which I try to get in loop and next three are common for all rows. But I'm getting error after first iteration saying that variable must be unique.

string str = Properties.Settings.Default.con;
SqlConnection con = new SqlConnection(str);

char[] az = Enumerable.Range('A', 'Z' - 'A' + 1).Select(i => (Char)i).ToArray();


SqlCommand cmd = new SqlCommand("Execute InsertPurchase  @ShipTo,@StoreName,@desc,@Alpha", con);
try
{
    cmd.Parameters.AddWithValue("@ShipTo", txtstoreto.Text);
    cmd.Parameters.AddWithValue("@StoreName", txtstorefrom.Text);
    cmd.Parameters.AddWithValue("@desc", Globals.s_desc.ToString());
    con.Open();
    foreach (var c in az)
    {
        cmd.Parameters.AddWithValue("@Alpha", c.ToString());
        cmd.ExecuteNonQuery();
    }
    con.Close();
    j = true;
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

Error message:

The variable name '@Alpha' has already been declared. Variable names must be unique within a query batch or stored procedure.

Upvotes: 0

Views: 74

Answers (4)

Andrey Korneyev
Andrey Korneyev

Reputation: 26856

You're adding parameter Alpha in the loop over and over again.

So instead of adding it in the loop you just have to set its value:

cmd.Parameters.Add("@Alpha", SqlDbType.VarChar);
con.Open();
foreach (var c in az)
{
    cmd.Parameters["@Alpha"].Value = c.ToString();
    cmd.ExecuteNonQuery();
}

Also notice that using of AddWithValue is not a good idea in most cases since it tries to deduce parameter type from the value passed. In most cases it is better to explicitly set parameter type.

Upvotes: 3

Shatabda Dhar Gupta
Shatabda Dhar Gupta

Reputation: 21

You have to clear the variable to add again Something like this: 

foreach (var c in az)
    {
        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@ShipTo", txtstoreto.Text);
        cmd.Parameters.AddWithValue("@StoreName", txtstorefrom.Text);
        cmd.Parameters.AddWithValue("@desc", Globals.s_desc.ToString());
        cmd.Parameters.AddWithValue("@Alpha", c.ToString());
        cmd.ExecuteNonQuery();
    }

Upvotes: 0

Soner Gönül
Soner Gönül

Reputation: 98750

As an alternative, you can move all parameter declarations in your for loop and Clear them in every iteration.

foreach (var c in az)
{
    cmd.Parameters.Clear();
    cmd.Parameters.AddWithValue("@ShipTo", txtstoreto.Text);
    cmd.Parameters.AddWithValue("@StoreName", txtstorefrom.Text);
    cmd.Parameters.AddWithValue("@desc", Globals.s_desc.ToString());
    cmd.Parameters.AddWithValue("@Alpha", c.ToString());
    cmd.ExecuteNonQuery();
}

But of course, this adds unnecessary first 3 parameter in every iteration and clear them again.

By the way, don't use AddWithValue as much as you can. It may generate unexpected and surprising results sometimes. Use Add method overload to specify your parameter type and it's size.

Also use using statement to dispose your connection and command automatically instead of calling Close or Dispose methods manually.

Upvotes: 0

AntDC
AntDC

Reputation: 1917

I think you keep on adding the @Alpha parameter over and over... First time add the parameter and set the value. Every subsequent iteration, just set the value.

eg first iteration....

cmd.Parameters.AddWithValue("@Alpha", c.ToString());

Subsequent iterations....

cmd.Parameters["@Alpha"].Value = c.ToString();

Good luck.

Upvotes: 0

Related Questions