This is a discussion on Recordset and Stored Procedure issues! within the ASP Development forums, part of the Programming & Scripting category; i believe it should. i mean, i've run stored procs like you're trying and have not had an issue, but ...
| |||||||
|
#21
| ||||
| ||||
| 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.
__________________ Quote of the Month: Mistakes: It could be that the purpose of your life is only to serve as a warning to others. Questions to Ponder: Why do banks charge you a "non-sufficient funds fee" on money they already know you don't have? iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm") copyright © 2008 sbenj69 Sarchasm: The gulf between the author of sarcastic wit and the person who doesn't get it. |
|
#22
| ||||
| ||||
| 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.
__________________ Wolffy ------------------------ Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Rework for your specific environment may be required. Void where prohibited by law. Not valid in California. Your mileage may vary. |
|
#23
| ||||
| ||||
| Quote:
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??????????? |
|
#24
| ||||
| ||||
| 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"))
|
|
#25
| ||||
| ||||
| 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 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] Last edited by Wolffy; June 23rd, 2009 at 11:37 AM. |
|
#26
| ||||
| ||||
| 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
|
|
#27
| ||||
| ||||
| Quote:
Quote:
|
|
#28
| ||||
| ||||
| 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"))
|
|
#29
| ||||
| ||||
| line 8 error: Name redefined this is line 8 : Code: Const adCmdStoredProc = &H0004 |
|
#30
| ||||
| ||||
| do you have an include file like adovbs.inc on your page? if so, remove your const lines. |
![]() |
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
| |
| ||||
| 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 |