基础的问题,解决给分~
Private Sub Timer2_Timer()
Dim i As Integer
sqlserverconnect = "driver={sql server};" & "server=al;" & "database=h
aha;" & "uid=;" & "pwd="
Set conn1 = New ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
For i = 1 To 41
Dim strupdate As String
strupdate = strupdate & "UPDATE TestTable Set 时间= '" & GpTime(i
) & "'" & ","
strupdate = strupdate & " WHERE (站号 = " & i & ")"
conn1.Open sqlserverconnect
rs.Open strupdate, conn1, adOpenKeyset, adLockOptimistic
rs.Close
conn1.Close
Next i
Set rs = Nothing
Set conn1 = Nothing
End Sub
为什么总是说我rs.Close 提示错误?一运行到这就死翘了!求求高人解决一下~
[解决办法]
update语句直接用conn.execute sql方法执行,无需rs
[解决办法]
Private Sub Timer2_Timer()
Dim i As Integer
on error goto Err_Label
sqlserverconnect = "driver={sql server};" & "server=al;" & "database=haha;" & "uid=;" & "pwd="
Set conn1 = New ADODB.Connection
conn1.Open sqlserverconnect
For i = 1 To 41
Dim strupdate As String
strupdate = "UPDATE TestTable Set 时间= '" & GpTime(i) & " '" & ","
strupdate = strupdate & " WHERE (站号 = " & i & ")"
conn1.execute strupdate
Next i
Err_Exit:
if not conn1 is nothing then
Set conn1 = Nothing
end if
Exit Sub
Err_Label:
MsgBox Err.Description
goto Err_Exit
End Sub
[解决办法]
'这样频繁打开数据库资源开销很大,建议做成单独的SUB放到模块里,要连接时再调用,查询时只需打开数据源,下面是一般的常用结构
Sub cnn()
On Error GoTo sqlerr1
Dim strCnn As String '数据库连接字符串
strCnn = "Provider=SQLOLEDB.1;Password=;Persist Security Info=false;User ID=sa;Initial Catalog=system;Data Source=192.168.X.X"
Set cnn1 = New ADODB.Connection
cnn1.CursorLocation = adUseClient
' cnn1.CommandTimeout = 10
cnn1.Open strCnn
sqlerr1:
If Err.Number <> 0 Then
msgbox err.Description
'MsgBox "数据库连接失败,程序将关闭!请注意以下几点:" & vbCrLf & vbCrLf & "1、本机和数据库服务器联网中断(可能VPN未连接)" _
& vbCrLf & "2、SQL数据库IP地址或机器名设置不正确" & vbCrLf & "3、SQL服务器管理器没有启动服务", 16, "错误:" & Err.Number
End
End If
End Sub