Forum Discussion

PeterMitcham's avatar
PeterMitcham
Occasional Contributor
10 years ago

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 E...
  • PeterMitcham's avatar
    PeterMitcham
    10 years ago

    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.

PeterMitcham's avatar
PeterMitcham
Occasional Contributor
10 years ago

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?

  • PeterMitcham's avatar
    PeterMitcham
    Occasional Contributor
    10 years ago

    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.