How do you fill a Dataset using mulitple tables in the Select Statemen

[calcal]

I am brand new to .net but not to SQL.

I am using an OleDbConnection to an Oracle database and am having difficulties filling a dataset when there are multiple tables in the SQL query.

This statement poses no difficulty.
SELECT emp.firstname, emp.lastname, emp.orgid
FROM emp
WHERE emp.empid = user_name_param

This statement, selecting from two tables, is the issue.

SELECT emp.firstname, emp.lastname, emp.orgid, org.segment
FROM emp, org
WHERE emp.orgid = org.orgid
AND emp.empid = user_name_param

I must be missing something. Can any steer me in the right direction?

The complete code is :
Public Shared Function GetEmpData() As DataSet
Dim dsEmpData As New DataSet
Dim cmdEmpData As New System.Data.OleDb.OleDbCommand
Dim sessEmpId = GetSessionEmployee().empId

'Create select statment.
Dim sSelect As String = "SELECT empid, firstname, lastname, orgid FROM emp WHERE empid = ? "

'Assign the properties for our OleDB command.
cmdEmpData.CommandText = sSelect
cmdEmpData.Connection = Connection()
cmdEmpData.Parameters.Add("?", OleDbType.Char, 9).Value = sessEmpId
Dim mysql As String = sSelect
'Create an Ole DB Adapter.
Dim daEmpData As New System.Data.OleDb.OleDbDataAdapter
'Assign values to our Data Adapter properties.
daEmpData.SelectCommand = cmdEmpData
daEmpData.MissingSchemaAction = MissingSchemaAction.AddWithKey

'Execute the stament and fill our Data Set with the result set.
daEmpData.Fill(dsEmpData, "EmpDataTable")
Return dsEmpData
End function

 

[PankajBanga]

First of all why are you using OleDb Data Provider for accessing Oracle database, use OracleClient which is Found in System.Data.OracleClient

I didn't get a chance to have a look at the code, but this is how i normally do it with SQL Server. Logic should be the same, just a matter of using different objects

'Create a new Dataset.
Dim ds As New DataSet

'Connectiion string.
Dim strConn As String = "Server=DEVSERVER;Database=Northwind;Persist Security Info=False;" & _
"Integrated Security=True;"

'SQL string
Dim strSQL As String = "SELECT O.OrderID, OrderDate, ProductID, UnitPrice, Quantity " & _
"FROM Orders O, [Order Details] D " & _
"WHERE O.OrderID = D.OrderID " & _
"AND O.OrderID = @ID"

'Create SqlParameter object.
Dim param As New SqlParameter("@ID", SqlDbType.Int)
param.Value = CType(TextBox1.Text, Integer)

'Initialize SqlCommand.
Dim cmd As New SqlCommand(strSQL, New SqlConnection(strConn))
'Add parameter.
cmd.Parameters.Add(param)

'Initialize SqlDataAdapter object using SqlCommand object.
Dim da As New SqlDataAdapter(cmd)

'Fill the DataSet.
da.Fill(ds, "OrderDetails")

'Bind DataSet to DataGrid.
DataGrid1.DataSource = ds.Tables(0)

Hope this helps