Where & How to work with exec method, Help Please!

[alec]

I need to create simple application using Visual basic 6.0 and I have a problem with the syntax as far as how to use exec method and where to place it.
I appreciate any help! thanks

First I set up:
Microsoft Common Controll 6.0
Microsoft Tabbed Dialog Control
Microsoft ADO Data Controls
Microsoft DataList Controls

Then I created connection object, command object and recordset object using VB code
Then I bound the control ( a ListView control ) to the recordset object.
But I did not get the result correctly ( ListView is Blank!!! )
The problem is : I need to use system stored procedure ( I'm using SQL server as back end ). So instead of using "select group_name from sp_helpgroup"
I need to use "exec sp_helpgroup" and get the group_name from here.
I don't know where and where I should place this Exec statement so that the group_name appear in the ListView drop down menu.
I've tested the code below with a select statement using different table, the code is working and I get the list I wanted. Anyone can help Me please?

Below is my simple code, any help is appreciated very much:

'create a connection object, command object and recordset object:
Option Explicit

Private WithEvents cnCCS As Connection
Private GetGroupList As Command
Private rsStoredGroupList As Recordset
-----------------------------------------------------------------------------------------------------------
Sub Form_Load()
Set cnCCS = New Connection

With cnCCS
.Provider = "MSDASQL.1;Persist Security Info=False;User ID=myname;Data Source=WEB"
.ConnectionString = cnCCS
.Open
End With

'Instantiate a command object
Set GetGroupList = New Command

'Setting the properties of Command Object
With GetGroupList
.ActiveConnection = cnCCS
.CommandText = &quot;Exec sp_helpgroup&quot; <-------- I also tried .CommandText =&quot;sp_helpgroup&quot; and did not work.
.Execute
End With

' Create an instant of the recordset object
Set rsStoredGroupList = New Recordset

'Using command object instead of recordset object property setting
Set rsStoredGroupList = GetGroupList.Execute

' Binding the control to the recordset object, in this case binding dbcboGroupList ( which is a ListView Control ) to the recordest
Set dbcboGroupList.RowSource = rsStoredGroupList

With dbcboGroupList
.ListField = &quot;group_name&quot;
.BoundColumn = &quot;group_id&quot;
.DataChanged = False
End With
End Sub
------------------------------------------------------------------------------------------------------------------------------------

Private Sub cnCCS_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
MsgBox &quot;Connection successful!&quot;
End Sub
------------------------------------------------------------------------------------------------------------------------------------

' After finish cerating Connection , recordset and Command object, in Form_Unload event procedures destroy them
Private Sub Form_Unload(Cancel As Integer)

rsStoredGroupList.Close
Set rsStoredGroupList = Nothing
Set GetGroupList = Nothing
cnCCS.Close
Set cnCCS = Nothing

End Sub
--------------------------------------------------------------------------------------------------------------------------------------

 

[oneshadow]

Tell the type of your ADODB.Command like this (not tested):

'Setting the properties of Command Object
With GetGroupList
.ActiveConnection = cnCCS
.CommandType = adCmdStoredProc
.CommandText = &quot;sp_helpgroup&quot;
'.Execute 'execute this command later
End With

'Execute this command
Set rsStoredGroupList = GetGroupList.Execute
Do Until rsStoredGroupList.EOF
'Do what you need here
rsStoredGroupList.MoveNext
Loop


See SQL Server Books Online for more help. Search for 'Stored procedures, executing \ Executing a store procedure

 

[alec]

Hi oneshadow!
I tried your suggestion and it is still the same. I did not get the list I wanted to appear on the DbCombo.

The only thing I need is to get the list of group from SQL system procedure sp_helpGroup and show it on the drop down list of DataCombo control.

If I use the same code but change the &quot;exec sp_helpgroup&quot; statement with &quot;select * from other table&quot; , the code is working. But because I need to use a SQL system procedure
I failed to make it work.

 

[oneshadow]

Hi alec,
sorry I haven't test my code above (tight deadline)
I've just realized something in your code. What is the connection string value of cnCCS? It points to master database on your SQL Server, doesn't it?

Here my code tested using listbox. It works well.

Dim Db As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim cnt As String

cnt = &quot;Provider=SQLOLEDB.1;&quot; & _
&quot;Persist Security Info=False;&quot; & _
&quot;User ID=sa;&quot; & _
&quot;pwd=;&quot; & _
&quot;Initial Catalog=master;&quot; & _
&quot;Data Source=SQLSERVER;&quot; & _
&quot;Connect Timeout=30&quot;
Db.ConnectionString = cnt
Db.Open

cmd.ActiveConnection = Db
cmd.CommandType = adCmdStoredProc
cmd.CommandText = &quot;sp_helpgroup&quot;

Set rs = cmd.Execute
lst.Clear
Do Until rs.EOF
lst.AddItem rs.Fields(0).Value 'add group name to list box
rs.MoveNext
Loop

Set Db = Nothing

Hope this helps.
Another suggestion, check the return value of rsStoredGroupList. If it's not BOF and EOF, then the ADODB.Command syntax was succesful. The problem may lay on when you send data to DBCombo

 

[alec]

Hi oneshadow ,
I really appreciate you taking the time to help me. I will try your suggestion and Let you know.

The thing with this project, if I used adodc connection, setting the properties from the property's window instead of doing it with the code like I'm doing it now, I get the result just like I wanted.
But since I'm not using the navigation buttons provided by ADODC control and all I need is the connection, I decided to create the connection through the code instead. I still use DataCombo to show the end result.
Since I'm using DataCombo I need to use its rowsource property to get the list appear and this is when I get the problem.

With your exmaple, You use ListBox instead of DataCombo. This way you avoid using the rowsource property, so you are kinda solving my problem.
But my question is still exist though, can we use DataCombo with its rowsource property to show a list of record when
this records came from a System Store Procedure?

With my code, I was able to show a list of record in the DataCombo when I eliminate Command object and create Recordset object instead, like this:
------------------------------------------------------------
Dim MyGroupList As String
MyGroupList = &quot;select courseTitle, courseID from tblCourseInformation&quot;

' Create an instant of the recordset object
' set the properties as needed using the above connection
Set rsStoredGroupList = New ADODB.Recordset

With rsStoredGroupList
.ActiveConnection = cnCCS
.CursorType = adOpenKeyset
.Open MyGroupList
End With
------------------------------------------------------------

Can you see the problem I am facing? Alec

 

[oneshadow]

Alec,
RowSource property can not be manipulated at run time (See MSDN). It's only available at design time, that's why the DataCombo always blank. Your problem does not lay on the code but on component used.

If you only need to show a list of group name, why don't you use combo box/list box? It reduce the use of OCXs in your project while you still can get the same result.

 

[alec]

I see, Thanks for the information. I'm new at VB and I have a lot to learn, thank yo again - Alec