CODEWITHSUNDEEP
c#sql.netsql-serverinsert
NetUser101
NetUser101

Reputation: 302

SQL Insert statement syntax

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!

Upvotes: 5

Views: 32383

Answers (3)

Soner Gönül
Soner Gönül

Reputation: 98740

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.

Upvotes: 19

Darren
Darren

Reputation: 70718

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/

Upvotes: 4

Michael Edenfield
Michael Edenfield

Reputation: 28338

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.

Upvotes: 4

Related Questions