469,593 Members | 1,927 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,593 developers. It's quick & easy.

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
Nov 13 '07 #1
8 10066
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.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
Nov 13 '07 #2
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.
Nov 13 '07 #3
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.
Nov 13 '07 #4
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?
Nov 13 '07 #5
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
Nov 13 '07 #6
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
Nov 13 '07 #7
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

Nov 14 '07 #8
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.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
Nov 14 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

15 posts views Thread by JIM.H. | last post: by
5 posts views Thread by Roy Lawson | last post: by
1 post views Thread by amber | last post: by
10 posts views Thread by dauphian | last post: by
3 posts views Thread by Tom | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.