VB.NET Check box as Boolean conundrum

• • Reply

  11 years ago

  by fthrjack

  Fthr JACK

  Bradford, United Kingdom

  Joined 11 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 :)

  Report abuse
• • Reply

  11 years ago

  by fthrjack

  Fthr JACK

  Bradford, United Kingdom

  Joined 11 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.

  Report abuse