codes for search button in vb6

[meLiel]

hi programming masters! i need your help..how can i SEARCH a record in a database and display the results?? example: if i type an ID number or a name on the text box and click the search button it will look for the records and display it..
i have a search button (cmdSearch) and a text box (Text1)..
im using ms access

DATABASE NAME = ListofMemebers
tablename= Census
fieldnames:

ID
LastName
FirstName
MiddleName
Age
Sex
BirthDate
BirthPlace
Address
ContactNo

note:
im using this codes:
Private Sub cmdSearch_Click()
Dim con As ADODB.Connection
Set con = New ADODB.Connection
Dim rsSearch As ADODB.Recordset
Set rsSearch = New ADODB.Recordset
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\ListofMembers.MDB;Persist Security Info=False"

rsSearch.Open "SELECT * FROM Census WHERE ID LIKE " & "'" & Text1.Text & "'", con, adOpenStatic, adLockOptimistic

' i dont know whats next....

rsSearch.Close
con.Close
End Sub

[debasisdas]

You need to polulate the controls from the recordset before closing it. Of course you have to check for BOF and EOF.

something like this.
If rsSearch.EOF <> True And rsSearch.BOF <> True Then
Text1.Text = rsSearch.Fields(0)

[meLiel]

@debasisdas

i did it like this:

If rsSearch.EOF = True or rsSearch.BOF True Then
Msgbox "No Record Found"

End If

but it didnt work...anyway i truly appreciate your help..thanks..:)

[Jx_Man]

I don't know where you display the searching data. But i give an example searching with listview.
Try this example :

Private Sub cmdSearch_Click()
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    Dim rsSearch As ADODB.Recordset
    Set rsSearch = New ADODB.Recordset
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\ListofMembers.MDB;Persist Security Info=False"

    rsSearch.Open "SELECT * FROM Census WHERE ID LIKE " & "'%" & Text1.Text & "%'", con, adOpenStatic, adLockOptimistic
    ListView1.ListItems.Clear

    While Not rsSearch.EOF
        Set View = ListView1.ListItems.Add
        View.Text = rs!ID
        View.SubItems(1) = rs!LastName
        View.SubItems(2) = rs!FirstName
        View.SubItems(3) = rs!MiddleName
        View.SubItems(4) = rs!Age
        View.SubItems(5) = rs!Sex
        'and soon
        rsSearch.MoveNext
    Wend
    rsSearch.Close
    con.Close
End Sub

[AndreRet]

First of all, if you will be searching your database with a LIKE statement as you are doing now, you will have a lot of returned records... The LIKE statement works fine when you serach by names or strings etc. Id's is normally a set returned value.

So, I will change the SELECT statement to -

Dim strId As String ''If the Id will contain other characters and not just numbers, otherwise you can use Dim xId As Integer if it is only numbers (integers)

strId = Text1.Text

rsSearch.Open "SELECT * FROM Census WHERE ID ='" & strId & "'", con, adOpenStatic, adLockOptimistic
''If you are using ONLY numbers, you can change the select statement to...
''rsSearch.Open "SELECT * FROM Census WHERE ID =" & strId & "", con, adOpenStatic, adLockOptimistic

''Now, show the returned data...

If rsSearch.BOF = True OR rsSearch.EOF = True Then
    msgBox "No records Found."

    Exit Sub
        Else
    ''Lets say you are using textboxes to show the name and surname of the returned id...
    txtName.Text = rsSearch!FirstName
    txtSurname.Text = rsSearch!LastName

    rsSearch.Close
End If

[BitBlt]

Please note that the asterisk wildcard character should be used for MSAccess SQL. The percent sign, as in @JxMan's example, is the wildcard character for MS SQL Server. There are other wildcard characters that are used for different purposes. You can find a full list of them in the MSAccess help file.

Just a coda to that...if you don't include a wildcard, then "LIKE" will only return rows where the searched column is completely equal.

So, for instance, if your ID is actually alphanumeric (think "drivers license number") then you could do something like this:

SELECT * FROM Census WHERE ID LIKE '*A24*'

This would bring back results like 'FREDA24-ORE' and 'A24D99' or 'XZZA24'.

If your ID column is strictly numeric, then @AndreRet's solution will perform better.

[meLiel]

@Jx Man

i dont have so much knowledge about the ListView..i tried your codes but it didn't work for me..:))) but thank you so much for the help...:)))

@Andreret:

i was using DataGrid but i changed my mind and use the text boxes instead..and it works...:))) thank you so much.. :)))

anyway, is it possible to search using either ID and LastName? how?

[Jx_Man]

Its not a problem when you use datagrid or listview. The pressure point is how you'll get searching data from database (exact or like), it will influence how you display the result. if you want display the exact result then you can use textbox to display it like andre solution.

[meLiel]

.,.thank you everyone....you're such a big help..:))) Continue to be a blessing..:))

[AndreRet]

Only a pleasure :) Happy coding.

[priya.pri]

i created one mini project for libray management.plz help me to do coding for that, using create,update, search, delete cmd button

[rishif2]

first , create your own thread.New thread.
then ask your exact problem there and don't ask us to write code for you.

Click Here to create a new thread.

[mrzhafir]

anyone can help myproblem for code VB?

[rishif2]

ask your problem Here.don't raise old threads.

[Arnoldprog]

Hii, Im using the code above but keep geting a message box 'Compile error User-defined type not defined' then a highlight on 'Con As New ADODB.connection'

[Reverend Jim]

You have to add adodb as a .NET reference to your project and do

Imorts ADODB