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.SqlDa taReader
Dim sSQL As String

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

Dim oCommand As New SqlClient.SqlCo mmand()
Try
oCommand.Comman dText = sSQL
oCommand.Connec tion = DatabaseConnect ion
oCommand.Comman dType = CommandType.Tex t
oDR = oCommand.Execut eReader(Command Behavior.Single Result)
Dim intRA As Integer = oDR.RecordsAffe cted()
Catch oX As Exception

MessageBox.Show (oX.Message)

Finally
oCommand = Nothing
End Try

Thanks in advance,


Bill

[Alien2_51]

RE: Return error codes from Sql Server

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_ValidateLog in
@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
"William Oliveri" wrote:
[color=blue]
> 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.SqlDa taReader
> Dim sSQL As String
>
> sSQL = "SELECT password from tbl_users WHERE (username ='" &
> Trim(Me.txtUser Name.Text) & "')"
>
> Dim oCommand As New SqlClient.SqlCo mmand()
> Try
> oCommand.Comman dText = sSQL
> oCommand.Connec tion = DatabaseConnect ion
> oCommand.Comman dType = CommandType.Tex t
> oDR = oCommand.Execut eReader(Command Behavior.Single Result)
> Dim intRA As Integer = oDR.RecordsAffe cted()
> Catch oX As Exception
>
> MessageBox.Show (oX.Message)
>
> Finally
> oCommand = Nothing
> End Try
>
> Thanks in advance,
>
>
> Bill
>
>
>[/color]