issue a parameterized SQL statement to odbc excel data source

Posted on 2003-03-24
Medium Priority
Last Modified: 2007-12-19
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!
Question by:jclee_sg
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2

Expert Comment

ID: 8200514
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

Author Comment

ID: 8200710
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]


Expert Comment

ID: 8201201

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..
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.


Author Comment

ID: 8201302

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.


Expert Comment

ID: 8207353
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


Author Comment

ID: 8207498
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.


Accepted Solution

anfortas earned 1000 total points
ID: 8758088
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, "")

LVL 17

Expert Comment

ID: 10352121
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
LVL 17

Expert Comment

ID: 10392316
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question