DeveloperBarn

mehere · #21 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 · #22 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 · #23 June 23rd, 2009, 11:29 AM

Quote:

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 · #24 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 · #25 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 · #26 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 · #27 June 23rd, 2009, 12:01 PM

Quote:

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"))

Quote:

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 · #28 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 · #29 June 23rd, 2009, 12:08 PM

line 8 error:

Name redefined

this is line 8 :

Code:

Const adCmdStoredProc = &H0004

mehere · #30 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 Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
SQL Job or stored procedure	todd2006	SQL Development	5	February 11th, 2009 02:20 PM
stored procedure	todd2006	ASP Development	7	February 5th, 2009 03:02 PM
stored procedure	todd2006	Microsoft SQL Server	1	February 5th, 2009 01:25 PM
If statement stored procedure	peebman2000	SQL Development	32	May 23rd, 2008 03:54 PM
Dynamic Stored Procedure	jmurrayhead	Microsoft SQL Server	16	March 26th, 2008 11:19 AM

DeveloperBarn

Recordset and Stored Procedure issues!

Recordset and Stored Procedure issues!

Similar Threads