Unable to Open Recordset on one system.

I have one system, WIN98SE with WSH 5.6, that is unable to open recordsets
whether I use the RS.Open method or Conn.Execute.
The code below presents both methods. This the only machine that is having
this problem, I re-installed WSH as a basic trouble-shooting step, but that
wasn't it.
Does anyone have an idea what may be happening here?

Thanks, Patrick
----------------------------------------------------------------------------
-------------
sConn = "DSN=SOTAMAS90;UID=RPT;COMPANY=PLY;PWD=*******"
Set oConn = CreateObject("ADODB.Connection")
oConn.Provider = "MSDASQL"
oConn.Open sConn, adAsyncConnect
Set rsOrder = CreateObject("ADODB.Recordset") 'Comment Out if
Connection.Execute method is used
Set rsItems = CreateObject("ADODB.Recordset") 'Comment Out if
Connection.Execute method is used

sSQLOrd = "SELECT SO1.SalesOrderNumber, SO1.SalesOrderDate, SO1.ShipToName,
SO1.ShipToCity, SO1.ShipToState,"
sSQLOrd = sSQLOrd & " SO1.TaxableAmount + SO1.NonTaxableAmount +
SO1.SalesTaxAmount as SalesTotal"
sSQLOrd = sSQLOrd & " FROM SO1_SOEntryHeader SO1"
sSQLOrd = sSQLOrd & " WHERE SO1.Division = " & chr(39) & sCustDiv & chr(39)
& " AND"
sSQLOrd = sSQLOrd & " SO1.CustomerNumber = " & chr(39) & sCustNo & chr(39)
& " AND"
sSQLOrd = sSQLOrd & " SO1.ShipToCode = " & chr(39) & sShipTo & chr(39) & "
AND"
sSQLOrd = sSQLOrd & cStr(" SO1.SalesOrderDate >= {d " & chr(39) & dBegDT &
chr(39) & "}")
sSQLOrd = sSQLOrd & " ORDER BY SO1.SalesOrderNumber DESC"

'Set rsOrder = oConn.Execute(sSQLOrd)
rsOrder.Open sSQLOrd, oConn
----------------------------------------------------------------------------
-------------

Viatcheslav
Fri Feb 06 09:00:03 CST 2004

