"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
>

> 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" 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.
>

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

"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
> >
>

> 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" wrote:

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

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

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