SqlCommand Parameter error : "The variable name has already been declared"

Question

I would like to insert all the items row by row after clicking Submit button but somehow my variable '@DrinkName' is same and got errors. Kindly see my code and screenshot for better understanding.

Category.aspx.cs

protected void BtnSubmit_Click(object sender, EventArgs e)
{
        string connString = "sql database";
        string insertCommand = "INSERT INTO tbDrinks ( DrinkName, DateOfOrder, Qty, UserName, UserCompany) " +
           "values(@DrinkName, @DateOfOrder, @Qty, @UserName, @UserCompany)"; //for testing purpose

        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();

            using (SqlCommand sqlcmd = new SqlCommand(insertCommand, conn))
            {                         
                    sqlcmd.Parameters.AddWithValue("@DrinkName", lblCoffee.Text);
                    sqlcmd.Parameters.AddWithValue("@DrinkName", lblEnglishTea.Text);
                    sqlcmd.Parameters.AddWithValue("@DateOfOrder", DateTime.Today);
                    sqlcmd.Parameters.AddWithValue("@Qty", ddlCoffee.SelectedValue);
                    sqlcmd.Parameters.AddWithValue("@Qty", ddlEnglishTea.SelectedValue);
                    sqlcmd.Parameters.AddWithValue("@UserName", txtUserName.Text);
                    sqlcmd.Parameters.AddWithValue("@UserCompany", txtCompanyName.Text);

                    sqlcmd.ExecuteNonQuery();
            }              
        }
}

This is Category.aspx which I created with different dropdown list for each item.

<table  class="auto-style6">
    <tr>
        <td class="auto-style9">
            <strong>
                <asp:Label ID="lblCompany" runat="server" Text="Company Name : "></asp:Label>
            </strong>
        </td>
        <td class="auto-style10">
            <strong>
                <asp:TextBox ID="txtCompanyName" runat="server"></asp:TextBox>
            </strong>
        </td>           
    </tr>
</table> 

 <table id="tbHotDrinks" class="auto-style3" border="1" bordercolor="#1FC3F3" runat="server">

    <tr>
        <td>
            <asp:Label ID="lblCoffee" runat="server" Text="Coffee"></asp:Label>
        </td>
        <td class="auto-style2">
            <asp:Image ID="coffee" runat="server" Height="76px" ImageUrl="~/images/coffee.gif" Width="99px" ImageAlign="TextTop" />
        </td>
        <td class="auto-style11">
            <asp:DropDownList ID="ddlCoffee" runat="server">
                <asp:ListItem>Qty</asp:ListItem>
                <asp:ListItem>1</asp:ListItem>
                <asp:ListItem>2</asp:ListItem>
                <asp:ListItem>3</asp:ListItem>
                <asp:ListItem>4</asp:ListItem>
                <asp:ListItem>5</asp:ListItem>
                <asp:ListItem>6</asp:ListItem>
                <asp:ListItem>7</asp:ListItem>
                <asp:ListItem>8</asp:ListItem>
                <asp:ListItem>9</asp:ListItem>
            </asp:DropDownList>
        </td>

    </tr>
    <tr>
        <td>
            <asp:Label ID="lblEnglishTea" runat="server" Text="English Tea"></asp:Label>
        </td>
        <td class="auto-style2">
            <asp:Image ID="Image1" runat="server" Height="76px" ImageUrl="~/images/EnglishTea.gif" Width="99px" ImageAlign="TextTop" />
        </td>
        <td class="auto-style11">
            <asp:DropDownList ID="ddlEnglishTea" runat="server">
                <asp:ListItem>Qty</asp:ListItem>
                <asp:ListItem>1</asp:ListItem>
                <asp:ListItem>2</asp:ListItem>
                <asp:ListItem>3</asp:ListItem>
                <asp:ListItem>4</asp:ListItem>
                <asp:ListItem>5</asp:ListItem>
                <asp:ListItem>6</asp:ListItem>
                <asp:ListItem>7</asp:ListItem>
                <asp:ListItem>8</asp:ListItem>
                <asp:ListItem>9</asp:ListItem>
            </asp:DropDownList>
        </td>

    </tr>
</table>

<asp:Button ID="BtnSubmit" runat="server" OnClick="BtnSubmit_Click" Text="Submit" />

Below here is my example database screenshot that I would like to add row by row.

