DataAdapter.Fill syntax error

Question

So,

Trying to write a very simple method to update a single column in a database. I keep getting a runtime error of "Syntax Error" near the commented line below

public void SaveStatus(string id, string step)
    {
        // assuming that there is only one matching student ID
        connect = new SqlConnection(connectionString);
        connect.Open();
        dataSet = new DataSet();
        string command = "SELECT * FROM tblSubmissions WHERE Id = " + id;
        dataAdapter = new SqlDataAdapter(command, connect);

        dataAdapter.Fill(dataSet, "tblSubmissions");  // syntax error near here

        dataSet.Tables[0].Rows[0]["StatusID"] = step;

        dataAdapter.Update(dataSet, "tblSubmissions");
        dataAdapter.Dispose();
        connect.Close();
        connect.Dispose();

    }

Hoping someone can point out the obvious problem I'm missing

Post the exact error. Runtime error != syntax error, so it's in the SQL. Post the CREATE TABLE script. — H H, Oct 18 '13 at 18:16
Debug and check what your `Id` variable is? You should be using a parameterised query rather than passing a string. — Kaf, Oct 18 '13 at 18:17
Error is: System.Data.SqlClient.SqlException: Incorrect syntax near 'bba6323c0' — deadEddie, Oct 18 '13 at 18:18
Remove second parameter (`"tblSubmissions"`) of `Fill()` method and check. — Kaf, Oct 18 '13 at 18:20

Marek Grzenkowicz · Accepted Answer · 2013-10-19T14:55:29.603

3

The query should be "SELECT * FROM tblSubmissions WHERE Id = 'id_value' - you're missing the quotes around the id value.

Use a parametrised query instead of string concatenation to fix your problem and get rid of the SQL injection issue:

SqlCommand cmd = new SqlCommand("SELECT * FROM tblSubmissions WHERE Id = @id" , connect);
cmd.Parameters.Add("@id", SqlDbType.UniqueIdentifier);
cmd.Parameters["@id"].Value = id;

edited Oct 19 '13 at 14:55

answered Oct 18 '13 at 18:21

Marek Grzenkowicz

17,024
9
81
111

Thanks Marek. So, I have since realized that the data type in the table I'm saving this value to is a Guid, so even though you gave me the best possible answer to the question I asked, I'm a little stuck on that (since there isn't a SqlDbType.Guid) – deadEddie Oct 18 '13 at 23:21
@deadEddie I that case I think you need to use `SqlDbType.UniqueIdentifier`. – Marek Grzenkowicz Oct 19 '13 at 14:55

DataAdapter.Fill syntax error

1 Answers1