Check that SOMTAMAS90 exists and points to correct data source that have
SO1_SOEntryHeader table.
Do not use adAsyncConnect - all your action are synchronous and require
connection to be open.
Install (re-install) latest MDAC (http://www.microsoft.com/data or
http://www.microsoft.com/downloads).

//------------------------------------
Regards,
Vassiliev V. V.
http://www-sharp.com - best scripting/compiled HTA IDE

"Patrick Cohan" <patrickc@poly-tex.com.NO_SPAM> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅÅ: news:u4pTp5L7DHA.1592@TK2MSFTNGP10.phx.gbl...
> I have one system, WIN98SE with WSH 5.6, that is unable to open recordsets
> whether I use the RS.Open method or Conn.Execute.
> The code below presents both methods. This the only machine that is
having
> this problem, I re-installed WSH as a basic trouble-shooting step, but
that
> wasn't it.
> Does anyone have an idea what may be happening here?
>
> Thanks, Patrick
> --------------------------------------------------------------------------
--
> -------------
> sConn = "DSN=SOTAMAS90;UID=RPT;COMPANY=PLY;PWD=*******"
> Set oConn = CreateObject("ADODB.Connection")
> oConn.Provider = "MSDASQL"
> oConn.Open sConn, adAsyncConnect
> Set rsOrder = CreateObject("ADODB.Recordset") 'Comment Out if
> Connection.Execute method is used
> Set rsItems = CreateObject("ADODB.Recordset") 'Comment Out if
> Connection.Execute method is used
>
> sSQLOrd = "SELECT SO1.SalesOrderNumber, SO1.SalesOrderDate,
SO1.ShipToName,
> SO1.ShipToCity, SO1.ShipToState,"
> sSQLOrd = sSQLOrd & " SO1.TaxableAmount + SO1.NonTaxableAmount +
> SO1.SalesTaxAmount as SalesTotal"
> sSQLOrd = sSQLOrd & " FROM SO1_SOEntryHeader SO1"
> sSQLOrd = sSQLOrd & " WHERE SO1.Division = " & chr(39) & sCustDiv &
chr(39)
> & " AND"
> sSQLOrd = sSQLOrd & " SO1.CustomerNumber = " & chr(39) & sCustNo &
chr(39)
> & " AND"
> sSQLOrd = sSQLOrd & " SO1.ShipToCode = " & chr(39) & sShipTo & chr(39) &
"
> AND"
> sSQLOrd = sSQLOrd & cStr(" SO1.SalesOrderDate >= {d " & chr(39) & dBegDT &
> chr(39) & "}")
> sSQLOrd = sSQLOrd & " ORDER BY SO1.SalesOrderNumber DESC"
>
> 'Set rsOrder = oConn.Execute(sSQLOrd)
> rsOrder.Open sSQLOrd, oConn
> --------------------------------------------------------------------------
--
> -------------
>
>

Patrick
Fri Feb 06 14:59:00 CST 2004

MDAC is at v2.8.
Note, the code below this sentence DOES work...now I am really stumped.
----------------------------------------------------------------------------
------
sConn = "DSN=SOTAMAS90;UID=***;COMPANY=***;PWD=********"
Set oConn = CreateObject("ADODB.Connection")
oConn.Provider = "MSDASQL"
oConn.Open sConn
sSQL = "Select IM1.ItemDescription, IM1.ProductLine, IM1.ProductType "
sSQL = sSQL & "From IM1_InventoryMasterfile IM1 "
sSQL = sSQL & "Where IM1.ItemNumber = '" & ML_ITEM & "'"
Set rsItem = oConn.Execute(sSQL)
----------------------------------------------------------------------------
------

"Viatcheslav V. Vassiliev" <msnewsgroup@www-sharp.com> wrote in message
news:u8WynHM7DHA.260@TK2MSFTNGP11.phx.gbl...
> Check that SOMTAMAS90 exists and points to correct data source that have
> SO1_SOEntryHeader table.
> Do not use adAsyncConnect - all your action are synchronous and require
> connection to be open.
> Install (re-install) latest MDAC (http://www.microsoft.com/data or
> http://www.microsoft.com/downloads).
>
> //------------------------------------
> Regards,
> Vassiliev V. V.
> http://www-sharp.com - best scripting/compiled HTA IDE


sConn = "DSN=SOTAMAS90;UID=RPT;COMPANY=PLY;PWD=*******"
Set oConn = CreateObject("ADODB.Connection")
oConn.Provider = "MSDASQL"
oConn.Open sConn, adAsyncConnect
Set rsOrder = CreateObject("ADODB.Recordset") 'Comment Out if
Connection.Execute method is used
Set rsItems = CreateObject("ADODB.Recordset") 'Comment Out if
Connection.Execute method is used

sSQLOrd = "SELECT SO1.SalesOrderNumber, SO1.SalesOrderDate,
SO1.ShipToName, SO1.ShipToCity, SO1.ShipToState,"
sSQLOrd = sSQLOrd & " SO1.TaxableAmount + SO1.NonTaxableAmount +
SO1.SalesTaxAmount as SalesTotal"
sSQLOrd = sSQLOrd & " FROM SO1_SOEntryHeader SO1"
sSQLOrd = sSQLOrd & " WHERE SO1.Division = " & chr(39) & sCustDiv & chr(39)
& " AND"
sSQLOrd = sSQLOrd & " SO1.CustomerNumber = " & chr(39) & sCustNo & chr(39) &
" AND"
sSQLOrd = sSQLOrd & " SO1.ShipToCode = " & chr(39) & sShipTo & chr(39) & "
AND"
sSQLOrd = sSQLOrd & cStr(" SO1.SalesOrderDate >= {d " & chr(39) & dBegDT &
chr(39) & "}")
sSQLOrd = sSQLOrd & " ORDER BY SO1.SalesOrderNumber DESC"

'Set rsOrder = oConn.Execute(sSQLOrd)
rsOrder.Open sSQLOrd, oConn
--------------------------------------------------------------------------

Bob
Fri Feb 06 16:56:05 CST 2004

Patrick Cohan wrote:
> I have one system, WIN98SE with WSH 5.6, that is unable to open
> recordsets whether I use the RS.Open method or Conn.Execute.
> The code below presents both methods. This the only machine that is
> having this problem, I re-installed WSH as a basic trouble-shooting

Why did you do this? Was there an error message that pointed to WSH being
the problem?


> step, but that wasn't it.
> Does anyone have an idea what may be happening here?
>
> Thanks, Patrick
> --------------------------------------------------------------------------
--
> -------------
> sConn = "DSN=SOTAMAS90;UID=RPT;COMPANY=PLY;PWD=*******"
> Set oConn = CreateObject("ADODB.Connection")
> oConn.Provider = "MSDASQL"
> oConn.Open sConn, adAsyncConnect
> Set rsOrder = CreateObject("ADODB.Recordset") 'Comment Out if
> Connection.Execute method is used
> Set rsItems = CreateObject("ADODB.Recordset") 'Comment Out if
> Connection.Execute method is used
>
> sSQLOrd = "SELECT SO1.SalesOrderNumber, SO1.SalesOrderDate,
> SO1.ShipToName, SO1.ShipToCity, SO1.ShipToState,"
> sSQLOrd = sSQLOrd & " SO1.TaxableAmount + SO1.NonTaxableAmount +
> SO1.SalesTaxAmount as SalesTotal"
> sSQLOrd = sSQLOrd & " FROM SO1_SOEntryHeader SO1"
> sSQLOrd = sSQLOrd & " WHERE SO1.Division = " & chr(39) & sCustDiv &
> chr(39) & " AND"
> sSQLOrd = sSQLOrd & " SO1.CustomerNumber = " & chr(39) & sCustNo &
> chr(39) & " AND"
> sSQLOrd = sSQLOrd & " SO1.ShipToCode = " & chr(39) & sShipTo &
> chr(39) & " AND"
> sSQLOrd = sSQLOrd & cStr(" SO1.SalesOrderDate >= {d " & chr(39) &
> dBegDT & chr(39) & "}")
> sSQLOrd = sSQLOrd & " ORDER BY SO1.SalesOrderNumber DESC"
>
> 'Set rsOrder = oConn.Execute(sSQLOrd)
> rsOrder.Open sSQLOrd, oConn
> --------------------------------------------------------------------------
--
> -------------

What does "unable to open" mean? Error message? If so, what is the error
message? How do you expect anyone to figure out your problem if you don't
tell us the symptoms?

Have you tried looking at the value of sSQLOrd to make sure you've built
your sql statement correctly?

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Viatcheslav
Sat Feb 07 03:03:31 CST 2004

Now you do not use adAsyncConnect and you have valid connection object after
"oConn.Open sConn". When you use adAsyncConnect you should wait until
connection is open (ConnectComplete event).

Or you have problems with SQL (using '{' and '}' characters seems to be
invalid - look help for date representation in your DBMS documentation).

//------------------------------------
Regards,
Vassiliev V. V.
http://www-sharp.com - best scripting/compiled HTA IDE


"Patrick Cohan" <patrickc@poly-tex.com.NO_SPAM> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅÅ: news:%23raUNQP7DHA.452@TK2MSFTNGP11.phx.gbl...
> MDAC is at v2.8.
> Note, the code below this sentence DOES work...now I am really stumped.
> --------------------------------------------------------------------------
--
> ------
> sConn = "DSN=SOTAMAS90;UID=***;COMPANY=***;PWD=********"
> Set oConn = CreateObject("ADODB.Connection")
> oConn.Provider = "MSDASQL"
> oConn.Open sConn
> sSQL = "Select IM1.ItemDescription, IM1.ProductLine, IM1.ProductType "
> sSQL = sSQL & "From IM1_InventoryMasterfile IM1 "
> sSQL = sSQL & "Where IM1.ItemNumber = '" & ML_ITEM & "'"
> Set rsItem = oConn.Execute(sSQL)
> --------------------------------------------------------------------------
--
> ------
>
> "Viatcheslav V. Vassiliev" <msnewsgroup@www-sharp.com> wrote in message
> news:u8WynHM7DHA.260@TK2MSFTNGP11.phx.gbl...
> > Check that SOMTAMAS90 exists and points to correct data source that have
> > SO1_SOEntryHeader table.
> > Do not use adAsyncConnect - all your action are synchronous and require
> > connection to be open.
> > Install (re-install) latest MDAC (http://www.microsoft.com/data or
> > http://www.microsoft.com/downloads).
> >
> > //------------------------------------
> > Regards,
> > Vassiliev V. V.
> > http://www-sharp.com - best scripting/compiled HTA IDE
>
>
> sConn = "DSN=SOTAMAS90;UID=RPT;COMPANY=PLY;PWD=*******"
> Set oConn = CreateObject("ADODB.Connection")
> oConn.Provider = "MSDASQL"
> oConn.Open sConn, adAsyncConnect
> Set rsOrder = CreateObject("ADODB.Recordset") 'Comment Out if
> Connection.Execute method is used
> Set rsItems = CreateObject("ADODB.Recordset") 'Comment Out if
> Connection.Execute method is used
>
> sSQLOrd = "SELECT SO1.SalesOrderNumber, SO1.SalesOrderDate,
> SO1.ShipToName, SO1.ShipToCity, SO1.ShipToState,"
> sSQLOrd = sSQLOrd & " SO1.TaxableAmount + SO1.NonTaxableAmount +
> SO1.SalesTaxAmount as SalesTotal"
> sSQLOrd = sSQLOrd & " FROM SO1_SOEntryHeader SO1"
> sSQLOrd = sSQLOrd & " WHERE SO1.Division = " & chr(39) & sCustDiv &
chr(39)
> & " AND"
> sSQLOrd = sSQLOrd & " SO1.CustomerNumber = " & chr(39) & sCustNo & chr(39)
&
> " AND"
> sSQLOrd = sSQLOrd & " SO1.ShipToCode = " & chr(39) & sShipTo & chr(39) &
"
> AND"
> sSQLOrd = sSQLOrd & cStr(" SO1.SalesOrderDate >= {d " & chr(39) & dBegDT &
> chr(39) & "}")
> sSQLOrd = sSQLOrd & " ORDER BY SO1.SalesOrderNumber DESC"
>
> 'Set rsOrder = oConn.Execute(sSQLOrd)
> rsOrder.Open sSQLOrd, oConn
> --------------------------------------------------------------------------
>
>

Patrick
Mon Feb 09 11:43:02 CST 2004

Bob,

The SQL statement works on 14 other systems. However, the message from this
affected system does imply that it is having trouble with the SQL syntax
because it is stating:
'[ProvideX][ODBC Driver]Expected lexical element not found: <indentifier>'
The version of the ODBC driver is the same as that on the other machines.
Still Stumped.

Patrick

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uzypnRQ7DHA.1592@TK2MSFTNGP10.phx.gbl...
> Patrick Cohan wrote:
> > I have one system, WIN98SE with WSH 5.6, that is unable to open
> > recordsets whether I use the RS.Open method or Conn.Execute.
> > The code below presents both methods. This the only machine that is
> > having this problem, I re-installed WSH as a basic trouble-shooting
>
> Why did you do this? Was there an error message that pointed to WSH being
> the problem?
>
>
> > step, but that wasn't it.
> > Does anyone have an idea what may be happening here?
> >
> > Thanks, Patrick
>
> --------------------------------------------------------------------------
> --
> > -------------
> > sConn = "DSN=SOTAMAS90;UID=RPT;COMPANY=PLY;PWD=*******"
> > Set oConn = CreateObject("ADODB.Connection")
> > oConn.Provider = "MSDASQL"
> > oConn.Open sConn, adAsyncConnect
> > Set rsOrder = CreateObject("ADODB.Recordset") 'Comment Out if
> > Connection.Execute method is used
> > Set rsItems = CreateObject("ADODB.Recordset") 'Comment Out if
> > Connection.Execute method is used
> >
> > sSQLOrd = "SELECT SO1.SalesOrderNumber, SO1.SalesOrderDate,
> > SO1.ShipToName, SO1.ShipToCity, SO1.ShipToState,"
> > sSQLOrd = sSQLOrd & " SO1.TaxableAmount + SO1.NonTaxableAmount +
> > SO1.SalesTaxAmount as SalesTotal"
> > sSQLOrd = sSQLOrd & " FROM SO1_SOEntryHeader SO1"
> > sSQLOrd = sSQLOrd & " WHERE SO1.Division = " & chr(39) & sCustDiv &
> > chr(39) & " AND"
> > sSQLOrd = sSQLOrd & " SO1.CustomerNumber = " & chr(39) & sCustNo &
> > chr(39) & " AND"
> > sSQLOrd = sSQLOrd & " SO1.ShipToCode = " & chr(39) & sShipTo &
> > chr(39) & " AND"
> > sSQLOrd = sSQLOrd & cStr(" SO1.SalesOrderDate >= {d " & chr(39) &
> > dBegDT & chr(39) & "}")
> > sSQLOrd = sSQLOrd & " ORDER BY SO1.SalesOrderNumber DESC"
> >
> > 'Set rsOrder = oConn.Execute(sSQLOrd)
> > rsOrder.Open sSQLOrd, oConn
>
> --------------------------------------------------------------------------
> --
> > -------------
>
> What does "unable to open" mean? Error message? If so, what is the error
> message? How do you expect anyone to figure out your problem if you don't
> tell us the symptoms?
>
> Have you tried looking at the value of sSQLOrd to make sure you've built
> your sql statement correctly?
>
> HTH,
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

Bob
Mon Feb 09 12:59:35 CST 2004

Patrick Cohan wrote:
> Bob,
>
> The SQL statement works on 14 other systems. However, the message
> from this affected system does imply that it is having trouble with
> the SQL syntax because it is stating:
> '[ProvideX][ODBC Driver]Expected lexical element not found:
> <indentifier>' The version of the ODBC driver is the same as that on
> the other machines. Still Stumped.
>

I've never seen this error message. What is your back-end database?

A Google search brings back results that suggest the wrong outer join
operator was used, but i don't see any joins in your query, let alone an
outer join. Have done a respense.write of your variable containing the SQL
statement so you could verify that it was built correctly? That should
always be your first step when getting a sql syntax error, regardless of
whether it works on 13 other machines or not. When creating a dynamic sql
statement, the goal is to create a string that will execute as-is in the
database's query tool. The only way to verify this is to write the contents
of the sql variable to the screen so you can copy and paste it into the
query tool to test it.
The next step in the debugging process is to start with a simplified query
that works and start adding the dynamic bits to it until it stops working.

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Patrick
Mon Feb 09 13:17:40 CST 2004

The back-end database is ProvideX which supports Best Software's MAS-200
Accounting system. It is not pretty, but switching to SQL-Server in the
planning stage.
I will have to run the query tool on that affected client and see if I can
gather more information, but the messaging from their ODBC driver is less
than desirable. The other thing that I could do, though very klugy, it so
have an Access database linked to ProvideX and then have the script point
that instead. *coin-toss*

Thanks for looking this over though,

Patrick


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:Oo76e7z7DHA.2168@TK2MSFTNGP12.phx.gbl...
> Patrick Cohan wrote:
> > Bob,
> >
> > The SQL statement works on 14 other systems. However, the message
> > from this affected system does imply that it is having trouble with
> > the SQL syntax because it is stating:
> > '[ProvideX][ODBC Driver]Expected lexical element not found:
> > <indentifier>' The version of the ODBC driver is the same as that on
> > the other machines. Still Stumped.
> >
>
> I've never seen this error message. What is your back-end database?
>
> A Google search brings back results that suggest the wrong outer join
> operator was used, but i don't see any joins in your query, let alone an
> outer join. Have done a respense.write of your variable containing the SQL
> statement so you could verify that it was built correctly? That should
> always be your first step when getting a sql syntax error, regardless of
> whether it works on 13 other machines or not. When creating a dynamic sql
> statement, the goal is to create a string that will execute as-is in the
> database's query tool. The only way to verify this is to write the
contents
> of the sql variable to the screen so you can copy and paste it into the
> query tool to test it.
> The next step in the debugging process is to start with a simplified query
> that works and start adding the dynamic bits to it until it stops working.
>
> HTH,
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>