DataGridView Database Transaction and Refresh

[retkehing]

Below is the code that i used to display the data using DataGridView and i couldn't do refresh, for example after i do some update or delete, i want my DataGridView to display the latest data.

Apart from that, may i know whether the following coding is fine and clean? If no, can you guys provide some of the sample source code? Thank you.

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Table_Name [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"Emp_Profile"[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] SQLSTRING1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"SELECT emp_no, emp_name, nric_no, dept_code, hp_no FROM Emp_Profile ORDER BY emp_no"[/COLOR][/SIZE]
 
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DTable [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataTable[/SIZE]
[SIZE=2]DTable = DataSet1.Tables(Table_Name)[/SIZE]
[SIZE=2]DTable = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable(Table_Name)[/SIZE]
 
[SIZE=2]DataSet1.Clear()[/SIZE]
[SIZE=2]DTable.Clear()[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ColID [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataColumn([/SIZE][SIZE=2][COLOR=#800000]"emp_no"[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2]))[/SIZE]
[SIZE=2][COLOR=#008000]' ColID.DefaultValue = String.Empty[/COLOR][/SIZE]
[SIZE=2]DTable.Columns.Add(ColID)[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ColName [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataColumn([/SIZE][SIZE=2][COLOR=#800000]"emp_name"[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2]))[/SIZE]
[SIZE=2][COLOR=#008000]'colDescription.DefaultValue = "select emp_name from Emp_profile"[/COLOR][/SIZE]
[SIZE=2]DTable.Columns.Add(ColName)[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ColNric [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataColumn([/SIZE][SIZE=2][COLOR=#800000]"nric_no"[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2]))[/SIZE]
[SIZE=2][COLOR=#008000]' ColID.DefaultValue = String.Empty[/COLOR][/SIZE]
[SIZE=2]DTable.Columns.Add(ColNric)[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ColDeptCode [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataColumn([/SIZE][SIZE=2][COLOR=#800000]"dept_code"[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2]))[/SIZE]
[SIZE=2][COLOR=#008000]' ColID.DefaultValue = String.Empty[/COLOR][/SIZE]
[SIZE=2]DTable.Columns.Add(ColDeptCode)[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ColHPNo [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataColumn([/SIZE][SIZE=2][COLOR=#800000]"hp_no"[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2]))[/SIZE]
[SIZE=2][COLOR=#008000]'ColHPNo.DefaultValue = True[/COLOR][/SIZE]
[SIZE=2]DTable.Columns.Add(ColHPNo)[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ColCombo [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataColumn([/SIZE][SIZE=2][COLOR=#800000]""[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff]Boolean[/COLOR][/SIZE][SIZE=2]))[/SIZE]
[SIZE=2]ColCombo.DefaultValue = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[SIZE=2]ColCombo.ColumnName = [/SIZE][SIZE=2][COLOR=#800000]"Select"[/COLOR][/SIZE]
[SIZE=2]DTable.Columns.Add(ColCombo)[/SIZE]
 
[SIZE=2]DataSet1.Tables.Add(DTable)[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DataViewList [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataView = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataView(DataSet1.Tables(Table_Name))[/SIZE]
[SIZE=2]DataViewList.AllowDelete = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]DataViewList.AllowEdit = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[SIZE=2]DataViewList.AllowNew = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]DataGridView1.DataSource = DataViewList[/SIZE]
[SIZE=2]DataGridView1.Columns(0).Width = 50[/SIZE]
[SIZE=2]DataGridView1.Columns(1).Width = 130[/SIZE]
[SIZE=2]DataGridView1.Columns(2).Width = 130[/SIZE]
[SIZE=2]DataGridView1.Columns(3).Width = 80[/SIZE]
[SIZE=2]DataGridView1.Columns(4).Width = 80[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DAdapter1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter(SQLSTRING1, OleDbConnection1)[/SIZE]
[SIZE=2]DAdapter1.Fill(DataSet1, Table_Name)[/SIZE]

 

[retkehing]

I tried to do in the other way as the following code and however everytime when i tick on the checkbox at the last row, it will generate another new empty row in the DataGridView, may i know how to get rid of this? Thank you.

[SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] constring [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"Provider=SQLOLEDB; Data Source=YAP\SQLEXPRESS; Database=CLDatabase; UID=sa; PWD=sa;"
[/COLOR][/SIZE][SIZE=2]OleDbConnection1 = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection(constring)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Table_Name [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"Emp_Profile"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] SQLSTRING1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"SELECT emp_no, emp_name, nric_no, dept_code, hp_no FROM Emp_Profile ORDER BY emp_no"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DAdapter1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter(SQLSTRING1, OleDbConnection1)[/SIZE]
[SIZE=2] 
DataSet1.Clear()
DAdapter1.Fill(DataSet1, [/SIZE][SIZE=2][COLOR=#800000]"Emp_Profile"[/COLOR][/SIZE][SIZE=2])
DataGridView1.DataSource = DataSet1.Tables([/SIZE][SIZE=2][COLOR=#800000]"Emp_Profile"[/COLOR][/SIZE][SIZE=2])
 
[/SIZE][SIZE=2][COLOR=#008000]'Insert Combobox and Checkbox Column
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DGVColumn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataGridViewComboBoxColumn
DGVColumn = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataGridViewComboBoxColumn()
DGVColumn.DataPropertyName = [/SIZE][SIZE=2][COLOR=#800000]"Name"
[/COLOR][/SIZE][SIZE=2]DGVColumn.Name = [/SIZE][SIZE=2][COLOR=#800000]"Other"
[/COLOR][/SIZE][SIZE=2]DGVColumn.Width = [/SIZE][SIZE=2][COLOR=#800000]"200"
[/COLOR][/SIZE][SIZE=2]DataGridView1.Columns.Add(DGVColumn)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DGVColumn1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataGridViewCheckBoxColumn
DGVColumn1.DataPropertyName = [/SIZE][SIZE=2][COLOR=#800000]"Name"
[/COLOR][/SIZE][SIZE=2]DGVColumn1.Name = [/SIZE][SIZE=2][COLOR=#800000]"Other"
[/COLOR][/SIZE][SIZE=2]DGVColumn1.Width = [/SIZE][SIZE=2][COLOR=#800000]"200"
[/COLOR][/SIZE][SIZE=2]DataGridView1.Columns.Add(DGVColumn1)
[/SIZE]

 

[lingsn]

Option 1:
Hope the DataView can give you a little help

Dim m_dgListView As DataView = New DataView(DataSet1.Tables([SIZE=2][COLOR=#800000]"Emp_Profile"[/COLOR][/SIZE][SIZE=2])[/SIZE])
DataGridView1.DataSource = m_dgListView

Option 2:
In the DataGridView properties, set the AllowUserToAddRows = False