VB调用SQLServer数据库的系统存储过程,比如:
Set CnBill = New ADODB.Connection
CnBill.Open Cn.ConnectionString
CnBill.BeginTrans
Set RsView = CnBill.Execute("sp_columns 'icstockbill'")
For i = 0 To RsView.RecordCount
Next i
CnBill.CommitTrans
CnBill.Close
Set CnBill = Nothing
但是得到的结果是RsView.RecordCount=-1,而我的表结构不只一个字段。不知道怎么回事,有没有其它好的方法????
Set CnBill = New ADODB.Connection
CnBill.Open Cn.ConnectionString
CnBill.BeginTrans
Set RsView = CnBill.Execute("sp_columns 'icstockbill'")
For i = 0 To RsView.RecordCount
Next i
CnBill.CommitTrans
CnBill.Close
Set CnBill = Nothing
但是得到的结果是RsView.RecordCount=-1,而我的表结构不只一个字段。不知道怎么回事,有没有其它好的方法????
RsView.CursorLocation = adUseClient
RsView.CursorLocation = adUseClient
或者 Set CnBill = New ADODB.Connection
CnBill.Open Cn.ConnectionString
CnBill.BeginTrans
Set RsView = CnBill.Execute("sp_columns 'icstockbill'")
if RsView.eof=false or RsView.bof=false then RsView.movefirst
do while not RsView.eof RsView.movenext
loop
CnBill.CommitTrans
CnBill.Close
Set CnBill = Nothing
无论如何你是取不到recordset的值的,总是-1
如果你要取该值,必须设置为客户端游标
.CursorLocation = adUseClient
但客户端游标比服务器游标的性能要差一些
如果性能很重要,可以在原来的存储过程中最后加上
select @@rowcount
将上一次sql语句影响的行数返回一个值
也可以达到你的目的
'1.打开数据
Set Cn = New ADODB.Connection
'Set RS = New ADODB.Command
lsQtSQLConnStr = "Provider=SQLOLEDB.1;Persist Security " & _
"Info=False;Initial Catalog=" & _
"LYB" & ";Data Source=" & _
"lyb" & ";User ID=" & _
"sa" & ";Password=" & _
"" & ";"
Cn.Open lsQtSQLConnStr
"user id"是用户名,"password"是密码.
CnBill.Open Cn.ConnectionString
CnBill.BeginTrans
CnBill.CursorLocation = adUseClient
Set RsView = CnBill.Execute("sp_columns 'icstockbill'")
For i = 0 To RsView.RecordCount
Next i
CnBill.CommitTrans
CnBill.Close
Set CnBill = Nothing