PDA

View Full Version : adodb works in v6 but fails in v5


ABC123

Eric Miller
02-22-2005, 12:27 PM
Hi,

I'm trying to grab data from a SQL Server database table using adodb. The following example script works under v6. Has anyone been able to get something like this to work under v5?

Here's the v6 script:
objConn = ole.create("ADODB.Connection")
objRs = ole.create("ADODB.Recordset")
objComm = ole.create("ADODB.Command")
connectionstring = "Provider=SQLOLEDB;Data Source=DATASOURCE;Initial Catalog=DATABASE;User Id=theuser;Password=thepassword;"
CommandText = "SELECT TOP 10 * FROM SomeTable"
objConn.Open(ConnectionSTring)
objRs.open(CommandText, objConn)
? objRs.eof
False
? objRs.fields.item(0).value
00001
objRs.close()
objConn.close()

And the same script under v5 works until I use objRs:
? objRs.eof
= "No data returned"
? objRs.fields.item(0).value
ERROR: field types don't match

Lenny Forziati
02-22-2005, 12:33 PM
ADO support was greatly improved in V6, what works there may not work in V5

-Lenny

Eric Miller
02-22-2005, 12:48 PM
I understand that support is "better" in v6. However, I'm still looking for a "work around" for v5 if there is one.

I've tried using a lookup on a linked table through ODBC but the SQL Profiler indicates that incorrectly formated statements are sent to the server, for example:

DECLARE @sSQL nvarchar(200)
SET @sSQL = N'SELECT * FROM ''dbo''.''tGRAENROL_Agent'''
PRINT @sSQL
EXEC sp_executesql @sSQL

This results in an "Incorrect syntax near 'dbo'" error. Changing the ODBC settings so that double quotes are used produces a correct statement trace in the profiler but still results in an error.

Why were linked ODBC tables dropped in v6? It would be much easier for us to solve the current problem with a set containing the linked table.

Thanks,

Eric Miller