473,322 Members | 1,241 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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 10991
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Frnak McKenney | last post by:
I'm using an in-core DataSet as an image of my application's 'database' (a multi-table Access97 mdb file). Updates are made to the DataTables within the DataSet via forms with bound TextBoxes,...
3
by: Bill C. | last post by:
Hi, I've got a simple console app that just reads an XML file into a DataSet then prints out a description of each table in the DataSet, including column names and row values for each column. ...
15
by: JIM.H. | last post by:
Hello, Can I send a dataset as a parameter into stored procedure and import data to a table in the stored procedure? Thanks, Jim.
1
by: cindy | last post by:
Get data into datatable, add to dataset dsSearch " Get data into datatable, add to dataset dsSearch Using In-Memory SQL Engine join the tables and select the filenames from the join, add to...
5
by: Roy Lawson | last post by:
I am having no problems connecting to a DB, creating a DataAdapter, and creating a dataset...and connecting to the data. Using the builtin data objects to do all this. My only problem now is...
1
by: amber | last post by:
Hello, What is the best method of clearing a datagrid? I'm having issues with it not 'resetting' when I want to repopulate it. I usually design my datagrids using the .net toolbar/properties, but...
10
by: dauphian | last post by:
Hello, I am new to .net and am trying to build a report application that queries 4 different tables based on a id, and I need to return them in the same table for easy viewing. Basically, I...
3
by: Tom | last post by:
I have a dataTable being returned from my datalayet, I need to convert it to a dataSet so I can do some data manipulation to it prior to populating my datagrid. How can I convert the datatable to a...
3
by: Ken Fine | last post by:
This is a question that someone familiar with ASP.NET and ADO.NET DataSets and DataTables should be able to answer fairly easily. The basic question is how I can efficiently match data from one...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.