使用ODBC只是连接到数据库而已 如下面几种连接数据库的方式,连接数据库之后调用存储过程'几种数据库连接方式 Public Sub ConnectionStringX() Dim cnn1 As adodb.Connection Dim cnn2 As adodb.Connection Dim cnn3 As adodb.Connection Dim cnn4 As adodb.Connection ' 不使用数据源名 (DSN) 打开连接。 Set cnn1 = New adodb.Connection cnn1.ConnectionString = "driver={SQL Server};" & _ "server=bigsmile;uid=sa;pwd=pwd;database=pubs" cnn1.ConnectionTimeout = 30 cnn1.Open
' 使用 DSN 和 ODBC 标记打开连接。 Set cnn2 = New adodb.Connection cnn2.ConnectionString = "DSN=Pubs;UID=sa;PWD=pwd;" cnn2.Open
' 使用 DSN 和 OLE DB 标记打开连接。 Set cnn3 = New adodb.Connection cnn3.ConnectionString = "Data Source=Pubs;User ID=sa;Password=pwd;" cnn3.Open
' 使用 DSN 和单个参数而非连接字符串打开连接。 Set cnn4 = New adodb.Connection cnn4.Open "Pubs", "sa", "pwd"
' 显示连接的状态。 MsgBox "cnn1 state: " & GetState(cnn1.State) & vbCr & _ "cnn2 state: " & GetState(cnn2.State) & vbCr & _ "cnn3 state: " & GetState(cnn3.State) & vbCr & _ "cnn4 state: " & GetState(cnn4.State) cnn4.Close cnn3.Close cnn2.Close cnn1.CloseEnd Sub Public Function GetState(intState As Integer) As String Select Case intState Case adStateClosed GetState = "adStateClosed" Case adStateOpen GetState = "adStateOpen" End SelectEnd Function建立数据连接之后,就可以调用存储过程了'调用存储过程返回值的例子'例子的意思是根据一个起始日期和日期间隔,算出结束日期并返回这个结束日期 '存储过程中包含三个参数,两个输入类型,一个输出类型 'sp_日期 为存储过程名Public Function DateWorkout(ByVal BeginDate As String, ByVal WorkoutDate As Integer) As String On Error GoTo ErrTrap Dim cmdWork As New adodb.Command Dim param1 As Parameter, param2 As Parameter, param3 As Parameter Set cmdWork.ActiveConnection = gadoCN cmdWork.CommandText = "sp_日期" cmdWork.CommandType = adCmdStoredProc Set param1 = cmdWork.CreateParameter("@BeginDate", adDBTimeStamp, adParamInput) cmdWork.Parameters.Append param1 Set param2 = cmdWork.CreateParameter("@DateNum", adInteger, adParamInput) cmdWork.Parameters.Append param2 Set param3 = cmdWork.CreateParameter("@EndDate", adDBTimeStamp, adParamReturnValue) cmdWork.Parameters.Append param3 param1.Value = BeginDate param2.Value = WorkoutDate param3.Value = 0 Call cmdWork.Execute DateWorkout = param3.Value On Error GoTo 0 Exit Function ErrTrap: DateWorkout = 0 On Error GoTo 0 End Function'示例存储过程代码 'CREATE PROCEDURE sp_日期(@BeginDate datetime,@DateNum integer,@EndDate datetime output) 'AS ' select @EndDate = @BeginDate+@DateNum 'GO
Set cnt = New ADODB.Connection cnt.ConnectionTimeout = 3 cnt.CommandTimeout = 300 cnt.excute("存储过程名 参数1,参数2") 也一样
如下面几种连接数据库的方式,连接数据库之后调用存储过程'几种数据库连接方式
Public Sub ConnectionStringX() Dim cnn1 As adodb.Connection
Dim cnn2 As adodb.Connection
Dim cnn3 As adodb.Connection
Dim cnn4 As adodb.Connection ' 不使用数据源名 (DSN) 打开连接。
Set cnn1 = New adodb.Connection
cnn1.ConnectionString = "driver={SQL Server};" & _
"server=bigsmile;uid=sa;pwd=pwd;database=pubs"
cnn1.ConnectionTimeout = 30
cnn1.Open
' 使用 DSN 和 ODBC 标记打开连接。
Set cnn2 = New adodb.Connection
cnn2.ConnectionString = "DSN=Pubs;UID=sa;PWD=pwd;"
cnn2.Open
' 使用 DSN 和 OLE DB 标记打开连接。
Set cnn3 = New adodb.Connection
cnn3.ConnectionString = "Data Source=Pubs;User ID=sa;Password=pwd;"
cnn3.Open
' 使用 DSN 和单个参数而非连接字符串打开连接。
Set cnn4 = New adodb.Connection
cnn4.Open "Pubs", "sa", "pwd"
' 显示连接的状态。
MsgBox "cnn1 state: " & GetState(cnn1.State) & vbCr & _
"cnn2 state: " & GetState(cnn2.State) & vbCr & _
"cnn3 state: " & GetState(cnn3.State) & vbCr & _
"cnn4 state: " & GetState(cnn4.State) cnn4.Close
cnn3.Close
cnn2.Close
cnn1.CloseEnd Sub
Public Function GetState(intState As Integer) As String Select Case intState
Case adStateClosed
GetState = "adStateClosed"
Case adStateOpen
GetState = "adStateOpen"
End SelectEnd Function建立数据连接之后,就可以调用存储过程了'调用存储过程返回值的例子'例子的意思是根据一个起始日期和日期间隔,算出结束日期并返回这个结束日期
'存储过程中包含三个参数,两个输入类型,一个输出类型
'sp_日期 为存储过程名Public Function DateWorkout(ByVal BeginDate As String, ByVal WorkoutDate As Integer) As String
On Error GoTo ErrTrap
Dim cmdWork As New adodb.Command
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter
Set cmdWork.ActiveConnection = gadoCN
cmdWork.CommandText = "sp_日期"
cmdWork.CommandType = adCmdStoredProc
Set param1 = cmdWork.CreateParameter("@BeginDate", adDBTimeStamp, adParamInput)
cmdWork.Parameters.Append param1
Set param2 = cmdWork.CreateParameter("@DateNum", adInteger, adParamInput)
cmdWork.Parameters.Append param2
Set param3 = cmdWork.CreateParameter("@EndDate", adDBTimeStamp, adParamReturnValue)
cmdWork.Parameters.Append param3
param1.Value = BeginDate
param2.Value = WorkoutDate
param3.Value = 0
Call cmdWork.Execute
DateWorkout = param3.Value
On Error GoTo 0
Exit Function
ErrTrap:
DateWorkout = 0
On Error GoTo 0
End Function'示例存储过程代码
'CREATE PROCEDURE sp_日期(@BeginDate datetime,@DateNum integer,@EndDate datetime output)
'AS
' select @EndDate = @BeginDate+@DateNum
'GO
cnt.ConnectionTimeout = 3
cnt.CommandTimeout = 300
cnt.excute("存储过程名 参数1,参数2")
也一样