SQL Insert statement syntax

Question

Basically what I am doing is that upon clicking a button, the program will extract data from a particular row according to what the user chose and place it in a different table by using INSERT. The following is the code.

private void button3_Click(object sender, EventArgs e)
{
        const String connectionString = "Data Source = Vanessa-PC\\SQLEXPRESS; Initial Catalog = IUMDC; Connect Timeout = 15; Integrated Security = true";
        SqlConnection con = new SqlConnection(connectionString);

        //int SituationID1; 
        label24.Show();

        foreach (SitID x in Sittbl)
        {
            if (x.ID == Convert.ToInt16(comboBox1.SelectedItem))
            {
                try
                {
                    con.Open();
                    SqlCommand command = new SqlCommand("SELECT * FROM Situation WHERE SituationID=" + x.SitIDs, con);
                    SqlDataReader dr = command.ExecuteReader();

                    while (dr.Read())
                    {
                        sitid1 = Convert.ToInt32(dr[0]);
                        name1 = dr[4].ToString();
                        incident1 = Convert.ToDateTime(dr[1]);
                        charges1 = dr[5].ToString();
                        nature1 = dr[2].ToString();
                    }

                    con.Close();
                }
                catch (SqlException ex)
                {
                    MessageBox.Show("Database failed to connect" + ex.Message);
                }

                //SituationID = x.SitIDs;
            }
        }

        try
        {                       
           con.Open();
           SqlCommand command1 = new SqlCommand("INSERT INTO CurrentSit VALUES (" + sitid1 + ",'" + incident1.ToString("YYYY-mm-DD") + "', '" + nature1 + "', '" + name1 + "', '" + charges1 + "'", con);
           SqlDataReader dr1 = command1.ExecuteReader();
           con.Close();
        }
        catch (SqlException ex)
        {
            MessageBox.Show("Database failed to connect" + ex.Message);
        }
        //Situation Sit = new Situation();
        //Sit.ShowDialog();
    }

My code fails and says

"Incorrect syntax near row item"

The types of the two tables are the same and I have tried to test this thoroughly!

When doing an INSERT, you're not returning a collection of data - therefore, you should use command1.ExecuteNonQuery(); instead of the .ExecuteReader() where you then just ignore the reader, really.... — marc_s
– marc_s, Commented Jun 15, 2013 at 20:04

Community · Accepted Answer · 2017-05-23 11:55:03Z

19

Looks like your last sql statement is not correct. Maybe the reason is usage of apostrophe in your sql stament, but you should not care about it. I explan in the middle of my answer why you shouldn't care.

SqlCommand command1 = new SqlCommand("INSERT INTO CurrentSit VALUES (" + sitid1 + ",'" + incident1.ToString("YYYY-mm-DD") + "', '" + nature1 + "', '" + name1 + "', '" + charges1 + "'", con);

To find out what is exactly the problem, you can specify column names instead. But I suggest you to use parameterized query not even necessary to specify your column names.

SqlCommand command1 = new SqlCommand("INSERT INTO CurrentSit VALUES(@sitid1, @incident1, @nature1, @name1, @charges1)", con);

command1.Parameters.AddWithValue("@sitid1", sitid1);
command1.Parameters.AddWithValue("@incident1", incident1.ToString("YYYY-mm-DD"));
command1.Parameters.AddWithValue("@nature1", nature1);
command1.Parameters.AddWithValue("@name1", name1);
command1.Parameters.AddWithValue("@charges1", charges1);

command1.ExecuteNonQuery();

You should always use parameterized queries. This kind of codes are open for SQL Injection attacks.

Also as Marc mentioned, there is no point to use ExecuteReader() for this sql statement because it is only INSERT data, doesn't returns any data. Because of that, you only need to use ExecuteNonQuery() in this case.

edited May 23, 2017 at 11:55

CommunityBot

11 silver badge

answered Jun 15, 2013 at 19:39

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NetUser101 Over a year ago

You have no idea of how thankful I am right now! Thank you so much!

Michael Edenfield · Accepted Answer · 2013-06-15 19:40:53Z

4

Your immediate problem is that your INSERT statement has some invalid syntax it in; most likely one of the values you are using to build it has an apostrophe in it that's terminating your string early.

Your bigger problem is that you should never ever build insert statements this way. Besides being highly prone to errors of the sort you're seeing, it's also a classic opening to SQL injection attacks. (Imagine, for example, if a situation's nature foo'); drop table situation; --).

You should be using parameterized queries:

var sql = "INSERT INTO CurrentSit VALUES (@sitid, @incident, @nature, @name, @charges)"
var cmd = new SqlCommand(sql, con);
cmd.Parameters.Add("@Sitid", SqlDbType.Int).Value = sitid1;
// etc.

answered Jun 15, 2013 at 19:40

Michael Edenfield

28.4k6 gold badges90 silver badges121 bronze badges

Comments

Darren · Accepted Answer · 2013-06-15 19:43:29Z

4

Try specifying the columns in your query:

"INSERT INTO CurrentSit (sitid, incident, nature, name1, charges) VALUES (" + sitid1 + ",'" + incident1.ToString("YYYY-mm-DD") + "', '" + nature1 + "', '" + name1 + "', '" + charges1 + "'", con)";

As an additional note, also learn to use paramerterized queries. For instance:

command1.Parameters.AddWithValue("@name1", name1);  
command1.Parameters.AddWithValue("@charges1", charges1);

http://johnhforrest.com/2010/10/parameterized-sql-queries-in-c/

edited Jun 15, 2013 at 19:43

answered Jun 15, 2013 at 19:38

Darren

71.2k24 gold badges141 silver badges146 bronze badges

Collectives™ on Stack Overflow

SQL Insert statement syntax

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related