how to run the stored procedure 'sp_who' and get the result in VB

[lechuck]

I want to get the result from the stored procedure sp_who (on a SQL server) into my VB code.
sp_who shows an table so it would be nice to get the result in an adodc recordset.

Thanks
/lechuck

 

[chiph]

When you call the Open method on your ADO Recordset object, set the option (I think it's the last one) to adCmdStoredProc.

Chip H.

 

[lechuck]

I have tried the following code but it dosn't work. Any idea why?
/Regards LeChuck


Dim cnn1 As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim strCnn As String

Set cnn1 = New ADODB.Connection
strCnn = "Provider=SQLOLEDB.1;Password=;Persist Security Info=False;User ID=sa;Initial Catalog=MyDB;Data Source=(local)
cnn1.Open strCnn
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn1

cmd.CommandText = "sp_who"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 10

Set rst = cmd.Execute()

 

[lechuck]

I have now solved this problem, if anyone want to have the code here it is:


Dim cn As New rdoConnection
Dim qr As New rdoQuery
Dim rs As rdoResultset

With cn
.Connect = "DRIVER={SQL Server};Server=(local);Database=MyDB;Uid=sa;Pwd="
.EstablishConnection prompt = rdDriverNoPrompt
End With

cn.Execute "Use Master"
qr.SQL = "sp_who"
qr.CursorType = rdOpenDynamic
Set qr.ActiveConnection = cn
qr.Execute

Set rs = qr.OpenResultset(rdOpenForwardOnly, rdConcurReadOnly)

'To print the result in a listview:
Dim lstItem As ListItem

Do While Not rs.EOF
Set lstItem = ListView1.ListItems.Add(, , Trim(rs!spid))
lstItem.SubItems(1) = Trim(rs!Status)
lstItem.SubItems(2) = Trim(rs!loginame)
lstItem.SubItems(3) = Trim(rs!HostName)
lstItem.SubItems(4) = Trim(rs!blk)
lstItem.SubItems(5) = Trim(rs!dbname)
lstItem.SubItems(6) = Trim(rs!cmd)

rs.MoveNext
Loop