How to handle empty dataset?

Question

I am making Log in page for my project but I am getting an error "There is no row at position 0" while running. I tried these lines of codes.

Imports System.Data.SqlClient
Imports System.Data

Partial Class Dept_login
    Inherits System.Web.UI.Page

    Protected Sub BtnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnSubmit.Click
        Dim ds As New DataSet
        'Try
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("VMSConnectionString").ConnectionString)
        con.Open()
        Dim cmd As New SqlCommand("SELECT password FROM Dept_login WHERE user_id='" + Txtuname.Text + "'", con)
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(ds)

        If Not IsDBNull(ds) Then
            If Txtpwd.Text = ds.Tables(0).Rows(0).Item("password") Then
                Response.Redirect("Online Services.aspx") 'the page i want to redirect to after login successful
            Else
                Label1.Visible = True 'initially visible is false and text is INVALID PASSWORD
            End If

            con.Close()
            ' Catch ex As Exception

            ' End Try
        End If
    End Sub

    Private Function Dept_login() As Integer
        Throw New NotImplementedException
    End Function

End Class

plz help me now.i need to show this project to my teacher tomorrow. — user3030098, Dec 12 '13 at 15:59

John Gibb · Answer 1 · 2013-12-12T16:25:52.023

1

This line doesn't make sense:

If Not IsDBNull(ds) Then

ds will never be DBNull. Instead, check for the number of rows coming back, like:

If ds.Tables(0).Rows.Length > 0 Then

You're trying to get the first row (.Rows(0)) when there aren't any - that's what the error is telling you.

Try using something like this:

    If ds.Tables(0).Rows.Count > 0 AndAlso Txtpwd.Text = ds.Tables(0).Rows(0).Item("password") Then
        Response.Redirect("Online Services.aspx", False) 'the page i want to redirect to after login successful
        Context.ApplicationInstance.CompleteRequest();
    Else
        Label1.Visible = True 'initially visible is false and text is INVALID PASSWORD
    End If

    con.Close()
    ' Catch ex As Exception

    ' End Try

(Note: You should use parameterization for the SQL query. You're leaving yourself open to a SQL injection attack.)

edited Dec 12 '13 at 16:25

answered Dec 12 '13 at 15:58

John Gibb

10,603
2
37
48

please can you help me in telling this in a simple way coz i am very new to asp.net,actually just learning it.so kindly can you tel me where should i put these changes and what.thank you. – user3030098 Dec 12 '13 at 16:04
I thought I did. Change the first "if" statement in my answer to the second. – John Gibb Dec 12 '13 at 16:06
but its showing length is not a member of 'system.data.datarowcollection'.for the last time can you help with this.hehe – user3030098 Dec 12 '13 at 16:10
Also see the redirect code change I made based on the answer in this question http://stackoverflow.com/questions/2777105/why-response-redirect-causes-system-threading-threadabortexception – John Gibb Dec 12 '13 at 16:30
i am sorry to say but what you is working but it only showing message when the password is wrong but other than if it is correct its doing anything.but thank you for your help – user3030098 Dec 12 '13 at 16:38
need more info to help. – John Gibb Dec 12 '13 at 16:42
Ok ill tell you in detail.i am making a login page where i kept two textboxes for user name and password.so i am coing if the sign in is successful than the user will get redirected to another page or it will give an error message.but now it is not giving any error but it is not working as it should be – user3030098 Dec 12 '13 at 16:50
It sounds like you need to use the original `Response.Redirect` (without the false). The `ThreadAbortException` is expected, so you can probably just ignore it. – John Gibb Dec 12 '13 at 16:53

score 0 · Answer 2 · answered Dec 12 '13 at 16:08

0

This line doesn't make sense:

If Not IsDBNull(ds) Then

Makes sense

  If ds.Tables(0).Rows.count > 0 andalso ds.Tables..count > 0 Then

END IF

Hope this helps

answered Dec 12 '13 at 16:08

Amarnath Balasubramanian

9,300
8
34
62

when i tried your code its running fine but its not doing what it should do that is to take me to other page after success login or if invalid password rhan one msg.these are the msgs i am getting A first chance exception of type 'System.Threading.ThreadAbortException' occurred in mscorlib.dll An exception of type 'System.Threading.ThreadAbortException' occurred in mscorlib.dll but was not handled in user code – user3030098 Dec 12 '13 at 16:20
Response.Redirect("Online Services.aspx",false) add this hope this helps – Amarnath Balasubramanian Dec 12 '13 at 16:27
i am sorry but not working.as i am a learner to asp.net so i am not able to correct it with your suggestions.but thank you' – user3030098 Dec 12 '13 at 16:39

score 0 · Answer 3 · answered Dec 12 '13 at 20:34

Did you try using a datareader instead of a dataadapter?

    Try
        Dim datare As SqlDataReader
        Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings("VMSConnectionString").ConnectionString)
            Using cmd As New SqlCommand("SELECT password FROM Dept_login WHERE user_id='@User'", cn)
                cmd.Parameters.AddWithValue("@User", Txtuname.Text)
                cn.Open()
                datare = cmd.ExecuteReader()
                With datare
                    If .Read() Then
                        If .Item(0) = txtpwd.Text Then
                            Response.Redirect("Online Services.aspx")
                        Else
                            Label1.Visible = True
                        End If
                    End If
                End With
            End Using
        End Using
    Catch ex As Exception
        Throw ex
    End Try

Also, on the query you write user_id. did you mean username? Is you query correct?

How to handle empty dataset?

3 Answers3