2

I am using this query to get some results from my vbscript in a classic asp page and It does not come back with any values. The page is blank and there are no errors too. Any suggestion please?

dim cmd, admin_no
admin_no = request.QueryString("admin")

set cmd = server.CreateObject("ADODB.Command")
cmd.ActiveConnection = con
sql = ""
sql = sql & "DECLARE @admin_no int;"
sql = sql & "SELECT field1, field2 FROM mydb.mytable where admin_no = @admin_no"
cmd.CommandText = sql
cmd.CommandType = adCmdText
cmd.Parameters.Append cmd.CreateParameter("@admin_no", adInteger, adParamInput, , admin_no)

'response.write(cmd.Parameters(0))
'response.write(cmd.CommandText)

set rs = server.CreateObject("ADODB.Recordset") 
rs.Open cmd
user692942
  • 16,398
  • 7
  • 76
  • 175
Pravi
  • 77
  • 1
  • 8

1 Answers1

3

Problem here is adCmdText uses ? placeholder for passing parameters, change the line

sql = sql & "SELECT field1, field2 FROM mydb.mytable where admin_no = @admin_no"

to

sql = sql & "SELECT field1, field2 FROM mydb.mytable where admin_no = ?"

If you are wanting to pass named parameters to your query you don't have too many options but this little trick is useful.

sql = ""
sql = sql & "DECLARE @admin_no int;"
sql = sql & "SET @admin_no = ?;"
sql = sql & "SELECT field1, field2 FROM mydb.mytable where admin_no = @admin_no"

Useful Links

Community
  • 1
  • 1
user692942
  • 16,398
  • 7
  • 76
  • 175
  • Cheers @Lankymart , It seems to work, but I acutally wanted to pass parameter names in the Sql statment so that its more readable, how do I achieve this? when I say parameter names, I mean in the above sql, I say ...where admin_no=@admin_no instead of admin_no=? – Pravi Mar 22 '16 at 13:01
  • @Pravi I'm fairly sure you can't, ADO doesn't support it. – user692942 Mar 22 '16 at 13:04
  • @Pravi See [Is it possible to use named parameters when command type is adCmdText?](http://stackoverflow.com/a/11788727/692942). I demonstrate the workaround described there in [this example](http://stackoverflow.com/a/36127333/692942). – user692942 Mar 22 '16 at 13:05
  • I will assume there are some rules to it, fieldname should match the name of the createParameter function, am I correct?? – Pravi Mar 22 '16 at 13:09
  • @Pravi There is a lot of information in those links at the end of my answer. But basically unless using `Refresh()` method with `NamedParameters = True` you have to specifiy / append the parameters in order they appear in the SQL. – user692942 Mar 22 '16 at 13:12
  • If I go with the example shown by Cheran ie Is it possible to use named parameters when command type is adCmdText? I get Microsoft OLE DB Provider for SQL Server error '80040e14' Cannot assign a default value to a local variable. – Pravi Mar 22 '16 at 13:23
  • @Pravi That sounds more like a SQL Server error then an Classic ASP one, make sure the syntax you are using is correct. Should be `DECLARE @admin_no AS INT` then `SET @admin_no = ?` then in the rest of the SQL you can use `@admin_no` where needed. – user692942 Mar 22 '16 at 13:26
  • @Pravi Have provided a quick example in [the answer](http://stackoverflow.com/a/36154194/692942). – user692942 Mar 22 '16 at 13:31
  • 1
    If you are really wanting to name your parameters like you suggest why not move this to a stored proc? You have all the naming clarity you are looking for and you have taken a step towards separating the data layer and business layer. It would be a win-win. – Sean Lange Mar 22 '16 at 13:38
  • thank you guys, @Lankymart, you answered my question and Sean Lange, will look into your suggestion. Cheers – Pravi Mar 22 '16 at 14:00