Bind Variables via ADO

dentab
dentab used Ask the Experts™
on
I am trying to query oracle using bind variables, this is just a demo project to get the mechanism right.

This however returns an error "ORA-01008: not all variables bound"

Can you help?
  Dim conn As ADODB.Connection
  Dim comm As ADODB.Command
  Dim rs As ADODB.Recordset
  
  Set conn = New ADODB.Connection
  Set comm = New Command
  Set rs = New ADODB.Recordset
  conn.CursorLocation = adUseClient
  conn.Open "DRIVER={Microsoft ODBC for Oracle};UID=(some user); PWD=(some password); ConnectString=(some database);"
  Set comm.ActiveConnection = conn
  comm.CommandType = adCmdText
  comm.Parameters.Append comm.CreateParameter(":gender", adVarChar, adParamInput, 1)
  comm.Parameters(":gender") = "M"
  comm.CommandText = "select * from person where upper(p_surname) like 'N%' and p_gender=:gender "
  Set rs = comm.Execute()
  Set DataGrid1.DataSource = rs
  Set comm = Nothing
  Set rs = Nothing
  Set conn = Nothing

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Commented:
try to define the type of the variable more preciselly:
comm.Parameters.Append comm.CreateParameter(":gender", OracleType.VarChar, adParamInput, 1)

Author

Commented:
oracletype.VarChar doesnt exist for me... is there another reference I need to include?
Top Expert 2009

Commented:
Hi try setting the CommandText before setting the parameters. I think the problem may be your ordering.


