problem with parameter queries

ph0neman · July 1st, 2008, 11:51 AM

I have a stored proc called usp_users in my sql db and I am trying to pass a parameter called @username into it from the following two files and I cant get it to work... any ideas what I am doing wrong?

FORM: test.html

Code:

<form action="parameter_query.asp" method="post">
  Username:  <input type="text" name="username"><br>
<input type="submit">
</form>

QUERY: parameter_query.asp

Code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
    Dim strUser
    strUser = Request.Form("username")

set objConn = Server.CreateObject("ADODB.Connection")
    objConn.ConnectionTimeout = 15
    objConn.CommandTimeout = 30
    objConn.Open "Driver={SQL Server};Server=MYSERVER;UID=USERNAME;PWD=PASSWORD;Language=us_english;Database=testdb;DSN=;"
set objComm = Server.CreateObject("ADODB.Command")
    objComm.ActiveConnection = objConn
    objComm.CommandText = "usp_users"
    objComm.CommandType = 1
set objParam = objComm.CreateParameter("@username",200,1,50)
    objComm.Parameters.Append objParam
    objComm.Parameters("@username") = strUser
set objParam = Nothing
set objRecords = objComm.Execute
    if objRecords.EOF<>True then
       arrOutput=objRecords.GetRows
    end if
set objRecords = Nothing
set objComm = Nothing
    objConn.Close
set objConn = Nothing
%>

<% = username %>

Whenever I run the html file with a parameter in the form it kicks back the parameter_query.asp with the following error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function 'usp_users' expects parameter '@username', which was not supplied.
/ph0neman/parameter_query.asp, line 18

Any help would be appreciated!!! Thanks

Old Pedant · July 1st, 2008, 04:22 PM

I don't see it off top of my head, but why not just shorten the code???

Code:

objComm.Parameters.Append objComm.CreateParameter("@username",200,1,50,strUser)

Of course, the even better way is to get rid of the Command object and Parameter object, altogether.

Code:

<%
...
set objConn = Server.CreateObject("ADODB.Connection")
    objConn.ConnectionTimeout = 15
    objConn.CommandTimeout = 30
    objConn.Open "Driver={SQL Server};Server=MYSERVER;UID=USERNAME;PWD=PASSWORD;Language=us_english;Database=testdb;DSN=;"

set objRecords = Server.CreateObject("ADODB.Recordset")

' yes, you CAN use SP name as a METHOD on the connection object!
' and then if last argument is a recordset object, it receives the RS result
' other arguments are positional arguments to the SP
objConn.usp_users strUser, objRecords

if Not objRecords.EOF then
   arrOutput = objRecords.GetRows
end if

objRecords.Close
set objRecords = Nothing
objConn.Close
set objConn = Nothing
...
%>

ph0neman · July 1st, 2008, 08:57 PM

thanks for the response Old Pedant! I will try that solution in my project tomorrow. I appreciate your feedback:D

EDIT: hey that worked out great thanks a ton!!