Parameter Markers

[ogri]

Hi

Anyone any experience of using parameter markers in SQL?

Trying to get an ASP app to use these to access a DB2 database (it should be far more efficient than using normal dynamic SQL).

Basically got it working for character fields but I cannot get it to work for numeric fields. For example, this code fragment works:-

set rsY = server.createobject("adodb.recordset")
set cmdY = server.createobject("adodb.command")
s2ql = "Select KEY_DESC from dbeim" & sDbPrefix & ".DB1234 A WHERE A.REFERENCE = ? AND A.KEY_FORMAT = ? AND EFF_TO IS NULL"
cmdY.Parameters.Append cmdY.CreateParameter("@REFERENCE",adChar,adParamInput,len(var1),var1)
cmdY.Parameters.Append cmdY.CreateParameter("@KEY_FORMAT",adChar,adParamInput,len(var2),var2)
cmdY.CommandType = adCmdText
cmdY.CommandText = s2ql
On Error Resume Next
set rsY = cmdY.Execute
if rsY.eof then
Response.Write("<SPAN onMouseover=""showToolTip('" & Replace(Server.HTMLEncode(rsX.fields(8) & " - " & rsX.fields(2) & " - " & rsX.fields(7)),"'","`") & "');"" onMouseOut=""hideToolTip()"">" & txtOutputField & "</SPAN>" & "|")
else
rsY.movefirst
Response.Write("<SPAN onMouseover=""showToolTip('" & Replace(Server.HTMLEncode(rsX.fields(8) & " - " & rsX.fields(2) & " - " & rsX.fields(7)),"'","`") & " - Meaning <I>" & Replace(Server.HTMLEncode(trim(rsY.fields(0))),"'","`") & "</I>');"" onMouseOut=""hideToolTip()"">" & txtOutputField & "</SPAN>" & "|")
end if
rsY.Close
On error goto 0

However on some other code I have some numeric parameters I would like to use (partition numbers for the tables). Using the following line (a seperate SQL call) to add the parameter doesn't work (yet essentially the same code does work in VB6 against an Oracle database).

cmdX.Parameters.Append cmdY.CreateParameter("@REFERENCE",adNumeric,adParamInput,,var1)

Any ideas? Suspect it is something simple, but finding example or help on parameter markers is difficult.

All the best

Keith

 

[ogri]

Hi

Well, found the solution.

Used the following like to set up the numeric parameter:-

cmdX.Parameters.Append cmdX.CreateParameter("@REFERENCE",adInteger ,adParamInput,,Cint(var1))

This seems to have fixed it.

All the best

Keith