clearing dataset or datatable

[cj]

I have a program to display queries to a SQL db. I type my query in a
textbox and click a button and the results display in a datagrid. I
could use either dataset or datatable to read the data in then I make
the datagrid.datasource = myds.Tables(0) or mydt. Now what I want is to
be able to change the query and click the button again and get the new
results. I can use clear() to clear the data from a ds/dt but I need
the structure gone too. How can I do this? Here is sample code using dt.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim Sql2000DataAdapter As New
System.Data.SqlClient.SqlDataAdapter(TextBox2.Text, TextBox1.Text)

mydt.Clear()
Try
Sql2000DataAdapter.Fill(mydt)
Catch ex As Exception
MessageBox.Show("Error: " & ex.Message)
Exit Sub
End Try
DataGridView1.DataSource = mydt
End Sub

Private Sub DataGridView1_CellContentClick(ByVal sender As
System.Object, ByVal e As
System.Windows.Forms.DataGridViewCellEventArgs) Handles
DataGridView1.CellContentClick
TextBox3.Text = DataGridView1.CurrentCell.Value
End Sub

 

[Guest]

Why not just create a new DataTable....
Replace the line "mydt.clear" with ....
mydt = New DataTable
hopefully you are not holding more then the 1 reference to the datatable.

 

[cj]

I'll show my ignorance here but what happens to the old datatable? Is
it cleared out of memory when a new one is created with the same name
(mydt)?

 

[cj]

Actually that didn't work either. The problem is if I have a sql table
with the fields: first_name, last_name, address, group

I run
select last_name from addr_table where group = 'A'

I get a list of last names in the datagrid as desired

I then decide to run
select * from addr_table where group = 'A'

the grid I get has the columns in the order last_name, first_name,
address, group instead of in the order they are in the sql table. If I
had run the second query first I would have gotten them in the correct
order.

Perhaps it is the datagrid that needs clearing?

 

[Guest]

well I suppose you could clear it first, that may get rid of some of the
memory usage sooner. The reason that I memtioned not haveing any other
references to the table, was so that it could be garbage collected at some
point in time. And if you don't, it will. Remember that mydt is just a
reference to an object. A 'pointer' to the DataTable. Probably the best
thing to do would be to 'Dispose' of the DataTable first.
If mydt IsNot Nothing Then
mydt.Dispose()
End If
mydt = New DataTable

 

[Guest]

should be a way to get it to 'forget' about the colums it had before - maybe
set its datasource to 'Nothing' first?

 

[cj]

yes, that did it. Thanks!

 

[cj]

It sounds like from what your saying as soon as mydt=new datatable runs
the second time the pointer is changed to a new datatable and the old
datatable is just left waiting on garbage collection. That's ok if
that's correct. Am I getting this right?

 

[Guest]

You got it.
--
Terry

It sounds like from what your saying as soon as mydt=new datatable runs
the second time the pointer is changed to a new datatable and the old
datatable is just left waiting on garbage collection. That's ok if
that's correct. Am I getting this right?

 

[Linda Liu[MSFT]]

Hi Cj,

Alternatively, you can clear the columns in the DataTable to remove the
existing schema in the DataTable. To do this, call the Clear method on the
Columns property of the DataTable. For example:

mydt.Columns.Clear()

Actually, when the AutoGenerateColumns property of a DataGridView is set to
true, if you set the DataSource property of the DataGridView to a data
source, e.g. a DataTable, DataGridView will generate columns according to
the data columns in the DataTable automatically.

At this time, if you clear the data columns in the DataTable or set the
DataSource property of the DataGridView to Nothing, the columns in the
DataGridView which were populated by the DataGridView automatically will be
removed automatically as well.

On the other hand, if you set another DataTable, which has different schema
from the previous DataTable, as the data source of the DataGridView,
DataGridView will check the existing columns in it and then generate
columns for those data columns in the new DataTable which have no
corresponding columns in the DataGridView. This is why you see the columns
in the DataGridView are "last_name, first_name, address, group" after you
run "select * from addr_table where group = 'A'".

So the complete solution to your problem is to call the Clear method on the
DataTable first and then call the Clear method on the Columns property of
the DataTable. The following is a sample:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim Sql2000DataAdapter As New
System.Data.SqlClient.SqlDataAdapter(TextBox2.Text, TextBox1.Text)

mydt.Clear()
mydt.Columns.Clear()
Try
Sql2000DataAdapter.Fill(mydt)
Catch ex As Exception
MessageBox.Show("Error: " & ex.Message)
Exit Sub
End Try
DataGridView1.DataSource = mydt
End Sub

Hope this helps.
If you have any question, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support

 

[cj]

Linda,

Thanks for your excellent description of what takes place when using a
datatable and datagrid like this. It makes sense to me now. I can see
how there are several ways to solve the problem I was seeing but I think
your suggestion is the best.