Question "There is no row at position 0."??

[Htborn]

I have a problem with my code, I am doing a Login form for students and I have linked the student's details to a database. Now when I enter the correct details, it logs me in, however, when I enter the incorrect details, it says "There is no row at position 0.".

I am using SQL code to link the database with Visual Basic.

Here is my code:
Private Sub BtnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnLogin.Click
Dim sql As String
sql = " SELECT * FROM LoginDetails WHERE UsernameID = '" & TxtUsername.Text & "' AND Password = '" & TxtPassword.Text & "'"
ds = db.sqlSelect(sql)
Dim i As Integer
Dim Username As String = ds.Tables("LoginDetails").Rows(i)("UsernameID")
Dim Password As String = ds.Tables("LoginDetails").Rows(i)("Password")
''''''STUDENT LOGIN'''''''

If TxtUsername.Text = "" And TxtPassword.Text = "" Then
MsgBox("No username and password entered!")
ElseIf TxtUsername.Text = "" Then
MsgBox("No username entered!")
ElseIf TxtPassword.Text = "" Then
MsgBox("No password entered!")
End If
Username = TxtUsername.Text.ToLower
Password = TxtPassword.Text.ToLower

If TxtUsername.Text.ToLower = Username And TxtPassword.Text = Password Then
FrmMainMenu.Show()
Me.Hide()
FrmMainMenu.LblWelcome.Text = "Welcome " & ds.Tables("LoginDetails").Rows(i)("Student Name") & "!"
ElseIf TxtUsername.Text.ToLower = Username And TxtPassword.Text <> Password Then
MsgBox("Wrong password entered!")
End If

If TxtUsername.Text.ToLower <> Username And TxtPassword.Text <> Password Then
MsgBox("Wrong password or username!")
Else
End If
If Len(Username) <> 7 Then
MsgBox("Username must be exactly 7 characters long and must be in the following format: 1XlXXXX")
End If
If Len(Password) < 6 And Len(Password) > 30 Then
MsgBox("Password must be between 6 and 30 characters!")
End If

 

[jmcilhinney]

Firstly, please don't paste code as HTML. The syntax highlighting helps but it is still hard to read because of the excessive empty space and the lack of indenting. Please post as plain text like this:

[xcode=vb]your code here[/xcode]

As for the question, I haven't read your code but I know what that error message means. You're trying to access the item at index 0 in the Rows collection of a DataTable and there is no such item, i.e. the DataTable has no rows in it.

 

[Hoogie]

I'm no expert programmer, but I believe the problem lies here:

[COLOR=#333333][FONT=Verdana]sql = " SELECT * FROM LoginDetails WHERE UsernameID = '" & TxtUsername.Text & "' AND Password = '" & TxtPassword.Text & "'"[/FONT][/COLOR]
[FONT=Verdana][SIZE=2][COLOR=#333333]ds = db.sqlSelect(sql)[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]

[/COLOR][/SIZE][/FONT]

You are filling your dataset only with the username/password combination that matches the user input. Meaning if the user enters "fhdreteswarfa" as a username/password, your program is trying to find that record in your database and place that in your dataset. However, because that record probably doesn't exist, your dataset won't have any records in it.

 

[Htborn]

Thanks, do you know any way to fix the problem?

 

[Hoogie]

There are multiple ways you can fix it.

A: Change your SQL statement to grab everything in the table (eliminate where clauses), and do your username/password checks against the entire table.
B: Put in a Try, Catch that will let the user know that there is no username/password combination that matches the user input.
C: Count the records in the data that's been pulled and if it is equal to 0 then you know the username/password combination failed.
Etc.

Personally, I would cycle through the data looking for the username/password combination from user input. Something like this, it can be cleaned up more however. I also didn't test but that should give you the right idea.

[COLOR=#333333]Private Sub BtnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnLogin.Click[/COLOR]
[COLOR=#333333]Dim sql As String[/COLOR]
[COLOR=#333333]sql = " SELECT * FROM LoginDetails"[/COLOR]
[COLOR=#333333]ds = db.sqlSelect(sql)[/COLOR]
[COLOR=#333333]Dim i As Integer[/COLOR]
[COLOR=#333333]Dim Username As String[/COLOR]
[COLOR=#333333]Dim Password As String[/COLOR]
[COLOR=#333333]''''''STUDENT LOGIN'''''''[/COLOR]

i = 0

Do Until i = ds.Tables("LoginDetails").rows.count - 1
Username = [COLOR=#333333]ds.Tables("LoginDetails").Rows(i)("UsernameID")
[/COLOR]Password = [COLOR=#333333]ds.Tables("LoginDetails").Rows(i)("Password")[/COLOR]
[COLOR=#333333]If TxtUsername.Text = "" And TxtPassword.Text = "" Then[/COLOR]
[COLOR=#333333]MsgBox("No username and password entered!")[/COLOR]
[COLOR=#333333]ElseIf TxtUsername.Text = "" Then[/COLOR]
[COLOR=#333333]MsgBox("No username entered!")[/COLOR]
[COLOR=#333333]ElseIf TxtPassword.Text = "" Then[/COLOR]
[COLOR=#333333]MsgBox("No password entered!")[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]Username = TxtUsername.Text.ToLower[/COLOR]
[COLOR=#333333]Password = TxtPassword.Text.ToLower[/COLOR]

[COLOR=#333333]If TxtUsername.Text.ToLower = Username And TxtPassword.Text = Password Then[/COLOR]
[COLOR=#333333]FrmMainMenu.Show()[/COLOR]
[COLOR=#333333]Me.Hide()[/COLOR]
[COLOR=#333333]FrmMainMenu.LblWelcome.Text = "Welcome " & ds.Tables("LoginDetails").Rows(i)("Student Name") & "!"[/COLOR]
[COLOR=#333333]ElseIf TxtUsername.Text.ToLower = Username And TxtPassword.Text <> Password Then[/COLOR]
[COLOR=#333333]MsgBox("Wrong password entered!")[/COLOR]
[COLOR=#333333]End If[/COLOR]

[COLOR=#333333]If TxtUsername.Text.ToLower <> Username And TxtPassword.Text <> Password Then[/COLOR]
[COLOR=#333333]MsgBox("Wrong password or username!")[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]If Len(Username) <> 7 Then[/COLOR]
[COLOR=#333333]MsgBox("Username must be exactly 7 characters long and must be in the following format: 1XlXXXX")[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]If Len(Password) < 6 And Len(Password) > 30 Then[/COLOR]
[COLOR=#333333]MsgBox("Password must be between 6 and 30 characters!")[/COLOR]
End If
i = i + 1
Loop