Listing Stores Under Certain Areas

OK, I did not look through all the source we have been playing around with.
The below pseudo code works

Dim MySQL As String = "Select AreaID, Area from Area where State = '" & strChosenState & "'"

Dim MyConn As New SQLConnection(strConn)

Dim ds As DataSet = New DataSet

Dim Cmd As New SqlDataAdapter(MySQL, MyConn)

Cmd.Fill(ds, "Area")
Dim cmd2 As SqlDataAdapter = New SqlDataAdapter("Select * from SpaStore Join Area ON SpaStore.AreaID = Area.AreaID where State = '" & strChosenState & "'", MyConn)

cmd2.Fill(ds, "SpaStore")
ds.Relations.Add("myrelation", ds.Tables("Area").Columns("AreaID"), ds.Tables("SpaStore").Columns("AreaID"), True)

dlArea.DataSource = ds.Tables("Area").DefaultView
DataBind()

The problem you had that the parent row does not have corresponding parent values is because you chose SpaStore that are in the strChosenAreaID but you chose ALL Areas, that will cause a conflict, you need to set the same filter on the spaStore table. I put above code in a page and verified it does work well.

AreaName is actually right. I accidentally called the AreaID in the SpaStore Table Area at one time which was not a good idea.

Let me tell you some information about my tables that are relevant:

Area: AreaID, AreaName, State
SpaStore: SpaStoreID, SpaStoreName, AreaID, State, Approve, ListingType, etc.

I used the above code that you gave me. I had to modify it a little because State in the second SQL Statement had to be SpaStore.State. I didn't get any error messages. But I am getting all of the areas even if there are no stores under a certain area. I only want areas that have stores under them to be displayed. I've tried some experiments by changing some of the code, but I have been unsuccessful.

Here's my code:

Dim MySQL As String = "Select AreaID, AreaName from Area where State = '" & strChosenState & "' Order By AreaName" - I think that we need to modify this sql statement. This sql statement chooses all of the areas that are under a certain state. For example, (GA) Georgia might have the following areas: Atlanta Area, Middle Georgia, South Georgia, and Northeast Georgia. All of the areas would be displayed with this sql statement. Let's say that I only have one store under North Georgia and three stores under Atlanta Area. I would like for only North Georgia and Atlanta Area to be displayed as areas on the web page.

Dim MyConn As New SQLConnection(strConn)
Dim ds As DataSet = New DataSet
Dim Cmd As New SqlDataAdapter(MySQL, MyConn)

Cmd.Fill(ds, "Area")

Dim cmd2 As SqlDataAdapter = New SqlDataAdapter("Select * from SpaStore Join Area ON SpaStore.AreaID = Area.AreaID where Area.State = '" & strChosenState & "' AND ListingType IN ('Featured','National') AND Approve = 0 ORDER by SpaStoreName", MyConn)

cmd2.Fill(ds, "SpaStore")

ds.Relations.Add("myrelation", ds.Tables("Area").Columns("AreaID"), ds.Tables("SpaStore").Columns("AreaID"), True)

dlArea.DataSource = ds.Tables("Area").DefaultView

DataBind()