SQL Server Local DB cannot insert into data in new table

Question

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+"')

Sorry i have changed the variable names from my language to English suma is for total I have tried to use it in other versions of INSERT INTO — Ace_White, May 30 '15 at 03:06

score 2 · Accepted Answer · answered May 30 '15 at 03:19

2

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);

answered May 30 '15 at 03:19

Grant Winney

65,241
13
115
165

Tanks for fixing my stupid mistake with Values I have must deleted accidentally when i was changing insert into code type... But the problem was thia brackets [ ] around the table name, maybe you can drop me a link or explain me why they are necessary, when you use string variable insted of plain text? – Ace_White May 30 '15 at 03:27

SQL Server Local DB cannot insert into data in new table

1 Answers1