SOLVED: VB.NET Check box as Boolean conundrum

[FthrJACK]

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

Edited June 27, 2009 by FthrJACK

[CoffeeFiend]

the table columns are varchar(5) for the check boxes.

Why in the world would you do that (use variable length string to store what's essentially a boolean value), instead of using a bit?

[FthrJACK]

because then its saving the value, whatever it is at the minute, till i get it to save as true/false

i changed it because it was crapping out on creating a record, so i needed to see what value it was saving. i dont think bit would be right anyway would it? ive tried s many tweakings now im a bit lost from where i was origionally - but i seem to remember on editing a record id get a SQL error about saving a varchar value "True" to a bit column.

Ill change it back and see what i get, thanks for pointing it out CF.

Though off the top of my head... bit would be for values 0 1 2? And im wanting to write true/false boolean, however a bit value is what im getting. checkboxname.checked should give a boolean value and it isnt doing.

Edited June 27, 2009 by FthrJACK

[FthrJACK]

Thanks CF! setting the column 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.