not able to load odbcdatareader to a datatable

Question

I am using an Informix db, and I am trying to get data for a specific item and store it in a datatable.

I checked the following:

1) connection string looks good

2) the connection is able to open

3) I used the same connection string from the web.config on a dataset creating a table adapter and it is able to retrieve the record.

This is the code I am using:

var connectionstring = ConfigurationManager.ConnectionStrings["TestDataTable"].ConnectionString;
OdbcConnection con = new OdbcConnection(connectionstring);
//con.ConnectionString = connectionstring;
if (TxtItem.Text != hold_item)
{
    con.Open();
    OdbcCommand cmd = new OdbcCommand(@"Select t_item,t_idsc,t_upct,
                                        t_item_upc,t_ctyp,t_citg,
                                        t_best,t_disp,t_mold,t_csel 
                                        from informix.tsckcm907
                                        where t_item = " + stitem, con); 
    OdbcDataReader myReader = cmd.ExecuteReader();
    DataTable testdt = new DataTable();
    testdt.Load(myReader);
    foreach (DataRow row in testdt.Rows)
    {
       lbldesc.Text = row["t_idsc"].ToString();
       Spanish_Item();
       {
           DropDownList2.SelectedIndex = 1;
           object stlanguage = 1;
           hold_language = Convert.ToString(stlanguage);
           TxtBestBefore.Text = row["t_best"].ToString();
           holdbest = Convert.ToInt16(TxtBestBefore.Text);
       }
   }
   myReader.Close();
   myReader.Dispose();
   cmd.Dispose();
   con.Close();
   con.Dispose();
}

in debug mode my error occurs at the OdbcDataReader line: error message:

An exception of type 'System.Data.Odbc.OdbcException' 
occurred in System.Data.dll but was not handled in user code

Additional information: ERROR [42000] [Informix]
[Informix ODBC Driver][Informix]A syntax error has 
occurred.

what is the actual value of _stitem_ You use string concatenation, very bad on many level including the presence of a single quote in your value. And if t_item is a text field then you need quotes around the variable — Steve, Feb 08 '16 at 16:21

score 0 · Answer 1 · answered Feb 17 '16 at 11:08

If your Informix ODBC driver says: "A syntax error has occurred" then you have to check your SQL statement:

"Select t_item,...  from informix.tsckcm907 where t_item = " + stitem

I think that something is wrong with stitem. We don't know what type and value it is, but if its type is some kind of string or date then it may be in the wrong form. Easiest way is to extract full SQL statement (simply print it before execution) and use it with some database editor (for example db_access from Informix). Then make it work in SQL editor and transform stitem variable into acceptable form (add quotes, escape internal quotes, escape special characters etc.)

I also recommend use of PreparedStatement that separates your query from data. This way you do not need to worry about stitem form. No quotes, no escaping, just place holder in query string and value added separately.

I don't use C# but I see that C# can work with preapred statements with unnamed parameters:

cmd.CommandText = "SELECT ... FROM ... WHERE t_item = ?";
cmd.Parameters.Add("@t_item", ObdcType.VarChar, 200).Value = t_item;

or with named parameters:

cmd.CommandText = "SELECT ... FROM ... WHERE t_item = @t_item";
cmd.Parameters.Add("@t_item", ObdcType.VarChar, 200).Value = t_item;

I use unnamed parameters from ODBC so Informix driver can work with such parameters but you will have to check it yourself with C#.

not able to load odbcdatareader to a datatable

1 Answers1