Hi All,

I have one vb form with two textbox,one checkbox, and two buttons. I am using SQL Database as back end for this form. Controls of my form goes like that:

1. txtGenderID
2. txtGenderName
3. chkIsDefault
4. btnSave
5. btnCancel

SQL Table as follows:

Table Name: tblGender

GsGenderCode char(4)
GsGenderName varchar(50)
IsDefault bit (0 = false, 1=true)
SetDate datetime

In this form i want to make some events on "chkIsDefault", i want this check box to validate true value. i.e. if the user has already set one record as true and if user set another value as true then it should validate saying that "True value has already been set.". I did coding but not working.

My Code are as follow:

Function
-------------------------------------------------------------------------------

Public Function CheckBoxValidation(ByVal sCheckBoxCheck As Boolean) As Boolean
 Try
 Dim oCtsDr As OleDb.OleDbDataReader
 Dim CtsSQL As String = "SELECT IsDefault FROM dbo.tblGender WHERE IsDefault = '" & sCheckBoxCheck & "'"
 Dim CtsCommand As New OleDb.OleDbCommand(CtsSQL, SQLConn)
 SQLConn.ConnectionString = oFunc.GetConnectionString(strINIPath)
 SQLConn.Open()
 oCtsDr = CtsCommand.ExecuteReader()
 CheckBoxValidation = oCtsDr.HasRows
 oCtsDr.Close()
 SQLConn.Close()
 Catch ex As Exception
 Throw ex
 End Try
 End Function

-------------------------------------------------------------------------------
btnSave Code

Private Sub btnCtsSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCtsSave.Click
 If ValidateData() Then
 If vilGender.CheckBoxValidation(True) Then
 MessageBox.Show("Another Gender already set as Default. Please check", "Data Validation", MessageBoxButtons.OK, MessageBoxIcon.Information)
 ctsDefaultCheckBox.Focus()
 Exit Sub
 Else
 btnCtsSave.Focus()
 End If
 Try
 If bNewData Then
 SQLConn.Open()
 trns = SQLConn.BeginTransaction
 InsertCMD()
 trns.Commit()
 SQLConn.Close()
 GetData()
 bNewData = False
 bEditData = False
 Count()
 MessageBox.Show("Record Saved Successfully", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information)
 EnableControlsLoadMode(True)
 Else
 SQLConn.Open()
 trns = SQLConn.BeginTransaction
 UpdateCMD()
 trns.Commit()
 SQLConn.Close()
 GetData()
 bNewData = False
 bEditData = False
 Count()
 MessageBox.Show("Record Updated Successfully", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information)
 EnableControlsLoadMode(True)
 End If
 Catch ex As Exception
 trns.Rollback()
 MessageBox.Show("Critical Error!" & ex.Message, "Critical Error.", MessageBoxButtons.OK, MessageBoxIcon.Error)
 End Try
 End If
 End Sub

-------------------------------------------------------------------------------

CAN ANYONE TELL ME WHERE DID I WENT WRONG? WHEN I CLICK BUTTON IT VALIDATES BOTH THE VALUE [TRUE AND FALSE].

Are you saying it calls the CheckBoxValidation twice? Because I can't see where that would happen. you only call the method with the True parameter.
As a point of coding design you replicate code in your IF statment that you don't need to. Apart from UpdateCMD inside of InsertCMD the two sections are the same. It would be better to do this:

SQLConn.Open()
trns = SQLConn.BeginTransaction
string messageStr
If bNewData Then
   InsertCMD()
   messageStr = "Record Saved Successfully"
Else 
   UpdateCMD()
   messageStr = "Record Updated Successfully"
End If
trns.Commit()
SQLConn.Close()
GetData()
bNewData = False
bEditData = False
Count()
MessageBox.Show(messageStr, "Save", MessageBoxButtons.OK, MessageBoxIcon.Information)
EnableControlsLoadMode(True)

Also your try block is quite long and you use the beginTransaction and commit immediately on either side of the insert or update commands. The problem here is that you still have potential causes of an exception being thrown in your try/catch block - namely GetData() and Count() - which would then hopelessly call the rollback() command. You've already committed by that point so its too late to roll back.

Thanks for your correction and your comments. So can you tell me if i need to validate the checkbox in same code how would i?

Just so I'm clear you want to check the database to see if the IsDefault column has a 1 in it anywhere to see if the default has already been set for another name? Only one record in the table can be 1 the rest have to be 0?

Yes, thats what i want to have in my system, if IsDefaul column already contains the value 1 then it should validate saying that "The default value is already set". I have shown my function code which seems to be not working. My Function validates both the value (1 and 0). I have called the validation function at "Save" button, as i want to have validation to be trigered while click save button while adding new record as well as while updating the records.

So can you please help me out?

This question has already been answered. Start a new discussion instead.