添加具有相同名称的SQL参数 [英] Adding sql parmeter with the same name

问题描述

首先对我的英语感到抱歉
根据条件，我需要插入一行.我必须将行插入三遍
我有三个复选框.我正在为我的订阅ID生成随机整数.如果复选框为true，则向其添加参数，但会生成错误

错误:变量名称"@subid"已被声明.变量名称在查询批处理或存储过程中必须是唯一的

代码:

first of all sorry about my english
Based on a condition i need to insert a row . i have to insert the row three times
i am having three checkboxes. and i am generating random integer for my subscription id. if the check box is true i am adding parameters to it but it is generating error

error : the variable name ''@subid'' has already been declared. variable names must be unique within a query batch or stored procedures

Code :

string insertsubscriptionquery = " INSERT INTO Subscription(SubscriptionId,TypeOfMagazine) values (@subid,@TypeOfMagazine)";
SqlCommand subcmd = new SqlCommand(insertsubscriptionquery,connstring);
if (checkbox1.IsChecked == true)
                {
                    subcmd.Parameters.AddWithValue("@subid", subscripid1);
                    subcmd.Parameters.AddWithValue("@TypeOfMagazine", magtype1);
                     subcmd.ExecuteNonQuery();
                     
                }
                if (checkbox2.IsChecked == true)
                {
                    subcmd.Parameters.AddWithValue("@subid", subscripid2);
                    subcmd.Parameters.AddWithValue("@TypeOfMagazine", magtype2);
                    subcmd.ExecuteNonQuery();
                    
                }
                if (checkbox3.IsChecked == true)
                {
                    subcmd.Parameters.AddWithValue("@subid", subscipid3);
                    subcmd.Parameters.AddWithValue("@TypeOfMagazine", magtype3);
                    subcmd.ExecuteNonQuery();
                    noofrows++;
                }

订阅ID是主键

如何同时插入所有值

subscription id is a primary key

How to insert all values at the same time

推荐答案

问题是，当您执行第一个值时，需要设置参数.然后，当您尝试执行第二个命令时，该命令存在，并且如果选中checkbox1，则已经设置了参数.

我会略有不同:

The problem is that when you execute the first one, you set the parameters. When you then try to execute the second, the command exists and has the parameters set already if checkbox1 is checked.

I would do it slightly differently:

private string insertsubscriptionquery = " INSERT INTO Subscription(SubscriptionId,TypeOfMagazine) values (@subid,@TypeOfMagazine)";
private static void InsertIfRequired(bool required, object id, object magazineType)
   {
   using (SqlCommand subcmd = new SqlCommand(insertsubscriptionquery,connstring))
      {
      if (required)
         {
         subcmd.Parameters.AddWithValue("@subid", id);
         subcmd.Parameters.AddWithValue("@TypeOfMagazine", magazineType);
         subcmd.ExecuteNonQuery();
         }
      }
   }

...
                InsertIfRequired(checkbox1.IsChecked, subscripid1, magtype1);
                InsertIfRequired(checkbox2.IsChecked, subscripid2, magtype2);
                InsertIfRequired(checkbox3.IsChecked, subscripid3, magtype3);
...

您可以用适当的类型替换object参数.

You can replace the object parameters with the appropriate type.

更改

if (checkbox2.IsChecked == true)
{
    if(subcmd.Parameters.Contains("@subid"))
    {
       subcmd.Parameters["@subid"].Value=subscripid2;
    }
    else
    {
      subcmd.Parameters.AddWithValue("@subid", subscripid2);
    }
    if(subcmd.Parameters.Contains("@TypeOfMagazine"))
    {
       subcmd.Parameters["@TypeOfMagazine"].Value=magtype2;
    }
    else
    {
      subcmd.Parameters.AddWithValue("@TypeOfMagazine", magtype2);
    }
    subcmd.ExecuteNonQuery();
}

与if (checkbox3.IsChecked == true)
相同
另外，仅当checkbox3.IsChecked == true时，您才增加noofrows吗?.

Same for if (checkbox3.IsChecked == true)

Also you increment noofrows only if checkbox3.IsChecked == true, is this wanted?.

这篇关于添加具有相同名称的SQL参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！