remove redundant columns in selectable query datagridview

Question

I have a single Datagridview on one of my windows forms that I intend to have the user select a query from a combo-box drop down menu, click a button, then the query results get displayed on the datagridview.

That is all working properly, however when a different query is selected and submitted, the datagridview displays additional empty columns used by the previously selected query.

What would be the best way to stop this from happening? is this based on the datagridview itself? or my subroutine to connect to the database and perform the query

I have tried clearing out the datatable(used in the query subroutine) and columns of the datagridview, but this has not solved the issue.

My various attempts at clearing the datagridview with a button (tried in various combinations):

Private Sub btnclearquery_Click(sender As System.Object, e As System.EventArgs) Handles btnclearquery.Click
    mysql.qrydata.Clear()
    DataGridViewqry.DataSource = Nothing
    DataGridViewqry.Columns.Clear()
    DataGridViewqry.Rows.Clear()
End Sub

My subroutine to perform the queries (referenced in another code file. sqlquery string is filled using select case when the user selects a query from the combo-box):

Public qrysqlconn As MySqlConnection
Public qrycommand As New MySqlCommand
Public qryadapter As New MySqlDataAdapter
Public qrydata As New DataTable
Public qrysql As String

Public Sub doquery(ByVal sqlquery As String)
    qrysqlconn = New MySqlConnection
    qrysqlconn.ConnectionString = "server=localhost;" _
        & "user id=root;" _
        & "password=W1nd0ws;" _
        & "database=hystest"
    qrysql = sqlquery
    Try
        qrysqlconn.Open()
        qrydata.Clear()
        qrycommand.Connection = qrysqlconn
        qrycommand.CommandText = qrysql
        qryadapter.SelectCommand = qrycommand
        qryadapter.Fill(qrydata)

        queries.DataGridViewqry.DataSource = qrydata
        queries.DataGridViewqry.FirstDisplayedScrollingRowIndex = queries.DataGridViewqry.RowCount - 1
    Catch myerror As MySqlException
        MessageBox.Show("Database error: " & myerror.Message)
    Finally
        qrysqlconn.Close()
        qrysqlconn.Dispose()
    End Try
End Sub

score 0 · Answer 1 · answered Aug 01 '13 at 15:42

0

I have had an issue like this before as well.
you may need to create a new instance of DataView for each new view you wish to display to get around this:

Dim dataview As DataView = _ds.YourStuff.DefaultView
dataview.Stuff

answered Aug 01 '13 at 15:42

Adam

490
7
21

score 0 · Answer 2 · answered Aug 05 '13 at 08:07

I managed to solve this, or at least create a work-around to the problem, by creating an individual subroutine/connection/datatable for each query. so instead of:

doquery(sql command)

I have used:

query1(<parameters>)
query2(<parameters>)
...
etc

This seems to work okay for now. this would imply that the issue was based on re-using the same datatable over and over again.

remove redundant columns in selectable query datagridview

2 Answers2