how to clear sqldatatable

[tcl4p]

I have an application where I'm loading several combo boxes from calls to stored procedures on the load of the form. The code is simple, but I have a question on how to clear the data table variable I'm using. As you can see from the code below I used two separate data table variables dt and dt1, which works fine. If I just used the single variable dt for each of the combo boxes what I would see is blank items in the second combo box loaded, which I assume came from the first use of the data table. I then thought I could clear the dt using the dt.clear method and it(dt) would be reloaded with the second combo box fill method (da.Fill(dt)). However when I tried this the second combo box was blank. As I said, my solution works (using two variables), but I'd like to know why the clear method causes a problem since its' followed by the second fill and is there a better way to handle this situation.

Thanks,
Tom

Me.cboStation.DisplayMember = "StationDesc"
Me.cboStation.ValueMember = "sID"
da = clsDept.GetStations
da.Fill(dt)
Me.cboStation.DataSource = dt

' I had the code dt.clear here, but it doesn't work

Me.cboStatus.DisplayMember = "Status"
Me.cboStatus.ValueMember = "sID"
da = clsDept.GetMemberStatus
da.Fill(dt1)
Me.cboStatus.DataSource = dt1

 

[cjard]

Your combos are showing data from different sources (database tables with different columns)

Let us suppose your DB only has 2 columns in each table:

StationsTable: sID, StationDesc
StatusTable: sID, Status

So you fill the first time, and your empty datatable will have 2 columns created by the adapter doing the fill:

myDT.Columns = { sID, StationDesc }

Now your myDT looks like:

sID, StationDesc
1, London Euston
2, Glasgow Central
3, Birmingham New Street


Now you reuse the same datatable to fill again from a different table. The sID column exists, but the Status column doesnt so the adapter creates it:

myDT.Columns.Add("Status")

Now your myDT looks like:

sID, StationDesc, Status
1, London Euston, null
2, Glasgow Central, null
3, Birmingham New Street, null

See the nulls creep in?

Now it fills:

sID, StationDesc, Status
1, London Euston, null
2, Glasgow Central, null
3, Birmingham New Street, null
1, null, Open
2, null, Closed
3, null, Building


Then you go and bind it to both your combos, one combo or the other is going to show blanks (depending on what you selected in the other combo) because when 2 combos dip out of the same DataView (when you set .DataSource = dt it actually uses the DataView returned by dt.DefaultView) they change in step. You select row 4 in one combo, the other combo shows row 4


Were you hoping that your table would become this:
sID, StationDesc, Status
1, London Euston, Open
2, Glasgow Central, Closed
3, Birmingham New Street, Building

i.e. you wanted vb.net to guess that equal sIDs should be reused? It won't do that. Instead, write your query as:

SELECT * FROM stations sn INNER JOIN status su ON sn.sID = su.sID


Alternately if you want to be able to select the station independently of the status, you HAVE to use two different datatables (or at least 2 different views, but I'll not get into that; this is confusing enough)