SQL Server Local DB cannot insert into data in new table

Ace_White

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

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.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

SQL Server Local DB cannot insert into data in new table

From Dev

SQL Server insert table data script with new Primary Key

From Dev

Cannot insert data into sql table

From Dev

Update a local db table from remote SQL Server db table

From Dev

Cannot Insert Values into Table (SQL Server)

From Dev

insert from linked server table in SQL server into local table

From Dev

How to Insert xml data into SQL Server table?

From Dev

insert data in sql server from other table

From Dev

Insert data into table in SQL Server from XML

From Dev

Using SSIS, how to insert new data from Excel sheet into my existing SQL Server table

From Dev

Update SQL Server table with local data

From Dev

SQL Server trigger update insert between table in different DB

From Dev

SQL Insert data from 2 table to new table

From Dev

BULK INSERT data from local file to remote sql server

From Dev

Cannot insert text having ' (apostrophe) into SQL Server table

From Dev

BULK INSERT into SQL Server table using pyodbc: cannot find file

From Dev

Select table from Sql server and insert data to Mysql table

From Dev

Insert statement into SQL Server db

From Dev

Insert statement into SQL Server db

From Dev

C# cannot insert data into SQL Server database

From Dev

Cannot Insert data from C# into SQL Server

From Dev

C# cannot insert data into SQL Server database

From Dev

Copy SQL Server Table's Data to a New Table (in Code)

From Dev

populate UI from local db table initially, then if webserivice has new data update table and UI

From Dev

How to insert multiple rows into SQL Server Parallel Data Warehouse table

From Dev

Insert data from text file into a table SQL Server

From Dev

How to insert data to SQL Server table using R?

From Dev

Microsoft SQL Server insert data into large table at every second

From Dev

Insert data from text file into a table SQL Server

Related Related

  1. 1

    SQL Server Local DB cannot insert into data in new table

  2. 2

    SQL Server insert table data script with new Primary Key

  3. 3

    Cannot insert data into sql table

  4. 4

    Update a local db table from remote SQL Server db table

  5. 5

    Cannot Insert Values into Table (SQL Server)

  6. 6

    insert from linked server table in SQL server into local table

  7. 7

    How to Insert xml data into SQL Server table?

  8. 8

    insert data in sql server from other table

  9. 9

    Insert data into table in SQL Server from XML

  10. 10

    Using SSIS, how to insert new data from Excel sheet into my existing SQL Server table

  11. 11

    Update SQL Server table with local data

  12. 12

    SQL Server trigger update insert between table in different DB

  13. 13

    SQL Insert data from 2 table to new table

  14. 14

    BULK INSERT data from local file to remote sql server

  15. 15

    Cannot insert text having ' (apostrophe) into SQL Server table

  16. 16

    BULK INSERT into SQL Server table using pyodbc: cannot find file

  17. 17

    Select table from Sql server and insert data to Mysql table

  18. 18

    Insert statement into SQL Server db

  19. 19

    Insert statement into SQL Server db

  20. 20

    C# cannot insert data into SQL Server database

  21. 21

    Cannot Insert data from C# into SQL Server

  22. 22

    C# cannot insert data into SQL Server database

  23. 23

    Copy SQL Server Table's Data to a New Table (in Code)

  24. 24

    populate UI from local db table initially, then if webserivice has new data update table and UI

  25. 25

    How to insert multiple rows into SQL Server Parallel Data Warehouse table

  26. 26

    Insert data from text file into a table SQL Server

  27. 27

    How to insert data to SQL Server table using R?

  28. 28

    Microsoft SQL Server insert data into large table at every second

  29. 29

    Insert data from text file into a table SQL Server

HotTag

Archive