Re: How to retrieve varbinary datatype from VB.NET?

[Ernest Morariu]

Dim myTextPasswrod as string
myTextPassword="bill"
Dim myPasswordArray() as Byte
myPasswordArray=System.Text.Encoding.GetBytes(myTextPassword)
Dim myRow as DataRow
myRow("myPasswordField")=myPasswordArray

Ernest

Hi,
I created a login screen with username and password textboxes. I couldn't

open the next form since i couldn't authenticate the password. The problem
is the password datatype is varbinary in Sql Server DB. Need help on this
one.

 

[Guest]

Thanks Ernest

Here's the code I just modify it a bit to test my code. Everytime I used my username and password that is stored in the db it says authentication failed. The password datatype is varbinary. When I created a new table and have a varchar datatype for the password my code works fine.

Dim conn As New OleDbConnection("Integrated Security=SSPI;Provider=Microsoft OLE DB Provider for SQL Server;Data Source=Sample;Initial Catalog=test;Workstation ID=myworkstation"
Dim cmd As OleDbComman

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Clic
Dim sql = "SELECT UserName, Password FROM Users WHERE Username='" & TextBox1.Text & "' AND Password='" & TextBox2.Text & "'
cmd = New OleDbCommand(sql, conn
conn.Open(
Dim myreader As OleDbDataReader = cmd.ExecuteReade

Tr
If myreader.Read = False The
MessageBox.Show("Authentication failed..."
Els
MessageBox.Show("Login successfully..."
End I
Catch ex As Exceptio
MsgBox(ex.Message
End Tr
conn.Close(
End Su
End Clas

 

[Ernest Morariu]

If you use the varbinay type, then you should download the password of the
user from the db-server and make the comparison on the client side, not on
the db-server side as you did when you used varchar type.

Ernest

Thanks Ernest,

Here's the code I just modify it a bit to test my code. Everytime I used

my username and password that is stored in the db it says authentication
failed. The password datatype is varbinary. When I created a new table and
have a varchar datatype for the password my code works fine.

Dim conn As New OleDbConnection("Integrated

Security=SSPI;Provider=Microsoft OLE DB Provider for SQL Server;Data
Source=Sample;Initial Catalog=test;Workstation ID=myworkstation")

Dim cmd As OleDbCommand

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles Button1.Click

Dim sql = "SELECT UserName, Password FROM Users WHERE Username='"

& TextBox1.Text & "' AND Password='" & TextBox2.Text & "'"

 

[Ernest Morariu]

Note: As you use SqlServer as your back-end, you should consider to use the
classes from the Sql namepsce insted of the ones in the OleDb namespace.

Dim conn As New OleDbConnection("Integrated

Security=SSPI;Provider=Microsoft OLE DB Provider for SQL Server;Data
Source=Sample;Initial Catalog=test;Workstation ID=myworkstation")

Dim cmd As OleDbCommand

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles Button1.Click
Dim dbPassword as String
' the select command is modified
Dim sql = "SELECT UserName, Password FROM Users WHERE Username='"
& TextBox1.Text & "'

cmd = New OleDbCommand(sql, conn)
conn.Open()
Dim myreader As OleDbDataReader = cmd.ExecuteReader Try
myReader=cmd.ExecuteReader

if myReader.HasRows Then
myReader.Read()

dbPassword=System.Text.Encoding.GetString(myReader.GetString(1))
if dbPassword=TextBox2.Text.Trim() Then
Messagebox.Show("Login successfully...")
else
MessageBox.Show("The password is not valid")
endif
else
MessageBox.Show("The username is not valid")
endif