2 Replies Latest reply on Jul 20, 2011 3:55 AM by UzairTahir

    ODBC Error in .net

    StefanAppelgren

      Title

      ODBC Error in .net

      Your post

      Im using the ODBC ( 11.0.61.0) connector to get and save data in a Filmaker 11 database.

      Everything works fine except that im trying to get and save text to a textfield in Filemaker that has more then 2046 characters then it crash.
      If i trye to get the same data from for example Excel everything works fine.
      I dont get anny exception or error message it just crash...

        • 1. Re: ODBC Error in .net
          ranja

          By .net do you mean ASP.NET?

          In the case of ASP.NET with DataDirect ODBC SequeLink driver (FileMaker 10 or earlier), I overcame it by using System.Data.DbType.StringFixedLength instead of System.Data.DbType.String for command parameters.  I also had to specify the Size property explicitly (three times the max size of the string to save Japanese characters successfully).

           

          • 2. Re: ODBC Error in .net
            UzairTahir

            After spending days, I could not find a solution to this issue and finally modified my .Net code to split the select query into the chunks of 2046 characters and then re-arranged the resultset.

            Below is my .Net code to load a File Maker's Job_Orders table (containing more than 2046 characters in the Description field):

             

            OdbcConnection

            odbcConn = new OdbcConnection("DSN=DSN_Name; Uid=User_Name; Pwd=Password");

             

            OdbcCommand odbcCmd = new OdbcCommand();

             

            OdbcDataAdapter da = new OdbcDataAdapter();

             

            DataTable dtLength = new DataTable();

             

            DataTable dtDesc = null;

             

            DataSet ds = new DataSet();

             

            DataTable dt = new DataTable();

            dt.Columns.Add(

            "JobOrderID", typeof(int));

            dt.Columns.Add(

            "Description", typeof(string));

            odbcConn.Open();

            odbcCmd.Connection = odbcConn;

            odbcCmd.CommandType =

            CommandType.Text;

            odbcCmd.CommandText =

            "select JobOrderID, length(Description) as Num_Of_Char from Job_Orders";

             

            da.SelectCommand = odbcCmd;

            da.Fill(dtLength);

            odbcConn.Close();

             

            int Num_Of_Char = 0;

             

            int index = 0;

             

            foreach (DataRow drLength in dtLength.Rows)

            {

            Num_Of_Char =

            Convert.ToInt32(drLength["Num_Of_Char"]);

            index = 0;

            dtDesc =

            new DataTable();

             

            while (Num_Of_Char >= 0)

            {

            odbcConn.Open();

            odbcCmd.Connection = odbcConn;

            odbcCmd.CommandType =

            CommandType.Text;

            odbcCmd.CommandText =

            "select substring(Description, " +

            index +

            ", 2046) as Description " +

             

            "from Job_Orders where JobOrderID=" +

             

            Convert.ToInt32(drLength["JobOrderID"]);

            da.SelectCommand = odbcCmd;

            da.Fill(dtDesc);

            odbcConn.Close();

            index += 2047;

            Num_Of_Char -= 2046;

            }

             

            string desc = "";

             

            foreach (DataRow drDesc in dtDesc.Rows)

            {

            desc += drDesc[

            "Description"].ToString();

            }

             

            if (dtDesc.Rows.Count > 0)

            {

             

            DataRow dr = dt.NewRow();

            dr[

            "JobOrderID"] = dtDesc.Rows[0]["JobOrderID"];

            dr[

            "Description"] = desc;

            dt.Rows.Add(dr);

            }

            }

             

            if (dt != null)

            {

            ds.Tables.Add(dt);

            }

             

            if (ds.Tables[0].Rows.Count > 0)

            {

             

            //Job_Orders table has been loaded into ds.Tables[0] and you can continue your stuff here...

            }