How to insert multiple records into database with SqlParameter in C#

Question

I am trying to run the following code:

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

    StringBuilder sqlStr = new StringBuilder("INSERT into Customers values ( @name, @address, @city, @state)");

    SqlCommand cmd = new SqlCommand(sqlStr.ToString(), conn);
    cmd.Parameters.Add(new SqlParameter("@name", "John Smith"));
    cmd.Parameters.Add(new SqlParameter("@address", "123 Main St."));
    cmd.Parameters.Add(new SqlParameter("@city", "Detroit"));
    cmd.Parameters.Add(new SqlParameter("@state", "Michigan"));

    cmd.ExecuteReader();

    cmd.Parameters["@name"].Value = "William Jones";
    cmd.Parameters["@address"].Value = "500 Blanchard Ave";
    cmd.Parameters["@city"].Value = "Chicago";
    cmd.Parameters["@state"].Value = "Illinois";

    cmd.ExecuteReader();
}

However, I am getting an error. I have tried multiple other ways, but nothing seems to work. What is the proper way to insert multiple records using SqlParameters?

@code4life: that's definitely ***not*** the problem ... SQL Server parameter typically do have a leading `@` — marc_s, Apr 05 '13 at 05:33
Ugh, my bad - I should rephrase. How about removing the `@` in the `cmd.Parameters["@name"].Value`? — code4life, Apr 05 '13 at 12:34

BizApps · Answer 1 · 2013-04-05T03:09:42.570

First of all cmd.ExecuteReader(); is used on data retrieval and not not the proper way in doing insert.

Use

cmd.ExecuteNonQuery();

Change your code to this:

Create a method that has 4 parameter where you pass name,address,city and state that includes your insert code

private void InsertCustomers(string name,string address,string city,string state)
{
    using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
                    StringBuilder sqlStr = new StringBuilder("INSERT into Customers values ( @name, @address, @city, @state)");
                    SqlCommand cmd = new SqlCommand(sqlStr.ToString(), conn);
                    cmd.Parameters.Add(new SqlParameter("@name", name));
                    cmd.Parameters.Add(new SqlParameter("@address", address));
                    cmd.Parameters.Add(new SqlParameter("@city", city));
                    cmd.Parameters.Add(new SqlParameter("@state", state));
                    cmd.ExecuteNonQuery();

                }

}

then on your click or any events that you want to insert new customer just do

InsertCustomers("A","B","C","D");

InsertCustomers("E","F","G","H");

And maybe you need to start learning the basic first on ADO.NET

The C# Station ADO.NET Tutorial

After completing the tutorial, you will now be familiarize on ado.net.

Best Regards

Oh wow, I didn't even notice that. I've learned all the ADO basics, but thanks for the input. — girlcode, Apr 05 '13 at 03:09
Welcome...please accept as answer if my post help you..thanks — BizApps, Apr 05 '13 at 03:54
The `using(SqlConnection ...) { ... }` approach is good and advisable - but you should *also* use this on `SqlCommand`! — marc_s, Apr 05 '13 at 05:01

score 1 · Answer 2 · answered Apr 05 '13 at 10:38

1

Alter your code

put AddWithValue instead of Add

cmd.Parameters.AddWithValue(new SqlParameter("name", "John Smith"));

and ExecuteNonQuery() instead of ExecuteReader();

cmd.ExecuteNonQuery();

answered Apr 05 '13 at 10:38

Karthik AMR

1,694
21
29

score 0 · Answer 3 · answered Apr 05 '13 at 03:06

using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                StringBuilder sqlStr = new StringBuilder("INSERT into Customers values ( @name, @address, @city, @state)");
                SqlCommand cmd = new SqlCommand(sqlStr.ToString(), conn);
                cmd.Parameters.Add(new SqlParameter("name", "John Smith"));
                cmd.Parameters.Add(new SqlParameter("address", "123 Main St."));
                cmd.Parameters.Add(new SqlParameter("city", "Detroit"));
                cmd.Parameters.Add(new SqlParameter("state", "Michigan"));
                int rowsAffected = cmd.ExecuteNonQuery();

                cmd.Parameters["name"].Value = "William Jones";
                cmd.Parameters["address"].Value = "500 Blanchard Ave";
                cmd.Parameters["city"].Value = "Chicago";
                cmd.Parameters["state"].Value = "Illinois";
                rowsAffected = cmd.ExecuteNonQuery();
            }

score 0 · Answer 4 · answered Apr 05 '13 at 12:40

I used to just keep the reference to the SqlParameter object and reuse it, like this:

var paramName = new SqlParameter("@name", "John Smith");
var paramAddress = new SqlParameter("@address", "123 Main St.");
var paramCity = new SqlParameter("@city", "Detroit");
var paramState = new SqlParameter("@state", "Michigan");

SqlCommand cmd = new SqlCommand(sqlStr.ToString(), conn);
cmd.Parameters.AddRange(new[]{paramName, paramAddress, paramCity, paramState});
cmd.ExecuteReader();

paramName.Value = "William Jones";
paramAddress.Value = "500 Blanchard Ave";
paramCity.Value = "Chicago";
paramState.Value = "Illinois";

cmd.ExecuteReader();

This should resolve your problems now. As an added benefit, this also eliminates the collection lookup that you were doing:

cmd.Parameters["blah"]

And replaces it with direct access to the parameter you need. I think this should save a few cycles in the clock (not much though, lol).

How to insert multiple records into database with SqlParameter in C#

4 Answers4