Stored Procedures Help

Results 1 to 2 of 2

Thread: Stored Procedures Help

  1. #1
    Join Date
    Dec 1969

    Default Stored Procedures Help

    I&#039;m having a LOT of trouble with stored procedures. I&#039;ve tried several tutorials but they none of them work.<BR><BR>I have two inputs -- username and password -- and I want to get an output of the userid for the person who has that username and password. Here&#039;s what I have so far:<BR><BR>set CmdSP = Server.CreateObject("ADODB.Command")<BR>CmdSP.Acti veConnection = "Driver={SQL Server};server=localhost;Uid=sa;Pwd=;Database=soft switch_copy"<BR>CmdSP.CommandText = "dms_checkuser"<BR>CmdSP.CommandType = adCmdSPStoredProc<BR>CmdSP.Parameters.Append CmdSP.CreateParameter ("@username",adVarChar,adParamInput,255)<BR>CmdSP. Parameters.Append CmdSP.CreateParameter ("@password",adVarChar,adParamInput,255)<BR>CmdSP. Parameters.Append CmdSP.CreateParameter ("@content_user_id",adInteger,adParamOutput,4)<BR> CmdSP.Execute<BR><BR>strContentUserID = CmdSP.Parameters("@content_user_id").Value<BR><BR> Response.write strContentUserID<BR><BR><BR><BR>And the stored procedure:<BR><BR>CREATE PROCEDURE dbo.dms_checkuser<BR>(<BR>@username varchar(255),<BR>@password varchar(255),<BR>@content_user_id int OUTPUT<BR><BR>)<BR>AS<BR>SELECT content_user_id = @content_user_id FROM tblContentUser WHERE content_user_email = @username<BR> AND content_user_password = @password<BR><BR>GO<BR><BR><BR><BR>Obviously, I want to print the user&#039;s ID (just for testing purposes)<BR><BR>I keep getting the following error: "Error Type:<BR>Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)<BR>[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure &#039;dms_checkuser&#039; expects parameter &#039;@username&#039;, which was not supplied.<BR>/test/sp.asp, line 147"<BR><BR><BR>How do I add parameters with this kind of stored procedure? The tutorial never bothered to explain that :(

  2. #2
    Join Date
    Dec 1969

    Default Chuckle

    couple of things wrong, you aren&#039;t passing in your input parameters:<BR><BR><BR>CmdSP.Parameters.Append CmdSP.CreateParameter ("@username",adVarChar,adParamInput,255,"my username")<BR><BR>second is that your query itself is backwards. Should be<BR><BR>SELECT @content_user_id = content_user_id FROM tblContentUser WHERE content_user_email = @username <BR>AND content_user_password = @password <BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts