0

I have a parameterized query which is giving

"ORA-01008: not all variables bound" error.

Dim Conn
Dim Cmd
Dim RS

Dim strID
Dim param

strID = Request.QueryString("id") 
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConnect    


Set Cmd = Server.CreateObject("ADODB.Command")


Cmd.CommandText = "SELECT column_name FROM table WHERE (id = :id)"
Set param = Cmd.CreateParameter("id", adVarChar , adParamInput ,50 , strID)
Cmd.Parameters.Append param
Cmd.CommandType = adCmdText

Set Cmd.ActiveConnection = Conn


Set RS = Cmd.Execute()

I'm trying to modify in syntax in several ways, then it is giving

ORA-00936: missing expression

Please help me to get out of this. For your information, there is no problem with connection as i am able to connect with normal query.

user692942
  • 16,398
  • 7
  • 76
  • 175
Anil Kumar
  • 21
  • 6
  • Admittedly, I don't use Oracle but is the provider syntax any different then other ADODB providers when using `adCmdText`? Have you tried `?` instead of `:id` in your SQL string? – user692942 Sep 26 '17 at 06:41
  • Thanks for your response, I'm not sure about the syntax. I have tried `?` as suggested but still facing "ORA-01008: not all variables bound" error – Anil Kumar Sep 26 '17 at 06:52
  • Just to confirm, you tried `Cmd.CommandText = "SELECT column_name FROM table WHERE (id = ?)"` without success? – user692942 Sep 26 '17 at 07:14
  • Yes, @Lankymart – Anil Kumar Sep 26 '17 at 07:17
  • @Lankymart , Can you please help me to get out of this. – Anil Kumar Sep 26 '17 at 11:10
  • As I don't use Oracle myself the best I can do is point you to [a wealth of information on that exact error on Google](https://www.google.co.uk/search?safe=off&rlz=1C1CHBF_en-GBGB725GB725&q=ORA-01008%3A+not+all+variables+bound+adodb&oq=ORA-01008%3A+not+all+variables+bound+adodb&gs_l=psy-ab.3...195830.197739.0.198039.6.6.0.0.0.0.142.592.4j2.6.0....0...1.1.64.psy-ab..0.5.509...0j0i67k1j0i22i30k1j33i21k1j33i160k1.0.1hzZUjewr0w). – user692942 Sep 26 '17 at 11:35
  • Hopefully one of the many suggestions will help you find the cause, the problem is the error can have umpteen causes from fields not being nullable to a corrupt index on a table. – user692942 Sep 26 '17 at 11:35

2 Answers2

0

a few things to check:

1) try hard coding a value for strID, so instead of:

strID = Request.QueryString("id")

try

strID = 100

2) double check your column definitions and make sure you're selecting from a varchar(50) field

3) make sure you have adovbs.inc referenced on your page for the ADO constants definitions

Josh Montgomery
  • 882
  • 5
  • 10
0

Thanks @Lankymart, luckily i got solution for this as below. It is working fine for me and sorry for the delay in posting the answer, my issue resolved 2 hours ago. 

Dim Conn
Dim Cmd
Dim RS

Dim strID
Dim param

strID = Request.QueryString("id") 
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConnect    
Set Cmd = Server.CreateObject("ADODB.Command")

With Cmd
.CommandText = "SELECT column_name FROM table WHERE id = ?"
.Parameters.Append  .CreateParameter(,200, 1 ,50 ,strID)
 Set .ActiveConnection = Conn
End With

Set RS = Cmd.Execute()
Anil Kumar
  • 21
  • 6
  • As Josh [mentions at point 3.](https://stackoverflow.com/a/46428521/692942), it will be the lack of the named constants like `adCmdText`, `adVarChar` etc being defined. I didn't mention it though because it should have caused an error before reaching Oracle, because `Cmd.CommandType = adCmdText` I would also have expected it to fail. Look at [this answer](https://stackoverflow.com/a/35530750/692942) for how to solve it using METADATA without having to remove the named constants. – user692942 Sep 26 '17 at 18:23