VB6-ADO-COM-ODBC-INFORMIX : Problem running query. SQL State 37000

From: rmsterling (rmsterling_at_avaya.com)
Date: 10/21/04 

Date: Thu, 21 Oct 2004 07:39:04 -0700

Problem running SELECT SQL query....

Using....
  Visual Basic 6.0 SP5
  ADO COM Object
  Using ODBC
  MDAC 2.8 Installed
  Connecting to a Informix 7.3 Database located on a Unix Server.

Connection statement below connects the database with no problems.....

    sConnectStr = "DSN=testname;UID=testuser;PWD=testpwd"
    With gadoConnection_Test
        .CursorLocation = adUseServer
        .Provider = "MSDASQL"
        .ConnectionString = sConnectStr
        .CommandTimeout = 300
        .Open
    End With

When I go to execute a query .....

    gsSQL = "SELECT testdata FROM testtable WHERE testdata='TEST' ORDER BY
testdata2;"
    adoRS_Test.Open gsSQL, gadoConnection_Test, adOpenDynamic, adLockReadOnly
    If Not adoRS_Test.BOF Then

Above query produces this error....

  Error : -2147217900.
  Native Error : 0.
  SQL State : 37000.
  Source : Microsoft OLE DB Provider for ODBC Drivers.
  Description : [OpenLink][ODBC][Driver]Syntax error or access violation.

Tried running query through the ODBC layer in Access 2003 and the Query
executes fine.

I think the problem lies in the statement below which tells VB what ODBC
driver to use....

        .Provider = "MSDASQL"

ODBC Driver details are below....

 OpenLink Generic ODBC Driver
 5.10.00.00
 OpenLink Software
 OLOD5032.DLL
 18Sep2003

Could you tell me what I place in the .Provider property to make my
application work? 

-- 
----------------------------
Regards,
Richard Sterling 
Senior Software Engineer Tel : +44 (0)1707 392200 ext 4815 
Avaya ECS Ltd, United Kingdom 
mailto:rmsterling@avaya.com

Relevant Pages

  • Re: MS Access looks for .mdb rather than Progress schema
    ... # Same query previously worked in Progress 8.x using SQL89. ... This entry allows you to keep your existing code written with the ODBC ... BTW, if you put all of the connect information into the connect string, ... I also tried putting the whole thing in the connection string: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Word 2000/2002 - Proper Mail Merge steps for ODBC?
    ... > I don't get the "Database has been placed in a state by ... Access runs the query and will prompt for any ... > my ODBC entry and click the "Configure" button, ... >>using OLEDB, it uses a more exclusive mode than it really ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Word 2000/2002 - Proper Mail Merge steps for ODBC?
    ... I was able to find the MS Query button and locate my ... in both is ODBC. ... >> be certain that I am using the OLEDB method? ... >from the list of possible connection options. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: MS Access looks for .mdb rather than Progress schema
    ... This is not passthrough query sql. ... and should be translated into ODBC SQL. ... BTW, if you put all of the connect information into the connect string, ... I also tried putting the whole thing in the connection string: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Simple query executes fast but renders slow...
    ... After you restart Access and run your query, JET will write an extensive log ... "SQL Queries for Mere Mortals" ... >>Then I wrote queries and reports based on the local ... So, ODBC, right? ...
    (microsoft.public.access.queries)