1.查询模块:
Public Function Exesql(ByVal sql As String, msgstring As String) As ADODB.Recordset
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
On Error GoTo runsql_error
cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=student"
cn.Open
cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
cmd.CommandText = sql
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
rs.Open cmd
Set Exesql = rs
Exit Function
runsql_error:
MsgBox "错误:" & Err.Description
End Function
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
On Error GoTo runsql_error
cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=student"
cn.Open
cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
cmd.CommandText = sql
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
rs.Open cmd
Set Exesql = rs
Exit Function
runsql_error:
MsgBox "错误:" & Err.Description
End Function
2.综合模块:
Public Function Executesql(ByVal sql As String, msgstring As String) As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stokens() As String
On Error GoTo executesql_error
stokens = Split(sql)
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=student"
If InStr("insert,delete,update", UCase$(stokens(0))) Then
cnn.Execute sql
msgstring = stokens(0) & "query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(sql), cnn, adOpenKeyset, adLockOptimistic
Set Executesql = rst
msgstring = "查询到" & rst.RecordCount & "条记录"
End If
executesql_exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
executesql_error:
msgstring = "查询错误" & Err.Description
Resume executesql_exit
End Function
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stokens() As String
On Error GoTo executesql_error
stokens = Split(sql)
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=student"
If InStr("insert,delete,update", UCase$(stokens(0))) Then
cnn.Execute sql
msgstring = stokens(0) & "query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(sql), cnn, adOpenKeyset, adLockOptimistic
Set Executesql = rst
msgstring = "查询到" & rst.RecordCount & "条记录"
End If
executesql_exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
executesql_error:
msgstring = "查询错误" & Err.Description
Resume executesql_exit
End Function
3.调用示例:
Private Sub Command1_Click()
Dim txtsql As String
Dim mrc As ADODB.Recordset
Dim msgtext As String
txtsql = "select * from student where 学号='" & Text1.Text & "' "
Set mrc = Exesql(txtsql, msgtext)
Set DataGrid1.DataSource = mrc
End Sub
Dim txtsql As String
Dim mrc As ADODB.Recordset
Dim msgtext As String
txtsql = "select * from student where 学号='" & Text1.Text & "' "
Set mrc = Exesql(txtsql, msgtext)
Set DataGrid1.DataSource = mrc
End Sub
转载于:https://blog.51cto.com/lucky/23385