Form 1(frmMain) - The Main Form
btnNew - New Record Button
btnUpdate - Update Record Button
btnDelete - Delete Record Button
dgv1 - DataGridView for Records
txtSearch - Search Records
First to connect to any Database source you have to setup your connection string. I usually go with Access or SQL Server. If you are looking into using another datasource check out www.connectionstrings.com for other data sources like MySQL, Oracle and lots more.
Imports System.Data.OleDb
Public Class frmMain
' Our Connection String - Be sure you change the Data Source
Dim con1 As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\Sethro\Desktop\Test.mdb")
Now we go into selecting our records from the Data Source and populating out DataGridView with them.
' This displays our records in the DataGridView so the User can select them to update, delete, or search
Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' Our SQL Statement
Dim sql As String
sql = "SELECT * FROM users"
' This is our DataAdapter. This executes our SQL Statement above against the Database
' we defined in the Connection String
Dim adapter As New OleDbDataAdapter(sql, con1)
' Gets the records from the table and fills our adapter with those.
Dim dt As New DataTable("users")
adapter.Fill(dt)
' Assigns our DataSource on the DataGridView
dgv1.DataSource = dt
'
Dim sql1 As String
sql1 = "SELECT * FROM users"
Dim adapter1 As New OleDbDataAdapter(sql1, con1)
Dim cmd1 As New OleDbCommand(sql1, con1)
'Dim dt1 As New DataTable("users")
con1.Open()
Dim myreader As OleDbDataReader = cmd1.ExecuteReader
myreader.Read()
con1.Close()
End Sub
Now that we have our records, and an application to interact with our database, we dont have to open the data file and edit it. We can show the New Record form and we can add records from it. The code to the New Record form(Form2) is below. We also have our update button and delete button. With our Update it allows us to select a record from the DataGridView, then we can click the Update button and the Update Record form shows with the corresponding information from the selected row in the DataGridView.
You can also double click the record and it will open the Update Record form with the corresponding information.
Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click Shows the New Record form(Form2) frmNew.Show() End Sub Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click frmUpdate.Show() ' What this does is when the User selects a record in the DataGridView ' it will populate the textbox on the other form with the corresponding ' Cell Value. frmUpdate.txtFirst.Text = dgv1.CurrentRow.Cells(0).Value.ToString frmUpdate.txtLast.Text = dgv1.CurrentRow.Cells(1).Value.ToString frmUpdate.txtAddress.Text = dgv1.CurrentRow.Cells(2).Value.ToString frmUpdate.txtCity.Text = dgv1.CurrentRow.Cells(3).Value.ToString frmUpdate.txtZip.Text = dgv1.CurrentRow.Cells(4).Value.ToString frmUpdate.txtPhone.Text = dgv1.CurrentRow.Cells(5).Value.ToString frmUpdate.txtEmail.Text = dgv1.CurrentRow.Cells(6).Value.ToString frmUpdate.txtID.Text = dgv1.CurrentRow.Cells(7).Value.ToString End Sub Private Sub dgv1_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv1.CellDoubleClick frmUpdate.Show() ' Same thing as btnUpdate_Click except the user can actually DoubleClick ' a record. You want to be sure you get the field names and cell numbers correct or the information will be misconstrued. frmUpdate.txtFirst.Text = dgv1.Rows(e.RowIndex).Cells(0).Value.ToString frmUpdate.txtLast.Text = dgv1.Rows(e.RowIndex).Cells(1).Value.ToString frmUpdate.txtAddress.Text = dgv1.Rows(e.RowIndex).Cells(2).Value.ToString frmUpdate.txtCity.Text = dgv1.Rows(e.RowIndex).Cells(3).Value.ToString frmUpdate.txtZip.Text = dgv1.Rows(e.RowIndex).Cells(4).Value.ToString frmUpdate.txtPhone.Text = dgv1.Rows(e.RowIndex).Cells(5).Value.ToString frmUpdate.txtEmail.Text = dgv1.Rows(e.RowIndex).Cells(6).Value.ToString frmUpdate.txtID.Text = dgv1.Rows(e.RowIndex).Cells(7).Value.ToString End Sub
Below is the code for our Delete Record function. You are able to select a record and hit the Delete button and the record will be deleted from both the DataGridView and the data source file. Be sure you have a Unique ID or Field in your Data file so you can use the WHERE statement to be sure that the correct record is deleted.
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
' This is our DELETE Statement. To be sure we delete the correct record and not all of
them.
' We use the WHERE to be sure only that record that the user has selected is deleted.
Dim sqldelete As String
sqldelete = "DELETE * FROM users WHERE IDNum='" & dgv1.CurrentRow.Cells(7).Value.ToString & "'"
' This is our DataAdapter. This executes our SQL Statement above against the Database
' we defined in the Connection String
Dim adapter As New OleDbDataAdapter(sqldelete, con1)
' Gets the records from the table and fills our adapter with those.
Dim dt As New DataTable("users")
adapter.Fill(dt)
' Assigns the edited DataSource on the DataGridView and the refreshes the
' view to ensure everything is up to date in real time.
dgv1.DataSource = dt
This is a Sub in Module 1 to refresh the DataGridView when information is added,
updated, or deleted.
RefreshDGV()
End Sub
Now is the important part. When a user has a bunch of records they want to sort though they needto be able to Search for what they are looking for without having to browse through all the records. Below is the code for our Search function.
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSearch.TextChanged
' SQL Statement so our User can search for either FirstName or LastName
Dim sqlsearch As String
sqlsearch = "SELECT * FROM users WHERE FirstName LIKE '%" & txtSearch.Text & "%'" & " OR LastName LIKE '%" & txtSearch.Text & "%'"
' Once again we execute the SQL statements against our DataBase
Dim adapter As New OleDbDataAdapter(sqlsearch, con1)
' Shows the records and updates the DataGridView
Dim dt As New DataTable("users")
adapter.Fill(dt)
dgv1.DataSource = dt
'
End Sub
End Class
Now the code for the New Record Form to create new records.
Form 2(frmNew) - New Record Form
txtFirst
txtLast
txtAddress
txtCity
txtZip
txtPhone
txtEmail
txtID
btnSave
Imports System.Data.Oledb
Public Class frmNew
' Our Connection String
Dim con1 As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\Sethro\Desktop\Test.mdb")
The Save button is what saves the new information to our Data source.
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim sqlinsert As String
We use the INSERT statement which tells our program to add the information
from the Forms Text fields into the Databases columns.
sqlinsert = "INSERT INTO users(FirstName, LastName, Address, City, Zip, Phone, Email, IDNum)" & _
"VALUES(@FirstName, @LastName, @Address, @City, @Zip, @Phone, @Email, @IDNum)"
Dim cmd As New OleDbCommand(sqlinsert, con1)
' This assigns the values for our columns in the DataBase.
' To ensure the correct values are written to the correct column
cmd.Parameters.Add(New OleDbParameter("@FirstName", txtFirst.Text))
cmd.Parameters.Add(New OleDbParameter("@LastName", txtLast.Text))
cmd.Parameters.Add(New OleDbParameter("@Address", txtAddress.Text))
cmd.Parameters.Add(New OleDbParameter("@City", txtCity.Text))
cmd.Parameters.Add(New OleDbParameter("@Zip", txtZip.Text))
cmd.Parameters.Add(New OleDbParameter("@Phone", txtPhone.Text))
cmd.Parameters.Add(New OleDbParameter("@Email", txtEmail.Text))
cmd.Parameters.Add(New OleDbParameter("@IDNum", txtID.Text))
' This is what actually writes our changes to the DataBase.
' You have to open the connection, execute the commands and
' then close connection.
con1.Open()
cmd.ExecuteNonQuery()
con1.Close()
' This are subs in Module1, to clear all the TextBoxes on the form
' and refresh the DataGridView on the MainForm to show our new records.
ClearTextBox(Me)
RefreshDGV()
Me.Close()
End Sub
End Class
Now our code for the Update form to update any records.
Form 3(frmUpdate) - Update Record Form
txtFirst
txtLast
txtAddress
txtCity
txtZip
txtPhone
txtEmail
txtID
btnUpdate
Imports System.Data.Oledb
Public Class frmUpdate
' Our Connection String
Dim con1 As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\Sethro\Desktop\Test.mdb")
The code for the Update button.
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim sqlupdate As String
Here we use the UPDATE Statement to update the information. To be sure we are
updating the right record we also use the WHERE clause to be sureno information
is added or changed in the other records
sqlupdate = "UPDATE users SET FirstName=@FirstName, LastName=@LastName, " & _
"Address=@Address, City=@City, Zip=@Zip, Phone=@Phone, Email=@Email WHERE IDNum='" & txtID.Text & "'"
Dim cmd As New OleDbCommand(sqlupdate, con1)
' This assigns the values for our columns in the DataBase.
' To ensure the correct values are written to the correct column
cmd.Parameters.Add(New OleDbParameter("@FirstName", txtFirst.Text))
cmd.Parameters.Add(New OleDbParameter("@LastName", txtLast.Text))
cmd.Parameters.Add(New OleDbParameter("@Address", txtAddress.Text))
cmd.Parameters.Add(New OleDbParameter("@City", txtCity.Text))
cmd.Parameters.Add(New OleDbParameter("@Zip", txtZip.Text))
cmd.Parameters.Add(New OleDbParameter("@Phone", txtPhone.Text))
cmd.Parameters.Add(New OleDbParameter("@Email", txtEmail.Text))
' This is what actually writes our changes to the DataBase.
' You have to open the connection, execute the commands and
' then close connection.
con1.Open()
cmd.ExecuteNonQuery()
con1.Close()
' This are subs in Module1, to clear all the TextBoxes on the form
' and refresh the DataGridView on the MainForm to show our new records.
ClearTextBox(Me)
Me.Close()
RefreshDGV()
End Sub
End Class
Module 1 holds the code for the RefreshDGV and ClearTextBox Subs.
Imports System.Data.OleDb
Module Module1
Dim con1 As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\Sethro\Desktop\Test.mdb")
Sub RefreshDGV()
Dim sql As String
sql = "SELECT * FROM users"
Dim adapter As New OleDbDataAdapter(sql, con1)
Dim dt As New DataTable("users")
adapter.Fill(dt)
frmMain.dgv1.DataSource = dt
End Sub
Sub ClearTextBox(ByVal FormName As Form)
For Each txt As Control In FormName.Controls
If TypeOf txt Is TextBox Then
CType(txt, TextBox).Text = ""
End If
Next
End Sub
End Module
Ive attached the project file so you can open it up and see all the action for yourself.
[attachment=15608:attachment]
MultiQuote
| 