Updating an Access DB Yes/No field with a Checkbox value

[Harry Devine]

I have a DataGrid that is configured to use the Edit/Update/Cancel concept
correctly. My grid shows values from 5 database fields. I only need to
update that last 4 fields. The last field is a Yes/No value in Access.

Using the OleDbCommand, if I do not consider the Yes/No field, the
ExecuteNonQuery command, using my UPDATE SQL statement, updates the record
correctly. However, if I put the Yes/No field into the mix, ExecuteNonQuery
returns 0 (0 records updated).

The Yes/No field in Access is setup to show checkboxes in the database
itself. I have tried setting the value to update to "on"/"off",
"yes"/"no", -1/0, and none of these seem to work. Anyone have any idea how
to update this type of field?

Thanks for any help.
Harry

 

[Cowboy \(Gregory A. Beamer\) [MVP]]

You will probably have to translate to Boolean values: true/false. Have a
friend working on a similar issue right now, so I may have some additional
info within a day or so.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************

 

[Earl]

I got so sick of dealing with Yes/No issues in Access that I've found the
simplest way of dealing with it is to convert the field to a 3-character
text field. Save "Yes", save "No" -- how's that for a revelation. I'll
probably get blasted by those who "know better", but to me, the issue isn't
worth the grief of fighting the translation in code coming and going.

 

[Mary Chipman]

The problem is that Access doesn't support a true Boolean in the
Yes/No data type, which can return a yes/true value (-1), no/false
value (0) and unknown (null). This state of affairs pitches you
headlong into the three-valued logic quagmire in your code. The best
solution is to alter the Yes/No field to not allow nulls and give it a
default value. I don't know what your client code looks like, but once
you have done the schema changes, all comparisons and evaluations
should be to False, which is zero (0) in all languages.

--Mary

 

[Harry Devine]

I tried making the field a text field, and I am saving "-1" in the field if
the box is checked, and "0" if not, but the ExecuteNonQuery still returns 0.
Like I said earlier, using only the other 4 fields, which are text fields
also, work OK. I now have text fields across the table, so now I'm really
stumped.

Harry

 

[Earl]

You are saving the "-1" or "0" as strings correct? If so, you might consider
dropping that field and recreating it.

 

[Harry Devine]

That's correct, they're strings. By drop it, you mean delete the field from
the database and recreate it?

Thanks,
Harry

 

[Earl]

Yes, that was just an idea of something to easily try. I've dealt with
Access a lot during VB days, but not much lately and I do not use the grid
in the manner you are describing.

You might want to check what is actually being returned from the grid before
it updates the db. Also, since a seemingly simple field update is failing
and you know what field it is, you may want to post a code snippet for your
update routine so folks here can see what the issue might be.

 

[Harry Devine]

I recreated the Text field by deleting it and re-inserting it, but to no
avail. Here is my update routine:

