Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 843
  • Last Modified:

issue a parameterized SQL statement to odbc excel data source

Hi experts

I need to access an excel data file via odbc using ado, and furthermore I need to issue a parameterized SQL statement in my vb code, like:

1    mycnn as adodb.connection
2    mycmd as adodb.command
    ... 'codes for connect to data source and assign it to mycmd
3    mycmd.commandText = "select * from mytable where col1 = ? or col2 = ?"
    ... 'codes for append necessary parameters to mycmd.parameters collection
4    mycmd.execute

I got error message at line 4, like:

    -2147217887:[Microsoft][ODBC Excel Driver]Invalid precision value

I've tried but failed to find enough helpful document about this and I'm wondering if MS supports this kind of feature for excel driver, or am I just not on the right road?

Need your help!
  • 3
  • 2
  • 2
  • +2
1 Solution
Aloha jclee

I hacked away and got the following code segment to work for me.

Private Sub Form_Load()
Dim xlsConn As New ADODB.Connection
Dim xlsCmd As New ADODB.Command
Dim m_XLSdir As String
Dim m_xlsfile As String
    m_XLSdir = "c:" 'where the XLS file located
    m_xlsfile = "ExcelData.xls"
    With xlsConn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source = " & m_XLSdir & "\" & m_xlsfile & ";" & "Extended Properties=Excel 8.0;"
        .CursorLocation = adUseClient
    End With
    With xlsCmd
        .ActiveConnection = xlsConn

        .CommandText = "SELECT * from ispRuleAction where ActionId = 'ABUSE_ACT1'"
    End With

End Sub

I should add that I got the table ispRuleAction from my own database, just exporting it from sqlServer to an excel file.
I was also able to do an update set sql statement instead of just the select statement that does not really make sense for as a command.

Anyhow... that's my 5 cents
jclee_sgAuthor Commented:
Hi HugoForte

Thanks for your quick response, but I'm afraid that didn't hit my problem.

My code works fine if I just simply issue a SQL statement like, "select * from mytable". The problem is the parameters! What I want to find out is how I can pass parameters to a SQL statement just as in the code segment that I mentioned in my previous question.

Plus, I've tried but also failed to do so via [odbc text driver] and [odbc access driver]


First question is whether you are required to use the parameterized call? You could just change the commandtext
for each call.

commandtext = "select * from table where col1='abc' or col2 = 6"

otherwise, you could look at these two things.
- from MSDN help -
Calling the fields.Append method for an open Recordset or a Recordset where the ActiveConnection property has been set, will cause a run-time error. You can only append fields to a Recordset that is not open and has not yet been connected to a data source. Typically, these are new Recordset objects that you create with the CreateRecordset method or by explicitly assigning a new Recordset object to an object variable

or, you may have the wrong paramtype value set when you add to the paramters collection.
go to the bottom of this page...

hopefully helpful..
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

jclee_sgAuthor Commented:

Let me make it clear.

1. The same code works fine if I link it to a [ODBC Oracle Driver]. So please ignore the order how I use those adodb stuffs. The code I provided before is just a sample to show the problem.

2. Since it's within a teamwork, so I have to use the parameterized SQL in the way I mentioned before. Of course I have to give it up if it's really not supported by MS, and that's also what I need to find out. But giving it up means changes on basic design.

Aloha again,

So am I correct in assuming you want to do something like this:

Dim param1 as string
dim param2 as string
dim sqlStatement as string
param1 = value1
param2 = value2

sqlStatement = "select * from Mytable where someField1 = <param1> and someField2 = <param2>"

sqlStatement = replace(sqlStatement, "<param1>", param1)
sqlStatement = replace(sqlStatement, "<param2>", param2)

and then execute sqlStatement via adodb.connection?

Just want to make sure before I hack away again;)

Hugo Forte

jclee_sgAuthor Commented:
Hi Hugo

Thanks for the response.

I understand your solution. But what I'm interested in is whether the SQL pattern in my question is workable, because it provides more flexibilities for our design.

Again, the code I provided is just sample and in real application the SQL source and parameter feeding are from different application and SQL may be run by another application, so that makes it more complicated. And that's why I'm trying to find out if it's feasible or not.

I know there're many roads to rome, but I just want a direct answer to my question.

Of course, you are right that using a parameterized SQL statement is vastly superior to trying to build a simple SQL statment with string concatenation. Usually the most straightforward way is to just say:

mycmd.CommandText = "select * from mytable where col1 = ? or col2 = ?"
mycmd.Execute , Array(myval1, myval2)

There seems be a peculiar problem when doing this with the ODBC provider, as opposed to, say, the Jet provider. If a parameter value is empty or null, then Execute gives the error "Invalid precision value." It seems to end up internally attempting parameter creation like this:

mycmd.Parameters.Append mycmd.CreateParameter(, adChar, , -1, "")

If you call this explicitly but specify a size of 0 rather than -1 you immediately get the error "Parameter object is improperly defined." I'm not sure why (perhaps for the reasons outlined in Microsoft KB Q132960), but using either of these instead will make the problem go away:

mycmd.Parameters.Append mycmd.CreateParameter(, adChar, , 1, "")
mycmd.Parameters.Append mycmd.CreateParameter(, adBSTR, , 0, "")

This question has been classified as abandoned.  I will make a recommendation to the moderators on its resolution in approximately 4 days.  I would appreciate any comments by the experts that would help me in making a recommendation.

It is assumed that any participant not responding to this request is no longer interested in its final deposition.

If the asker does not know how to close the question, the options are here:

EE Cleanup Volunteer
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: anfortas {http:#8758088}

Please leave any comments here within the next four days.

EE Cleanup Volunteer

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now