Problem with data display using Datagridview

drexler_kk

Hello all, I have a problem here need someone to help here. I'm doing a searching function from the database which is using MySQL5.0. A textbox(textbox2) is used for the user to search data by clicking the search button(Button1). The search return result will be display using a DataGridView1. Well,when I search for the 1st time,it runs well with the result expected show at DataGridView1. But when I search for the second time,the result display is out of alliengment. It grows at a new row but shown on column2 and column3.It should be at column1 and column2.And beside that,the previous data is shown at the datagridview as well. After test the above scenarios,I also found that after I search a data which is not inside the datatable.And try to search for something that available with result,the datagridview only display me the header of the column1 and column2 but no result. I have try about 2hrs to find this solution,but I can't figure out how to solve this. I paste the search button code below,anyone can give me some solution? Sorry,please feel free to ask. I know its difficult to understand my poor english.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim x As String

    Try
        If Trim(TextBox2.Text) <> "" Then
            x = TextBox2.Text.ToUpper

            SQL = "SELECT Comp\_Code,Comp\_Name FROM company\_detail WHERE Comp\_Name LIKE '" & x & "%'"
            ObjComm = New MySqlCommand(SQL, ObjMyConn)
            ObjMyConn.Open()
            ObjRead = ObjComm.ExecuteReader
            If ObjRead.Read Then
                ObjRead.Close()
                DataGridView1.DataSource = Nothing
                ObjAdapter = New MySqlDataAdapter(SQL, ObjMyConn)
                ObjAdapter.Fill(ds, "Client\_Comp")
                dt = ds.Tables("Client\_Comp")
                dt.Columns(0).ColumnName = "Company Code"
                dt.Columns(1).ColumnName = "Company Name"
                DataGridView1.DataSource = dt.DataSet.Tables("Client\_Comp")
                DataGridView1.Refresh()
                TextBox2.Clear()
            Else
                
                dt.DataSet.Tables("Client\_Comp").Reset()
                DataGridView1.DataSource = Nothing
                MessageBox.Show("No such company stored.")
                TextBox2.Clear()
            End If

        Else

Dave Kreskowiak

Your code makes no sense in certain sections.

SQL = "SELECT Comp_Code,Comp_Name FROM company_detail WHERE Comp_Name LIKE '" & x & "%'"
ObjComm = New MySqlCommand(SQL, ObjMyConn)
ObjMyConn.Open()
ObjRead = ObjComm.ExecuteReader
If ObjRead.Read Then
ObjRead.Close()
DataGridView1.DataSource = Nothing

You open a reader on a returned set, but if there is a result, you close the read immediately?? Then execute the same SQL to fill a dataset?? That's completely unnecessary. Just execute a fill on the DataSet, then bind the DataGridView to it. There's no need to check if something came back first.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008

drexler_kk

Hello Mr Dave Kreskowiak, so happy someone help me to check about this. Actually the reason why I did that is just to check the search data is available or not on the database. If its not available,I would not fill anything into the dataset and go into dataable anymore. Do you have any idea about what happen to the datagridview1? The result display at the datagridview1 is weird.

Dave Kreskowiak

drexler_kk wrote:

Actually the reason why I did that is just to check the search data is available or not on the database

This puts twice the load on the database. Just do it once and fill your DataTable. If the record Count in that table is greater than 0, you've got results. Your setting up columns in the DGV on each pass of this search. You should only do it once, then forget about it since the columns in the DGV will not change.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008

Jon_Boy

You're doing multiple calls to the database which slays performance and is unnecessary (like the bank bailouts - zing!). Do the fill on the dataset and bind it. If there's any data, it'll be displayed. If you want to check for the existence of data, check the dataset after the fill like so:

if dataset.tables(0).rows.count > 0 then
'we have data - hurray for us.
else
'wtf happened, there's nothing here.
end if

Any suggestions, ideas, or 'constructive criticism' are always welcome. "There's no such thing as a stupid question, only stupid people." - Mr. Garrison

drexler_kk

Thanks Jon_Boy and Mr. Dave for the help. I have solve the problem here by changing what you recommend to me. It work well now. :)