stiflas
Network Sniffer Expert
LEVEL 2
1000 XP
This lesson is a continuation of our last topic called “How to Save Record in Database using C#”. At this time we will be focusing on how to update and delete a record from the database using C#. To start with, this application. Open our project called “usermanagement”, then we need to add another control such as buttons, textbox and label. Then arrange all the objects like as shown below.
Before we proceed on adding a code to our application, we need to understand first the process on how the application works. First the user we will click the “Load record” button, then all the records will be listed in the datagridview. Next when the user wants to update the a specific record, the user should click the specific record listed in the datagridview. And the user can observe that the “User ID” of a specific record, we display in the textbox like as shown below.
This time we’re going to add functionality to our application by adding a code that will get the user id and put it into the textbox when the specific record has been click by the user. And here’s the following code:
Next for the “Update Record” button, add the following code:
And for the “Delete Record” button, add the following code:
If you have observed, our code in “Save Record”, “Update Record” and “Delete Record” button is similar except the query. Because when you are doing the saving of record you are Inserting a new record, and if you are modifying a record you are updating a record as well as in the deleting of record.
Here’s all the code used in this application:
You can now test the application by pressing the “F5” or the start button.
Download
Before we proceed on adding a code to our application, we need to understand first the process on how the application works. First the user we will click the “Load record” button, then all the records will be listed in the datagridview. Next when the user wants to update the a specific record, the user should click the specific record listed in the datagridview. And the user can observe that the “User ID” of a specific record, we display in the textbox like as shown below.
This time we’re going to add functionality to our application by adding a code that will get the user id and put it into the textbox when the specific record has been click by the user. And here’s the following code:
- int
i =
e.
RowIndex
;
- DataGridViewRow r =
dataGridView1.
Rows
[
i]
;
- int
id =
Convert.
ToInt32
(
r.
Cells
[
0
]
.
Value
)
;
- txtuserID.
Text
=
r.
Cells
[
0
]
.
Value
+
""
;
Next for the “Update Record” button, add the following code:
- //set our SQL UPDATE 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(
)
;
And for the “Delete Record” button, add the following code:
- 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(
)
;
If you have observed, our code in “Save Record”, “Update Record” and “Delete Record” button is similar except the query. Because when you are doing the saving of record you are Inserting a new record, and if you are modifying a record you are updating a record as well as in the deleting of record.
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(
)
;
- }
- }
- }
You can now test the application by pressing the “F5” or the start button.
Download
You must upgrade your account or reply in the thread to view the hidden content.