Informix db connection and loading to datatable is not working

Question

i am trying to connect to an informix database in my web application and retrieve the data based on an item code entered by the user and store it in a datatable later i want to take the data from the datatable and display it on my textboxes for the item selected. its an odbc connection DRIVER={IBM INFORMIX ODBC DRIVER} in my connection string

    if (DropDownList4.Text == "***.**.**.**" || DropDownList4.Text == "***.**.**.**" || DropDownList4.Text == "***.**.**.**")
{
//string abilene = ConfigurationManager.ConnectionStrings["Abeliene"].ConnectionString.ToString();
 IfxConnection conn = new IfxConnection("User Id=****;Password=****;" +"Host=abkrisc1;Server=abkrisc1;" +"Service=1719;DB=circa119;");
     DataTable Abilene = new DataTable();
     Abilene.Columns.Add("item");
     Abilene.Columns.Add("desc");
     Abilene.Columns.Add("upc");
     Abilene.Columns.Add("itemupc");
     Abilene.Columns.Add("ctyp");
     Abilene.Columns.Add("citg");
     Abilene.Columns.Add("best");
     Abilene.Columns.Add("disp");
     Abilene.Columns.Add("mold");
     Abilene.Columns.Add("csel");
IfxCommand cmd;
cmd = new IfxCommand("Select t_item,t_idsc,t_upct,t_item_upc,t_ctyp,t_citg,t_best,t_disp,t_mold,t_csel from tsckcm907 where t_item = @item'");
     conn.Open();
  cmd.Parameters.Add("@item", IfxType.VarChar).Value = TxtItem.Text;
          try
              {
          IfxDataReader myreader = cmd.ExecuteReader();
          Abilene.Load(myreader);
          Response.Write(Abilene.Columns);
          con = true;     
              }
         catch (Exception ex)
              {
               throw ex;
               con = false;
              }
        if (con == true)
                {
                    while (Abilene.Rows.Count > 0)
                    {
                        TxtItem.Text = Abilene.Rows[0]["item"].ToString();
                        lbldesc.Text = Abilene.Rows[1]["desc"].ToString();
                    }
                }

The error i get when i debugged was at conn.open() -> ERROR [HY000] [Informix .NET provider][Informix]Server abkrisc1 is not listed as a dbserver name in sqlhosts.

i have done a similar scenario with sql database, but informix i have never worked with. Please if anyone could help me with this code that would be awesome, even this code that i used was found from ibm website.

Answer 1

Check your informix server configuration (the problem might be in your sqlhosts file, as the error states), it should contain server with the name abkrisc1, listening at port 1719.

If that's ok, then check your DSN configuration (this may vary depending on your operating system - check out this page for details).

Answer 2

Documentation in internet and the error message are misleading. I had the same error and meanwhile I found out how to solve it.

When you install the Informix Client SDK for Windows and use the ODBC driver for Informix (with or without .NET) you must pass the following parameters:

1. Host
2. Server
3. Service or Port
4. Protocol
5. User ID
6. Password

All these parameters are mandatory. The database is optional.

In your connection string the protocol is missing. In the case that any parameter is missing the OBDC driver searches the server in the registry under

32 bit driver: HKLM\Software\Wow6432Node\Informix\SQLHOSTS\Servername

64 bit driver: HKLM\Software\Informix\SQLHOSTS\Servername

If the server is not listed there you get the error "Server XYZ is not listed as a dbserver name in sqlhosts."

Please note that only Linux uses an sqlhosts file. On Windows the same data is stored in the registry instead.

You do NOT need to create a sqlhosts file although the error message and the documentation and several webpages make you think that.

You do NOT need to use the IBM tool SetNet32 to generate entries for your server.

You just have to pass ALL mandatory parameters to the ODBC driver and the error is gone.

It is really a pitty that IBM is not able to give a more intelligent error message.