Public Sub UserGrid_Update (Source As Object, E As
DataGridCommandEventArgs)
Dim objConn as new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\inetpub\wwwroot\applecart\pool03.mdb")
Dim cmd As OleDbCommand = new OleDbCommand("UPDATE tblPlayerStats SET
EMail = @EMail, RealName = @RealName, Phone = @Phone, Paid = @Paid WHERE
Name = @Name", objConn)

Dim chkPaid As CheckBox =
CType(e.Item.FindControl("edit_chkDonated"), CheckBox)
Dim tName As TextBox = e.Item.Cells(2).Controls(0)
Dim sName As String = tName.Text
Dim tRealName As TextBox = e.Item.Cells(3).Controls(0)
Dim sRealName As String = tRealName.Text
Dim tEmail As TextBox = e.Item.Cells(4).Controls(0)
Dim sEmail As String = tEmail.Text
Dim tPhone As TextBox = e.Item.Cells(5).Controls(0)
Dim sPhone As String = tPhone.Text
Dim iRet As Integer
Dim sPaid As String

if chkPaid.Checked then
sPaid = "Yes"
else
sPaid = "No"
end if

cmd.Parameters.Add(new OleDbParameter("@Name", sName))
cmd.Parameters.Add(new OleDbParameter("@EMail", sEmail))
cmd.Parameters.Add(new OleDbParameter("@RealName", sRealName))
cmd.Parameters.Add(new OleDbParameter("@Phone", sPhone))
cmd.Parameters.Add(new OleDbParameter("@Paid", sPaid))

objConn.Open()
iRet = cmd.ExecuteNonQuery()
objConn.Close

UserGrid.EditItemIndex = -1
BindData()

End Sub

Here is what my DataGrid is setup as:

<asp:datagrid id="UserGrid" runat=server AutoGenerateColumns="False"
BorderStyle="Dotted" BorderWidth="2"
BackgroundColor="red"
CellPadding="5"
Font-Name="Arial" Font-Size="8pt"
OnEditCommand="UserGrid_Edit"
OnCancelCommand="UserGrid_Cancel"
OnUpdateCommand="UserGrid_Update"
OnItemCommand="UserGrid_Command"
OnItemCreated="UserGrid_ItemCreated">

<HeaderStyle BackColor="#aaaadd">
</HeaderStyle>

<EditItemStyle BackColor="yellow">
</EditItemStyle>

<ItemStyle Wrap="false">
</ItemStyle>

<Columns>
<asp:EditCommandColumn
ButtonType ="LinkButton"
CancelText = "Cancel"
EditText = "Edit"
UpdateText = "Update">
</asp:EditCommandColumn>
<asp:ButtonColumn
HeaderText="Delete?"
ButtonType="LinkButton"
Text="Delete"
CommandName="Delete"/>
<asp:BoundColumn
DataField = "Name"
HeaderText = "Name"
ReadOnly = "True" />
<asp:BoundColumn
DataField = "RealName"
HeaderText = "Real Name"/>
<asp:BoundColumn
DataField = "EMail"
HeaderText = "EMail"/>
<asp:BoundColumn
DataField = "Phone"
HeaderText = "Phone"/>
<asp:TemplateColumn HeaderText="Donated?">
<ItemTemplate>
<asp:Checkbox runat="server" name="chkDonated" ID="chkDonated"
enabled="False" Checked='<%# IIF(DataBinder.Eval(Container.DataItem, "Paid")
="Yes", "True", "False") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:Checkbox runat="server" id="edit_chkDonated" enabled="True"
checked='<%# IIF(DataBinder.Eval(Container.DataItem, "Paid") = "Yes",
"True", "False") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:ButtonColumn
HeaderText="View PWD"
ButtonType="LinkButton"
Text="View PWD"
CommandName="ViewPWD"/>
<asp:ButtonColumn
HeaderText="Reset PWD"
ButtonType="LinkButton"
Text="Reset"
CommandName="ResetPWD"/>
</Columns>
</asp:datagrid>


All of the fields in my Access Database are Text Fields. I can give the
properties of each field if anyone thinks that this would be helpful or
necessary. Thanks for all of your help.
Harry

 

[Earl]

Some ideas: Set a breakpoint before your parameters and see what your
variables contain. Set sPaid to a fixed string value and see if you get the
exception. Then rem out the EditItemIndex and BindData and see if you still
get an exception. My money is on BindData causing the error.

 

[Harry Devine]

I'm not using Visual Studio .NET, just an aspx page in Internet Explorer.
I've had an <asp:label> field that I update to show the return value of
ExecuteNonQuery, and it always returns 0. I'll try to rem out the
EditItemIndex and BindData and see how that works out. I'll let you know.

Thanks,
Harry

 

[Harry Devine]

I commented out the EditItemIndex and BindData, and printed out the return
value for ExecuteNonQuery, and the return value was 0, meaning 0 rows were
updated. I even tried to update just 1 of the fields only, and it still
returns 0. It appears now that the update isn't working at all for any
field, not just the paid field.

Harry

 

[Earl]

So what you are saying is that before you "commented" out EditItemIndex and
BindData, you could update all the fields but the paid field without an
exception? Now with those commented out, nothing is updating? And do you get
an exception? Do you have a piece of code for BindData?

 

[Harry Devine]

I don't get an exception, nothing updates. Here are the functions as I have
them currently. If you'd like, I can attach a copy of the aspx file.

I appreciate all of you help on this.
Harry

