Populate Typed Dataset wit UNION ALL SQL query

Question

I have a strange situation here and I hope someone can help me. I don't have the same problem when using an untyped dataset.

OK. I have a typed dataset MyDS with a typed datatable MyTable (TableID, Title, Message).

The table gets filled with results from two tables, using a UNION ALL

Select
  TableAID,
  TableATitle,
  Message
FROM TableA

UNION ALL

Select
  TableBID,
  TableBTitle,
  Message
FROM TableB

Dim cmd As SqlCommand = New SqlCommand("SQL", conn)

Dim da AS SqlDataAdapter = New SqlDataAdapter(cmd)
da.Fill(MyDS.MyTable)

The query runs well on SQL Server, but when I step through the code, I find incorrect TableAID or TableAID.

TableA has 1 row and TableB has 15 rows. So my dataset returns 16 rows (so basically it's a master-detail situation). All the columns return correct respective data, except the TableAID and TableBID.

TableAID has value = 100 and TableBID has 10, 11, 12, 13, .........24, but when I step through, the

MyDS.MyTable(i).TableID

remains 100 throughout the 16 records, instead of 100, 10, 11, 12, 13, .........24. The Title, Message data is correct. TableAID and TableBID are my primary keys.

i = 0 to 15

Is this how typed datasets handles UNION? I didn't want to create two typed datatables and create separate SQL for each and create a relation.

Answer 1

I managed to fix it. It wasn't the dataset at all. I was actually using a stored proc that was selecting from a view, and I had 2 views (view1 and view2) that were almost the same. So the above sql was from view2, but the stored proc was using view1. I used views because the statement was complicated than that. So my moto is, if I have a complicated SELECT sql that I need to use in my stored proc, put it on view and have a simple proc to work with. The code is

            
 Dim cmd As SqlCommand = New SqlCommand("StoredProcedureName", conn)
 cmd.CommandType = CommandType.StoredProcedure

Not

Dim cmd As SqlCommand = New SqlCommand("SQL", conn)

I almost wanted to replace my typed dataset with untyped one, because I was running out of time. Deadlines. LOL Thank you for making me think that it was not the dataset since you could not figure out what could be the problem. LOL