How do I add multiple rows in a table?

Question

string date = p_text_data.Text;
string sql = @"INSERT INTO Warehouse (title,count,price,date) ";
try
{
    using (SqlConnection connection = ConnectToDataBase.GetConnection())
    {
        SqlCommand command = new SqlCommand(sql, connection);
        for (int i = 0; i < mdc.Count; i++)
        {
            sql += "SELECT @title" + i + ",@count" + i + ",@price" + i + ",@date" + i + " ";
            command.Parameters.AddWithValue("@title" + i, mdc[i].Title);
            command.Parameters.AddWithValue("@count" + i, mdc[i].Count);
            command.Parameters.AddWithValue("@price" + i, mdc[i].Price);
            command.Parameters.AddWithValue("@date" + i, Conver_Data(date));
            if (mdc.Count-1 != i)
                sql += "UNION ALL ";
        }
        sql += " ;";
        connection.Open();// *sql
        string id_Partner = command.ExecuteScalar().ToString();
    }
}
catch (SqlException se)
{
    MessageBox.Show(se.Message);
}

*sql = "INSERT INTO Warehouse (title,count,price,date) SELECT @title0,@count0,@price0,@date0 UNION ALL SELECT @title1,@count1,@price1,@date1 ;"

Then he flies an exception

Incorrect syntax near ')'

clarify - count - int, price - double, date - Date

what am I doing wrong?

edit: Table

CREATE TABLE [dbo].[Warehouse] (
  [ID] int IDENTITY(1, 1) NOT NULL,
  [title] char(30) COLLATE Cyrillic_General_CI_AS NULL,
  [count] int NULL,
  [price] float NULL,
  [date] datetime NULL,
  CONSTRAINT [PK__Warehous__3214EC277F60ED59] PRIMARY KEY CLUSTERED ([ID])
)
ON [PRIMARY]
GO

I'm used SQL Server 2008

What version of SQL Server are you using?

NakedBrunch
– NakedBrunch

2010-09-30 13:55:37 +00:00
Commented Sep 30, 2010 at 13:55 — NakedBrunch
– NakedBrunch, Commented Sep 30, 2010 at 13:55
The query works fine for me in SQL Server 2008.

Timwi
– Timwi

2010-09-30 14:13:23 +00:00
Commented Sep 30, 2010 at 14:13 — Timwi
– Timwi, Commented Sep 30, 2010 at 14:13

Jeffrey L Whitledge · Accepted Answer · 2010-09-30 15:04:14Z

The problem is that you are never updating the SQL command text of the command object with anything after the ")". Just because you update the sql variable doesn't mean that SqlCommand object is going to see that update.

(Another problem that you will run into is that you are not returning anything from this query, so you won't be able to use ExecuteScalar().)

Try this instead:

string date = p_text_data.Text; 
string sql = @"INSERT INTO Warehouse (title,count,price,date) "; 
try 
{ 
    using (SqlConnection connection = ConnectToDataBase.GetConnection()) 
    { 
        SqlCommand command = new SqlCommand(sql, connection); 
        for (int i = 0; i < mdc.Count; i++) 
        { 
            sql += "SELECT @title" + i + ",@count" + i + ",@price" + i + ",@date" + i + " "; 
            command.Parameters.AddWithValue("@title" + i, mdc[i].Title); 
            command.Parameters.AddWithValue("@count" + i, mdc[i].Count); 
            command.Parameters.AddWithValue("@price" + i, mdc[i].Price); 
            command.Parameters.AddWithValue("@date" + i, Conver_Data(date)); 
            if (mdc.Count-1 != i) 
                sql += "UNION ALL "; 
        } 
        sql += " ;"; 
        command.CommandText = sql;    //  Set your SQL Command to the whole statement.
        connection.Open();// *sql 
        command.ExecuteNonQuery();    //  Execute a query with no return value.
    } 
} 
catch (SqlException se) 
{ 
    MessageBox.Show(se.Message); 
}

Chris Diver · Accepted Answer · 2010-09-30 15:19:43Z

0

You are trying to use the String sql as a reference type, although it is a reference type, it is a special case where it acts like a value type. The line

sql += "SELECT @title" + i + ",@count" + i + ",@price" + i + ",@date" + i + " ";

appears to be appending to sql but in fact it is creating a new string, stored in a different location in memory to String that you passed to the SqlCommand.

If you array is large you may see a performance benefit by using the StringBuilder class to build your string then assign it to your SqlCommand object after it has been built.

Either way you need to assign it to SqlCommand.CommandText after you have the complete SQL in your sql variable.

answered Sep 30, 2010 at 15:19

Chris Diver

19.9k4 gold badges50 silver badges58 bronze badges

Collectives™ on Stack Overflow

How do I add multiple rows in a table?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related