Need HELP with a Select Stored Procedure

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)

Need HELP with a Select Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-09-26 : 08:30:52

John writes "Every select stored procedure I've ever seen on visual basic uses a combo box. I need a select stored procedure that takes an entry from a text box, then returns the record when a command button is clicked.

I need a client to enter a name and password, then when the select stored procedure runs, if the password is correct, the application may resume. How do I call a select stored procedure in vb instead of using this:

If i > 1 Then
rs.Open "Select * from NickNames where NickName='" & txtName.Text & "' and Password='" & txtPass.Text & "'", MyConnection, adOpenKeyset, adLockOptimistic
If rs.RecordCount = 0 Then
MsgBox "Incorrect NickName or Password", vbCritical, "Failure"
Else
cmdEnter.Visible = False
cmdCancel.Visible = False
txtName.Visible = False
txtPass.Visible = False
lblNickName.Visible = False
lblPassword.Visible = False
lblEnjoyYourself.Visible = True
cmdOkay.Visible = True
End If
rs.Close
i = i - 1
Else
MsgBox "Incorrect NickName or Password", vbCritical, "THIRD STRIKE!!!"
End
End If

Please Help,
Thanks--John"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-26 : 10:11:58

First do you want the password failure count to be volatile?
Keeping track in the app means that if he closes the app he has more goes at it - if you hold it in the database then you can lock him out.
You can still keep the same functionality but it gives you more options.

Just call a stored procedure with the name and password as parameters and result as an output parameter.

the SP will be

create proc spTestPassword
@NickName varchar(100) ,
@Password varchar(100)
@Result int output
as

if exists (select * from NickNames where NickName = @NickName and Password = @Password)
begin
select @Result = 1
-- probably want to do some logging here and update failure counts
end
else
begin
select @Result = 0
-- probably want to do some logging here and update failure counts
end
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources