0

I am trying to use a while loop to loop through some data and then add each one to sql. This is the code I've done so far:

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = SqlConn;
        while (dr.Read())
            {
                cmd.CommandText = "insert into CPC_Coupons(PortalID, CreatedByUser, CouponCode, ProductID, ExpiresOn, Quantity, Title, FirstName, LastName, Company, Address1, City, Region, Zip, Country, WorkPhone, Email, Campaign, Source, Market, Notes) values(@PortalID, @CreatedByUser, @Coupon, @ProductID, @ExpiresOn, @Quantity, @Title, @FirstName, @LastName, @Company, @Address1, @City, @Region, @Zip, @Country, @WorkPhone, @Email, @Campaign, @Source, @Market, @Notes)";
                cmd.Parameters.Add("@PortalID", SqlDbType.NVarChar).Value = 0;
                cmd.Parameters.Add("@Coupon", SqlDbType.NVarChar).Value = dr[0].ToString();
                cmd.Parameters.Add("@CreatedByUser", SqlDbType.NVarChar).Value = "3517";
                cmd.Parameters.Add("@ProductID", SqlDbType.NVarChar).Value = "0";
                cmd.Parameters.Add("@ExpiresOn", SqlDbType.NVarChar).Value = "01/01/2013";
                cmd.Parameters.Add("@Quantity", SqlDbType.NVarChar).Value = "100";
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = "Mr.";
                cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = dr[3].ToString();
                cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = dr[4].ToString();
                cmd.Parameters.Add("@Company", SqlDbType.NVarChar).Value = dr[2].ToString();
                cmd.Parameters.Add("@Address1", SqlDbType.NVarChar).Value = dr[5].ToString();
                cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = dr[6].ToString();
                cmd.Parameters.Add("@Region", SqlDbType.NVarChar).Value = dr[7].ToString();
                cmd.Parameters.Add("@Zip", SqlDbType.NVarChar).Value = dr[8].ToString();
                cmd.Parameters.Add("@Country", SqlDbType.NVarChar).Value = dr[9].ToString();
                cmd.Parameters.Add("@WorkPhone", SqlDbType.NVarChar).Value = dr[10].ToString();
                cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = dr[11].ToString();
                cmd.Parameters.Add("@Campaign", SqlDbType.NVarChar).Value = txtCampaign.Text;
                cmd.Parameters.Add("@Source", SqlDbType.NVarChar).Value = dr[12].ToString();
                cmd.Parameters.Add("@Market", SqlDbType.NVarChar).Value = txtMarketSegment.Text;
                cmd.Parameters.Add("@Notes", SqlDbType.NVarChar).Value = txtNotesToSales.Text;
                cmd.CommandType = CommandType.Text;
                SqlConn.Open();
                cmd.ExecuteNonQuery();
                SqlConn.Close();

            }

But one the second pass through it gets to the cmd.ExecuteNonQuery(); and gives this error: The variable name '@PortalID' has already been declared. Variable names must be unique within a query batch or stored procedure.

Any idea what I am doing wrong?

Thanks!

Improve this question

5 Answers 5

Reset to default
3

