Home > Visual Basic Language > SQL CODE PROBLEM

SQL CODE PROBLEM

Dear all, I'm using SQL as a database in VB 2010, I already adda table to my form as a datagrid view but I don't know how to add buttons for filtering, deleting and adding things in my table, for example I want to have one button on my form call it search button to search in table names or Phone Numbers. how can I do that and where shall I write the code?
waiting for help.

Moved byedhickeyMSFTMonday, November 23, 2009 9:13 PMMore appropriate Forum (From:Microsoft SQL Server Modeling)
Moved byedhickeyMSFTMonday, November 23, 2009 9:18 PMMore appropriate Forum (From:.NET Framework inside SQL Server)

allan77 Sunday, November 22, 2009 8:49 PM

Question: How to search and filer database table records with DataGridView?

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

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

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.

Marked As Answer byMartin Xie - MSFTMSFT, ModeratorMonday, November 30, 2009 7:37 AM

Martin Xie - MSFT Monday, November 30, 2009 7:36 AM

Dear all, I'm using SQL as a database in VB 2010, I already adda table to my form as a datagrid view but I don't know how to add buttons for filtering, deleting and adding things in my table, for example I want to have one button on my form call it search button to search in table names or Phone Numbers. how can I do that and where shall I write the code?
waiting for help.

have a look here please:

http://www.java2s.com/Code/VB/CatalogVB.htm

Don't judge me, just Upgrade me. Thanks!

Malange Tuesday, November 24, 2009 11:01 PM

Hi Allan,

Welcome to MSDN community!

Here are four methods tomake simple Data Access application(Next, Previous, First, Last, Update,Delete,Insert, Save) for you to check.

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/

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.

Martin Xie - MSFT Monday, November 30, 2009 7:32 AM

Question: How to search and filer database table records with DataGridView?

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

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

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.

Marked As Answer byMartin Xie - MSFTMSFT, ModeratorMonday, November 30, 2009 7:37 AM

Martin Xie - MSFT Monday, November 30, 2009 7:36 AM

You can use google to search for other answers

Custom Search

More Threads

• which event to use to monitor Trackbar status?

• Few Question

• sending text file over the network

• why i can remove in the collection inside this for each? I think I should not be able to do it

• Error while inserting data in to sql server from vb.net 2003

• Icons in Menus

• Help using SDK

• How do I assign security rights to a windows service that will allow it to access an intranet windows share?

• create new object of type (type of another object) ... or better solution

• 'yield' keyword in VB.Net