ExecuteNonQuery() SQLException Incorrect Syntax Near ',' WPF

Question

I am trying to run a SQL command that saves all the items in a column back to a database table. The users enter in this information, and when they close the window the newly entered information should get saved back to a database. I am attempting to do this using a SQL connection and SQL command. The issue is when I enter in the information and close the window, I get an error at the comm.ExecuteNonQuery() that says 'Incorrect syntax near ','.' I am unsure exactly where in my command there is an issue considering I entered my syntax in a online SQL syntax checker and everything came out fine. Here is my code for the Window_Closing event below:

public void Window_Closing(object sender, CancelEventArgs e)
    {
        var checkNoValue = ((DataProperties)gridView.SelectedItem).Check_No;
        var checkDateValue = ((DataProperties)gridView.SelectedItem).Check_Date;

        SqlConnection conn;
        SqlCommand comm;
        string connstring = "server = testserver; database = test; user = someusername; password = somepassword";
        conn = new SqlConnection(connstring);
        conn.Open();
        comm = new SqlCommand("insert into DeductionInfo (" + gridView.Columns["checkNo"] + ", " + gridView.Columns["checkDate"] + ") values (" + checkNoValue + ", " + checkDateValue + ")", conn);

        comm.ExecuteNonQuery();
        MessageBox.Show("Saved");
    }

I am trying to insert the new data into the DeductionInfo datatable located in my database. I think I can sort of guess what the issue is, since I have checkNoValue and checkDateValue set to my gridView.SelectedItem property, however I am unsure how to set that to be each value in those specific columns (I am using Telerik's RadGridView by the way). Keep in mind that Check_No is an integer and Check_Date is a datetime. Am I tackling this all wrong or am I on the right track? This is the first time I've handled SQL queries in WPF and C#, so I'm pretty new to this. Any help would be appreciated :)

Answer 1

You should always use parameters when executing dynamic SQL queries and you should always dispose IDisposables as soon as you are done using them.

Besides, the column names of a table in a database aren't dynamic. You need to know the actual names of these to be able to insert some data into them.

Try this:

public void Window_Closing(object sender, CancelEventArgs e)
{
    var checkNoValue = ((DataProperties)gridView.SelectedItem).Check_No;
    var checkDateValue = ((DataProperties)gridView.SelectedItem).Check_Date;

    SqlConnection conn;
    SqlCommand comm;
    string connstring = "Server = testserver; Database = test; User Id = test; Password = somepassword;"
            conn = new SqlConnection(connstring);
    conn.Open();
    comm = new SqlCommand("insert into DeductionInfo (checkNo, checkDate) values (@checkNoValue, @checkDateValue)", conn);
    comm.Parameters.AddWithValue("@checkNoValue", checkNoValue);
    comm.Parameters.AddWithValue("@checkDateValue", checkDateValue);

    comm.ExecuteNonQuery();
    comm.Dispose();
    conn.Dispose();

    MessageBox.Show("Saved");
}