查询数据库后如何在datagridview中显示
问题
答案
-
如果我用这种方法，那么当我直接在DataGridView修改完查询得到的数据之后，怎么更新数据库里面这几条记录啊（因为查询得到的可能有几条记录）......
我的意思，你应该弄一个类似SqlDataAdapter的东西（全局变量），配合四个Command（Select，Insert，Update和Delete），分别写上对应的SQL语句或者使用SqlCommandBuilder生成，然后把Select出来的DataTable绑定给DataGridView，这样一旦记录变更，调用Update(DataTable)方法即可更新到真实的数据库中。
如果你有其它意见或私下交流，请直接发送maledong_work@foxmail.com；或者
If you do not have QQ, please open the page and download it and click the image to talk or leave message for me.
下载MSDN桌面工具(Vista,Win7)
下载Technet桌面小工具(Vista,Win7)
慈善点击，点击此处- 已标记为答案 _Lei 2011年8月12日 4:51
-
如果我用这种方法，那么当我直接在DataGridView修改完查询得到的数据之后，怎么更新数据库里面这几条记录啊（因为查询得到的可能有几条记录）......
我的意思，你应该弄一个类似SqlDataAdapter的东西（全局变量），配合四个Command（Select，Insert，Update和Delete），分别写上对应的SQL语句或者使用SqlCommandBuilder生成，然后把Select出来的DataTable绑定给
相应的详细代码实现如下：Via DataGridView: Update (Insert/Update/Delete) data back into MS Access database from DataGridView.
Imports System.Data.OleDb
Public Class Form1
Dim myDA As OleDbDataAdapter
Dim myDataSet As DataSet
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=|DataDirectory|\myDB.mdb")
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)
con.Open()
myDA = New OleDbDataAdapter(cmd)
'Here one CommandBuilder object is required.
'It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object
Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)
myDataSet = New DataSet()
myDA.Fill(myDataSet, "MyTable")
DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
con.Close()
con = Nothing
End Sub
' Save data back into database
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Validate()
Me.myDA.Update(Me.myDataSet.Tables("MyTable"))
Me.myDataSet.AcceptChanges()
End Sub
End Class
Additionally, select/click your database file in Solution Explorer -> Properties Pane -> change the "copy to ouput directory" to "copy if newer"
Code sample: http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/5980181e-f666-4f0a-ab50-c4ebecf96f02/
Martin Xie [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
- 已标记为答案 _Lei 2011年8月12日 4:51
全部回复
-
datagridview已经绑定了数据库，并且我在窗口上放置了一个TextBox和Button用于查询（这里我打算用SQL语句来进行查询，因为datagridview已经绑定了数据库，这时我不知道要怎么将查询结果显示在datagridview上），我想实现当有查询结果后在datagridview将光标跳到该行或者只显示该行的数据应该怎么做啊，谢谢各位啦~~~~
1）显示数据：using (SqlDataAdapter adapter = new SqlDataAdapter("select * from xxx where [字段]=@parameter",new SqlConnection("你的数据库字符连接串"))
{
DataTable dt = new DataTable();
adapter.SelectCommand.Parameters.AddWithValue("@parameter",TextBox1.Text);
adapter.Fill(dt);
if(dt.Rows.Count>0)
{
dataGridView1.DataSource = dt;
//自动选择行选择
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView1.Rows[0].Selected = true; //默认选择第一行，光标定位}
}
如果你有其它意见或私下交流，请直接发送maledong_work@foxmail.com；或者
If you do not have QQ, please open the page and download it and click the image to talk or leave message for me.
下载MSDN桌面工具(Vista,Win7)
下载Technet桌面小工具(Vista,Win7)
慈善点击，点击此处 -
如果我用这种方法，那么当我直接在DataGridView修改完查询得到的数据之后，怎么更新数据库里面这几条记录啊（因为查询得到的可能有几条记录）......
我的意思，你应该弄一个类似SqlDataAdapter的东西（全局变量），配合四个Command（Select，Insert，Update和Delete），分别写上对应的SQL语句或者使用SqlCommandBuilder生成，然后把Select出来的DataTable绑定给DataGridView，这样一旦记录变更，调用Update(DataTable)方法即可更新到真实的数据库中。
如果你有其它意见或私下交流，请直接发送maledong_work@foxmail.com；或者
If you do not have QQ, please open the page and download it and click the image to talk or leave message for me.
下载MSDN桌面工具(Vista,Win7)
下载Technet桌面小工具(Vista,Win7)
慈善点击，点击此处- 已标记为答案 _Lei 2011年8月12日 4:51
-
谢谢Wei-Dong和小章的帮助！
Hi 小_Lei,
这里列举三种途径来查询数据库并过滤记录.
1) 使用 T-SQL Select 语句查询并过滤
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) 使用 DataView.RowFilter 属性来过滤数据
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) 如果你用的是SQL Server数据库的话，你还可以通过LINQ to SQL来查询并过滤数据.
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
一些资料关于 LINQ to SQL:
http://msdn.microsoft.com/en-us/library/bb546190.aspx
http://blogs.msdn.com/mitsu/archive/2008/04/02/visual-linq-query-builder-for-linq-to-sql-vlinq.aspx
Martin Xie [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
-
如果我用这种方法，那么当我直接在DataGridView修改完查询得到的数据之后，怎么更新数据库里面这几条记录啊（因为查询得到的可能有几条记录）......
我的意思，你应该弄一个类似SqlDataAdapter的东西（全局变量），配合四个Command（Select，Insert，Update和Delete），分别写上对应的SQL语句或者使用SqlCommandBuilder生成，然后把Select出来的DataTable绑定给
相应的详细代码实现如下：Via DataGridView: Update (Insert/Update/Delete) data back into MS Access database from DataGridView.
Imports System.Data.OleDb
Public Class Form1
Dim myDA As OleDbDataAdapter
Dim myDataSet As DataSet
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=|DataDirectory|\myDB.mdb")
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)
con.Open()
myDA = New OleDbDataAdapter(cmd)
'Here one CommandBuilder object is required.
'It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object
Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)
myDataSet = New DataSet()
myDA.Fill(myDataSet, "MyTable")
DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
con.Close()
con = Nothing
End Sub
' Save data back into database
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Validate()
Me.myDA.Update(Me.myDataSet.Tables("MyTable"))
Me.myDataSet.AcceptChanges()
End Sub
End Class
Additionally, select/click your database file in Solution Explorer -> Properties Pane -> change the "copy to ouput directory" to "copy if newer"
Code sample: http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/5980181e-f666-4f0a-ab50-c4ebecf96f02/
Martin Xie [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
- 已标记为答案 _Lei 2011年8月12日 4:51
-
真的很感谢，我已经实现这个功能了！！！！
Glad to hear that...
如果你有其它意见或私下交流，请直接发送maledong_work@foxmail.com；或者
If you do not have QQ, please open the page and download it and click the image to talk or leave message for me.
下载MSDN桌面工具(Vista,Win7)
下载Technet桌面小工具(Vista,Win7)
慈善点击，点击此处 -
对了，我还想问下如果DataGridView已经绑定了数据库，然后我想修改数据库，好像在数据库直接修改是不行的，这要重新绑定过，有什么好的方法吗
其实用不着那么麻烦，就是用DataAdapter生成的DataTable（这个东西应该用作公用变量），同时Handle它的ColumnChanged事件，调用DataAdapter.Update及时更新。
如果你有其它意见或私下交流，请直接发送maledong_work@foxmail.com；或者
If you do not have QQ, please open the page and download it and click the image to talk or leave message for me.
下载MSDN桌面工具(Vista,Win7)
下载Technet桌面小工具(Vista,Win7)
慈善点击，点击此处 -
