Boolean Data Type not available (workaround for checkbox.checked storing?)

I have a page for inventory price entry that I have used for a while. Now I need to add a checkbox for whether or not the price includes shipping. I added the checkbox to the form and had it posting 'True' or 'False' to the database as nchar(10) data type. When the gridview pulls up the data, I have the Item Template like this, so it shows a disabled checkbox either checked or not:

<asp:CheckBoxID="CheckBox2"runat="server"Checked='<%# Convert.ToBoolean(Eval("Shipping")) %>'Enabled="False"/>

This works fine for displaying the values, but I copied the checkbox to the Edit Item Template, but did not disabled this one. At first, I didn't change the databindings, leaving it Convert.ToBoolean(Eval("Shipping")), which allowed me to go into Edit mode, change the checkbox, then click update. At which point it would return to it's original state (meaning Update wasn't actually updating). However if I change the databindings, then the page won't display.

I checked the SQL statement, and sure enough, it has the

UpdateCommand="UPDATE [PartsTable] ... SET [Shipping] = @.Shipping... WHERE [PartID] = @.original_PartID

After fiddling with the sql statement, now I get Object cannot be cast from DBNull to other types. I think that means that the checkbox is sending a null value to the database.

Any insight as to how to get this to work is much appreciated. Thanks in advance.

I don't know if this will work declaratively but your problem is ANSI SQL Boolean is three valued True/False/Null the reason ANSI SQL is called three valued logic. The links below will show how to use Nullable types of FCL(framework class library) 2.0 to solve your problem. Hope this helps.

http://www.codeproject.com/csharp/c__20_nullable_types.asp

http://www.c-sharpcorner.com/UploadFile/PashuSX/NullableTypes04282006114548AM/NullableTypes.aspx?ArticleID=b28a5114-a92a-4ced-a23c-06d8a29de6e4

|||

I found this thread:http://forums.asp.net/thread/1092916.aspx and after reading it, I've changed the data type in the database to Bit. This has significantly helped my problem .

BUT I have the checkbox in normal mode displaying perfectly fine. However, when I click edit, there is a textbox that displays either True or False (True is 1 and False is 0 in the database). If I change True to 0 or False to 1, it works. HOWEVER I want it to display a textbox. When I delete the textbox in the edit template, replace it with a checkbox and then set the databinding to the shipping field, I can click edit and the box appears just fine, but when I change it and click update, I get this Error:Syntax error converting the nvarchar value 'False' to a column of data type bit.Which to me means that it is trying to post the checkbox.checked ='s value, not just checkbox.checked which is stored as a bit in the database.

How/Where can I change this?

|||Figured it out! Turns out the auto generated update statement set the paramenter to

<asp:ParameterName="Shipping"Type="String"/>

So I took out the Type="String" and it fixed my problem.

Thanks.

|||I am glad to see your problem is resolved.

Bizarre case behavior.

I'm doing a select which includes the following:

case
when (rtrim(ltrim(T464.COMMENT_4)) = '' or T464.COMMENT_4 is null)
then ''
else
convert(datetime,left(replace(replace(T464.COMMENT _4,' QTR: ',''),' -
',''),10))
end as QuarterStartDate

The COMMENT_4 field is a char(51), and contains values like: ' QTR:
03/01/2005 - 05/31/2005', '', a number of spaces, or NULL. If I remove
the convert to datetime within the case, data returns as expected,
retaining NULLs and blanks. If I leave the convert in, it converts
every row to datetime and seems to disregard the case expression.

I've attempted converting COMMENT_4 to a varchar first, and that didn't
help either. Also this does not seem to be affected by removal of the
LTRIM and RTRIM. Googling for this turns up no similar results. Have
I simply nested too far within the case, or what?

Any and all help and advice would be greatly appreciated.

Thanks!<twbanks@.gmail.com> wrote in message
news:1109020542.481788.195910@.c13g2000cwb.googlegr oups.com...
> I'm doing a select which includes the following:
> case
> when (rtrim(ltrim(T464.COMMENT_4)) = '' or T464.COMMENT_4 is null)
> then ''
> else
> convert(datetime,left(replace(replace(T464.COMMENT _4,' QTR: ',''),' -
> ',''),10))
> end as QuarterStartDate
> The COMMENT_4 field is a char(51), and contains values like: ' QTR:
> 03/01/2005 - 05/31/2005', '', a number of spaces, or NULL. If I remove
> the convert to datetime within the case, data returns as expected,
> retaining NULLs and blanks. If I leave the convert in, it converts
> every row to datetime and seems to disregard the case expression.
> I've attempted converting COMMENT_4 to a varchar first, and that didn't
> help either. Also this does not seem to be affected by removal of the
> LTRIM and RTRIM. Googling for this turns up no similar results. Have
> I simply nested too far within the case, or what?
> Any and all help and advice would be greatly appreciated.
> Thanks!

A CASE expression can only return a single data type, which may be part of
your problem, but rather than guessing at exactly what your data looks like,
I suggest you provide a sample - if someone else can quickly copy and paste
some code into Query Analyzer, you are much more likely to get a useful
reply:

http://www.aspfaq.com/etiquette.asp?id=5006

Simon|||Thanks, Simon.

I wasn't thinking about that. Adding another convert to turn it into a
varchar did the trick.