Im just wondering that I cannot update my database throught datagrid datalist control, and the only thing works is that I can delete, cancel, edit but when I clicked update link there's an error that says: "Syntax error in UPDATE statement."
I wonder what's wrong with my code and how to correct this one?
Below are my codes:
Expand|Select|Wrap|Line Numbers
- OleDbConnection objConn = new OleDbConnection(
- "Provider=Microsoft.Jet.OleDb.4.0;" +
- "Data Source=D:\\ASPX\\" +
- "data-x.mdb");
- OleDbCommand objCmd;
- OleDbDataReader objRdr;
- String strCmd;
- void Page_Load() {
- if (!IsPostBack) {
- BindData();
- }
- }
- void BindData() {
- objConn.Open();
- if (Request.QueryString["id"] != null) {
- objCmd = new OleDbCommand(
- "SELECT * FROM Employees WHERE EmployeeID=" +
- Request.QueryString["id"], objConn);
- objRdr = objCmd.ExecuteReader();
- dgAddressBookDetails.DataSource = objRdr;
- dgAddressBookDetails.DataBind();
- } else {
- objCmd = new OleDbCommand("SELECT * FROM Employees", objConn);
- objRdr = objCmd.ExecuteReader();
- dgAddressBook.DataSource = objRdr;
- dgAddressBook.DataBind();
- }
- objRdr.Close();
- objConn.Close();
- }
- void dg_Edit(Object s, DataGridCommandEventArgs e) {
- dgAddressBook.EditItemIndex = e.Item.ItemIndex;
- BindData();
- }
- void dg_Cancel(Object s, DataGridCommandEventArgs e) {
- dgAddressBook.EditItemIndex = -1;
- BindData();
- }
- void dg_Update(Object s, DataGridCommandEventArgs e) {
- int intEmployeeID;
- String strName, strExtension;
- intEmployeeID = (int)dgAddressBook.DataKeys[e.Item.ItemIndex];
- strName = ((TextBox)e.Item.FindControl("txtName")).Text;
- strCmd = "UPDATE Employees SET Name=@Name, " +
- "WHERE EmployeeID=@EmployeeID";
- objCmd = new OleDbCommand(strCmd, objConn);
- objCmd.Parameters.Add("@Name", strName);
- objCmd.Parameters.Add("@EmployeeID", intEmployeeID);
- objConn.Open();
- objCmd.ExecuteNonQuery();
- objConn.Close();
- dgAddressBook.EditItemIndex = -1;
- BindData();
- }
- void dg_Delete(Object s, DataGridCommandEventArgs e) {
- int intEmployeeID;
- intEmployeeID = (int)dgAddressBook.DataKeys[e.Item.ItemIndex];
- strCmd = "DELETE FROM Employees WHERE EmployeeID=@EmployeeID";
- objCmd = new OleDbCommand(strCmd, objConn);
- objCmd.Parameters.Add("@EmployeeID", intEmployeeID);
- objConn.Open();
- objCmd.ExecuteNonQuery();
- objConn.Close();
- dgAddressBook.EditItemIndex = -1;
- BindData();
- }
Expand|Select|Wrap|Line Numbers
- <asp:DataGrid id="dgAddressBook" runat="server" AutoGenerateColumns="False" CellPadding="4" GridLines="None" OnEditCommand="dg_Edit" OnCancelCommand="dg_Cancel" OnUpdateCommand="dg_Update" OnDeleteCommand="dg_Delete" DataKeyField="EmployeeID">
- <ItemStyle Font-Names="Arial" Font-Size="Smaller" ForeColor="#000000" />
- <HeaderStyle Font-Names="Arial" Font-Size="Smaller" Font-Bold="true" ForeColor="#FFFFFF" BackColor="#003366" />
- <AlternatingItemStyle Font-Names="Arial" Font-Size="Smaller" BackColor="#CCCCCC" />
- <Columns>
- <asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" />
- <asp:TemplateColumn>
- <HeaderTemplate>Name</HeaderTemplate>
- <ItemTemplate><%# DataBinder.Eval(Container.DataItem, "Name") %></ItemTemplate>
- <EditItemTemplate>
- <asp:TextBox ID="txtName" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "Name") %>' />
- <asp:RequiredFieldValidator ID="rfvName" Display="Dynamic" ErrorMessage="Name is required!" ControlToValidate="txtName" runat="server" />
- </EditItemTemplate>
- </asp:TemplateColumn>
- <asp:BoundColumn DataField="Extension" HeaderText="Extension" ReadOnly="true" />
- <asp:ButtonColumn ButtonType="LinkButton" Text="Delete" CommandName="Delete" />
- </Columns>
- </asp:DataGrid>