Instantiating a new SQLCommand inside the loop would probably fix your issue. Like so:

    while (dr.Read())
    {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = SqlConn;
            cmd.CommandText = "insert into CPC_Coupons(PortalID, CreatedByUser, CouponCode, ProductID, ExpiresOn, Quantity, Title, FirstName, LastName, Company, Address1, City, Region, Zip, Country, WorkPhone, Email, Campaign, Source, Market, Notes) values(@PortalID, @CreatedByUser, @Coupon, @ProductID, @ExpiresOn, @Quantity, @Title, @FirstName, @LastName, @Company, @Address1, @City, @Region, @Zip, @Country, @WorkPhone, @Email, @Campaign, @Source, @Market, @Notes)";
            cmd.Parameters.Add("@PortalID", SqlDbType.NVarChar).Value = 0;
            cmd.Parameters.Add("@Coupon", SqlDbType.NVarChar).Value = dr[0].ToString();
            cmd.Parameters.Add("@CreatedByUser", SqlDbType.NVarChar).Value = "3517";
            cmd.Parameters.Add("@ProductID", SqlDbType.NVarChar).Value = "0";
            cmd.Parameters.Add("@ExpiresOn", SqlDbType.NVarChar).Value = "01/01/2013";
            cmd.Parameters.Add("@Quantity", SqlDbType.NVarChar).Value = "100";
            cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = "Mr.";
            cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = dr[3].ToString();
            cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = dr[4].ToString();
            cmd.Parameters.Add("@Company", SqlDbType.NVarChar).Value = dr[2].ToString();
            cmd.Parameters.Add("@Address1", SqlDbType.NVarChar).Value = dr[5].ToString();
            cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = dr[6].ToString();
            cmd.Parameters.Add("@Region", SqlDbType.NVarChar).Value = dr[7].ToString();
            cmd.Parameters.Add("@Zip", SqlDbType.NVarChar).Value = dr[8].ToString();
            cmd.Parameters.Add("@Country", SqlDbType.NVarChar).Value = dr[9].ToString();
            cmd.Parameters.Add("@WorkPhone", SqlDbType.NVarChar).Value = dr[10].ToString();
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = dr[11].ToString();
            cmd.Parameters.Add("@Campaign", SqlDbType.NVarChar).Value = txtCampaign.Text;
            cmd.Parameters.Add("@Source", SqlDbType.NVarChar).Value = dr[12].ToString();
            cmd.Parameters.Add("@Market", SqlDbType.NVarChar).Value = txtMarketSegment.Text;
            cmd.Parameters.Add("@Notes", SqlDbType.NVarChar).Value = txtNotesToSales.Text;
            cmd.CommandType = CommandType.Text;
            SqlConn.Open();
            cmd.ExecuteNonQuery();
            SqlConn.Close();

        }

But, are you sure you want to do this. Why not insert all your data in one go? -For instance see Yucks answers.

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

Comments

3

You should create these parameters outside the loop with default values and, during each iteration, access to them and set its new parameters arguments.

Summary: don't add them for each iteration, access them!

EDIT:

In order to be more concrete, check this MSDN entry:

Improve this answer

Comments

2

cmd has a scope outside your while loops, so everything you set (i.e. the parameters) persists between iterations.

Change it to this so it will work:

while (dr.Read())
{
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = SqlConn;
    // ... rest as before
}

You'd be better off filling a DataSet and then looping through that, or accumulating its results to be sent once to a stored procedure. For any large result sets this is going to be very inefficient.

Improve this answer

1 Comment

sorry, my mistake - probably didn't give you enough time to edit your answer. please re-edit it so I can re-vote.
1

As you are reusing the command object, you will be adding another set of parameters on the second iteration.

Add the parameters outside the loop, and set the values that changes inside the loop.

Also, you should open the database connection before the loop and close it after.

Improve this answer

Comments

0

You don't need to open and close the connection every time.

You don't need to create a new SqlCommand every time.

SqlCommand cmd = new SqlCommand();
cmd.Connection = SqlConn;
cmd.CommandText = "insert into CPC_Coupons(PortalID, CreatedByUser, CouponCode, ProductID, ExpiresOn, Quantity, Title, FirstName, LastName, Company, Address1, City, Region, Zip, Country, WorkPhone, Email, Campaign, Source, Market, Notes) values(@PortalID, @CreatedByUser, @Coupon, @ProductID, @ExpiresOn, @Quantity, @Title, @FirstName, @LastName, @Company, @Address1, @City, @Region, @Zip, @Country, @WorkPhone, @Email, @Campaign, @Source, @Market, @Notes)";
cmd.CommandType = CommandType.Text;
SqlConn.Open();

while (dr.Read())
{
    cmd.Parameters.Clear()

    cmd.Parameters.Add("@PortalID", SqlDbType.NVarChar).Value = 0;

    ''' other parameters here

    cmd.ExecuteNonQuery();
}

SqlConn.Close();
Improve this answer

1 Comment

It doesn't really matter if you do or not. Pooling the connection is ok.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.