VB.NET Check box as Boolean conundrum

VB.net , Checkbox , SQL Bradford, United Kingdom
  • 9 years ago

    Hi, i have a form with text boxes for username, initials, and password on. Also on this form are a few checkboxes which at the minute i simply want to save their state to SQL as true or false depending if ticked or not. The checkboxes or text boxes are not bound to anything, and are used only for creating a new record in the table.

    There is a gridview below these boxes showing already created records.

    The code im using to insert to SQL with is:

    If String.IsNullOrEmpty(UsernameTb.Text) Then
        		MsgBox("You must enter a Username!")
        	Else
        		If String.IsNullOrEmpty(InitialsTb.Text) Then
        			MsgBox("You must enter users Initials!")
        		Else
        			If String.IsNullOrEmpty(PasswordTb.Text) Then
        				MsgBox("You must enter a Password!")
        			Else
        				Try
        					Me.UsersTableAdapter.AddUserQry(UsernameTb.Text, InitialsTb.Text, PasswordTb.Text, ApplicationsChkbx.Checked, AdministrationChkBx.Checked, CustomerSearchChkBx.Checked, CustomerFormChkbx.Checked, UnderWrittingChkBx.Checked)
    
    'Update the Gridview and notify account created
        					Me.UsersTableAdapter.Fill(Me.WDA_SQLDataSet.Users)
        					MsgBox("Created user " & UsernameTb.Text)
    
    'Empty all the text boxes and set check boxes back to unticked state
    
    
        					UsernameTb.Text = Nothing
        					InitialsTb.Text = Nothing
        					PasswordTb.Text = Nothing
        					AdministrationChkBx.CheckState = 0
        					ApplicationsChkbx.CheckState = 0
        					CustomerFormChkbx.CheckState = 0
        					CustomerSearchChkBx.CheckState = 0
        					UnderWrittingChkBx.CheckState = 0
    
        				Catch ex As Exception
        					MsgBox("Error will robinson, error")
        				End Try
        			End If
        		End If
        	End If
    

    "AddUserQry" is a Query on the dataset for that table:

    INSERT INTO [Users] ([Username], [Initials], [Password], [Applications_Access], [Administration_Access], [CustomerSearch_Access], [CustomerForm_Access], [Underwritting_Access]) VALUES (@Username, @Initials, @Password, @Applications_Access, @Administration_Access, @CustomerSearch_Access, @CustomerForm_Access, @Underwritting_Access);
        SELECT ID, Username, Initials, Password, Applications_Access, Administration_Access, CustomerSearch_Access, CustomerForm_Access, Underwritting_Access FROM Users WHERE (ID = SCOPE_IDENTITY())
    

    For some reason when i create a new record, it saves the check state as -1 for checked, and 0 for unchecked. the table columns are varchar(5) for the check boxes.

    Using checkboxname.checkstate doesent work either - that saves the state as 1 or 0 (as opposed to -1 and 0)

    On another form i made for editing existing entries, i have the same text boxes and check boxes, which are bound so they show the record details. when using that form to EDIT an entry, it saves the check box states as "True" or "False" as it should do.

    Im completely stumped here, because if i create a new user it then breaks the gridview showing users, because it throws up error messages about "-1 not being valid boolean" when it attempts to render each check box in a grid view.

    Has anyone got any ideas why this is happening? and how i can make the insert save the check box state as boolean? im losing sleep over this one, litteraly!

    any help/suggestions much appreciated :)

  • 9 years ago

    Solved!

    setting the column type to 'bit' fixes it, that way SQL converts the numeric value on insert.

    Also Mycheckbox.checked.ToString() will work too, though thats having vb do the conversion not SQL server.

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“The greatest performance improvement of all is when a system goes from not-working to working.” - John Ousterhout