Resolved: Dataset with JOIN Sql Query.... how?

Hello,

I've been learning to work with DataSets and DataTables a bit in the past day or 2.. i despised the thought of moving from my Recordsets from ADO to these crazy datasets and tables... but upon researching a bit, they're quite nice actually...

anyways, i can query a table with a SqlDataAdapter, then Fill a Dataset with it... like such:

VB Code:

Public Shared Function GetProducts(ByVal intUserID As Integer) As DataTable
    Dim objConn As SqlConnection
    Dim dadProducts As SqlDataAdapter
    Dim dstProducts As DataSet
    Dim dtblProducts As DataTable
 
    Dim strSQL As String
 
    'Set SQL Query
    strSQL = "SELECT ID, Product_Name, Product_DescText, Expire_Date FROM Products WHERE CustomerID = " & intUserID
 
    'Set Connection
    objConn = New SqlConnection(Database.ConnectionString)
 
    'Create DataAdapter with SQL Query
    dadProducts = New SqlDataAdapter(strSQL, objConn)
 
    'Create Dataset and fill it with results
    dstProducts = New DataSet()
    dadProducts.Fill(dstProducts, "Products")
 
    'Return Datatable
    GetProducts = New DataTable()
    GetProducts = dstProducts.Tables("Products")
End Function

that works all fine because i'm selecting from only one table...

but what happens if i have a sql query with say a couple inner joins in it?

actually i have a very complex query with 2 inner joins in it, and another select statement inside an IN() clause... so, it's quite involved...

how do i return something like this into a dataset?

here's my query:



SELECT Products.ID As Prod_ID, Products.Product_Name As Prod_Name, Products.Product_DescText As Prod_Desc, Category.ID As Cat_ID, Category.Category As Cat_Cat, SubCategory.ID As SubCat_ID, SubCategory.SubCategory As SubCat_SubCat FROM Products INNER JOIN Category ON Category.ID = Products.CategoryID INNER JOIN SubCategory ON SubCategory.ID = Products.SubCategoryID WHERE DATEDIFF(dd, GETDATE(), Products.Expire_Date) > 0 AND Products.IsVisible <> 0 AND Products.CustomerID IN (SELECT ZipCodes.UserID FROM ZipCodes WHERE DATEDIFF(dd, GETDATE(), ZipCodes.ExpireDate) > 0 AND ZipCodes.Visible <> 0 AND ZipCodes.ZipCode = '11111') AND Products.Product_Keywords LIKE '%Keywords%' ORDER BY Category.Category, SubCategory.SubCategory, Products.Product_Name



Mostly what i don't understand is what tablename to put in the dadProducts.Fill(dstProducts, "Products") line... in the above example, it's Products, the table i'm selecting from.. but what would it be in that query with JOINS ?

Same goes for populating the DataTable with the Dataset.Tables("TableName")...

now, i've had enough difficulty figuring this query out to work how it is... i'm afraid i might have to do something with temp tables, or something like that, which i know nothing about :(

please help!

The SQL query returns a set of records that are put in to the dataset as one table, its not that a query with inner joins creates multiple tables.

Instead of the former ADO recordset your data has now become a new table within the dataset.

/Leyan

OK
So what I gleaned from the books that I have bought and Read (Fancesco Beleno, Matthew McDonald, Karl Moore etc) I could only extract the data from one table at a time in a DataSet - is this not the case - if I can use an SQL Statement to extract data fom multiple tables using joins then I can have a table in a dataset which contains joins across multiple tables. this query is then treated as a table ?? is that the case???
regards

BH

Yes, in the dataset a table is getting filled with the data you retrieved, wheter it comes from multiple tables on the server or not. So if you are quering the data for view purpose only there should be no problem.

What you might have read is that you might get problem updating the database as the commandbuilder does not support dynamic command creating with multiple base tables or queries with no base table information.

but i'm still unclear on how to write this line when using my sql statement:

VB Code:

dadProducts.Fill(dstProducts, "TableNameHere")

Shouldn't the second arguement in the .Fill routine be the name of the table in your select query?

or can i just name it anything and that is only for future reference like when i'm extracting that table to a datatable?

You can name it whatever you want. I think the confusion is happening here because you are thinking of a datatable object as the same as the database table, but as you see they are two different things. Datatables can be any mix of actual data and the tablename property works more like a key for the collection than anything else, since a dataset can hold multiple datatables.

that clears things up for me...


:)

thanks a bunch!