Return error codes from Sql Server

[William Oliveri]

Hi all,

I using Inline Sql statements to validate username and password against
sql server. I want to catch any errors from Sql server such as Username not
known, Password incorrect, etc. Is there anyway to do this? Below is the
code I'm using:


Dim oDR As SqlClient.SqlDataReader
Dim sSQL As String

sSQL = "SELECT password from tbl_users WHERE (username ='" &
Trim(Me.txtUserName.Text) & "')"

Dim oCommand As New SqlClient.SqlCommand()
Try
oCommand.CommandText = sSQL
oCommand.Connection = DatabaseConnection
oCommand.CommandType = CommandType.Text
oDR = oCommand.ExecuteReader(CommandBehavior.SingleResult)
Dim intRA As Integer = oDR.RecordsAffected()
Catch oX As Exception

MessageBox.Show(oX.Message)

Finally
oCommand = Nothing
End Try

Thanks in advance,


Bill

 

[Guest]

SQL Server won't know if your database logins password is incorrect you'll
have to build logic to determine that. You should use a stored procedure that
returns an output parameter indicating a reason code. Here's an example

CREATE PROC esp_ValidateLogin
@pUser VARCHAR(50),
@pPwd VARCHAR(50),
@pReason INT OUTPUT
AS
SELECT UserID from tbl_users WHERE username = @pUser and password = @pPwd
if @@ROWCOUNT = 0
BEGIN
SET @pReason = 1 --default to bad password
DECLARE @UserExists
SELECT @UserExists = COUNT(*) WHERE username = @pUser
IF @UserExists = 0
SET @pReason = 2 --bad user name
END

You should