Public Sub BindData()
Dim objConn as new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\inetpub\wwwroot\applecart\pool03.mdb")
objConn.Open()
Dim oaUser As OleDbDataAdapter
Dim UserDS as DataSet = New DataSet()
oaUser = New OleDbDataAdapter("Select * FROM tblPlayerStats ORDER BY
Name ASC", objConn)

oaUser.Fill(UserDS,"tblPlayerStats")
objConn.Close

UserGrid.DataSource = UserDS.Tables("tblPlayerStats")
UserGrid.DataBind()
End Sub

Public Sub UserGrid_Update (Source As Object, E As
DataGridCommandEventArgs)
Dim objConn as new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\inetpub\wwwroot\applecart\pool03.mdb")
' Dim cmd As OleDbCommand = new OleDbCommand("UPDATE tblPlayerStats
SET EMail = @EMail, RealName = @RealName, Phone = @Phone, Paid = @Paid WHERE
Name = @Name", objConn)
Dim cmd As OleDbCommand = new OleDbCommand("UPDATE tblPlayerStats SET
Paid = @Paid WHERE Name = @Name", objConn)

Dim chkPaid As CheckBox =
CType(e.Item.FindControl("edit_chkDonated"), CheckBox)
Dim sName As String = e.Item.Cells(2).Text
Dim sRealName As String = e.Item.Cells(3).Text
Dim sEmail As String = e.Item.Cells(4).Text
Dim sPhone As String = e.Item.Cells(5).Text
Dim iRet As Integer
Dim sPaid As String

if chkPaid.Checked then
sPaid = "Yes"
else
sPaid = "No"
end if
'labelPaid.Text = "sPaid: " & sPaid

cmd.Parameters.Add(new OleDbParameter("@Name", sName))
' cmd.Parameters.Add(new OleDbParameter("@EMail", sEmail))
' cmd.Parameters.Add(new OleDbParameter("@RealName", sRealName))
' cmd.Parameters.Add(new OleDbParameter("@Phone", sPhone))
cmd.Parameters.Add(new OleDbParameter("@Paid", "No"))

objConn.Open()
iRet = cmd.ExecuteNonQuery()
objConn.Close
labelEmail.Text = "Rows updated: " & iRet
' labelEmail.Text = "RealName: " & sRealName & " Email: " & sEmail & "
Phone: " & sPhone & " Name: " & sName & " Paid: " & sPaid

' UserGrid.EditItemIndex = -1
' BindData()

End Sub

<asp:datagrid id="UserGrid" runat=server AutoGenerateColumns="False"
BorderStyle="Dotted" BorderWidth="2"
BackgroundColor="red"
CellPadding="5"
Font-Name="Arial" Font-Size="8pt"
OnEditCommand="UserGrid_Edit"
OnCancelCommand="UserGrid_Cancel"
OnUpdateCommand="UserGrid_Update"
OnItemCommand="UserGrid_Command"
OnItemCreated="UserGrid_ItemCreated">

<HeaderStyle BackColor="#aaaadd">
</HeaderStyle>

<EditItemStyle BackColor="yellow">
</EditItemStyle>

<ItemStyle Wrap="false">
</ItemStyle>

<Columns>
<asp:EditCommandColumn
ButtonType ="LinkButton"
CancelText = "Cancel"
EditText = "Edit"
UpdateText = "Update">
</asp:EditCommandColumn>
<asp:ButtonColumn
HeaderText="Delete?"
ButtonType="LinkButton"
Text="Delete"
CommandName="Delete"/>
<asp:BoundColumn
DataField = "Name"
HeaderText = "Name"
ReadOnly = "True" />
<asp:BoundColumn
DataField = "RealName"
HeaderText = "Real Name"/>
<asp:BoundColumn
DataField = "EMail"
HeaderText = "EMail"/>
<asp:BoundColumn
DataField = "Phone"
HeaderText = "Phone"/>
<asp:TemplateColumn HeaderText="Donated?">
<ItemTemplate>
<asp:Checkbox runat="server" name="chkDonated" ID="chkDonated"
enabled="False" Checked='<%# IIF(DataBinder.Eval(Container.DataItem, "Paid")
="Yes", "True", "False") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:Checkbox runat="server" id="edit_chkDonated" enabled="True"
checked='<%# IIF(DataBinder.Eval(Container.DataItem, "Paid") = "Yes",
"True", "False") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:ButtonColumn
HeaderText="View PWD"
ButtonType="LinkButton"
Text="View PWD"
CommandName="ViewPWD"/>
<asp:ButtonColumn
HeaderText="Reset PWD"
ButtonType="LinkButton"
Text="Reset"
CommandName="ResetPWD"/>
</Columns>
</asp:datagrid>

 

[Earl]

Harry, the only thing that jumps out at me is that I don't see a field for
your Paid column in your ASP datagrid code. If that doesn't resolve the
issue, post your original subject combined with this code over in the
microsoft.public.dotnet.framework.aspnet forum.

 

[Harry Devine]

Thanks, I'll do that. The asp:TemplateColumn is what I am using for the
Paid field. I found an example somewhere online that showed how to
check/uncheck a Checkbox with values from a database.

Thanks for you time and assistance.
Harry