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.dataso urce = 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(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Dim Sql2000DataAdap ter As New
System.Data.Sql Client.SqlDataA dapter(TextBox2 .Text, TextBox1.Text)

mydt.Clear()
Try
Sql2000DataAdap ter.Fill(mydt)
Catch ex As Exception
MessageBox.Show ("Error: " & ex.Message)
Exit Sub
End Try
DataGridView1.D ataSource = mydt
End Sub

Private Sub DataGridView1_C ellContentClick (ByVal sender As
System.Object, ByVal e As
System.Windows. Forms.DataGridV iewCellEventArg s) Handles
DataGridView1.C ellContentClick
TextBox3.Text = DataGridView1.C urrentCell.Valu e
End Sub

[=?Utf-8?B?VGVycnk=?=]

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.
--
Terry
"cj" wrote:

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.dataso urce = 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(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Dim Sql2000DataAdap ter As New
System.Data.Sql Client.SqlDataA dapter(TextBox2 .Text, TextBox1.Text)

mydt.Clear()
Try
Sql2000DataAdap ter.Fill(mydt)
Catch ex As Exception
MessageBox.Show ("Error: " & ex.Message)
Exit Sub
End Try
DataGridView1.D ataSource = mydt
End Sub

Private Sub DataGridView1_C ellContentClick (ByVal sender As
System.Object, ByVal e As
System.Windows. Forms.DataGridV iewCellEventArg s) Handles
DataGridView1.C ellContentClick
TextBox3.Text = DataGridView1.C urrentCell.Valu e
End Sub

[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)?

Terry wrote:

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.

[=?Utf-8?B?VGVycnk=?=]

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

--
Terry
"cj" wrote:

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)?

Terry wrote:

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]

yes, that did it. Thanks!

Terry wrote:

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

[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?

Terry wrote:

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

[=?Utf-8?B?VGVycnk=?=]

You got it.
--
Terry
"cj" wrote:

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?

Terry wrote:

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

[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.Cl ear()

Actually, when the AutoGenerateCol umns 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(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Dim Sql2000DataAdap ter As New
System.Data.Sql Client.SqlDataA dapter(TextBox2 .Text, TextBox1.Text)

mydt.Clear()
mydt.Columns.Cl ear()
Try
Sql2000DataAdap ter.Fill(mydt)
Catch ex As Exception
MessageBox.Show ("Error: " & ex.Message)
Exit Sub
End Try
DataGridView1.D ataSource = 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.
Linda Liu[MSFT] wrote:

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.Cl ear()

Actually, when the AutoGenerateCol umns 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(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Dim Sql2000DataAdap ter As New
System.Data.Sql Client.SqlDataA dapter(TextBox2 .Text, TextBox1.Text)

mydt.Clear()
mydt.Columns.Cl ear()
Try
Sql2000DataAdap ter.Fill(mydt)
Catch ex As Exception
MessageBox.Show ("Error: " & ex.Message)
Exit Sub
End Try
DataGridView1.D ataSource = 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