user3172293 user3172293 - 1 year ago 58 Question

Row data into string conversion 2010

How can I convert a row data into string or text and display it into a label? My problem is when I click on my login button which contains the SQL code that gains a row data into alabel, the result in my label is false. not the text. How can I convert it into string?

Here's my code:

Private Sub cmdLog_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLog.Click
Dim connection As New SqlClient.SqlConnection
Dim command As New SqlClient.SqlCommand
Dim adaptor As New SqlClient.SqlDataAdapter
Dim dataset As New DataSet
Dim reader As MySqlDataReader = Nothing
Dim sapi
sapi = CreateObject("sapi.spvoice")

connection.ConnectionString = ("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Calupad\Desktop\HTF feat Yiyet\HTF feat Yiyet\Database1.mdf;Integrated Security=True;User Instance=True")
command.CommandText = "SELECT * FROM [Users] WHERE Username='" & txtUser.Text & "' AND Password ='" & txtPass.Text & "';"
txtWel.Text = "Welcome Back, " + txtUser.Text + "!....."

command.Connection = connection
adaptor.SelectCommand = command
adaptor.Fill(dataset, "0")

txtStat.text = command.CommandText = "SELECT Status FROM [Users] WHERE Username = '" & txtUser.Text & "' ".ToString

txtStat.Text = stat
Dim count = dataset.Tables(0).Rows.Count
If count > 0 Then

MsgBox("Login Successful!" & vbNewLine & txtStat.Text, MsgBoxStyle.Information, "Access Granted")
tries = 3
ctr = tries - 1
tries = ctr
sapi.speak(txtUser.Text + txtNot.Text)
MsgBox("Invalid Account!" + vbNewLine + "Attempts Remaining: " & tries, vbCritical, "Access Denied")
If tries = 0 Then
MsgBox("You've reached the maximum attempts!" + vbNewLine + "The program will be terminated.", vbCritical, "Terminated!")
End If
End If
End Sub

Answer Source

First of all, the way you check for username and password is weak and is most certainly volnurable to SQL injections. You are checking if the 'count' of rows is greater than zero then the user has logged in successfully, where as you should only compare count to 1. and instead of counting the rows, try to compare the row values to what the user has input in the username and passoword fields and what is returned from the database rows.

The "hacker" can simply type this and he will be allowed to log in according to the logic of your code:


You just need to retrieve the data stored into dataset variable that you filled using the adapter.

Assuming your database table contains fields like First_Name and 'Last_Name', here is how you can display them on any label control on your form:

adaptor.Fill(dataset, "0")
myFirstName.Text = dataset.Tables(0).Rows(0).Item("First_Name").ToString()
myLastName.Text = dataset.Tables(0).Rows(0).Item("First_Name").ToString()

You can also retrieve the column without having to know its name like this

myLabel.text = = dataset.Tables(0).Rows(0).Item(3).ToString() 
    'This will retrieve the 4th column from the table (zero based array)

You can also clean up your code by declaring a variable to hold the retrieved table

adaptor.Fill(dataset, "0")
Dim myTable as DataTable = dataset.Tables(0)
myFirstName.Text = myTable.Rows(0).Item(0).ToString()

Hope this helps