cancel
Showing results for 
Search instead for 
Did you mean: 

ADODB - Syntax error when trying to open a recordset when using the adCmdTable parameter

SOLVED
Occasional Contributor

ADODB - Syntax error when trying to open a recordset when using the adCmdTable parameter

Hi

 

I am trying to update a record in a sql server database table and when i try and open up a recordset using adCmdTable parameter i get the following syntax error.

 

Err.number: -2147217900

Err.Description: Incorrect syntax near the keyword 'SET'

 

The interesting part is this error occurs when trying to process the open method and appears to be related to the sql variable content

 

adoRS.Open sql, OpenResConn, adOpenKeyset, adLockOptimistic, adCmdTable

 

If i remove the adCmdTable parameter then the statement will process but the adOpenKeyset and adLockOptimistic settings will be default and not as specified. As i am trying to update a value in the table, the default settings are read only so i get an error relating to be unable to update due to lock state.

 

Additional infomation:

DB Version: SQL Server 12

Table:  the blob table has the ID column set as the primary key

BLOB field type is image

the adCmdTable parameter is correctly being returned as int 2.

 

Here is the code i am trying to run.

 

Dim OpenResConn, sql, sFileName, adoRS, iDriverID

 

iDriverID = 4

sFileName = "C:\Testing\Errors\TestUncertain.png"

 

Set OpenResConn = Sys.OleObject("ADODB.Connection")

OpenResConn.ConnectionString = "Provider=SQLNCLI11;" _

& "Server=auto-test-res;" _

& "Database=Results;" _

& "user id ='Results';" _

& "password='Password';" _

& "DataTypeCompatibility=80;"

OpenResConn.Open

 

sql = "SET NOCOUNT ON; "

sql = sql & "insert into BLOBCopy (DriverID, Filename)"

sql = sql & "values (" & iDriverID & ", '" & sFileName & "');"

sql = sql & "SELECT SCOPE_IDENTITY() as ID;"

 

 

Set adoRS = Sys.OleObject("ADODB.RecordSet")

Call adoRs.Open(sql, OpenResConn)

blobID= adoRs("ID")

adoRs.Close

 

'Open a recordset of just the BLOB column for the row we just created

sql = "SET NOCOUNT ON; "

sql = sql & "select BLOB from BLOBCopy where ID = " & blobID

 

adoRS.Open sql, OpenResConn, adOpenKeyset, adLockOptimistic, adCmdTable

 

'create a stream and populate it with the specified file

Dim adoSt

Set adoSt = Sys.OleObject("ADODB.Stream")

adoSt.Type = 1'adTypeBinary

adoSt.Open

adoSt.LoadFromFile sFileName

 

'Write the stream to the recordset and update it

adoRs.Fields("BLOB").Value = adoSt.Read

adoRs.Update

adoSt.Close

adoRs.Close

 

 

Does anyone have any ideas as to why this isnt working as intended?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Occasional Contributor

Re: ADODB - Syntax error when trying to open a recordset when using the adCmdTable parameter

Well it would appear the problem relates to the datatype being passed, adodb.stream expects it to be a byte() array and its being passed as a variant (as per vb script).

 

There were numerous examples of converting a variant to byte array available on forums\solutions sites etc, but i struggled to get them to work.

 

I solved it finally by writing a console app that replicates the adodb stream process and it works fine.

View solution in original post

2 REPLIES 2
Highlighted
Occasional Contributor

Re: ADODB - Syntax error when trying to open a recordset when using the adCmdTable parameter

Solved the first problem in that asCmdTable returns all the columns associated with the table, as i only need 1 columns not passing it as a parameter is a viable option which allows the script to continue...

 

Unfortunately i have now come across another issue relating the to ADODB.Stream

 

Dim adoSt

Set adoSt = Sys.OleObject("ADODB.Stream")

adoSt.Type = 1'adTypeBinary

adoSt.Open

adoSt.LoadFromFile sFileName

 

'Write the stream to the recordset and update it

adoRs.Fields("BLOB").Value = adoSt.Read

adoRs.Update

adoSt.Close

adoRs.Close

 

I get a type mismatch when i read the value back to the record set (the file is a .png)

 

Err.number -2147352571

Err.description: Type mismatch

Err.source: Provider

 

Any ideas?

Highlighted
Occasional Contributor

Re: ADODB - Syntax error when trying to open a recordset when using the adCmdTable parameter

Well it would appear the problem relates to the datatype being passed, adodb.stream expects it to be a byte() array and its being passed as a variant (as per vb script).

 

There were numerous examples of converting a variant to byte array available on forums\solutions sites etc, but i struggled to get them to work.

 

I solved it finally by writing a console app that replicates the adodb stream process and it works fine.

View solution in original post

New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors