Syntax error in UPDATE statement. ?

Farraj

hello, i have a Datalist and ACCESS DataBase, im using the update event in datalist to update two fields in my db. first one is subj the second is text. i get the Syntax error in update statement all the time while updating. the primary key is my ID field in the db, and it was set to be the same in the datalist. this is the C# code for the update method:

protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)
{
Label lblsubj = (Label)e.Item.FindControl("lblsubj");
string st = lblid.Text;
TextBox txtBoxId = (TextBox)e.Item.FindControl("txtBoxId");
TextBox txtBoxTitle = (TextBox)e.Item.FindControl("txtBoxTitle");
TextBox txtBoxFile = (TextBox)e.Item.FindControl("txtBoxFile");
TextBox txtBoxText = (TextBox)e.Item.FindControl("txtBoxText");
string queryStr = "UPDATE news SET subj='" + txtBoxTitle.Text + "', ";
queryStr += "text='" + txtBoxText.Text + "', ";
queryStr += "WHERE id='" + st + "'";

     string connectionStr = @"Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + Server.MapPath("~/App\_Data/db.mdb");
    OleDbConnection connectObj = new OleDbConnection(connectionStr);
    OleDbCommand cmd = new OleDbCommand(queryStr, connectObj);
    connectObj.Open();
    cmd.ExecuteNonQuery();
    connectObj.Close();
    this.DataList1.EditItemIndex = -1;
    BindTheDataList();
}

And this is my ASPX page code:

<asp:DataList ID="DataList1" runat="server" DataKeyField="subj" Height="96px"
OnCancelCommand="DataList1_CancelCommand" OnDeleteCommand="DataList1_DeleteCommand"
OnEditCommand="DataList1_EditCommand" OnUpdateCommand="DataList1_UpdateCommand"
Style="position: static" Width="641px">
<EditItemStyle BackColor="DarkGray" ForeColor="Black" />
<EditItemTemplate>
<table id="TABLE1" language="javascript" onclick="return TABLE1_onclick()">
<tr>
<td style="width: 90px">
Editing item:
<asp:Label ID="lblid" runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"id") %>'
Visible="False"></asp:Label></td>

Muhammad Gouda

Member 4429208 wrote:

"UPDATE news SET subj='aasda22', text='sdasd', WHERE id='4'"

Remove the comma before WHERE Concatenate a statement like this "UPDATE news SET subj='aasda22', text='sdasd' WHERE id='4'" Hope this helps

foreach(Minute m in MyLife) myExperience++;

Farraj

Thank you Mohammed, i've tried that too and it failed with a syntax error in update statement. any other ideas? thanks, Basil

MyDevBank

WHERE id='4' is your ID column type is a VAR/NVARcher type ? MyDevBank.com

Farraj

the ID column is an Auto-number.. ? :confused:

MyDevBank

you got WHERE ID='4' try WHERE ID=4

Farraj

still the same brother.

Lost User

This should do the trick;

UPDATE news
SET subj='aasda22'
,[text]='sdasd'
WHERE id='4'

"Text" should be avoided as a column-name; it's a reserved word.

I are Troll :suss:

MyDevBank

one of the fields name is wrong, Cap letter or something like that MyDevBank.com

Farraj

mydevbank thank you :) Eddy Vluggen!! thanks a lot brother, it worked. i never though of that lol!! TEXT is a reserved word. wth !! ive been dealing with this thing for a week and never though of changing columns names thanks anyway for all :)

Lost User

You're welcome :)

MyDevBank

:)

Muhammad Gouda

Deserve MVP :thumbsup:

foreach(Minute m in MyLife) myExperience++;

Lost User

Thanks :-O