My db access code is like following:
set recordset = Server.CReateObject("ADODB.Recordset")
set cmd1 = Server.CreateObject("ADODB.Command")
cmd1.ActiveConnection = Conn //connection object already created
cmd1.CommandText = "SELECT * FROM lbr_catmaster where catname = ?"
cmd1.CommandType = adCmdText
set prm = cmd1.CreateParameter("@prm", 200, 1,200 , "development")
cmd1.Parameters.Append prm
set recordset = cmd1.Execute
But there is no db hit going. Please help with this. I am using sql server 2005.
Thanks.
In my code, this is how I get a recordset from a command:
Set rs = server.createobject("ADODB.Recordset")
Set cmd = server.createobject("ADODB.Command")
cmd.ActiveConnection = Conn //connection object already created
cmd.CommandText = "SELECT * FROM lbr_catmaster where catname = ?"
cmd.CommandType = adCmdText
cmd.CommandTimeout = 900
set prm = cmd1.CreateParameter("@prm", 200, 1, 200, "development")
cmd.Parameters.Append prm
' Execute the query for readonly
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenForwardOnly, adLockReadOnly
Hope it helps
Looks like you aren't referencing your named parameter correctly in your query.
Try replacing:
cmd1.CommandText = "SELECT * FROM lbr_catmaster where catname = ?"
with:
cmd1.CommandText = "SELECT * FROM lbr_catmaster where catname = @prm"
and see if that helps.
I like using Parameters.Refresh, i.e.
set recordset = Server.CReateObject("ADODB.Recordset")
set cmd1 = Server.CreateObject("ADODB.Command")
cmd1.ActiveConnection = Conn //connection object already created
cmd1.CommandText = "SELECT * FROM lbr_catmaster where catname = ?"
cmd1.CommandType = adCmdText
cmd1.Prepared = True ' only needed if u plan to reuse this command often
cmd1.Parameters.Refresh
cmd1.Parameters(0).Value = "development"
set recordset = cmd1.Execute
Try leaving off the parameter name:
set prm = cmd1.CreateParameter(, 200, 1,200 , "development")