|
Subject:
|
problem with parameter queries
|
|
Posted By:
|
ph0neman
|
Post Date:
|
7/1/2008 11:51:15 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
<form action="parameter_query.asp" method="post">
Username: <input type="text" name="username"><br>
<input type="submit">
</form>
QUERY: parameter_query.asp
<%@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
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
7/1/2008 4:22:56 PM
|
I don't see it off top of my head, but why not just shorten the 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.
<%
...
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
...
%>
|
|
Reply By:
|
ph0neman
|
Reply Date:
|
7/1/2008 8:57:25 PM
|
thanks for the response Old Pedant! I will try that solution in my project tomorrow. I appreciate your feedback
EDIT: hey that worked out great thanks a ton!!
|
