以下是用VB在SQL Server中建表代码
Dim conn As New Connection
Dim rs As New Recordset
Dim sql As StringOn Error GoTo Err
conn.BeginTrans '启动事务
'sql = "master.dbo.xp_cmdshell 'osql -U " & UserID & " -P " & PWD & " -i """ & ExternFile & """'"
sql = "master.dbo.xp_cmdshell 'osql -U " & UserID & " -P " & PWD & " -d Text1 -i """ & ExternFile & """'"
conn.Open "provider=sqloledb;data source=" & ServerName & ";user id=" & UserID & ";pwd=" & PWD
conn.Execute sql '这一句执行的可能会慢一些
conn.CommitTrans '操作成功,提交事务更新数据
MsgBox "建表成功!"
Err:
conn.RollbackTrans '提交时出错,事务回滚【问题】:
1.事务使用方法是否正确?
2.捕捉错误后既让事务回滚,代码中的事务回滚方法是否正确?
3.在使用SQL Server作为数据库时,是否所有的提交、修改、更新等数据操作都要使用事务?
Dim conn As New Connection
Dim rs As New Recordset
Dim sql As StringOn Error GoTo Err
conn.BeginTrans '启动事务
'sql = "master.dbo.xp_cmdshell 'osql -U " & UserID & " -P " & PWD & " -i """ & ExternFile & """'"
sql = "master.dbo.xp_cmdshell 'osql -U " & UserID & " -P " & PWD & " -d Text1 -i """ & ExternFile & """'"
conn.Open "provider=sqloledb;data source=" & ServerName & ";user id=" & UserID & ";pwd=" & PWD
conn.Execute sql '这一句执行的可能会慢一些
conn.CommitTrans '操作成功,提交事务更新数据
MsgBox "建表成功!"
Err:
conn.RollbackTrans '提交时出错,事务回滚【问题】:
1.事务使用方法是否正确?
2.捕捉错误后既让事务回滚,代码中的事务回滚方法是否正确?
3.在使用SQL Server作为数据库时,是否所有的提交、修改、更新等数据操作都要使用事务?
Dim rs As New Recordset
Dim sql As StringOn Error GoTo Err'sql = "master.dbo.xp_cmdshell 'osql -U " & UserID & " -P " & PWD & " -i """ & ExternFile & """'"
sql = "master.dbo.xp_cmdshell 'osql -U " & UserID & " -P " & PWD & " -d Text1 -i """ & ExternFile & """'"
conn.Open "provider=sqloledb;data source=" & ServerName & ";user id=" & UserID & ";pwd=" & PWD
conn.BeginTrans '启动事务 //移到Connection.Open()之後
conn.Execute sql '这一句执行的可能会慢一些
conn.CommitTrans '操作成功,提交事务更新数据
MsgBox "建表成功!"
Exit Sub '//这个就不要我说了.
Err:
conn.RollbackTrans '提交时出错,事务回滚
3.在使用SQL Server作为数据库时,是否所有的提交、修改、更新等数据操作都要使用事务?
>>
是.否则SQLServer默认就是每条sql语句一个transaction.