returning a table from sql server to vb

[acs]

I am new-bee to vb. I have a stored procedure that queries 2 tables. gets Data from tab1 into a cursor and loops over cursor to retrieve data from tab2.

I am calling this stored procedure from vb and I want to display data returned by both the table and also I need to perform totals which will be displayed at end of each row from tab1.

Can someone please provide me with the syntax for this.

thanks

acs

 

[culshaja]

What you need to use is ADO.
The following example might help:

Dim cnDB as ADODB.Connection
Dim cmData1 as ADODB.Command
Dim cmData2 as ADODB.Command
Dim rsResults1 as ADODB.Recordset
Dim rsResults2 as ADODB.Recordset

set cnDB = New ADODB.Command
cmDB.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbname;Data Source=ServerName"
cmDB.Open

set cmData1 = New ADODB.Command
set cmData1.ActiveConnection = cnDB
cmData1.CommandType = adCmdStoredProc
cmData1.Commandtext = "StoredProcedure1"
set rsResults1 = cmData1.Execute


while not rsResults1.EOF
    set cmData2 = New ADODB.Command
    set cmData2.ActiveConnection = cnDB	
    cmData2.Commandtype = adStoredProc
    cmData2.Commandtext = "StoredProc2"
    cmData2.Paramaters.Append cmData2.CreateParamater("ID", adInteger, adParamInput, , rsResults1("ID"))
    set rsResults2 = cmData2.Execute
    'do you processing of second recordset here
    '.......
    rsResults1.MoveNext
wend

You need to set a reference to ADO in you references.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305