Hello,

I am building a form that inserts into an existing table. I have a couple different checkboxes that I want to use. When I test the insert using the form debug against the existing table in my database it gives me the following error:

"Conversion failed when converting the varchar value 'Yes' to data type bit."

Is there a way to make it so that the CheckBox function in the form sends a bit versus a yes or no?

HI Nathan, yes you can create a store procedure for your insertion. In your SQL table, make sure the checkbox columns are of type bit (which only accepts values 0 or 1, 0 being unchecked, 1 being checked). Create a stored procedure like something:

create procedure WriteToSQL (@checkbox nvarchar(10) )
as
begin
declare @checkconvert bit
if (@checkbox = 'Yes') set @checkconvert = 1 else set @checkconvert = 0
insert into tablename(checkboxcolumn) values (@checkconvert)
end

Call this stored procedure in your form completion SQL event like:
WriteToSQL '$(checkbox)'
-- David

Hi David,

Great, I'm going to test this out today and will post back with my results.

Thanks, Nathan

Just so I am sure that I'm doing this correctly...the table that I am inserting to has three different columns with checkbox values. How do I properly insert into the multiple columns?

Can I add them in here?

"insert into tablename(checkboxcolumn, checkboxcolumn2, checkboxcolumn3) values (@checkconvert)
end"

HI Nathan, no it would be like this:
create procedure WriteToSQL (@checkbox1 nvarchar(10),@checkbox2 nvarchar(10),@checkbox3 nvarchar(10) )
as
begin
declare @checkconvert1 bit
declare @checkconvert2 bit
declare @checkconvert3 bit
if (@checkbox1 = 'Yes') set @checkconvert1 = 1 else set @checkconvert1 = 0
if (@checkbox2 = 'Yes') set @checkconvert2 = 1 else set @checkconvert2 = 0
if (@checkbox3 = 'Yes') set @checkconvert3 = 1 else set @checkconvert3 = 0
insert into tablename(checkboxcolumn1,checkboxcolumn2,checkboxcolumn3) values (@checkconvert1,@checkconvert2,@checkconvert3)
end

Call this stored procedure in your form completion SQL event like:
WriteToSQL '$(checkbox1)' ,'$(checkbox2)', '$(checkbox3)'

-- David

Ok great, thanks. So last confirmation...where exactly in the event should I call it. Here is what my event generally looks like (abbreviated). Do I place it at the very end like so...?

INSERT INTO  [dbo].[user_tbl](
[firstName],
[middleName],
[lastName],
[checkbox1],
[checkbox2],
[checkbox3],
)
VALUES (
'$(firstName)',
'$(middleName)',
'$(lastName)',
'$(checkbox1)',
'$(checkbox2)',
'$(checkbox3)',
WriteToSQL '$(checkbox1)' '$(checkbox2)' '$(checkbox3)'
)

Not successful yet and not sure how to debug. Previously I would enable debugging and then test the insert in SSMS, but I'm not sure how to do this with the stored procedure.

My procedure (abbreviated) looks like this:

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

Create PROCEDURE DNNConvert2Bit (@firstName nvarchar(50),@middleName nvarchar(50),@lastName nvarchar(50),@checkbox1 nvarchar(10),@checkbox2

nvarchar(10),@checkbox3 nvarchar(10))
as
begin
declare @checkconvert1 bit
declare @checkconvert2 bit
declare @checkconvert3 bit
if (@checkbox1 = 'Yes') set @checkconvert1 = 1 else set @checkconvert1 = 0
if (@checkbox2 = 'Yes') set @checkconvert2 = 1 else set @checkconvert2 = 0
if (@checkbox3 = 'Yes') set @checkconvert3 = 1 else set @checkconvert3 = 0

INSERT INTO User_tbl(firstName,middleName,lastName,checkbox1,checkbox2,checkbox3)

VALUES (@firstName,@middleName,@lastName,@checkconvert1,@checkconvert2,@checkconvert3)
end

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

My SQL Event looks like this:

DNNConvert2Bit
'$(firstName)',
'$(middleName)',
'$(lastName)',
'$(checkbox1)',
'$(checkbox2)',
'$(checkbox3)'
)

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

The debug response I get in DF shows something like this:

Executing SQL Event
SQL Query: DNNConvert2Bit 'Scott', '', 'Apple', 'Yes', 'Yes', 'Yes' )

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

I have executed the stored procedure in SSMS, entered some values and received an error that says:

Msg 102, Level 15, State 1, Line 8

Incorrect syntax near '/'.

Any ideas on what I might be doing wrong?

Ahhhh the little things in life.

That appeared to be it and all looks well in the db.

Thanks so much for working through this with me.

-Nathan