locked
c# using ODBC + SQL SubString Function not working RRS feed

  • Question

  • User-2040560040 posted

    I am building a SQL in-line query string which works through query tool (FlySpeed SQL Query) but when I try to execute through ODBC Jet 4.0 I receive an error;

    IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

    I tried using both OleDbCommand and OdbcCommand methods....  The code works if I remove the substring, some of my code is listed below....  Any ideas how i can get around the SubString issue?

    string sSql = "Select '" + DateTime.Now.ToShortDateString() + "' as CurrentDate,  ROUND(SUM(Balanc_128) + SUM(Balanc_129),0) AS Balance, ROUND(SUM(Budget_128) + SUM(Budget_129),0) AS BudgetFROM  gldoll WHERE (SUBSTRING(NUM,1,3)=  '120' OR SUBSTRING(NUM,1,3)=  '123') AND SUBSTRING(NUM,6,2)=  '11'  Group By SUBSTRING(NUM,2,2)";

    string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\\\xxxxxxx\\dsk0\\010000\\;Extended Properties=dBase IV";

    System.Data.OleDb.OleDbConnection dBaseConnection = new System.Data.OleDb.OleDbConnection(sConnectionString);

    dBaseConnection.Open();

    System.Data.OleDb.OleDbCommand dBaseCommand = new System.Data.OleDb.OleDbCommand(sSql, dBaseConnection);
    //System.Data.Odbc.OdbcCommand dBaseCommand = new System.Data.Odbc.OdbcCommand(sSql, dBaseConnection);

    OleDbDataAdapter da = new OleDbDataAdapter(dBaseCommand);
    // OdbcDataAdapter da = new OdbcDataAdapter(dBaseCommand);

    da.Fill(ds, "XX");

    Tuesday, March 28, 2017 2:10 AM

All replies

  • User-707554951 posted

    Hi jsegreti.

    From your description. For I don’t know which error you encountered. I supposed that your problem is related to datatype of expression and  returned string.

    Returns character data if expression is one of the supported character data types. Returns binary data if expression is one of the supported binary data types. The returned string is the same type as the specified expression with the exceptions shown in the table in the following links:

    https://docs.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql

    Besides, as far as I know. When we using SUBSTR( ) with memo fields in a SELECT – SQL command, we used to  include the PADR( ) function in SUBSTR( ) so that empty or variable length memo fields produce consistent results when converted to character strings.

    https://msdn.microsoft.com/en-us/library/aa978501(v=vs.71).aspx

    After check the description above, if you still have problem on this, Would you please provide us with detailed information about your error?

    Best regards

    Cathy

    Wednesday, March 29, 2017 2:16 AM