Zip
Digital Marketplace Creator
LEVEL 1
500 XP
This tutorial is a continuation of our last topic called “How to Update and Delete Record in the Database using C#”. At this time we will be dealing with searching a Specific Record from the Database. To start with, we need to add another label, button and a textbox, then arranges it like as shown below.
First we will set the visibility of the label and textbox for searching to false, so that when the application load it will not be visible to the user.
Second on the “Search Record” button, add the following code:
The code below will take effect when the search record button is clicked then it will show the label and a textbox that will allow the user to accept inputs for searching of a specific record.
At this time we will add code for textbox.To do this, double click the textbox and add the following code:
The code below will take effect when the text inside the textbox is changing because we are using the textchanged event of the textbox.
After adding this code, you can test it by pressing the “F5” or the start button.
Take note!
You can modify the SQL Query using the Name or the Username of the user during searching of record.
And here’s all the code used in this application.
Download
First we will set the visibility of the label and textbox for searching to false, so that when the application load it will not be visible to the user.
Second on the “Search Record” button, add the following code:
The code below will take effect when the search record button is clicked then it will show the label and a textbox that will allow the user to accept inputs for searching of a specific record.
- lblsearch.
Show
(
)
;
- txtsearch.
Show
(
)
;
- btnsearch.
Enabled
=
false
;
At this time we will add code for textbox.To do this, double click the textbox and add the following code:
The code below will take effect when the text inside the textbox is changing because we are using the textchanged event of the textbox.
- dt =
new
DataTable(
)
;
- string
sql =
"Select * from tbluseraccounts WHERE username LIKE '%"
+
txtsearch.
Text
+
"%'"
;
- OleDbDataAdapter da =
new
OleDbDataAdapter(
sql, conn)
;
- da.
Fill
(
dt)
;
- dataGridView1.
DataSource
=
dt;
After adding this code, you can test it by pressing the “F5” or the start button.
Take note!
You can modify the SQL Query using the Name or the Username of the user during searching of record.
And here’s all the code used in this application.
- using
System
;
- using
System.Collections.Generic
;
- using
System.ComponentModel
;
- using
System.Data
;
- using
System.Drawing
;
- using
System.Linq
;
- using
System.Text
;
- using
System.Windows.Forms
;
- using
System.Data.OleDb
;
- namespace
WindowsFormsApplication1
- {
- public
partial
class
Form1 :
Form
- {
- //declare new variable named dt as New Datatable
- DataTable dt =
new
DataTable(
)
;
- //this line of code used to connect to the server and locate the database (usermgt.mdb)
- static
string
connection =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "
+
Application .
StartupPath
+
"/usermgt.mdb"
;
- OleDbConnection conn =
new
OleDbConnection(
connection)
;
- public
Form1(
)
- {
- InitializeComponent(
)
;
// calling the function
- }
- private
void
Form1_Load(
object
sender, EventArgs e)
- {
- }
- private
void
loadrecord(
)
- {
- dt =
new
DataTable(
)
;
- string
sql =
"Select * from tbluseraccounts"
;
- OleDbDataAdapter da =
new
OleDbDataAdapter(
sql , conn)
;
- da.
Fill
(
dt)
;
- dataGridView1.
DataSource
=
dt;
- }
- private
void
button1_Click(
object
sender, EventArgs e)
- {
- loadrecord(
)
;
- }
- private
void
btninsert_Click(
object
sender, EventArgs e)
- {
- OleDbCommand cmd =
new
OleDbCommand(
)
;
- //set our SQL Insert INTO statement
- string
sqlInsert =
"INSERT INTO tbluseraccounts ( username, userusername, userpassword, usertype ) VALUES('"
+
txtname.
Text
+
"','"
+
txtuser.
Text
+
"','"
+
txtpass.
Text
+
"','"
+
txttype.
Text
+
"')"
;
- try
- {
- //open the connection
- conn.
Open
(
)
;
- //set the connection
- cmd.
Connection
=
conn;
- //get the SQL statement to be executed
- cmd.
CommandText
=
sqlInsert;
- //execute the query
- cmd.
ExecuteNonQuery
(
)
;
- //display a message
- MessageBox.
Show
(
"New Record Added!...."
)
;
- //close the connection
- conn.
Close
(
)
;
- }
- catch
(
Exception ex)
- {
- //this will display some error message if something
- //went wrong to our code above during execution
- MessageBox.
Show
(
ex.
ToString
(
)
)
;
- }
- //we call the loadrecord() function after adding a new record
- loadrecord(
)
;
- }
- private
void
dataGridView1_CellContentClick(
object
sender, DataGridViewCellEventArgs e)
- {
- int
i =
e.
RowIndex
;
- DataGridViewRow r =
dataGridView1.
Rows
[
i]
;
- int
id =
Convert.
ToInt32
(
r.
Cells
[
0
]
.
Value
)
;
- txtuserID.
Text
=
r.
Cells
[
0
]
.
Value
+
""
;
- }
- private
void
btnupdate_Click(
object
sender, EventArgs e)
- {
- OleDbCommand cmd =
new
OleDbCommand(
)
;
- //set our SQL Insert INTO statement
- string
sqlUpdate =
"UPDATE tbluseraccounts set username ='"
+
txtname.
Text
+
"' , userusername = '"
+
txtuser .
Text
+
"', userpassword = '"
+
txtpass.
Text
+
"', usertype= '"
+
txttype .
Text
+
"' where userID = "
+
txtuserID .
Text
+
""
;
- try
- {
- //open the connection
- conn.
Open
(
)
;
- //set the connection
- cmd.
Connection
=
conn;
- //get the SQL statement to be executed
- cmd.
CommandText
=
sqlUpdate;
- //execute the query
- cmd.
ExecuteNonQuery
(
)
;
- //display a message
- MessageBox.
Show
(
"Record Updated!...."
)
;
- //close the connection
- conn.
Close
(
)
;
- }
- catch
(
Exception ex)
- {
- //this will display some error message if something
- //went wrong to our code above during execution
- MessageBox.
Show
(
ex.
ToString
(
)
)
;
- }
- //we call the loadrecord() function after adding a new record
- loadrecord(
)
;
- }
- private
void
btndelteRecord_Click(
object
sender, EventArgs e)
- {
- OleDbCommand cmd =
new
OleDbCommand(
)
;
- //set our SQL DELETE statement
- string
sqlUpdate =
"Delete * from tbluseraccounts where userID= "
+
txtuserID.
Text
+
""
;
- try
- {
- //open the connection
- conn.
Open
(
)
;
- //set the connection
- cmd.
Connection
=
conn;
- //get the SQL statement to be executed
- cmd.
CommandText
=
sqlUpdate;
- //execute the query
- cmd.
ExecuteNonQuery
(
)
;
- //display a message
- MessageBox.
Show
(
"Record Deleted!...."
)
;
- //close the connection
- conn.
Close
(
)
;
- }
- catch
(
Exception ex)
- {
- //this will display some error message if something
- //went wrong to our code above during execution
- MessageBox.
Show
(
ex.
ToString
(
)
)
;
- }
- //we call the loadrecord() function after adding a new record
- loadrecord(
)
;
- }
- private
void
txtsearch_TextChanged(
object
sender, EventArgs e)
- {
- dt =
new
DataTable(
)
;
- string
sql =
"Select * from tbluseraccounts WHERE username LIKE '%"
+
txtsearch.
Text
+
"%'"
;
- OleDbDataAdapter da =
new
OleDbDataAdapter(
sql, conn)
;
- da.
Fill
(
dt)
;
- dataGridView1.
DataSource
=
dt;
- }
- private
void
btnsearch_Click(
object
sender, EventArgs e)
- {
- lblsearch.
Show
(
)
;
- txtsearch.
Show
(
)
;
- btnsearch.
Enabled
=
false
;
- }
- }
- }
Download
You must upgrade your account or reply in the thread to view hidden text.