Search value by textbox from .mdb file

[kerek2]

Hi Guys,

I'm really need help for this item, the situation is i got textbox1 as a input , button1 as searchbutton abd textbox2 to display the item that been enter in textbox1. All the table from .mdb

Can guide me plz

[4advanced]

I really don't understand a thing about your question!

If you are asking for some help you really need to explain everything into details. We can't guess about what your goals are.....

[kerek2]

Thank you for reply,

Actually i need do make one searchbox to search a data. It's make one textbox as inputbox, when click the button, it will refer to .mdb database table. and the result will display in another textbox. For inputbox just accept 10 character and the display will be name of person..can u guide me?

[Ole Raptor]

Do you already have your connection strings setup for the mdb? I will go with yes :)
are you sure that the search will only return one entry in the db? i would probably go with either a listbox or datagrid to display the returns.
this is a nice little tutorial for displaying data in a datagrid. http://www.vbdotnetheaven.com/UploadFile/mahesh/DataGridSamp04232005050133AM/DataGridSamp.aspx all you will need to do is change the query.

Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select * from Customers", myConnection)
'to this
Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select * from Customers Where FirstName like " & me.txtsearch.text & "%", myConnection)

[kerek2]

the connection string to mdb still stuck...i'm confuse where to start the coding...below button click or textbox?....plz help me

[Ole Raptor]

button click

[kerek2]

Then how to link from textbox to database.mdb?...plz can give me a sample for connection and search method?

[4advanced]

At least you are pointing to the right directions at this moment. The questions you ask seem to me you are now in the right 'developer-mode' :=)

Professor can be right but it's up to you where to open the connection. In most cases, and esspecially for .Net, the connections are made when you want to query, update, insert or delete something from the database and close it when ready. Anyways, you still can open the connection whenever you want & close it whenever you want.

In the case of using Access as a backend database, you're out of many options. You got to remember that Access is a file-based database which means that if you query some tables, all the tables in the query are pulled over to the client and after that they are being queried (client side). With a lot of data, you can experience a lot of performance problems.

Pointing to your question: leave all database actions within the button_click, as ProfessorPC suggested. Take a look at this post http://www.daniweb.com/forums/thread172996.html and look for rapture's code examples on oledb connections etc....
Also ProfessorPC's example is a nice solution ;)

Regards,
Richard
The Netherlands

[kerek2]

I'm already thry this code,but the result appear all the name,it not searching by id.....how to do that bro?....pls...

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

'Create a connection to the database
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Dim Sql As String

Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Database1.mdb;")
'cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; _Data Source=D:\Database1.mdb;")
'provider to be used when working with access database
'cn.Open()
cmd = New OleDbCommand("SELECT * from UMP WHERE ", cn)


cn.Open()




dr = cmd.ExecuteReader
While dr.Read()


ListBox1.Items.Add(dr(2))
'TextBox1.Text = dr(2)
'TextBox2.Text = dr(1)
'TextBox3.Text = dr(2)
' loading data into TextBoxes by column index
End While

Catch ex As Exception

End Try

'dr.Close()
cn.Close()

End Sub

[Jx_Man]

use condition on your select statment.

[4advanced]

Like Jx_Man says.... use that condition in your select statement. For example like this : SELECT * from UMP WHERE ID = yourID -> 'yourID' is the ID which you are looking for.

A better approach is : use parameterized queries so instead of yourID you can insert at that point @yourID and declare a parameter object, insert the correct value in it and attach the parameter to the commandobject! This way, it's sql-injection safe....

[kerek2]

Can you give a example plz?...because i'm already try but not ok..

[4advanced]

Okay, here it is :

Private Sub cmdSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdSearch.Click

        'variabeles (objects and strings)
        Dim _root As String = String.Empty
        Dim _Con As OleDb.OleDbConnection = Nothing
        Dim _WhereClause As String = String.Empty
        Dim _Parameter As OleDbParameter = Nothing
        Dim _DataTable As DataTable = Nothing
        Dim _Com As OleDbCommand = Nothing
        Dim _DataAdapter As OleDbDataAdapter

        'fill string variabeles
        _root = "yourPathToDatabase\MyDataBase.mdb"
        _Con = New OleDb.OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User Id=admin;Password=;", _root))

        'create objects
        _Com = New OleDbCommand
        _DataTable = New DataTable("Customers")
        _DataAdapter = New OleDbDataAdapter

        'check if the searchstring is numeric (if so, the search for an ID has been performed)
        If IsNumeric(txtSearch.Text) Then
            _WhereClause = "ID=@Parameter"
        Else
            _WhereClause = "LCASE(Customer_LastName)=@Parameter"
        End If

        Try
            'open the connection to the database
            _Con.Open()

            'fill the command with the selectquery and attach a parameter
            With _Com
                .CommandText = String.Format("SELECT * FROM Customers WHERE {0}", _WhereClause)
                .Parameters.AddWithValue("@Parameter", txtSearch.Text.ToLower)
                .Connection = _Con
            End With

            'attach command to dataadapter and fill the datatable
            _DataAdapter.SelectCommand = _Com
            _DataAdapter.Fill(_DataTable)

        Catch ex As Exception
            'inform the user something has happened
            MessageBox.Show(ex.Message)
        Finally

            'if the connection is open, close it
            If _Con.State = ConnectionState.Open Then _Con.Close()
            'dispose and remove objects
            _Con.Dispose()
            _Con = Nothing
            _Com.Dispose()
            _Com = Nothing
        End Try

        'attach the datatable to the datagridview
        Me.dgvCustomers.DataSource = _DataTable

        'dispose and remove objects
        _DataAdapter.Dispose()
        _DataAdapter = Nothing
        _DataTable.Dispose()
        _DataTable = Nothing

    End Sub

[kerek2]

Thank you.....but i still struck bro.....can u add something to my previous coding?,,,,,....i still not understand...so dumb...plz sir...

[4advanced]

No sorry, it's time to find out yourself....
I coded an example and added comments to it, so you know what steps to take.

Now it's up to you.......

[Ole Raptor]

took a quick, very quick look. to add the condition you need to finish the WHERE

cmd = New OleDbCommand("SELECT * from UMP WHERE ", cn)
'to
cmd = New OleDbCommand("SELECT * from UMP WHERE field like " & txtbox.text & "%", cn)

[Teme64]

took a quick, very quick look. to add the condition you need to finish the WHERE
cmd = New OleDbCommand("SELECT * from UMP WHERE ", cn)
'to
cmd = New OleDbCommand("SELECT * from UMP WHERE field like " & txtbox.text & "%", cn)

Here's a one more correction. Single quotes are needed around textual parameters cmd = New OleDbCommand("SELECT * from UMP WHERE field like '" & txtbox.text & "%'", cn) that is, if the "field" is textual field. If the "field" is numeric (integer), use cmd = New OleDbCommand("SELECT * from UMP WHERE field =" & CInt(txtbox.text) , cn) and trap possible errors.