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!

Answer 1

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.