OleDbDataAdapter string concatenation

Question

I have the following code, which does a simple SELECT query on a database.If i pass the whole query through the "group" string, the query is OK.But if i try to concatenate the string (only send the group ID) as it follows I get "Syntax error in query expression 'ID='group_data' " -note the three '

I took a peak on MSDN but I couldn't find anything related to it.

public void auth_st(string group)
    {
        conexiuneBD.Open();
        DataSet ds = new DataSet();
        OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT Notif FROM teams WHERE ID='"+group+"'", conexiuneBD);
        adapter.Fill(ds);
        conexiuneBD.Close();

        DataTable dt = ds.Tables[0];
        foreach (DataRow dr in dt.Rows)
        {
            listBoxCerer.Items.Add(dr["Notif"].ToString());

        }

    }

Any help would be really appreciated.Should I use command instead?

_Well_, sounds like `ID` is an numerical type, not character. Did you try without single quotes? — Soner Gönül, Jun 16 '14 at 06:46
Concatenate parameters is definitely not the right way to perform queries. Using parameters is better: please have a look to this other [Question](http://stackoverflow.com/questions/2675610/how-to-update-a-table-using-oledb-parameters) to get an example. — Larry, Jun 16 '14 at 06:48
What is the value of `group` exactly? I don't see any syntax error in your query. — Soner Gönül, Jun 16 '14 at 06:52
Group contains a string.If group="331C"m the error shows "Syntax error in querry expression 'ID='331C' " — user3605321, Jun 16 '14 at 06:56
What is the `'` before your `ID` column? It it not there in your question. — Soner Gönül, Jun 16 '14 at 07:00
I don't know, that's why I was pointing that out.There should be only two, yet the error shows 3 of them, although I am 100% sure (I rechecked the DB and code) that there is no extra ' — user3605321, Jun 16 '14 at 07:06

Larry · Accepted Answer · 2014-06-16T08:41:18.290

1

String concatenation is very bad, you should be using OleDB parameters this way:

public void auth_st(string group)
{
    conexiuneBD.Open();
    DataSet ds = new DataSet();
    OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT Notif FROM teams WHERE ID=?", conexiuneBD);
    adapter.SelectCommand.Parameters.AddWithValue("p1", group);
    adapter.Fill(ds);
    conexiuneBD.Close();

    DataTable dt = ds.Tables[0];
    foreach (DataRow dr in dt.Rows)
    {
        listBoxCerer.Items.Add(dr["Notif"].ToString());
    }
}

edited Jun 16 '14 at 08:41

answered Jun 16 '14 at 07:42

Larry

17,605
9
77
106

1

Thank you, I finally got it working thanks to your help! I will keep in mind your advice, you are a LIFE SAVER! I sincerely wish you an aweasome day! – user3605321 Jun 16 '14 at 08:04
Thank you for the accept :) I added a link (in "is very bad") that explains SQL injection and why concatenation of SQL parameter is bad. – Larry Jun 16 '14 at 08:42

score 0 · Answer 2 · 2014-06-16T07:01:27.833

0

public void auth_st(string group)
{
    string query="SELECT Notif FROM teams WHERE ID='{0}'";
    conexiuneBD.Open();
    DataSet ds = new DataSet();
    OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format(query,group), conexiuneBD);
    adapter.Fill(ds);
    conexiuneBD.Close();

    DataTable dt = ds.Tables[0];
    foreach (DataRow dr in dt.Rows)
    {
        listBoxCerer.Items.Add(dr["Notif"].ToString());

    }

}

edited Jun 16 '14 at 07:01

answered Jun 16 '14 at 06:48

If group="331C" I get "Syntax error in querry expression 'ID='331C' " – user3605321 Jun 16 '14 at 06:55
@user3605321 Are you sure you get error with `SELECT Notif FROM teams WHERE ID='331C'` query? – Soner Gönül Jun 16 '14 at 06:57
ok take your query text in one variable and pass that to oledbdataadapter() and DEBUG code and paste query variable text here. – Jun 16 '14 at 07:00
I tried that.I only get a message with the error and only this in the debug windows A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Windows.Forms.dll The thread 0xfb0 has exited with code 259 (0x103). The thread 0xebc has exited with code 259 (0x103). – user3605321 Jun 16 '14 at 07:12

OleDbDataAdapter string concatenation

2 Answers2