Hi all, I am working on a project affiliated to customer's database and i am required to insert, update, delete the data through a window's form program. The database i am using is MS SQL server 2005 and one of the columns i had is "Selection" which its datatype is set to "bits" which is used as a checkbox control in the datagridview in my program. The checkbox allows me to choose the specific row of customer's data, which i append to textboxes, but there is one problem, i am able to multiple-select the checkboxes. I want to restrict the datagridview checkbox column to only be able to select only one checkbox/one row at a time. The follow is part of the codes I've done so far:- Any help would be greatly appreciated. Thank you.
Imports System.Data
Imports System.Data.SqlClient
Imports System.Threading
Imports System.Text.RegularExpressions
Imports System.Text
Public Class Form3
Dim da As SqlDataAdapter
Dim conn As SqlConnection
Dim bsource As BindingSource = New BindingSource()
Dim ds As DataSet
Dim firstname As String
Dim sql As String
Dim pnum As String
Dim msg As String
Dim MaxRows As Integer
Dim i As Integer
Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
btnUpdate.Visible = False
Dim myCommand As New SqlCommand(sql, conn)
Dim connectionString As String = "Data Source=ITCU-23\SQLEXPRESS;Initial Catalog=compare;" & "Integrated Security=SSPI;"
conn = New SqlConnection(connectionString)
sql = "SELECT * FROM Alarms"
da = New SqlDataAdapter(sql, conn)
ds = New DataSet()
Dim commandBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)
da.Fill(ds, "Alarms")
DataGridView1.DataSource = ds.Tables("Alarms")
MaxRows = ds.Tables("Alarms").Rows.Count
MaxRows = MaxRows - 1
' MsgBox(ds.Tables("Alarms").Rows(MaxRows).Item(0))
btnEdit.Enabled = False
btnDel.Enabled = False
Dim i As Integer
Dim dr As DataTable = CType(DataGridView1.DataSource, DataTable)
For i = 0 To (dr.Rows.Count - 1)
DataGridView1.Rows(i).Cells("Selection").Value = DBNull.Value
Next i
'MsgBox(ds.Tables("Alarms").Rows(MaxRows).Item(0))
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
Dim name As String = txtName.Text
Dim blk As String = txtBlock.Text
Dim eNo As String = txtNum.Text
Dim unit As String = txtUnit.Text
Dim dsNewRow As DataRow
dsNewRow = ds.Tables("Alarms").NewRow()
dsNewRow.Item("CustID") = ds.Tables("Alarms").Rows(MaxRows).Item(0) + 1
Dim signal As String = "Unit " & ds.Tables("Alarms").Rows(MaxRows).Item(0) + 1 & " on fire"
dsNewRow.Item("Signal") = signal
If (String.IsNullOrEmpty(name) Or IsNumeric(name) = True) Then
MsgBox("Please enter a proper name")
Exit Sub
End If
If (String.IsNullOrEmpty(blk) = True) Then
MsgBox("Please enter Block Name")
Exit Sub
End If
If (String.IsNullOrEmpty(unit) = True) Then
MsgBox("Please enter a proper unit number")
Exit Sub
End If
If (IsNumeric(eNo) = False Or String.IsNullOrEmpty(eNo)) Then
MsgBox("Please enter a proper emergency number")
Exit Sub
End If
Try
dsNewRow.Item("CustName") = name
dsNewRow.Item("UnitNum") = unit
dsNewRow.Item("BlkName") = blk
dsNewRow.Item("EmerNum") = eNo
ds.Tables("Alarms").Rows.Add(dsNewRow)
conn.Open()
da.Update(ds, "Alarms")
conn.Close()
MsgBox("Your Personal Information have been updated")
txtName.Text = ""
txtBlock.Text = ""
txtUnit.Text = ""
txtNum.Text = ""
btnEdit.Enabled = False
Catch ex As Exception
End Try
End Sub
Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
txtName.Clear()
txtBlock.Clear()
txtUnit.Clear()
txtNum.Clear()
End Sub
Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
txtName.ReadOnly = False
txtBlock.ReadOnly = False
txtUnit.ReadOnly = False
txtNum.ReadOnly = False
btnEdit.Visible = False
btnClear.Enabled = True
btnUpdate.Visible = True
btnDel.Enabled = False
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Try
Dim i As Integer
For i = 0 To (ds.Tables("Alarms").Rows.Count - 1)
If DataGridView1.Rows(i).Cells("Selection").Value IsNot System.DBNull.Value Then
If DataGridView1.Rows(i).Cells("Selection").Value = True Then
conn.Open()
Dim nameCommand As SqlCommand = conn.CreateCommand()
nameCommand.CommandText = _
"UPDATE Alarms SET CustName = '" & txtName.Text & "', BlkName= '" & txtBlock.Text & "', UnitNum = '" & txtUnit.Text & "', EmerNum= '" & txtNum.Text & "' WHERE CustID = '" & TextBox1.Text & "'"
nameCommand.ExecuteNonQuery()
ds.AcceptChanges()
da.Update(ds, "Alarms")
conn.Close()
MsgBox("Customer Information updated")
btnUpdate.Visible = False
btnInsert.Enabled = True
btnClear.Enabled = True
btnEdit.Visible = True
btnEdit.Enabled = False
txtName.Text = ""
txtBlock.Text = ""
txtUnit.Text = ""
txtNum.Text = ""
DataGridView1.Rows(i).Cells("Selection").Value = DBNull.Value
End If
End If
Next i
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Private Sub btnSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelect.Click
Dim i As Integer
'Change to txtbox format
Dim ds As DataTable = CType(DataGridView1.DataSource, DataTable)
txtName.Text = ""
txtBlock.Text = ""
txtUnit.Text = ""
txtNum.Text = ""
For i = 0 To (ds.Rows.Count - 1)
If DataGridView1.Rows(i).Cells("Selection").Value IsNot System.DBNull.Value Then
If DataGridView1.Rows(i).Cells("Selection").Value = True Then
txtName.Text += DataGridView1.Rows(i).Cells("CustName").Value.ToString()
txtBlock.Text += DataGridView1.Rows(i).Cells("BlkName").Value.ToString()
txtUnit.Text += DataGridView1.Rows(i).Cells("UnitNum").Value.ToString()
txtNum.Text += DataGridView1.Rows(i).Cells("EmerNum").Value.ToString()
TextBox1.Text += DataGridView1.Rows(i).Cells("CustID").Value.ToString()
txtName.ReadOnly = True
txtBlock.ReadOnly = True
txtUnit.ReadOnly = True
txtNum.ReadOnly = True
btnInsert.Enabled = False
btnClear.Enabled = False
btnEdit.Enabled = True
btnDel.Enabled = True
End If
End If
Next i
End Sub
End Class
| | Charles Mohd Wednesday, July 29, 2009 9:41 AM | Hi Charles Mohd,
We can handle the CellValueChanged event of the DataGridView to uncheck other rows if one check box is checked. This is the code snippet:
'Check box column index
Private CheckColIndex As Integer = 0
Private Sub DataGridView1_CellValueChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged
'Check the column index and if the check box is checked.
If e.ColumnIndex = CheckColIndex Then
Dim isChecked As Boolean = CType(Me.DataGridView1(e.ColumnIndex, e.RowIndex).Value, Boolean)
If isChecked Then
'If check box is checked, uncheck all the rows, the current row would be checked later.
For Each row As DataGridViewRow In Me.DataGridView1.Rows
row.Cells(e.ColumnIndex).Value = False
Next
End If
End If
End Sub
Let me know if this helps.
Aland Li
Please mark the replies as answers if they help and unmark if they don't. This can be beneficial to other community members reading the thread. - Marked As Answer byCharles Mohd Friday, July 31, 2009 4:33 AM
-
| | Aland Li Thursday, July 30, 2009 12:19 PM | Try using a GroupBox and Radio Buttons in place of the Check Box..
Ashray Lavsi
If my post answers your Question, don't forget to "Mark it as Answer" | | Ashray Lavsi Wednesday, July 29, 2009 1:56 PM | Thank you Ashray for replying,
but before i attempt on that, is there any way that i can restrict the datagridview checkbox column to only be able to select only one checkbox/one row at a time? | | Charles Mohd Wednesday, July 29, 2009 3:22 PM | In the checkbox checkedstate change event, you could run a loop to check if any other checkboxes have been checked, and if yes, then e.cancel = true, would prevent that checkbox from getting checked.
Ashray Lavsi
If my post answers your Question, don't forget to "Mark it as Answer" | | Ashray Lavsi Wednesday, July 29, 2009 3:24 PM | Hi Ashray,
Sorry, iam new to VB.Net, if u dont mind, could you tell me howto writethe checkedstate change event into my program?
Thanks,
Charles | | Charles Mohd Wednesday, July 29, 2009 4:21 PM | 1) Select the CheckBox for which you want to write the property
2) In the properties window on the right, click on the "lightning bolt" symbol
3) This will show you a list of all the events associated with that checkbox
4) I think by default it is on the checkedstate change event, if not scroll to that event and double click on it
5) This will shift to the code view
6) In the event sub, write code tocheck the state of all the other checkbox
7) if any one of the checkbox is TRUE, then write e.cancel = TRUE, this will prevent your checkbox from being set
Note:
1) One event handler can be used for all the checkboxes, just add the name to the "handles ... " at the end of the line
2) You can find some snippets of code to loop through all the checkboxes in many threads on this forum. (Tell me if you can't find any)
Ashray Lavsi If my post answers your Question, don't forget to "Mark it as Answer" | | Ashray Lavsi Wednesday, July 29, 2009 5:58 PM | Hi Ashray,
the checkbox is inside the datagridview and the properties of datagridview is only accessible as a whole, therefore the checkbox i am not able to access its individual property. Its column type is DataGridViewCheckBoxColumn, and it belongs to a cell inside of the datagridview.
| | Charles Mohd Thursday, July 30, 2009 3:36 AM | Hi Charles Mohd,
We can handle the CellValueChanged event of the DataGridView to uncheck other rows if one check box is checked. This is the code snippet:
'Check box column index
Private CheckColIndex As Integer = 0
Private Sub DataGridView1_CellValueChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged
'Check the column index and if the check box is checked.
If e.ColumnIndex = CheckColIndex Then
Dim isChecked As Boolean = CType(Me.DataGridView1(e.ColumnIndex, e.RowIndex).Value, Boolean)
If isChecked Then
'If check box is checked, uncheck all the rows, the current row would be checked later.
For Each row As DataGridViewRow In Me.DataGridView1.Rows
row.Cells(e.ColumnIndex).Value = False
Next
End If
End If
End Sub
Let me know if this helps.
Aland Li
Please mark the replies as answers if they help and unmark if they don't. This can be beneficial to other community members reading the thread. - Marked As Answer byCharles Mohd Friday, July 31, 2009 4:33 AM
-
| | Aland Li Thursday, July 30, 2009 12:19 PM | Hi Aland,
Thanks for your help, from what i see, i thinkthat from your codes that your trying to use an datagridview event whereby it checks for any cells in the datagridview that changes values but i dont understand "If e.ColumnIndex = CheckColIndex",may i know whatis it checking for? But i tried to put ur method into my program but it doesnt seem to work. | | Charles Mohd Thursday, July 30, 2009 4:04 PM |
Hi Charles Mohd,
Sorry for not explain my idea clearly. CheckColIndex refers to the index of the DataGridViewCheckBoxColumn. In my program, it is 0, but it might be different in yours. You need to change its value to your check box column index. "If e.ColumnIndex = CheckColIndex",this linechecks whether the cell whose value is changed is in the check box column. In other words, it checks whether one check box is checked. Could you please change the CheckColIndex and test my code, then tell me if it works?
Best regards,
Aland Li
Please mark the replies as answers if they help and unmark if they don't. This can be beneficial to other community members reading the thread. | | Aland Li Friday, July 31, 2009 2:07 AM | Thanks a million, Aland, after i edited the value of CheckColIndex, it worked, probably because my "selection" checkbox column is at 6 but there are some problem occurred after your provided codes worked, i am thinking it might be this : "
Dim
isChecked As
Boolean
= CType
(Me
.DataGridView1(e.ColumnIndex, e.RowIndex).Value, Boolean
)
". My select user button which retrieves data from the datagridview for which ever checkbox being "checked" and append to textboxes after i click on it, stopped working. The following codes are what i have done for my select user button :
Private Sub btnSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelect.Click
Dim i As Integer
Dim ds As DataTable = CType(DataGridView1.DataSource, DataTable)
txtName.Text = ""
txtBlock.Text = ""
txtUnit.Text = ""
txtNum.Text = ""
For i = 0 To (ds.Rows.Count - 1)
If DataGridView1.Rows(i).Cells("Selection").Value IsNot System.DBNull.Value Then
If DataGridView1.Rows(i).Cells("Selection").Value = True Then
txtName.Text += DataGridView1.Rows(i).Cells("CustName").Value.ToString()
txtBlock.Text += DataGridView1.Rows(i).Cells("BlkName").Value.ToString()
txtUnit.Text += DataGridView1.Rows(i).Cells("UnitNum").Value.ToString()
txtNum.Text += DataGridView1.Rows(i).Cells("EmerNum").Value.ToString()
TextBox1.Text += DataGridView1.Rows(i).Cells("CustID").Value.ToString()
txtName.ReadOnly = True
txtBlock.ReadOnly = True
txtUnit.ReadOnly = True
txtNum.ReadOnly = True
btnInsert.Enabled = False
btnClear.Enabled = False
btnEdit.Enabled = True
btnDel.Enabled = True
End If
End If
Next i
End Sub
| | Charles Mohd Friday, July 31, 2009 4:32 AM |
|
index > Windows Forms Data Controls and Databinding > How to allow users to only check one checkbox in a datagrid view