Updating an Access Yes/No field from a datagrid

[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.

In the mean time, I have recreated the Yes/No field to be a Text Field which
stores "Yes" or "No", and the checkboxes update correctly in both display
mode and when I go into Edit mode. However, nothing gets updated now (where
the other 4 fields were OK earlier). I am posting my code below if anyone
could take a look, or have any ideas.

Thanks for any help.
Harry

Code:
<%@ Page Language="VB" Debug="True"%>
<%@ Register TagPrefix="mytree" Namespace="Microsoft.Web.UI.WebControls"
Assembly="Microsoft.Web.UI.WebControls"%>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<%@ import Namespace="System.String" %>
<%@ import Namespace="System.Web.Mail" %>

<script runat="server">

Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
BindData()
End If
End Sub

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_Edit (Source As Object, E As
DataGridCommandEventArgs)
UserGrid.EditItemIndex = E.Item.ItemIndex
BindData()
End Sub

Public Sub UserGrid_Cancel (Source As Object, E As
DataGridCommandEventArgs)
UserGrid.EditItemIndex = -1
BindData()
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

Public Sub UserGrid_Command(sender As Object, e As
DataGridCommandEventArgs)
Select (CType(e.CommandSource, LinkButton)).CommandName

Case "Delete"
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("DELETE FROM
tblPlayerStats WHERE Name = @Name", objConn)

cmd.Parameters.Add(new OleDbParameter("@Name",
e.Item.Cells(2).Text))
objConn.Open()
cmd.ExecuteNonQuery()
objConn.Close
Case "ViewPWD"
Dim objConn as new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\inetpub\wwwroot\applecart\pool03.mdb")
Dim sql As String = "SELECT Password FROM tblPlayerStats WHERE
Name = '" & e.Item.Cells(2).Text & "'"
objConn.Open()
Dim cmd as New OleDbCommand(sql, objConn)
Dim reader as OleDbDataReader = cmd.ExecuteReader

reader.Read()
ViewPasswordLabel.Text = "Name: " & e.Item.Cells(2).Text & "
Password: " & reader.Item("Password")
objConn.Close()

Case "ResetPWD"
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 Password = @Password WHERE Name = @Name", objConn)

cmd.Parameters.Add(new OleDbParameter("@Name",
e.Item.Cells(2).Text))
cmd.Parameters.Add(new OleDbParameter("@Password", "faatc1"))
objConn.Open()
cmd.ExecuteNonQuery()
objConn.Close
Case Else
' Do Nothing

End Select

BindData()
End Sub

Public Sub UserGrid_ItemCreated(sender As Object, e As
DataGridItemEventArgs)
Select Case e.Item.ItemType
Case ListItemType.Item, ListItemType.AlternatingItem,
ListItemType.EditItem

' Add confirmation to Delete button
Dim tblCell As TableCell
Dim btnDelete As LinkButton

tblCell = e.Item.Cells(1)
btnDelete = tblCell.Controls(0)
btnDelete.Attributes.Add("onclick", "return confirm('Are you
sure you want to delete this player?');")
End Select
End Sub

Public Sub AddUser_Click(sender As Object, e As EventArgs)
PlayerNameLabel.Visible = "True"
AddPlayerName.Visible = "True"
RealNameLabel.Visible = "True"
AddRealName.Visible = "True"
EmailLabel.Visible = "True"
AddEmail.Visible = "True"
PhoneLabel.Visible = "True"
AddPhone.Visible = "True"
AddPaid.Visible = "True"
AddNewUser.Visible = "True"
AddCancel.Visible = "True"
End Sub

Public Sub AddNewUser_Click(sender As Object, e As EventArgs)
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("INSERT INTO tblPlayerStats
(Name,Email,Password,RealName,Phone,Paid)
values(@Name,@Email,@Password,@RealName,@Phone,@Paid)", objConn)
Dim sPaid As String

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

cmd.Parameters.Add(new OleDbParameter("@Name", AddPlayerName.Text))
cmd.Parameters.Add(new OleDbParameter("@Email", AddEmail.Text))
cmd.Parameters.Add(new OleDbParameter("@Password", "faatc1"))
cmd.Parameters.Add(new OleDbParameter("@RealName", AddRealName.Text))
cmd.Parameters.Add(new OleDbParameter("@Phone", AddPhone.Text))
cmd.Parameters.Add(new OleDbParameter("@Paid", sPaid))

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

PlayerNameLabel.Visible = "False"
AddPlayerName.Visible = "False"
RealNameLabel.Visible = "False"
AddRealName.Visible = "False"
EmailLabel.Visible = "False"
AddEmail.Visible = "False"
PhoneLabel.Visible = "False"
AddPhone.Visible = "False"
AddPaid.Visible = "False"
AddNewUser.Visible = "False"
AddCancel.Visible = "False"

BindData()
End Sub

Public Sub AddCancel_Click(sender As Object, e As EventArgs)
PlayerNameLabel.Visible = "False"
AddPlayerName.Visible = "False"
RealNameLabel.Visible = "False"
AddRealName.Visible = "False"
EmailLabel.Visible = "False"
AddEmail.Visible = "False"
PhoneLabel.Visible = "False"
AddPhone.Visible = "False"
AddPaid.Visible = "False"
AddNewUser.Visible = "False"
AddCancel.Visible = "False"

' Reset text fields (for next time)
AddPlayerName.Text = ""
AddRealName.Text = ""
AddEmail.Text = ""
AddPhone.Text = ""
AddPaid.Checked = "False"
End Sub

</script>

<html>
<head>
</head>
<body>
<form method="post" runat="server">
<asp:label id="labelPaid" runat="server"/>
<asp:label id="labelEmail" runat="server"/>
<asp:button id="AddUser_Button" Text="Add New User" runat="server"
onClick="AddUser_Click" />
<asp:label id="ViewPasswordLabel" runat="server"/>
<table border=0>
<tr><td>
<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>
</td><td valign="top">
<table border=0 bgcolor=yellow>
<tr><td><asp:label id="PlayerNameLabel" Text="Player Name:"
visible="false" runat="server" /></td>
<tr><td><asp:textbox id="AddPlayerName" runat="server"
visible="false" /></td>
<tr><td><asp:label id="RealNameLabel" Text="Real Name:"
visible="false" runat="server" /></td>
<tr><td><asp:textbox id="AddRealName" runat="server" visible="false"
/></td>
<tr><td><asp:label id="EmailLabel" Text="EMail:" visible="false"
runat="server" /></td>
<tr><td><asp:textbox id="AddEmail" runat="server" visible="false"
/></td>
<tr><td><asp:label id="PhoneLabel" Text="Phone:" visible="false"
runat="server" /></td>
<tr><td><asp:textbox id="AddPhone" runat="server" visible="false"
/></td>
<tr><td><asp:checkbox id="AddPaid" Text="Paid?" runat="server"
visible="false" /></td>
<tr><td><asp:button id ="AddNewUser" Text="Add This User"
runat="server" visible="false" onClick="AddNewUser_Click" />
<td><asp:button id ="AddCancel" Text="Cancel" runat="server"
visible="false" onClick="AddCancel_Click"/>
</table>
</td></table>
</form>
</body>
</html>

 

[Jos]

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.

In the mean time, I have recreated the Yes/No field to be a Text
Field which stores "Yes" or "No", and the checkboxes update correctly
in both display mode and when I go into Edit mode. However, nothing
gets updated now (where the other 4 fields were OK earlier). I am
posting my code below if anyone could take a look, or have any ideas.

I always use True and False (without quotes)