Login lesson - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Author

Topic

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-01 : 13:21:42

I have a login page I am working on. One little problemo. I pass in a UserName and Password. If it checks, I want to return out of the sproc a varchar field.
I initially was using a ExecuteScalar with vb.net. But for some reason, even when it did return the field I wanted, the value of ExecuteScalar kept coming up -1.
So I figured I would pass in another parameter(output) and get a rowcount. If the rowcount is 1, then it can pass out the field to me.
Create Procedure LoginTest @uname varchar(50),@upassword binary(16),@cname varchar(50) OUTPUT, @rowCount SMALLINT OUTPUT As
Select @rowCount = COUNT (*) FROM userstable Where uname = @uname AND upassword = @upassword
If @rowCount = 1
BEGIN
SELECT cname FROM userstable Where uname = @uname AND upassword = @upassword
SET @cname = cname
END
ELSE
BEGIN
SET @cname = ''
END
For some reason, the SET @cname = cname won't be allowed. At the cname word. If I hard code cname to 'jake', it works.
And I also figure my coding is probably not the best anyway. And since it is short, it may not be a big aggravation for somebody to critique it.
Thank you.
John
I have tried (), [], around cname. Like to know what does not compute here.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-01 : 13:26:02

Change your SELECT to this:
SELECT @cname = cname
FROM userstable
Where uname = @uname AND upassword = @upassword

And remove the SET command because the SET command does not know about the previous SELECT. You set @cname to cname in the SELECT statement.

Which varchar field do you want to output from the stored proc?

Tara

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-01 : 13:28:06

Thank you very much. @cname is the varchar that gets outputted. @recCount is a smallint that gets outputted also. Thanks again. Will digest this.

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-05-01 : 13:31:35

In .net you need something like this to get that parameter back:

This is in VB.Net


  oCmd.Connection.Open()
  oCmd.ExecuteNonQuery()
  Return oCmd.Parameters("@cname").Value

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-01 : 14:07:07

Michael, that is exactly what I have. I am a little confused about one thing.
I did have a short var that I assigned cmd.executescalar(). Even when I successfully logged in and I could check and see that the varchar was being passed back out, the value of that short var was -1. That is why I went to plan b and had a smallint return the row count.
Thanks for all the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources