Hi Talimore,
Welcome to MSDN community!
The following tutorial may be helpful to you.
Tutorial 1: How to read/write database or make simple Data Access application(Next, Previous, First, Last, Update, Delete, Insert, Save)?
Method 1: Update (Insert/Update/Delete) data back into MS Access database from DataGridView.
http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/5980181e-f666-4f0a-ab50-c4ebecf96f02/
|
ImportsSystem.Data.OleDb
|
|
|
|
PublicClassForm1
|
|
|
|
DimmyDAAsOleDbDataAdapter
|
|
DimmyDataSetAsDataSet
|
|
|
|
PrivateSubForm1_Load(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMyBase.Load
|
|
DimconAsOleDbConnection=NewOleDbConnection("Provider=Microsoft.jet.oledb.4.0;datasource=|DataDirectory|\myDB.mdb")
|
|
DimcmdAsOleDbCommand=NewOleDbCommand("SELECT*FROMTable1",con)
|
|
con.Open()
|
|
myDA=NewOleDbDataAdapter(cmd)
|
|
'AutomaticallygeneratesDeleteCommand,UpdateCommandandInsertCommandforDataAdapterobject
|
|
DimbuilderAsOleDbCommandBuilder=NewOleDbCommandBuilder(myDA)
|
|
myDataSet=NewDataSet()
|
|
myDA.Fill(myDataSet,"MyTable")
|
|
DataGridView1.DataSource=myDataSet.Tables("MyTable").DefaultView
|
|
con.Close()
|
|
con=Nothing
|
|
EndSub
|
|
|
|
'Savedatabackintodatabase
|
|
PrivateSubButton1_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButton1.Click
|
|
Me.Validate()
|
|
Me.myDA.Update(Me.myDataSet.Tables("MyTable"))
|
|
Me.myDataSet.AcceptChanges()
|
|
EndSub
|
|
|
|
EndClass
|
Method 2: Execute Select/Insert/Delete/Update T-SQL commands in VB.NET code
Code sample: How to Select/Insert/Delete/Update records in MS Access database in VB.NET
http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/87913f28-992d-4705-963b-cb0ffa53d8dd/
Code sample: How to Select/Insert/Delete/Update records in SQL Server database in VB.NET
http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/89e1067d-16e7-44e8-b12d-d78845bf255f/
Method 3.Using Data Wizard with BindingNavigator control.
Please check the 11th post in this threadfor detailed walkthrough:
http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/ff3f953b-da66-4f03-b4e4-981bab7d783b/
Method 4. Using DataSet/DataTable/DataAdapter in VB.NET code
Please check the 12th post and 13th post in this thread for detailed code sample:
http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/ff3f953b-da66-4f03-b4e4-981bab7d783b/
Tutorial 2: How to search and filer database table records?
Here are three approaches to search records from database and filter them.
1) Use T-SQL Select command to filter records
Prerequisites: DataGridView1 and TextBox1 on Form1.
|
Imports System.Data.OleDb
Public Class Form1
' Handle TextBox_TextChanged event
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
Dim keywords As String = TextBox1.Text
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")
' Use wildcard
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1 WHERE Filed1 Like '%" & keywords & "%' ", con)
' or Where Filed1='" & keywords & "'
con.Open()
Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
Dim myDataSet As DataSet = New DataSet()
myDA.Fill(myDataSet, "MyTable")
DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
End Sub
End Class
|
2) Use DataView.RowFilter Property to filter records
Prerequisites: DataGridView1 and TextBox1 on Form1.
|
Imports System.Data.OleDb
Public Class Form1
Dim ds As DataSet
' Firstly binding all records to DataGridView
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)
con.Open()
Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
ds = New DataSet()
myDA.Fill(ds, "MyTable")
con.Close()
DataGridView1.DataSource = ds.Tables("MyTable").DefaultView
End Sub
'Then filter datatable view
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
Dim keywords As String = TextBox1.Text
ds.Tables("MyTable").DefaultView.RowFilter = "Field1 =" & keywords
' or = "Field1 Like '%" & keywords & "%' "
End Sub
End Class
|
Related thread:
http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/bdd212be-f815-4023-9db1-582b2439987a
3) You can use LINQ to SQL to filter records if you use SQL Server database.
|
Dim db As DataClasses1DataContext = New DataClasses1DataContext()
' Using Like wildcard in LING to SQL
Dim tableQuery = _
From t In db.Table1 Where t.Filed1 Like "%" & keywords & "%" _
Select t
' Or using String.Contains method instead of Like wildcard in LING to SQL
Dim tableQuery = _
From t In db.Table1 Where t.Filed1.Contains(keywords) _
Select t
DataGridView1.DataSource = tableQuery
|
Some tutorials about LINQ to SQL:
http://msdn.microsoft.com/en-us/library/bb546190.aspx
http://blogs.msdn.com/charlie/archive/2007/11/19/connect-to-a-sql-database-and-use-the-sql-designer.aspx
http://blogs.msdn.com/mitsu/archive/2008/04/02/visual-linq-query-builder-for-linq-to-sql-vlinq.aspx
Best regards,
Martin Xie
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
Home > Visual Basic Language > Need help with Management System Project, Searching a TXT file and getting data when clicking in a listbox.