vendredi 27 février 2015

how to retrieve data from datasource(excel) and display in datagridview base on multiple checkbox selection? Using vb.net

I've two datagridview, one to view imported Excel file data and the other is report in which the selective chechboxes columns along with their details will be display for editing and finalizing it in order to save as new excel file in machine.


Step 1. Allow user to import Excel file and then application will retrieve the data and display it in first datagridview. (this step is done and working perfect)


Step 2. Number of columns with their names are predefined so there will already be a checkbox outside first datagridview with columns name created in windows form. For example first datagridview column name "email" so checkbox name will also be "email" and link it with first datagridview email column's data. If a user check "email" checkbox the application should retrieve email column data and display it in second datagridveiw.


So far i've already done these two steps but problem is at the time of selecting multiple checkboxes the datagridview display one column and overwrite it with other selective checkbox. For example two checkboxes "email" and "fullName" being selected by user. The output in datagridview should be two columns with name "email" and "fullName" along with their data. When i select "email" checkbox it work fine, but when i select "fullName" checkbox it overwrite with "email" column and show "fullName" column with it details. How can i get specific number of columns base on user's checkbox selection with it details in it?


Secondly if a user uncheck the checkbox so the column of selective checkbox should be hide/remove from second datagridview, how this can be done?


Below is the coding of checkbox selection and display in DataGridView2: (multiple checkbox selected "email" and "fname"



Private Sub chkFname_CheckedChanged(sender As Object, e As EventArgs) Handles chkFname.CheckedChanged
Dim filePath As String = OpenFileDialog1.FileName
Dim header As String = rbHeaderYes.Checked
Dim conStr As String
conStr = String.Format(Excel07ConString, filePath, header)

' Populate selected checkbox on Report(Datagridview2)

If chkFname.Checked = True Then
If chkEmail.Checked = True Then
Try
Using con As New OleDbConnection(conStr)
Using cmd As New OleDbCommand()
Using oda As New OleDbDataAdapter()
Dim dt As New DataTable()
cmd.CommandText = (Convert.ToString("SELECT email, fname From [consumer$]"))
cmd.Connection = con
con.Open()
oda.SelectCommand = cmd
oda.Fill(dt)
con.Close()

'Populate DataGridView.
Report.DataGridView2.DataSource = dt
End Using
End Using
End Using
Catch ex As Exception
MsgBox("Error : " & ex.Message)
End Try

Else
Using con As New OleDbConnection(conStr)
Using cmd As New OleDbCommand()
Using oda As New OleDbDataAdapter()
Dim dt As New DataTable()
cmd.CommandText = (Convert.ToString("Select fname from [consumer$]"))
cmd.Connection = con
con.Open()
oda.SelectCommand = cmd
oda.Fill(dt)
con.Close()

Report.DataGridView2.DataSource = dt
End Using
End Using
End Using
End If

ElseIf chkFname.Checked = False Then
Try
If chkEmail.Checked = False Then
Report.DataGridView2.Columns(0).Visible = False
Else
Report.DataGridView2.Columns(1).Visible = False
End If
Catch ex As Exception
MsgBox("Error : " & ex.Message)
End Try
End If
End Sub




Aucun commentaire:

Enregistrer un commentaire