issue a parameterized SQL statement to odbc excel data source

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
        .Open
    End With
   
    With xlsCmd
        .ActiveConnection = xlsConn

        .CommandText = "SELECT * from ispRuleAction where ActionId = 'ABUSE_ACT1'"
        .Execute
       
    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

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]

Thanks
JC.L

JC,

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...
http://www-3.ibm.com/software/data/db2/udb/ad/v8/cli/r0002346.htm

hopefully helpful..

Hi

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.

Thanks
JC.L

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;)

Sincerely,
Hugo Forte

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.

Thanks
JC.L

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:
https://www.experts-exchange.com/help.jsp#hs5

walterecook
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.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

walterecook
EE Cleanup Volunteer