Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Bind Variables with ADO Provider (Read 3512 times)
YaBB Newbies


Posts: 10
Bind Variables with ADO Provider
17.03.10 at 14:16:41
I have been using the following code (below) with 0040 provider (SQLXL.Database.ConnectionType = litOO4O) without problem.
I have since been trying to use the same code (below) with the ADO provider (SQLXL.Database.ConnectionType = litSQLXLADO) and been getting an error message (Parameter Type not supported - 2147217872).  
It seems that the ADO provider and LitOO4O provider treat the declaration and initialization of bind variables differently, as I don't get the above error with the litOO4O provider but I do with the ADO provider.
SQLXL.Sql.setText "select example_field from example_table where vc= :vc;"
    Set SQLXL.Sql.Statements(1).Target = Targets(litExcel)
    With Targets(litExcel)
         .AutoFilter = False
         .AutoFit = False
         .Headings = False
         .Sort = False
         .StartFromCell = "$AI$1"
         .Transpose = False
         .SQLInNote = False
    End With
    SQLXL.Database.Parameters.BindVariables("vc").Value = Worksheets(1).Cells(3, 1).Value
    SQLXL.Database.Parameters.BindVariables("vc").Mode = litTypeNumber
    SQLXL.Database.Parameters.BindVariables("vc").Mode = litParamIn
    With SQLXL.Sql.Statements(1)
         .ShowParametersDlg = False
         .ShowResultsetDlg = False
    End With
Can someone please help.
Back to top
  IP Logged
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Re: Bind Variables with ADO Provider
Reply #1 - 17.03.10 at 16:42:44
Usually implicit data conversions are the culprit.
Please check that Worksheets(1).Cells(3, 1).Value is really a number. To make sure convert it explicitly using CLng or CDbl:
SQLXL.Database.Parameters.BindVariables("vc").Value = Clng(Worksheets(1).Cells(3, 1).Value)
Try it with a small number first. Is the number column vc able to store the decimals and the precision of the number?
Please check that your vc column in your example table is really a number. vc hints to be a varchar.  
To help you further when you keep having problems I suggest you send the table creation statement and a data row. Along with the value you are trying to insert.
Back to top
« Last Edit: 17.03.10 at 16:43:11 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1