How to search fo a string in access database?

[nanoh2001]

Hello dears in Daniweb,

I'm a beginner in vb.net

I have a question regarding searching in Access Databases.

to illustrate:

if I have this table in Access

which contains the name, mobile, Telephone, address and the area (highlighted in Red)

Now if I want to Search for persons who live in a certain area but I dont remember the name exactly what should I do?

to illustrate: I have these controls

If I typed in the textbox "Kingdom" only. I want the names of people who live in any county that contains "Kingdom" in the Area feild in the database.

how can I write the SQL query and how can I put the result in the list?

Please help me :'(

[nanoh2001]

I solved it already

thanks

[ggl0rd]

can show the code how u solved it?
i also want know..
thanks

[arunkp]

first you need to get some class that will enable you to connect to the database
hope these codes will help you

Public Class connect
    'Dim oledbcon As New System.Data.OleDb.OleDbConnection
    'Dim oledbada As New System.Data.OleDb.OleDbDataAdapter
    'Dim oledbcmd As New System.Data.OleDb.OleDbCommand
    Dim sqlcon As New System.Data.SqlClient.SqlConnection
    Dim sqlada As New System.Data.SqlClient.SqlDataAdapter
    Dim sqlcmd As New System.Data.SqlClient.SqlCommand
    Function filltable(ByVal selectquery As String) As System.Data.DataTable
        Dim datatable1 As New System.Data.DataTable
        connectioncheck()
        sqlcmd.CommandText = selectquery
        sqlcmd.Connection = sqlcon
        sqlada.SelectCommand = sqlcmd
        sqlada.Fill(datatable1)
        connectioncheck()
        Return datatable1
    End Function
    Sub runquery(ByVal query As String)
        connectioncheck()
        sqlcmd.CommandText = query
        sqlcmd.Connection = sqlcon
        sqlcmd.ExecuteNonQuery()
        connectioncheck()
    End Sub
    Sub connectioncheck()
        If sqlcon.State <> Data.ConnectionState.Open Then
            sqlcon.ConnectionString = "<connection string>"
            sqlcon.Open()
        ElseIf sqlcon.State <> Data.ConnectionState.Closed Then
            sqlcon.Close()
        End If
    End Sub
End Class

this class you need to add as an existing item to your poject
declare a new instance of it dim con as new connect() then call function filltable and give it to data soucre of a gridview
add your queryas select name from person where adda='United Kingdom

[nanoh2001]

can show the code how u solved it?
i also want know..
thanks

ggl0rd

sure

cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Khoms.mdb;")
        cn.Open()

        Dim strSearch As String

        strSearch = "Select * from Person where New_Year like'%" & cmb.Text & "'"


        cmd = New OleDbCommand(strSearch, cn)
        dr = cmd.ExecuteReader

=====

arunkp thanks for your assistance

[Arunabh Nag]

Can you please illustrate how searching for data in MS.ACCESS can be done using an OLEDB connection..??

[yorro]

Can you please illustrate how searching for data in MS.ACCESS can be done using an OLEDB connection..??

Are you able to connect to the database?

[nanoh2001]

Can you please illustrate how searching for data in MS.ACCESS can be done using an OLEDB connection..??

sure

First of all you need to import the name space, you should write this line on the top.

Imports System.Data.OleDb

Then you have to declare three objets:

1- Oledb Connection
2- Oledb Command
3- Oledb Data Reader

like this

Dim cn As OleDbConnection
    Dim cmd As OleDbCommand
    Dim dr As OleDbDataReader

those objects should be declared to be public for the form ... ie not private sub. Example:

Imports System.Data.OleDb

Public Class Form1
    Dim cn As OleDbConnection
    Dim cmd As OleDbCommand
    Dim dr As OleDbDataReader

.
.
.

Then, in the form create a text box and a search button.

and a label or labels to show the result.

here to establish a connection:
1- we need to provide the address of the database.
2- to open the connection.
3- to write the SQL statement to retrieve data from the database (Query).
4- to tell the command about the query
5- to execute the reader.

how do we translate this in code? double click on search button and write this code:

cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\HRIS.mdb;")
                cn.Open()
                Dim strSearch As String
                strSearch = "Select * from CVs"
                cmd = New OleDbCommand(strSearch, cn)
                dr = cmd.ExecuteReader

then the data reader will pass by the database in sequence, here is your chance to tell him the condition:

While dr.Read

                    If dr(1) = txtSearch.Text Then
label1.text=dr(0)
label2.text=dr(2)
label3.text=dr(3)

 End If

                End While
cn.Close()
                dr.Close()

what is dr(0), dr(1), dr(2)???

as we said, the data reader will pass by the table in sequence, for example:
if I have this table

[img]http://eman.mulla.googlepages.com/student.JPG[/img]

then dr(0) is Name
dr(1) is phone
dr(2) is stn_Number
dr(3) is Major
and
dr(4) is Age

so if I returned to my code.. and I imagined the form:

I asked the user to enter a phone number in the textbox
if he click on search button
the system will go thru the database and find a matching phone number dr(1)

if he found a result he will put the associate data (name, stn_Numer,....) in the labels .

at the end we have to close the connection and the data reader.

is it clear?

[sknake]

I have also done something similar and posted the code in C#. However the real work is all done by reading a DataTable from OleDbCommand, OleDbConnection, OleDbDatatReader, etc. so you can modify it for any oledb accessible data.

http://www.daniweb.com/forums/thread221141.html

[nanoh2001]

I have also done something similar and posted the code in C#. However the real work is all done by reading a DataTable from OleDbCommand, OleDbConnection, OleDbDatatReader, etc. so you can modify it for any oledb accessible data.

http://www.daniweb.com/forums/thread221141.html

thank you so much for the enrichment of the subject