Populating 2 tables in a dataset from 1 query

[JerryKreischer]

I have the following query:

Select *
From Employee
Where pkEmpNum = @EmpNo

SELECT EP.*, .ET.EmpTypeDescr
FROM EmployeePositions EP INNER JOIN
EmpTypes ET ON EP.fkEmpType = ET.pkEmpType
WHERE EP.fkEmpNum = @EmpNo

GO

I have a strongly-typed dataset defined with 2 corresponding tables in it. I'd like to know how I can fill the two tables in my dataset. I've used the 'FILL' command for 1 table from 1 query, but don't know what to do with 2 tables from 1 query...

So far, I have the following code:
The code I'm using is:

Try 
    myCmd.CommandType = CommandType.StoredProcedure 
    pParam = New SqlParameter("@EmpNo", SqlDbType.Int) 
    pParam.Value = EmpNum 
    myCmd.Parameters.Add(pParam) 
    myConn.Open() 
    With daMC1 
       .SelectCommand = myCmd 
       Dim recs As Int16 = .Fill(ds) 
    End With

When this code executes, the dataset contains my original 2 tables + 'Table' and 'Table1'...How can I just populate the 2 existing tables with the 2 results from the 1 query???

TIA

Jerry

 

[wsmall73]

As with many other languages the only result that can retrieved from the stored procedure will be the last select list. Given that.. the only results you will be able to obtain is the result from

SELECT EP.*, .ET.EmpTypeDescr
FROM EmployeePositions EP INNER JOIN
EmpTypes ET ON EP.fkEmpType = ET.pkEmpType
WHERE EP.fkEmpNum = @EmpNo

 

[JerryKreischer]

Sorry...that's not true...

The solution to my problem was adding TableMappings to my SqlDataAdapter.

With daMC1
   .TableMappings.Add("Table", "EmployeeRec")
   .TableMappings.Add("Table1", "Positions")
   .SelectCommand = myCmd
   .Fill(ds)
End With

With this code, I was able to populate the 2 tables I had defined in my dataset using 2 queries in 1 SProc...

Jerry

 

[AGIMA]

You can do this using a Datareader. You simply read the .Read() until you have extracted all the info from the first SQL and then use the .NextResult to move to the next SQL. You would then do the same thing to extract the data.

As for a data adapter I would state the table names in the Fill statment. example .Fill(ds.EmployeePositions) and .Fill(ds.Employee)

See how you go and let me know.



AGIMA.net