Recordset and Stored Procedure issues!

**mehere** · mehere, June 23rd, 2009 11:17 AM

i believe it should. i mean, i've run stored procs like you're trying and have not had an issue, but i have switched to doing the command object. it's better ... and it helps to prevent sql injection as well.

**Wolffy** · Wolffy, June 23rd, 2009 11:29 AM

Well there ya are then, that's a right mess. Is should be:
Exec spTestEdit1 'test@test.com'. If the user name is be retrieved as test@test.com then all you need do is: [b]sql = "Exec spTestEdit1 'test@test.com'

[footnote] Sorry, got interrupted with a production problem and there were a few posts while I was gone.

**Centurion** · Centurion, June 23rd, 2009 11:29 AM

Originally Posted by mehere

i believe it should. i mean, i've run stored procs like you're trying and have not had an issue, but i have switched to doing the command object. it's better ... and it helps to prevent sql injection as well.

<%Option explicit

Dim cmd, rs, connect, intNumber
Const adCmdStoredProc = &H0004
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adVarChar = 200
Const adInteger = 3

Set cmd = Server.CreateObject ("ADODB.Command")
connect = "Provider=SQLOLEDB;Persist Security Info=False;User ID=sa; &;Password=test;" &_
"Initial Catalog=coursesSQL;Data Source=xxxxxxxxxx"

cmd.ActiveConnection = connect
cmd.CommandText = "spTestEdit1 "
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter &_
("@username",adInteger,adParamOutput)
Set rs = cmd.Execute

intNumber = comm.Parameters("@username")
set cmd = nothing
%>

i place this? in my asp page???????????

**mehere** · mehere, June 23rd, 2009 11:32 AM

since @username is an email address, you wouldn't use adInteger, but instead adVarChar along with the number of characters. something like this:

Code:

cmd.Parameters.Append cmd.CreateParameter &_
("@username",adVarChar,50,adParamOutput,session("username"))

**Wolffy** · Wolffy, June 23rd, 2009 11:34 AM

Sort of like that. However, username is neither an integer nor an output so:

Code:

cmd.Parameters.Append cmd.CreateParameter &_
      ("@username",advarchar,adParamInput, 50)

I think I got that right anyway.

I would then have the stored procedure return the primary key value for the username (you do have a PK on this table right, which should be an identity column) as an output parameter, or have it return -1 for user not found.

[edit] and I like mehere's better, since it sets the value of the parameter too and saves a step.[/edit]

**Wolffy** · Wolffy, June 23rd, 2009 11:42 AM

Thus, your stored procedure would become something like:

Code:

Create procedure spTestEdit1  (
    @username varchar(20)
  , @userID   int output
)
as
    Set @userID = NULL
    SELECT  @userID = [TEST-1]
     FROM [dbo].[TEST-1]
    WHERE username=@username

    If @userID is Null Set @userID = -1

**Centurion** · Centurion, June 23rd, 2009 12:01 PM

Originally Posted by mehere

since @username is an email address, you wouldn't use adInteger, but instead adVarChar along with the number of characters. something like this:

Code:

cmd.Parameters.Append cmd.CreateParameter &_
("@username",adVarChar,50,adParamOutput,session("username"))

Originally Posted by Wolffy

Sort of like that. However, username is neither an integer nor an output so:

Code:

cmd.Parameters.Append cmd.CreateParameter &_
      ("@username",advarchar,adParamInput, 50)

I think I got that right anyway.

I would then have the stored procedure return the primary key value for the username (you do have a PK on this table right, which should be an identity column) as an output parameter, or have it return -1 for user not found.

[edit] and I like mehere's better, since it sets the value of the parameter too and saves a step.[/edit]

im lost here, which one do i use???????

**mehere** · mehere, June 23rd, 2009 12:03 PM

using mine will pass in the value for the parameter ... also, i transposed 2 of the values, and used output instead of input ... see changes below.

Code:

cmd.Parameters.Append cmd.CreateParameter &_
("@username",adVarChar,adParamInput,50,session("username"))

**Centurion** · Centurion, June 23rd, 2009 12:08 PM

line 8 error:

Name redefined

this is line 8 :

Code:

Const adCmdStoredProc = &H0004

**mehere** · mehere, June 23rd, 2009 12:13 PM

do you have an include file like adovbs.inc on your page? if so, remove your const lines.

Thread: Recordset and Stored Procedure issues!

LinkBack

Thread Tools

Display

Similar Threads

SQL Job or stored procedure

stored procedure

stored procedure

If statement stored procedure

Dynamic Stored Procedure

Bookmarks

Bookmarks

Posting Permissions