Working with ADO.NET - Searching Records
(Page 4 of 4 )
The second part of our application is the Search functionality. For this purpose, add a new Windows Form to the application (Project | Add Windows Form). Select the menu item titled Windows Form, give a meaningful name and click the Open button. Create a user interface as shown in figure 2 and suply the required properties.
The application links the new form you have created from the main form. For this purpose, double click the Search button and add the following line of code:
Dim search1 As Search = New Search
search1.Show()
We make use of a ComboBox and a TextBox for our search application. The ComboBox populates the list of all names from the database, and the textbox will display the corresponding e-mail address by searching the records from our database.
In the previous session, you learned about the use of built-in data controls for establishing a connection to the database. In this session, we will see how to do this job with the help of .NET code. The code for establishing the connection and populating a ComboBox is shown in the listing given below. Please be sure to supply the code inside the Load event of the form.
m_oledbconnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = D:/customer.mdb"
m_oledbconnection.Open()
Dim m_oledbdataadapter As OleDbDataAdapter
m_oledbdataadapter = New OleDbDataAdapter("Select * from customer_list", m_oledbconnection)
Dim cmd As New OleDbCommand("Select * from customer_list", m_oledbconnection)
Dim m_oledbcommandbuilder As OleDbCommandBuilder
m_oledbcommandbuilder = New OleDbCommandBuilder(m_oledbdataadapter)
Dim m_datatable As New DataTable
m_oledbdataadapter.Fill(m_datatable)
Dim objreader As OleDbDataReader = cmd.ExecuteReader()
'ComboBox Population
Do While objreader.Read()
cboName.Items.Add(objreader("Name"))
Loop
m_oledbconnection.Close()
cboName.SelectedIndex = 0
Please note that you will have to modify the path name of the database before attempting to execute the application. Double click the ComboBox control on the form and add the following lines of code:
Try
Dim strsql As String
strsql = "SELECT * FROM customer_list WHERE Name = '" & cboName.Text & "' "
m_oledbconnection.Open()
Dim cmd As New OleDbCommand(strsql, m_oledbconnection)
Dim objreader As OleDbDataReader = cmd.ExecuteReader
objreader.Read()
txtEmail.Text = objreader("E-mail")
objreader.Close()
m_oledbconnection.Close()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, Me.Text)
End Try
The above code will search for the field named ‘Name’ from the database customer_list. If it matches with the one listed on the ComboBox, the corresponding e-mail will be displayed on the TextBox control with the code given below:
txtEmail.Text = objreader("E-mail")
In the next part of this article, you will learn how to edit and delete data from a database using ADO.NET.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 20 
