I am facing a problem with SQL Server local db, everything works with insert into command, but when I want to make a new table for some data storing from dataGridView, it makes a new table but I can't insert into table the data, it says syntax error, but I have been using this code in other parts of the program and it works fine, why it doesn't work here?
string T_Name = tempId;
int suma;
string check_t = "IF NOT EXISTS (SELECT * FROM sys.sysobjects WHERE NAME = N'" + T_Name + "' AND xtype=N'U') CREATE TABLE [dbo].[" + T_Name + "](" + "[Drink] [varchar](50) NOT NULL," + "[Price] [INT] NOT NULL," + "[Amount] [INT] NOT NULL," + "[Total] [Int] NOT NULL," + ")";
sql_cn.Open();
try
{
SqlCommand ext = new SqlCommand(check_t,sql_cn);
ext.ExecuteNonQuery();
foreach (DataGridViewRow rw in dataGridView1.Rows)
{
suma = Convert.ToInt16(rw.Cells[1].Value) * Convert.ToInt16(rw.Cells[2].Value);
SqlCommand cmd1 = new SqlCommand("INSERT INTO " + T_Name + " (Drink,Price,Amount,Total) (@Drink,@Price,@Amount,@Total)", sql_cn);
cmd1.Parameters.Add("@Drink", SqlDbType.VarChar).Value = Convert.ToString(rw.Cells[0].Value);
cmd1.Parameters.Add("@Price", SqlDbType.VarChar).Value = Convert.ToInt16(rw.Cells[1].Value);
cmd1.Parameters.Add("@Amount", SqlDbType.VarChar).Value = Convert.ToInt16(rw.Cells[2].Value);
cmd1.Parameters.Add("@Total", SqlDbType.VarChar).Value = (Convert.ToInt16(rw.Cells[2].Value) * Convert.ToInt16(rw.Cells[1].Value));
cmd1.ExecuteNonQuery();
}
MessageBox.Show("Data Stored", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch(Exception err)
{
MessageBox.Show(err.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
sql_cn.Close();
}
I have also tried to use different insert code like :
INSERT INTO "+T_Name+" VALUES('"+Convert.ToString(rw.Cells[0].Value)+"','"+Convert.ToInt16(rw.Cells[1].Value)+"','"+Convert.ToInt16(rw.Cells[2].Value)+"','"+total+"')
Sounds like there are two issues.
It doesn't like the table name (I didn't think they could start with numbers, but I guess they can since that part seems to work fine for you), so surround it with square brackets.
Also, you're missing the VALUES
keyword between the column names and parameters.
SqlCommand cmd1 = new SqlCommand(
"INSERT INTO [" + T_Name + "] (Drink,Price,Amount,Total) VALUES (@Drink,@Price,@Amount,@Total)",
sql_cn);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments