我写一个简单的程序,操作sqlserver,程序能运行sql语句,并且能得到语句执行的结果
不用显示数据,完全由sql语句来操作
求例子
不用显示数据,完全由sql语句来操作
求例子
解决方案 »
- 以下xml文档转成XSL格式,如何使用DOC对象中的方法selectNodes方法
- CRC32得出负数问题
- 网络编程问题,如何第一时间知道IP冲突
- 自己学做.ocx控件按钮,可为什么不响应mousedown\up\move事件?
- 我的vb在写wisock时,常常在调试时出现卡死的现象,怎么解决?
- vb如何插入字符串
- 请问:多步操作产生错误,请检查每步的状态值!是什么错误?内有代码
- VB+SQL Server设备管理系统,望大虾们帮我 到现在我还不会VB和SQL Server连接
- 与VB有关的API
- 我想在程序中调用Winzip压缩/解压文件,有谁知道命令行参数?
- 分享我的自定义函数,还有一个环节有待解决.[大家多提意见]
- ODBC_for_Oracle连接错误,在线等待!
As String
'returns a DB ConnectString
ConnectString = "FileDSN=hotel.dsn;UID=sa;PWD="
End Function
Public Function ExecuteSQL(ByVal SQL _
As String, MsgString As String) _
As ADODB.Recordset
'executes SQL and returns Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
On Error GoTo ExecuteSQL_Error
sTokens = Split(SQL)
Set cnn = New ADODB.Connection
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
'rst.MoveLast 'get RecordCount
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & _
" 条记录 "
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
ExecuteSQL_Error:
MsgString = "查询错误: " & _
Err.Description
Resume ExecuteSQL_Exit
End Function这样的代码我看了,好像没有sql执行正确与否的标记
或者是否可以从Recordset中判断出正确与否呢?
Dim cn As Object, rs As Object
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.CursorLocation = adUseClient
'打开数据库连接,具体的自己改过来
cn.Open "Provider=MSDataShape;Data Provider=SQLOLEDB.1;Password=;Persist Security Info=False;User ID=sa;Initial Catalog=Test;Data Source=ljx"
Set rs = cn.Execute("select * from 表") '查询数据
MsgBox "查询到的记录数为:" & rs.RecordCount
If rs.State <> adStateClosed Then rs.Close
If cn.State <> adStateClosed Then cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
我希望sql执行后,有一个返回值,告诉我是否成功,
同样要求delete ,update
retrieve就给我Recordset就好了
急用,不想在这个上面发费时间去看书
As String, MsgString As String, Optional sFlag As Boolean = False) _
As ADODB.Recordset
'executes SQL and returns Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
sFlag = False ' 设置成功表示为False
On Error GoTo ExecuteSQL_Error
sTokens = Split(SQL)
Set cnn = New ADODB.Connection
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
'rst.MoveLast 'get RecordCount
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & _
" 条记录 "
End If
sFlag = True ' 如果执行过程没有出错,则让sflag返回True
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
ExecuteSQL_Error:
MsgString = "查询错误: " & _
Err.Description
Resume ExecuteSQL_Exit
End Function'调用例子:
Private Sub Command1_Click()
Dim strSql As String,sFlag As Boolean
strSql="Update 表 Set 列1='1'"
ExecuteSQL strSql,"",sFlag
If sFlag Then
MsgBox "语句执行成功!"
Else
MsgBox "语句执行失败!"
End If
End Sub