serching name- wise

[mshravs]

here, i got a problem which i could not find solution, here in this form i need to search the data from the sql database and show it in the formview, below is the code

Protected Sub searchn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles searchn.Click
        Dim a As String
        Dim b As Boolean
        Dim dr As SqlDataReader
        Dim ConnStr As String
        ConnStr = ConfigurationManager.ConnectionStrings("gate").ConnectionString()
        Dim conn As New SqlConnection(ConnStr)
        conn.Open()
        Dim command As New SqlCommand("select * from visitors_pass ", conn)
        dr = command.ExecuteReader
        b = False
        a = namen.Text
        While dr.Read()
            If (a = (dr(2))) Then                b = True
                dr.Close()
                conn.Close()
                Response.Redirect("view_name.aspx")
                Exit While
            End If
        End While
        If (b = False) Then
            MsgBox("no data matching to this name is found")
        End If
        dr.Dispose()
        conn.Dispose()
        dr.Close()
        conn.Close()
        serial.Text = ""
    End Sub

this is the searching name-wise. the problem is, if i change the dr() value (the highlighted one) to 3 which is officer to visit in the sqldatabase, it's actually searching and showing the result correctly. but when i'm changing to 2 which is visitor's name in the sqldatabase and the exact one that i should search, it is showing a msgbox stating "match not found to that name", where the data regarding that name is available in the database................plz help with the solution

[reach_yousuf]

here, i got a problem which i could not find solution, here in this form i need to search the data from the sql database and show it in the formview, below is the code

Protected Sub searchn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles searchn.Click
        Dim a As String
        Dim b As Boolean
        Dim dr As SqlDataReader
        Dim ConnStr As String
        ConnStr = ConfigurationManager.ConnectionStrings("gate").ConnectionString()
        Dim conn As New SqlConnection(ConnStr)
        conn.Open()
        Dim command As New SqlCommand("select * from visitors_pass ", conn)
        dr = command.ExecuteReader
        b = False
        a = namen.Text
        While dr.Read()
            If (a = (dr(2))) Then                b = True
                dr.Close()
                conn.Close()
                Response.Redirect("view_name.aspx")
                Exit While
            End If
        End While
        If (b = False) Then
            MsgBox("no data matching to this name is found")
        End If
        dr.Dispose()
        conn.Dispose()
        dr.Close()
        conn.Close()
        serial.Text = ""
    End Sub

this is the searching name-wise. the problem is, if i change the dr() value (the highlighted one) to 3 which is officer to visit in the sqldatabase, it's actually searching and showing the result correctly. but when i'm changing to 2 which is visitor's name in the sqldatabase and the exact one that i should search, it is showing a msgbox stating "match not found to that name", where the data regarding that name is available in the database................plz help with the solution

Hi mshravs,

I would suggest you few things,
1) Change your SQL Query to filter more closer result instead of load whole table: eg:
select * from visitors_pass where Visitor_name like "'%" & name.text & "%'"
2) DO NOT HARDCODE INDEX "DR(2) ": TRY THE FOLLOWING CODE

Dim dr As System.Data.SqlClient.SqlDataReader

        If dr.HasRows = False Then
            MsgBox("no data matching to this name is found")
        Else
            For I As Integer = 0 To dr.FieldCount - 1
                If dr(I) = NAME.TEXT Then
                    Response.Redirect("view_name.aspx")
                    Exit For
                End If
            Next
        End If

Mark as solved if it helps you!!

[mshravs]

any other idea this is not working