想用ADO的方式从SQL Server中读取一张表,在DATAGRID中显示。数据库已经连接成功,recordset也正常打开,但Datagrid就是一片空白,不知道为什么。做法已经和论坛上几位高手一样了呀,望指点!
Private Sub Form_Load() Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sqlstr As String
'使用ADO方式连接数据库
On Error GoTo Errorhandler
conn.ConnectionString = "Provider=sqloledb;" & _
"Server=127.0.0.1;" & _
"User Id=SA;" & _
"Password=SA;" & _
"Initial Catalog=Northwind;"
conn.Open
sqlstr = "SELECT * FROM Orders"
rs.Open sqlstr, conn, adOpenKeyset, adLockOptimistic
'测试rs是否绑定成功
'rs.MoveFirst
'msgbox rs.Fields(3).Value
'测试结果:连接成功,MSGBOX中弹出了数据库中相应的值 '绑定Recordset至Datagrid
Set DBGrid1.DataSource = rs
'关闭数据库连接
conn.Close
Set rs = Nothing
Set conn = Nothing
Exit Sub
'异常处理段
Errorhandler:
Dim ErrorMsg As String
ErrorMsg = "连接数据库失败!" & Err.Description
MsgBox ErrorMsg, vbCritical, "错误!"
conn.Errors.Clear
End Sub
Private Sub Form_Load() Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sqlstr As String
'使用ADO方式连接数据库
On Error GoTo Errorhandler
conn.ConnectionString = "Provider=sqloledb;" & _
"Server=127.0.0.1;" & _
"User Id=SA;" & _
"Password=SA;" & _
"Initial Catalog=Northwind;"
conn.Open
sqlstr = "SELECT * FROM Orders"
rs.Open sqlstr, conn, adOpenKeyset, adLockOptimistic
'测试rs是否绑定成功
'rs.MoveFirst
'msgbox rs.Fields(3).Value
'测试结果:连接成功,MSGBOX中弹出了数据库中相应的值 '绑定Recordset至Datagrid
Set DBGrid1.DataSource = rs
'关闭数据库连接
conn.Close
Set rs = Nothing
Set conn = Nothing
Exit Sub
'异常处理段
Errorhandler:
Dim ErrorMsg As String
ErrorMsg = "连接数据库失败!" & Err.Description
MsgBox ErrorMsg, vbCritical, "错误!"
conn.Errors.Clear
End Sub
引掉如下语句:
'关闭数据库连接
' conn.Close
' Set rs = Nothing
' Set conn = Nothing
Set DBGrid1.DataSource = rs加上
DBGrid1.refresh记录集打开使用静态试试
rs.Open sqlstr, conn, 1,1
Private Sub Command4_Click()
Set Conn = New ADODB.Connection
Set Rs = New ADODB.Recordset
Conn.ConnectionString = "PROVIDER=MSDASQL;DRIVER={SQL SERVER};SERVER=myh;UID=sa;PWD=myhwljghost;DATABASE=MySQLDB"
Rs.Open "select * from myh order by wlj ", 3, 1
Set Me.DataGrid1.DataSource = Rs
Conn.Open
End Sub
'这个一定可以,引用ado 2.5
'关闭数据库连接
conn.Close
Set rs = Nothing
Set conn = Nothing
连接不能关掉,否则肯定空白
换成FLEXGRID关了就可以.
两种控件的连接方式是不一样的.
'Private Sub Form_Load()
' Dim strConn As String
' Dim pubConn As New ADODB.Connection
' Dim rsTable As New ADODB.Recordset
' Dim strSQL As String
'
' strConn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=develop; password=12345;Data Source=ServerName"
' pubConn.Open strConn
'
' rsTable.CursorLocation = adUseClient
' strSQL = "select * from TableName"
' rsTable.Open strSQL, pubConn, adOpenDynamic, adLockOptimistic
' Set DataGrid1.DataSource = rsTable
'
'End Sub
原来不能关掉阿
唉,ASP编多了……
但这样的话会不会一直占用着数据库?To trendvb(Aaron):
请问如果要用FlexGrid,应该怎么写?
rs.CursorLocation = adUseClient
另外,在這樣的程序運行在內部網絡中,打開一個連接并保持不斷開,也只是在服務機上占用了大約一MB的緩存而已
RS 用全局变量,然后在窗体关闭时再关闭RS然后刷新的时候直接rs.Requery 刷新就可以
只要把 Set DBGrid1.DataSource = rs 改为 Set FlexGrid1.DataSource = rs 就可以了!
Dim Rst As ADODB.Recordset
Set Cnn = New ADODB.Connection
Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=数据库名;Persist Security Info=False"
Set Rst = New ADODB.Recordset
Rst.CursorLocation = adUseClient
Rst.Open "SELECT * FROM 表名", Cnn, adOpenForwardOnly, adLockReadOnly, adCmdText
Set Me.DataGrid1.DataSource = Rst '使用DataGrid控件把数据读入该控件
conn.Close
Set rs = Nothing
Set conn = Nothing
------------------------------------
不能关闭,否则报错
你调试时把
On Error GoTo Errorhandler
引掉,单步调试,你会发现当运行到conn.close前控件中是有内容的,当运行过这句以后就空了,这样你就能知道哪里出错了。呵呵,小小经验,见笑了。