偶用VB+SQL做个数据库处理的程序,但是每次对数据库的操作都要进行:连接-处理-断开,偶觉得好麻烦各位可否帮偶改为个共享连接!以下分别为VB菜码两例:///////////////////////////////////////////
对text控件的保存'數據連接
Dim ddns As String
Dim cN As New ADODB.Connection
cN.CommandTimeout = 0
cN.Provider = "SQLOLEDB"
cN.Properties("DATA SOURCE").Value = 127.0.0.1
cN.Properties("INITIAL CATALOG").Value = testdb
cN.Properties("INTEGRATED SECURITY").Value = "SSPI"
cN.Open
Dim FHQ As RecordsetDim strCKSPck As String
strCKSPck = "INSERT INTO CKPBRK(pch,ly,rq)" & _
"VALUES('" & Trim(text1.Text) & "','" & Trim(text2.Text) & "','" & CDate(Trim(rq.Value)) & "')"
Set FHQ = New ADODB.Recordset
FHQ.Open strCKSPck, cN
fhq.close
cn.close//////////////////////////////////////////////////////////////////////////对一窗口的MSHFlexGrid1控件的应用+ADODC1
Adodc.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=SA;Initial Catalog=testdb;Data Source=127.0.0.1"
Adodc1RecordSource = "select PCH,ly,rq from ckpbrk "
Adodc1Refresh
Set MSHFlexGrid1.DataSource = Adodc1
///////////////////////////////////////以上偶地菜码!
对text控件的保存'數據連接
Dim ddns As String
Dim cN As New ADODB.Connection
cN.CommandTimeout = 0
cN.Provider = "SQLOLEDB"
cN.Properties("DATA SOURCE").Value = 127.0.0.1
cN.Properties("INITIAL CATALOG").Value = testdb
cN.Properties("INTEGRATED SECURITY").Value = "SSPI"
cN.Open
Dim FHQ As RecordsetDim strCKSPck As String
strCKSPck = "INSERT INTO CKPBRK(pch,ly,rq)" & _
"VALUES('" & Trim(text1.Text) & "','" & Trim(text2.Text) & "','" & CDate(Trim(rq.Value)) & "')"
Set FHQ = New ADODB.Recordset
FHQ.Open strCKSPck, cN
fhq.close
cn.close//////////////////////////////////////////////////////////////////////////对一窗口的MSHFlexGrid1控件的应用+ADODC1
Adodc.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=SA;Initial Catalog=testdb;Data Source=127.0.0.1"
Adodc1RecordSource = "select PCH,ly,rq from ckpbrk "
Adodc1Refresh
Set MSHFlexGrid1.DataSource = Adodc1
///////////////////////////////////////以上偶地菜码!
As String) _
As ADODB.Recordset Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
Dim Msgstring As String
On Error GoTo ExecuteSQL_Error sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.CursorLocation = adUseClient
cnn.Open ConnectString
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 = NothingExit FunctionExecuteSQL_Error:
Msgstring = "查询错误: " & _
Err.Description
Debug.Print Msgstring
Resume ExecuteSQL_Exit
End Function用这个函数也可以.
调用示例:
set Rst=executesql("select * from table")call executesql("update table set ....")