如题我代码调试的时候
在执行UPDATE操作时 SELECT不了数据一直处于等待状态
直到退出函数
Public Function ExecuteSynEx(ByVal strConn As String, _
ByVal strTableName As String, _
ByVal bIsIdentity As Long, _
ByVal strAddSQL As Variant, _
ByVal strUpdSQL As Variant, _
ByVal strDelSQL As Variant, _
ByRef ReturnMsg As String) As Boolean
10 On Error GoTo HError
Dim cn As ADODB.Connection
Dim lInterID As Long, lTransType As Long
Dim i As Long
Dim strSQL As String
Dim obj As Object
Dim lSQLCount As Long
'20 GetObjectContext.SetAbort
30 ExecuteSynEx = False
40 Set cn = New ADODB.Connection
50 cn.Open strConn
60 cn.CursorLocation = adUseClient
70 cn.CommandTimeout = 3600'80 cn.Execute "alter table " & strTableName & " nocheck constraint all"
If bIsIdentity = 1 Then
cn.Execute "SET IDENTITY_INSERT " & strTableName & " on"
End If
90 Set obj = CreateObject("KFOX.StringBuilder")
100 lSQLCount = 0
110 For i = LBound(strDelSQL) To UBound(strDelSQL)
120 strSQL = Trim(strDelSQL(i))
130 If Len(strSQL) > 0 Then
140 lSQLCount = lSQLCount + 1
150 obj.Append strSQL
160 End If
170 If lSQLCount = 100 Then
180 cn.Execute obj.StringValue
190 obj.Remove 1, obj.length
200 lSQLCount = 0
210 If cn.Errors.Count > 0 Then
220 Err.Raise -1, "ExecuteSynEx", cn.Errors.Item(0).Description
230 End If
240 End If
250 Next i
260 If lSQLCount > 0 Then
270 cn.Execute obj.StringValue
280 obj.Remove 1, obj.length
290 lSQLCount = 0
300 If cn.Errors.Count > 0 Then
310 Err.Raise -1, "ExecuteSynEx", cn.Errors.Item(0).Description
320 End If
330 End If
340 Set obj = Nothing
350 Set obj = CreateObject("KFOX.StringBuilder")
360 lSQLCount = 0
370 For i = LBound(strAddSQL) To UBound(strAddSQL)
380 strSQL = Trim(strAddSQL(i))
390 If Len(strSQL) > 0 Then
400 lSQLCount = lSQLCount + 1
410 obj.Append strSQL
420 End If
430 If lSQLCount = 100 Then
440 cn.Execute obj.StringValue
450 obj.Remove 1, obj.length
460 lSQLCount = 0
470 If cn.Errors.Count > 0 Then
480 Err.Raise -1, "ExecuteSynEx", cn.Errors.Item(0).Description
490 End If
500 End If
510 Next i
520 If lSQLCount > 0 Then
530 cn.Execute obj.StringValue
540 obj.Remove 1, obj.length
550 lSQLCount = 0
560 If cn.Errors.Count > 0 Then
570 Err.Raise -1, "ExecuteSynEx", cn.Errors.Item(0).Description
580 End If
590 End If
600 Set obj = Nothing610 Set obj = CreateObject("KFOX.StringBuilder")
620 lSQLCount = 0
630 For i = LBound(strUpdSQL) To UBound(strUpdSQL)
640 strSQL = Trim(strUpdSQL(i))
650 If Len(strSQL) > 0 Then
660 lSQLCount = lSQLCount + 1
670 obj.Append strSQL
680 End If
690 If lSQLCount = 100 Then
700 cn.Execute obj.StringValue
710 obj.Remove 1, obj.length
720 lSQLCount = 0
730 If cn.Errors.Count > 0 Then
740 Err.Raise -1, "ExecuteSynEx", cn.Errors.Item(0).Description
750 End If
760 End If
770 Next i
780 If lSQLCount > 0 Then
790 cn.Execute obj.StringValue
800 obj.Remove 1, obj.length
810 lSQLCount = 0
820 If cn.Errors.Count > 0 Then
830 Err.Raise -1, "ExecuteSynEx", cn.Errors.Item(0).Description
840 End If
850 End If
860 Set obj = Nothing If bIsIdentity = 1 Then
cn.Execute "SET IDENTITY_INSERT " & strTableName & " off"
End If
'870 cn.Execute "alter table " & strTableName & " check constraint all"'880 GetObjectContext.SetComplete
890 ExecuteSynEx = True
900 Exit Function
HError:'910 cn.Execute "alter table " & strTableName & " check constraint all"
If bIsIdentity = 1 Then
cn.Execute "SET IDENTITY_INSERT " & strTableName & " off"
End If
920 GetObjectContext.SetAbort
930 ExecuteSynEx = False
940 Err.Raise -1, "ExecuteSynEx", Err.Description & "Erl:" & Erl
End Function
在执行UPDATE操作时 SELECT不了数据一直处于等待状态
直到退出函数
Public Function ExecuteSynEx(ByVal strConn As String, _
ByVal strTableName As String, _
ByVal bIsIdentity As Long, _
ByVal strAddSQL As Variant, _
ByVal strUpdSQL As Variant, _
ByVal strDelSQL As Variant, _
ByRef ReturnMsg As String) As Boolean
10 On Error GoTo HError
Dim cn As ADODB.Connection
Dim lInterID As Long, lTransType As Long
Dim i As Long
Dim strSQL As String
Dim obj As Object
Dim lSQLCount As Long
'20 GetObjectContext.SetAbort
30 ExecuteSynEx = False
40 Set cn = New ADODB.Connection
50 cn.Open strConn
60 cn.CursorLocation = adUseClient
70 cn.CommandTimeout = 3600'80 cn.Execute "alter table " & strTableName & " nocheck constraint all"
If bIsIdentity = 1 Then
cn.Execute "SET IDENTITY_INSERT " & strTableName & " on"
End If
90 Set obj = CreateObject("KFOX.StringBuilder")
100 lSQLCount = 0
110 For i = LBound(strDelSQL) To UBound(strDelSQL)
120 strSQL = Trim(strDelSQL(i))
130 If Len(strSQL) > 0 Then
140 lSQLCount = lSQLCount + 1
150 obj.Append strSQL
160 End If
170 If lSQLCount = 100 Then
180 cn.Execute obj.StringValue
190 obj.Remove 1, obj.length
200 lSQLCount = 0
210 If cn.Errors.Count > 0 Then
220 Err.Raise -1, "ExecuteSynEx", cn.Errors.Item(0).Description
230 End If
240 End If
250 Next i
260 If lSQLCount > 0 Then
270 cn.Execute obj.StringValue
280 obj.Remove 1, obj.length
290 lSQLCount = 0
300 If cn.Errors.Count > 0 Then
310 Err.Raise -1, "ExecuteSynEx", cn.Errors.Item(0).Description
320 End If
330 End If
340 Set obj = Nothing
350 Set obj = CreateObject("KFOX.StringBuilder")
360 lSQLCount = 0
370 For i = LBound(strAddSQL) To UBound(strAddSQL)
380 strSQL = Trim(strAddSQL(i))
390 If Len(strSQL) > 0 Then
400 lSQLCount = lSQLCount + 1
410 obj.Append strSQL
420 End If
430 If lSQLCount = 100 Then
440 cn.Execute obj.StringValue
450 obj.Remove 1, obj.length
460 lSQLCount = 0
470 If cn.Errors.Count > 0 Then
480 Err.Raise -1, "ExecuteSynEx", cn.Errors.Item(0).Description
490 End If
500 End If
510 Next i
520 If lSQLCount > 0 Then
530 cn.Execute obj.StringValue
540 obj.Remove 1, obj.length
550 lSQLCount = 0
560 If cn.Errors.Count > 0 Then
570 Err.Raise -1, "ExecuteSynEx", cn.Errors.Item(0).Description
580 End If
590 End If
600 Set obj = Nothing610 Set obj = CreateObject("KFOX.StringBuilder")
620 lSQLCount = 0
630 For i = LBound(strUpdSQL) To UBound(strUpdSQL)
640 strSQL = Trim(strUpdSQL(i))
650 If Len(strSQL) > 0 Then
660 lSQLCount = lSQLCount + 1
670 obj.Append strSQL
680 End If
690 If lSQLCount = 100 Then
700 cn.Execute obj.StringValue
710 obj.Remove 1, obj.length
720 lSQLCount = 0
730 If cn.Errors.Count > 0 Then
740 Err.Raise -1, "ExecuteSynEx", cn.Errors.Item(0).Description
750 End If
760 End If
770 Next i
780 If lSQLCount > 0 Then
790 cn.Execute obj.StringValue
800 obj.Remove 1, obj.length
810 lSQLCount = 0
820 If cn.Errors.Count > 0 Then
830 Err.Raise -1, "ExecuteSynEx", cn.Errors.Item(0).Description
840 End If
850 End If
860 Set obj = Nothing If bIsIdentity = 1 Then
cn.Execute "SET IDENTITY_INSERT " & strTableName & " off"
End If
'870 cn.Execute "alter table " & strTableName & " check constraint all"'880 GetObjectContext.SetComplete
890 ExecuteSynEx = True
900 Exit Function
HError:'910 cn.Execute "alter table " & strTableName & " check constraint all"
If bIsIdentity = 1 Then
cn.Execute "SET IDENTITY_INSERT " & strTableName & " off"
End If
920 GetObjectContext.SetAbort
930 ExecuteSynEx = False
940 Err.Raise -1, "ExecuteSynEx", Err.Description & "Erl:" & Erl
End Function
解决方案 »
- 用vb 远程访问(通过internet) SQL数据库,请问哪位有实例,教程,经验.麻烦赐教,谢谢.
- vb6的data report中如何让一页纸上打印固定的行数?
- 大虾:如何把SMALLDATETIME的值转换为单纯日期?就是去掉时间?
- 如何向已存在的EXECL文件添回一行数据?
- 如何删除一个树结点下的子结点?
- 网上说的不对呀(关于sndvol32.exe的问题)
- VB用到什么水平才算是高手?请各位高手提点建议!欢迎讨论
- 請問toolbar
- vb6.0 mschart
- sos,急救!!
- 当excel1中数据发生改变时,将数据复制到excel2中?VBA
- 正则表达式,实现VB内的Split功能
VB6 执行UPDATE操作是否会锁表?
我在查询分析器里面执行并不会锁
http://topic.csdn.net/u/20111219/11/4dbcb310-b322-42c3-8c18-6d4d79eeb6e7.html?27421
还没答案呢
而且是每条数据对应至少一条 SQL