The code I have tried in Category.aspx.cs is below. Somehow it's still not correct yet. I just want simplest way to do it.

    protected void BtnSubmit_Click(object sender, EventArgs e)
    {
        string connString = "Data Source";

        string insertCommand = "INSERT INTO tbDrinks ( DrinkName, DateOfOrder, Qty, UserName, UserCompany) " +
                               "VALUES (@DrinkName, @DateOfOrder, @Qty, @UserName, @UserCompany)";

        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();

            using (SqlCommand sqlcmd = new SqlCommand(insertCommand, conn))
            {                         
                    sqlcmd.Parameters.AddWithValue("@DrinkName", lblCoffee.Text);
                  //  sqlcmd.Parameters.AddWithValue("@DrinkName", lblEnglishTea.Text);
                    sqlcmd.Parameters.AddWithValue("@DateOfOrder", DateTime.Today);
                    sqlcmd.Parameters.AddWithValue("@Qty", ddlCoffee.SelectedValue);
                  //  sqlcmd.Parameters.AddWithValue("@Qty", ddlEnglishTea.SelectedValue);
                    sqlcmd.Parameters.AddWithValue("@UserName", txtUserName.Text);
                    sqlcmd.Parameters.AddWithValue("@UserCompany", txtCompanyName.Text);

                    sqlcmd.ExecuteNonQuery();
            }

            using (SqlCommand sqlcmd1 = new SqlCommand(insertCommand, conn))
            {
                // sqlcmd.Parameters.AddWithValue("@DrinkName", lblCoffee.Text);
                sqlcmd1.Parameters.AddWithValue("@DrinkName", lblEnglishTea.Text);
                sqlcmd1.Parameters.AddWithValue("@DateOfOrder", DateTime.Today);
                // sqlcmd.Parameters.AddWithValue("@Qty", ddlCoffee.SelectedValue);
                sqlcmd1.Parameters.AddWithValue("@Qty", ddlEnglishTea.SelectedValue);
                sqlcmd1.Parameters.AddWithValue("@UserName", txtUserName.Text);
                sqlcmd1.Parameters.AddWithValue("@UserCompany", txtCompanyName.Text);

                sqlcmd1.ExecuteNonQuery();
            }
        }
    }

Please see the coded I have tried Category.aspx.cs. It's got no error but I need simplest way to do it. — java404
– java404, Commented Nov 8, 2019 at 2:46
You should parameters to command using sqlcmd.Parameters.Add method. Then assign values to parameters using sqlcmd.Parameters["@DrinkName"].Value = lblCoffee.Text. Then execute the command. Then again set the parameter values and again execute the command. This way you don't have to create two SqlCommand. Only one SqlCommand object needed. — Chetan
– Chetan, Commented Nov 8, 2019 at 2:53

Muhammad Aftab · Accepted Answer · 2019-11-08 06:30:33Z

2

You have the error because you have declared the Sql Parameter twice with the same name:

sqlcmd.Parameters.AddWithValue("@Qty", ddlCoffee.SelectedValue);
sqlcmd.Parameters.AddWithValue("@DrinkName", lblEnglishTea.Text);

try as below:

protected void BtnSubmit_Click(object sender, EventArgs e) 
{
 string connString = "sql database";
 string insertCommand = "INSERT INTO tbDrinks ( DrinkName, DateOfOrder, Qty, UserName, UserCompany) " +
  "values(@DrinkName, @DateOfOrder, @Qty, @UserName, @UserCompany)"; //for testing purpose

 using(SqlConnection conn = new SqlConnection(connString)) 
 {
  conn.Open();
  using(SqlCommand sqlcmd = new SqlCommand(insertCommand, conn)) {

   sqlcmd.Parameters.AddWithValue("@DrinkName", lblCoffee.Text);
   // sqlcmd.Parameters.AddWithValue("@DrinkName", lblEnglishTea.Text);
   sqlcmd.Parameters.AddWithValue("@DateOfOrder", DateTime.Today);
   sqlcmd.Parameters.AddWithValue("@Qty", ddlCoffee.SelectedValue);
   // sqlcmd.Parameters.AddWithValue("@Qty", ddlEnglishTea.SelectedValue);
   sqlcmd.Parameters.AddWithValue("@UserName", txtUserName.Text);
   sqlcmd.Parameters.AddWithValue("@UserCompany", txtCompanyName.Text);
   sqlcmd.ExecuteNonQuery();
  }
 }
}