comm.CommandText = "select * from person where upper(p_surname) like 'N%' and p_gender=:gender "
comm.Parameters.Append comm.CreateParameter(":gender", adVarChar, adParamInput, 1)
comm.Parameters(":gender") = "M"
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thanks... I thought of that later too.... still no joy :(

Author

Commented:
I have also tried various changes to using ":" or not also "@" or "&" incase its some strange ADO thing.

Author

Commented:
I cant believe that nobody has done this before.  The code I am using at the moment uses replacements i.e. "Select foo from bar where blah='" & Replace(myValue, "'", "''") & "'"

This however means that oracle never caches its execute plan.  

I thought that for ONCE EE might be able to turn out some answers on this one.
Commented:
The attached code works.  The problem is then if I want to reference a variable more than once, I will need to add it more than once.  Also I would need to add them in a specific order.  I cannot believe how poorly ADO handles this and that there is absolutely no help out there for this.
  comm.CommandText = "select p_forenames, p_surname, p_gender, ?, ? from capd_person where upper(p_surname) like ?||'%'"
  comm.Parameters.Append comm.CreateParameter("va", ADODB.adVarChar, adParamInput, 1, "E")
  comm.Parameters.Append comm.CreateParameter("vb", ADODB.adVarChar, adParamInput, 1, "F")
  comm.Parameters.Append comm.CreateParameter("vc", ADODB.adVarChar, adParamInput, 1, "G")

Open in new window

Top Expert 2009

Commented:
@dentab: >>As usual there are no answers on EE except those I post MYSELF.
@dentab: >>I cant believe that nobody has done this before.

Your posted solution is not the solution to your original question.

You changed the SQL in the question. Your initial question only has one parameter.


>>"select * from person where upper(p_surname) like 'N%' and p_gender=:gender "


In your final sample there are 3 parameters:

>>"select p_forenames, p_surname, p_gender, ?, ? from capd_person where upper(p_surname) like ?||'%'"


There are 3. So your solution is to a question that was not asked. Unfortunately, the help we give can be no better than the information provided.  Who can know if the suggestions posted worked if you were using them for a different SQL than in your question? Since you came to a solution and posted it, I object to the deletion, whether you want to award points to yourself (refund) or not is up to you, but the solution is useful.

Author

Commented:
You changed the SQL in the question. Your initial question only has one parameter.

True, but my solution was the only one posted that addressed even ONE parameter.  You failed to solve the initial problem posted.  If you provided the answer that I DID then you would have been given the points.  You did not.   If you could have provided me any working solution then you would have got the points but, you did not.  If your suggestion on my original code had ANY effect I would give the points but it DID NOT.  Therefore  I am requesting the question closed.

Author

Commented:
woops, jut realised... to point.

I changed the code in MY solution to multiple parameters.  My question still shows ONE.  I solved my own problem in the end, just because I solved my own problem using one or multiple parameters (therefore being better than the answer I requested and did NOT recieve) you complain?

Author

Commented:
Seriously an EE Volenteer objecting?

Your posted solution is not the solution to your original question.

Yes it is.  My question was "CAN YOU HELP?" the code provided needed altering to work.  I can get bind variables to work by making all parameters ?.instead of :.  My example is with multiple parameters, yes.  Nobody answered even my 1st problem, and I did not expect anybody to answer the issues with my NEW code.  No "solutions" weere posted to any of my comments.

In your final sample there are 3 parameters:
>>"select p_forenames, p_surname, p_gender, ?, ? from capd_person where upper(p_surname) like ?||'%'"
There are 3. So your solution is to a question that was not asked

That is the stupidest thing I have hearn on EE so far.  It answers the question with even 1 parameter.  Obviously my OWN solution would have problems with 3, which is why I explained the flaw in my OWN solution and how it would work with multiple parameters.  People PAY for this?  However if you can not see how my answer is my own answer try this:

  Dim conn As ADODB.Connection
  Dim comm As ADODB.Command
  Dim rs As ADODB.Recordset
 
  Set conn = New ADODB.Connection
  Set comm = New Command
  Set rs = New ADODB.Recordset
  conn.CursorLocation = adUseClient
  conn.Open "DRIVER={Microsoft ODBC for Oracle};UID=(some user); PWD=(some password); ConnectString=(some database);"
  Set comm.ActiveConnection = conn
  comm.CommandType = adCmdText
  comm.Parameters.Append comm.CreateParameter("gender", ADODB.adVarChar, adParamInput, 1, "E")
 'Only one parameter now, happy?
  comm.CommandText = "select * from person where p_gender=?"
  Set rs = comm.Execute()
  Set DataGrid1.DataSource = rs
  Set comm = Nothing
  Set rs = Nothing
  Set conn = Nothing
Top Expert 2008
Commented:
Guys,

please understand:
NOBODY IS PERFECT!

The "experts" are volunteers and can not cover the whole spectrum of the problems in Oracle area.
The Askers are not trained to define the problem clear.

We trty to help and sometimes we fail, somettimes our advices are accepted (with reason or without reason).

But please be advised that "war" in Experts-Exchange is the last thing I will see here.
It is psychologically hard task to answer question and to open questions knowing that
there is a battle ....
I accept every opinion of the askers and avoid conflicts and explanation in details
of every decission that is not clear for one of the sides.
Please understand - war will ruin Experts-Exchange!

Author

Commented:
Hi Schwertner,

I appreciate your post and I apologise for being hostile.  For me Experts-Exchange is a great idea, but (this is not an attack on you as an individual) I never get my answers.  The best I can remember ever getting is an answer for my VB question in C++ code and leaving me to translate it.  However I usually get nothing.

For me it was bad enough not getting a working solution for this problem, but when I post the answer mrjoltcola objected when he had not actually contributed to the solution.  (The order does NOT affect the outcome as he suggested).  This annoyed me.  I do understand the problems with answering questions, as my points for spending are earnt by doing so.  So many posters here ask me to take their source and "fix it for them", and then get hostile when I don't have the time.

I awarded you the points because whilst mrjoltcola contributed a suggestion that had no effect, I was unable to test if your suggestion would work (it might), but I do not have the constant value you suggest I use.  I didnt award you with grade only A because there was no follow-up and did not solve my problem.  If you would like to answer in the other question (with a close-objection) I have posted (you have contributed to) I would be more than award you with full points.

Author

Commented:
"There are 3. So your solution is to a question that was not asked. Unfortunately, the help we give can be no better than the information provided."

So I have to provide the answer? If the code could have been made to work than that would have been great.  To me the objection of mrjoltcola was hostile and invalid.  

I do not care about the points, I was only trying to NOT accept an answer that was incorrect.  What should I have done?  I am happy for a discussion to be opened if that will make things simpler.
Top Expert 2009

Commented:
The key here is I objected to "deletion" and said your solution was useful. Not hostile. I did not object to you awarding points or choosing your own solution.

However, you stated in  your deletion "The problem is then if I want to reference a variable more than once, I will need to add it more than once.  Also I would need to add them in a specific order."

But in your original question, your query had a single parameter. How can we offer a solution with ordering and multiple calls to a query with one parameter? Very unfair to expect that and then criticize us in your closing statement. Your solution to call it more than once is really not the solution, however by posting your final query and solution, I felt it was valuable not to delete it, for future readers.

I only asked that you not delete a valuable solution. I did take offense to your hostile statements when closing the question, since you had changed the query, but that is understandable since you are experiencing some frustration with your code as we all understand. The solution can only be as clear as the question. At some point you changed your SQL locally and did not update the question.  I just ask that in the future you not ridicule or criticize volunteers when you have not posted the correct SQL that would let us give you a multi-parameter solution.

Please, I just suggested that you not delete, I am not sure why you see that as hostile.

Top Expert 2009

Commented:
Hi,

Reviewing the original query, I'm thinking that your original sequence of code was correct, but you used a colon in the name parameter:

  comm.Parameters.Append comm.CreateParameter(":gender", adVarChar, adParamInput, 1)

Should probably be (without the colon):

  comm.Parameters.Append comm.CreateParameter("gender", adVarChar, adParamInput, 1)

The colon is only used in the SQL query itself to notate the parameter.

That was probably the reason for your original error when using named paremeters. I'm sorry I did not notice prior.

Author

Commented:
The problem is then if I want to reference a variable more than once, I will need to add it more than once.  Also I would need to add them in a specific order."
Is the problem with my OWN solution.  I would have accepted an answer with A a single parameter solution.

I have tried both with and without colons.

Author

Commented:
Because I want the question closed

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial