retrieve data ADODB

[Learner010]

i want to retrieve data from Access table but i don't know where i am going wrong.here i am going to show my work which return errors:-

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset


Public Sub procedure1()
        con.Provider = "Microsoft.jet.oledb.4.0"
        con.Open(My.Application.Info.DirectoryPath & "\Database1.mdb")
End Sub



 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

            Dim str As String = InputBox("enter first name to search")
            Dim query As String = "Select * from ADODB where firstname="&str
            procedure1()
            rs.Open(query, con, 2, 3)
            If rs.EOF = True Or rs.BOF = True Then
                MsgBox("record not found")
            Else
                Me.TextBox1.Text = rs.Fields(1).ToString
                Me.TextBox1.Text = rs.Fields(2).ToString
                Me.TextBox1.Text = rs.Fields(3).ToString
            End If
            rs.Close()
            con.Close()
            rs = Nothing
            con = Nothing
End Sub

error message showing that there is something wrong at line 17

[Reverend Jim]

You didn't provide the error message. Try

"Select * from ADODB where firstname='" & str & "'"

Text fields have to have single quotes. I'm also assuming that your table is actually named ADODB. If not then this would be an error. Again, you could have been a little more helpful by giving us the error message.

[Learner010]

i updated the code as follow :-

Dim query As String = "Select * from ADODB where firstname='" & str & "'"

            If rs.EOF = True Or rs.BOF = True Then
                MsgBox("record not found")
            Else
                Me.TextBox1.Text = rs.Fields(1).ToString
                Me.TextBox2.Text = rs.Fields(2).ToString
                Me.TextBox3.Text = rs.Fields(3).ToString
            End If

now getting no errors but unexpected result :-

in textbox1 there is text "ADODB.InternalField"
and the same in textbox2 and textbox3

[Learner010]

Any Solution ?

[Reverend Jim]

Use

rs.Fields(1).Value

instead of

rs.Fields(1).ToString

[Learner010]

its working but another problem still there and that is

it works for one time only.
second time when i click on button and then input the name then it returns an error saying :-NullReferenceException was unhandled
object reference not set to an instance of object

error in procedure1

but when i remove the following lines then its working

            rs = Nothing
            con = Nothing

please explain why error occuring when using these lines

another one question is that what path does My.Application.Info.DirectoryPath return (i mean obj directory or bin directory or other directory)

i think its bin directory.

[Reverend Jim]

If you plan on reusing rs and con then use rs.Close() and con.Close(). If you set them to Nothing then you release the objects. Note that if they go out of scope they will automatically be released. Here is a sample of using ADO with MS SQL.

Private Sub btnADO_Click(sender As System.Object, e As System.EventArgs) Handles btnADO.Click

    ListView1.Items.Clear()

    Dim con As New ADODB.Connection
    Dim rec As New ADODB.Recordset

    con.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=PUBS;Trusted_Connection=yes;")
    rec.Open("SELECT au_lname,au_fname,zip FROM authors", con, CursorTypeEnum.adOpenStatic)

    Do Until rec.EOF
        ListView1.Items.Add(New ListViewItem({rec("au_lname").Value, rec("au_fname").Value, rec("zip").Value}))
        rec.MoveNext()
    Loop

    rec.Close()
    con.Close()

End Sub

[Learner010]

thanx for providing code but this is not working
here are two errors , it showing:-

Error 1 Expression Expected
Error 2 Comma, ')', or a valid expression continuation expected.

this is pointing to the line 12 of your code.

[Reverend Jim]

I ran the code here and it works fine. The ListView must be in details mode and have three columns defined.