edited Nov 8, 2019 at 6:30

answered Nov 8, 2019 at 5:17

Muhammad Aftab

1,1188 silver badges19 bronze badges

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

1 Comment

Hans Keﬆing Over a year ago

DrinkName is also used twice

java404 · Accepted Answer · 2019-11-08 09:21:50Z

First you are adding the same parameters twice. That need to be corrected by updating the value of that particular parameter.

Secondly, rather than creating two SqlCommand Instances, you can just update the value of parameter for the second insert, and run ExecuteNonQuery() the second time, with the updated value. You can do it like this :

 protected void BtnSubmit_Click (object sender, EventArgs e) {
     string connString = "Data Source";
     string insertCommand = "INSERT INTO tbDrinks ( DrinkName, DateOfOrder, Qty, UserName, UserCompany) " +
         "values(@DrinkName, @DateOfOrder, @Qty, @UserName, @UserCompany)";

     using (SqlConnection conn = new SqlConnection (connString)) {
         conn.Open();
         using (SqlCommand sqlcmd = new SqlCommand (insertCommand, conn)) {

             sqlcmd.Parameters.AddWithValue("@DrinkName", lblCoffee.Text);
             sqlcmd.Parameters.AddWithValue("@DateOfOrder", DateTime.Today);
             sqlcmd.Parameters.AddWithValue("@Qty", ddlCoffee.SelectedValue);
             sqlcmd.Parameters.AddWithValue("@UserName", txtUserName.Text);
             sqlcmd.Parameters.AddWithValue("@UserCompany", txtCompanyName.Text);
             sqlcmd.ExecuteNonQuery();

             // update the values and insert with updated values.   
             sqlcmd.Parameters["@DrinkName"].Value = lblEnglishTea.Text; 
             sqlcmd.Parameters["@Qty"].Value = ddlEnglishTea.SelectedValue;
             sqlcmd.ExecuteNonQuery();

         }
     }
 }

To make this a better answer, explain what you changed and why

Fabio · Accepted Answer · 2019-11-08 06:42:10Z

If you want simplify code to save two different purchases and make it easier to maintain,
create a class which will insert a record based on given arguments and reuse this class for adding multiple rows.
C# is object-oriented language, so we can benefit of it.

// Represents data which can be different for different drinks
public class DrinkSelection
{
    public string Name { get; set; }
    public int Qty { get; set; }
}

public class DrinkPurchase
{
    private readonly string _userName;
    private readonly string _userCompany;

    public DrinkPurchase(string userName, string userCompany)
    {
        _userName = userName;
        _userCompany = userCompany;
    }

    public void Save(DrinkSelection drink)
    {
        var insert = @"
            INSERT INTO tbDrinks (DrinkName, DateOfOrder, Qty, UserName, UserCompany)
            VALUES (@DrinkName, @DateOfOrder, @Qty, @UserName, @UserCompany)
        ";

        var parameters = new[]
        {
          new SqlParameter("@DrinkName", SqlDbType.Varchar) { Value = drink.Name },
          new SqlParameter("@DateOfOrder", SqlDbType.DateTime) { Value = DateTime.Today },
          new SqlParameter("@Qty", SqlDbType.Varchar) { Value = drink.Qty },
          new SqlParameter("@UserName", SqlDbType.Varchar) { Value = _userName },
          new SqlParameter("@UserCompany", SqlDbType.Varchar) { Value = _userCompany }
        };

        using (var connection = new SqlConnection(connectionString)) 
        using (var command = connection.CreateCommand())
        {
            command.CommandText = insert;
            command.Parameters.AddRange(parameters);

            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

Usage

protected void BtnSubmit_Click(object sender, EventArgs e)
{
    var purchase = new DrinkPurchase(txtUserName.Text, txtCompanyName.Text);

    var coffee = new DrinkSelection 
    { 
        Name = lblCoffee.Text, 
        Qty = ddlCoffee.SelectedValue 
    };
    var tea = new DrinkSelection 
    { 
        Name = lblEnglishTea.Text, 
        Qty = ddlEnglishTea.SelectedValue 
    };

    purchase.Save(coffee);
    purchase.Save(tea);
}

By extracting logic into dedicated class, you fee up business logic from dependencies on web framework.

Collectives™ on Stack Overflow

SqlCommand Parameter error : "The variable name has already been declared"

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related