SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Other Development Tools
 "ORDER by ?" in parameter query with dynamic SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

goldnhue
Starting Member

4 Posts

Posted - 04/16/2012 :  01:39:42  Show Profile  Reply with Quote
I am building Parameterized dynamic SQL queries (not stored procedures) in a classic ASP application (using SQL Server 2005).
When I try to pass a parameter to the " ORDER BY " clause, I get error "Application uses a value of the wrong type for the current operation". Below is example. What am I doing wrong? Thank you in advance.

' define variable value
fld1="ObjectNum"

' Define query
strSQL="SELECT ObjectNum, ObjName FROM qryVisMultiProcess WHERE ObjectNum >= 0 ORDER BY ?;"

' -- Setup Parameter Query and execute
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = application ("DBConStr")
objCmd.CommandType = adCmdText
objCmd.CommandText=strSQL
objCmd.Parameters(0) = CheckStringParam(fld1)
objCmd.execute(strSQL)
Set objCmd=Nothing

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 04/16/2012 :  07:13:49  Show Profile  Reply with Quote
Don't you need to create the parameter object before you can assign a value to it? The examples on this page seem to suggest so: http://support.microsoft.com/kb/164485
Go to Top of Page

goldnhue
Starting Member

4 Posts

Posted - 04/16/2012 :  10:15:53  Show Profile  Reply with Quote
While not first defining the parameter is less desireable technique, it does work when the query assigns the parameter to a specific column like this:
strSQL="SELECT ObjectNum, ObjName FROM qryVisMultiProcess WHERE ObjName = ?;"

But it fails when the query does not have a specific column to assign the parameter to, like this.
strSQL="SELECT ObjectNum, ObjName FROM qryVisMultiProcess WHERE ObjectNum >= 0 ORDER BY ?;"

So, if I predefine it, what would be the type and size for this parameter?
objCmd.Parameters.Append = objCmd.CreateParameter(fld1, type??, adParamInput, size?? , fld1)

I tried using the data type of the column being passed as below, but it still failed:

fld1="ObjectNum" ' this column is an integer type
strSQL="SELECT ObjectNum, ObjName FROM qryVisMultiProcess ORDER BY ?;"

Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = application ("DBConStr")
objCmd.CommandType = adCmdText
objCmd.CommandText=strSQL
objCmd.Parameters.Append = objCmd.CreateParameter("ObjectNum", adInteger, adParamInput, , fld1)
objCmd.execute(strSQL)

Would the data type correspond to the what SQL uses in the schema for storing all column names, not the definition of the specific column per-se? If yes, how would I get that info?




Edited by - goldnhue on 04/16/2012 10:35:52
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/16/2012 :  14:46:21  Show Profile  Reply with Quote
why do you need the data to be sorted from db itself? You're retrieving the entire dataset then why not do the sorting at your presentation layer? which is front end you're using?

alternatively you can make above query into procedure and add a parameter to that for sorting. then use parameter and add a case..when to vary sorting based on that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

goldnhue
Starting Member

4 Posts

Posted - 04/17/2012 :  14:44:49  Show Profile  Reply with Quote
This application is in Classic ASP. I'm only familiar with bubble sort or quicksort scripts for Classic, which I would not think would be efficient way to go if I can order in the query.

As you suggest, it is probably best to simply create a stored procedures instead.

Thanks much.

(But I sure would love to know how to pass that "Order By" parameter in original scenario, if anyone knows!)

Go to Top of Page

goldnhue
Starting Member

4 Posts

Posted - 04/17/2012 :  22:21:10  Show Profile  Reply with Quote
I found the solution, just needed to construct like normal stored procedure using "CASE", with separate CASE for different data types, and define the parameter as varchar type as below.

fld1="R"
strSQL= "SELECT ObjectNum, ObjName FROM qryVisMultiProcess ORDER BY " &_
"CASE WHEN ?='N' THEN ObjName END, " &_
"CASE WHEN ?='R' THEN ObjectNum END"

' -- Setup Parameter Query
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = application ("DBConStr")
objCmd.CommandType = adCmdText
objCmd.CommandText=strSQL
objCmd.Parameters.Append = objCmd.CreateParameter("ObjName", adVarChar, adParamInput, 100, fld1)
objCmd.Parameters.Append = objCmd.CreateParameter("ObjectNum", adVarChar, adParamInput, 100, fld1)
SET objRS = objCmd.execute(strSQL)
Set objCmd=Nothing
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000