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.
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:
Leaders: Leaders are like eagles. We don't have either of them here.
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.
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.
<%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???????????
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 of the Month:
Leaders: Leaders are like eagles. We don't have either of them here.
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.
Sort of like that. However, username is neither an integer nor an output so:
I think I got that right anyway.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.
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.
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
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.
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"))
Quote of the Month:
Leaders: Leaders are like eagles. We don't have either of them here.
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.
line 8 error:
Name redefined
this is line 8 :
Code:Const adCmdStoredProc = &H0004
do you have an include file like adovbs.inc on your page? if so, remove your const lines.
Quote of the Month:
Leaders: Leaders are like eagles. We don't have either of them here.
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.
Bookmarks