Bit type parameter for stored procedure

I am trying to supply a bit type parameter to a stored procedure. This is used to update a Bit type field in a table. The field is called PDI

The syntax I am trying to use is:

MyStoredProcedure.Parameters.Add(New SqlParameter("@.Pdi",SqlDbtype.bit))
MyStoredProcedure.Parameters("@.pdi").value = -1

When I do my ExecuteNonQuery I get error 8114

What am I doing wrong?The bit data type can hold one of the following values: 1, 0, or NULL. You cannot store a -1 in a bit column.

Terri|||Ok thanks for that. It will indeed accept 1 or 0

I now need to use the value from a CheckBox type control on my Form. So now
My Code reads:

MyStoredProcedure.Parameters.Add(New SqlParameter("@.Pdi",SqlDbtype.bit))
MyStoredProcedure.Parameters("@.pdi").value = pdi.value

This generates the Error: "String was not recognised as a valid boolean."

What is the syntax for casting to a bit type value?|||You will likely need to convert your string value to a boolean value, using the ToBoolean method, something like this:

MyStoredProcedure.Parameters("@.pdi").value = System.Convert.ToBoolean(pdi.value)

See this article with handy chart for reference:Accessing SQL Server Data in C# with ADO.NET.

Terri|||No that didn't work either.

Just for the hell of it I decided to put the value of the PDI control into a span control like this:

message.innerhtml=pdi.value

I expected to see True or False or 1 or 0 or something. What I got was S00817

What is that!! I'm obviosly going the wrong way about extracting the value from a CheckBox control|||We might need to see more of your code.

Are you using a CheckBox or a CheckBoxList?

If a CheckBox, then it would probably be pdi.Checked, which already returns a boolean value.

I am sorry to be giving you the answer in pieces.

Terri|||Hi,

You need to use:

MyStoredProcedure.Parameters.Add(New SqlParameter("@.Pdi",SqlDbtype.bit))
MyStoredProcedure.Parameters("@.pdi").value = pdi.checked

or unchecked.

HTH

JB|||Yes that fixed it.

MyStoredProcedure.Parameters("@.pdi").value = pdi.checked

Thanks for your help|||

my check box is in the datagrid. I used this line. it have error.

comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.p_PDA", System.Data.SqlDbType.Bit, "PDA")).Value = CType(e.Item.FindControl("ckPDA"), CheckBox).Checked

Here is my html code. I tried to assign the check box value to byte. It did not work, too. Thanks.

<asp:datagrid id="dtgPhysician" style="Z-INDEX: 102; LEFT: 8px; POSITION: absolute; TOP: 88px" runat="server" Font-Names="Arial" OnCancelCommand="dtgPhysician_Cancel" OnEditCommand="dtgPhysician_Edit" Width="985px" AutoGenerateColumns="False" PagerStyle-Mode="NumericPages" DataKeyField="License" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4" ShowHeader="true" EditItemStyle-BackColor="#eeeeee" BackColor="White" OnUpdateCommand="dtgPhysician_Update">
<SelectedItemStyle Font-Bold="True" ForeColor="#CCFF99" BackColor="#009999"></SelectedItemStyle>
<EditItemStyle BackColor="#EEEEEE"></EditItemStyle>
<AlternatingItemStyle BorderColor="#C0C0FF"></AlternatingItemStyle>
<ItemStyle ForeColor="#003399" BackColor="White"></ItemStyle>
<HeaderStyle Font-Size="Medium" Font-Names="Arial Black" Font-Bold="True" HorizontalAlign="Center" ForeColor="#CCCCFF" VerticalAlign="Middle" BackColor="#003399"></HeaderStyle>
<FooterStyle ForeColor="#003399" BackColor="#99CCCC"></FooterStyle>
<Columns>
<asp:EditCommandColumn ButtonType="PushButton" UpdateText="Update" CancelText="Cancel" EditText="Edit Info"></asp:EditCommandColumn>
<asp:BoundColumn DataField="PName" SortExpression="PNAME asc" ReadOnly="True" HeaderText="Name" HeaderStyle-Font-Name="arial">
<HeaderStyle Font-Names="arial"></HeaderStyle>
</asp:BoundColumn>
<asp:TemplateColumn HeaderText="Status">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "Status") %>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList runat="server" id="lstStatus" DataTextField="Status" SelectedIndex ='<%# GetSelIndex(Container.DataItem("Status")) %>'>
<asp:ListItem Value="0">DOC</asp:ListItem>
<asp:ListItem Value="1">POE</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Group">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "PGroup") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="txtGroup" Width="100%" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "PGroup") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Email">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "Email") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="txtEmail" Width="100%" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "Email") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Logon">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "LogonName") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="txtLogon" Width="100%" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "LogonName") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="PDA">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "PDA") %>
</ItemTemplate>
<EditItemTemplate>
<asp:CheckBox id="ckPDA" runat="server" Checked ='<%# DataBinder.Eval(Container.DataItem, "PDA") %>'>
</asp:CheckBox>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Training">
<EditItemTemplate>
<asp:DropDownList runat="server" id="dpTraining">
<asp:ListItem Value="0">Init Training</asp:ListItem>
<asp:ListItem Value="1">Retraining</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Date">
<EditItemTemplate>
<asp:textbox runat="server" id="txtDate"></asp:textbox>
<asp:CompareValidator id="vrDate" runat="server" ErrorMessage="*date*" ControlToValidate="txtDate" Type="Date" Operator="DataTypeCheck"></asp:CompareValidator>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:BoundColumn DataField="PID" SortExpression="PID" ReadOnly="True" HeaderText="Physician ID">
<HeaderStyle Font-Names="arial"></HeaderStyle>
</asp:BoundColumn>
</Columns>
<PagerStyle HorizontalAlign="Left" ForeColor="#003399" Position="Top" BackColor="#99CCCC" Mode="NumericPages"></PagerStyle>
</asp:datagrid>

|||

yan19454:

I used this line. it have error.

comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.p_PDA", System.Data.SqlDbType.Bit, "PDA")).Value = CType(e.Item.FindControl("ckPDA"), CheckBox).Checked

Please please please always post the error encountered, not just the fact that you have an error.