Help needed with SQL UPDATE statement

I am having a heck of a time getting an UPDATE statement to work. Can anyone point out what it is I'm doing wrong? Here is my statement.....

strSQL = "UPDATE tbl-Pnumber_list SET Project_Title = 'success' WHERE ID = @.IDParam"

Thanks!

Eugh

what is the error message?|||The statement looks correct|||Are you sure the problem is with your SQL UPDATE statement? It's obvious that it's part of your program, so I'm wondering if the rest of your code is where the problem lies. Post more codes to see if anyone can find the cause.|||

Sorry to post and run last Friday. Thanks for taking a look at it. I've never written anything in .net for web applications before. I installed IIS and VB.net myself so if the SQL statement looks correct I'm wondering if I have a permissions issue. I gave the ASPNET read/write permissions by right clicking on the DB in windows explorer, properties, security tab, add, (machinename\ASPNET). The DB is in a subfolder under wwwroot. Did I do something wrong there perhaps?

I'm using VB.Net 2003 Standard Edition w/ and Access 2003 DB. My exact error message is...

Server Error in '/Proposal_List' Application.

---

Syntax error in UPDATE statement.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.OleDb.OleDbException: Syntax error in UPDATE statement.

Source Error:

Line 112: objConn.Open()Line 113:Line 114: objCmd.ExecuteNonQuery()Line 115: objConn.Close()Line 116:

Source File:c:\inetpub\wwwroot\Proposal_List\Pnumberlist.aspx.vb Line:114

Stack Trace:

[OleDbException (0x80040e14): Syntax error in UPDATE statement.] System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) System.Data.OleDb.OleDbCommand.ExecuteNonQuery() Proposal_List.Pnumberlist.dgPnum_UpdateRow(Object sender, DataGridCommandEventArgs e) in c:\inetpub\wwwroot\Proposal_List\Pnumberlist.aspx.vb:114 System.Web.UI.WebControls.DataGrid.OnUpdateCommand(DataGridCommandEventArgs e) System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source, EventArgs e) System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source, EventArgs e) System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) System.Web.UI.Page.ProcessRequestMain()

HERE IS MY COMPLETE CODE

VB............................................................................................................

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

IfNot Page.IsPostBackThen

BindData()

EndIf

EndSub

Sub BindData()

'Create a connection

Const strConnStringAsString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Inetpub\wwwroot\Proposal_List\Proposal_List.mdb;User Id=admin;Password=;"

Dim objConnAsNew OleDbConnection(strConnString)

'create a command object for the query

Const strSQLAsString = "SELECT * FROM [tbl-Pnumber_list]"

Dim objCMDAsNew OleDbCommand(strSQL, objConn)

'create a dataadapter

Dim objDAAsNew OleDbDataAdapter

objDA.SelectCommand = objCMD

'Populate a dataset and close the connection

Dim objDSAsNew DataSet

objDA.Fill(objDS)

objConn.Close()

'specify the data source and bind the data

dgPnum.DataSource = objDS

dgPnum.DataBind()

EndSub

Sub dgPnum_EditRow(ByVal senderAsObject,ByVal eAs DataGridCommandEventArgs)

dgPnum.EditItemIndex = e.Item.ItemIndex

BindData()

EndSub

Sub dgPnum_UpdateRow(ByVal senderAsObject,ByVal eAs DataGridCommandEventArgs)

'get info from columns

Dim PMTextBoxAs TextBox = e.Item.Cells(3).Controls(0)

Dim Project_TitleTextBoxAs TextBox = e.Item.Cells(2).Controls(0)

Dim iIDAsInteger = dgPnum.DataKeys(e.Item.ItemIndex)

'update the database

Dim strSQLAsString

'strSQL = "UPDATE tbl-Pnumber_list SET Project_Manager = @.PMParam," & _

'"Project_Title = @.Project_TitleParam" & _

'"WHERE ID = @.IDParam"

strSQL = "UPDATE tbl-Pnumber_list SET Project_Title = 'success' WHERE ID = @.IDParam"

Const strConnStringAsString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Inetpub\wwwroot\Proposal_List\Proposal_List.mdb;User Id=admin;Password=;"

Dim objConnAsNew OleDbConnection(strConnString)

Dim objCmdAsNew OleDbCommand(strSQL, objConn)

Dim PMParamAsNew OleDbParameter("@.NameParam", OleDbType.VarChar, 200)

Dim Project_TitleParamAsNew OleDbParameter("@.CommentParam", OleDbType.VarChar, 254)

Dim IDParamAsNew OleDbParameter("@.IDParam", OleDbType.Integer, 4)

PMParam.Value = PMTextBox.Text

objCmd.Parameters.Add(PMParam)

Project_TitleParam.Value = Project_TitleTextBox.Text

objCmd.Parameters.Add(Project_TitleParam)

IDParam.Value = iID

objCmd.Parameters.Add(IDParam)

'Issue the SQL command

objConn.Open()

objCmd.ExecuteNonQuery()

objConn.Close()

dgPnum.EditItemIndex = -1

BindData()

EndSub

Sub dgpnum_CancelRow(ByVal senderAsObject,ByVal eAs DataGridCommandEventArgs)

dgPnum.EditItemIndex = -1

BindData()

EndSub

HTML....................................................................................................................................

<form id="Form1" method="post" runat="server">
<asp:datagrid id="dgPnum" style="Z-INDEX: 101; LEFT: 24px; POSITION: absolute; TOP: 32px" runat="server"
AutoGenerateColumns="False" CellPadding="3" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px"
BackColor="White" OnCancelCommand="dgPnum_CancelRow" OnUpdateCommand="dgPnum_UpdateRow" OneditCommand="dgPnum_EditRow"
DataKeyField="ID">
<FooterStyle ForeColor="#000066" BackColor="White"></FooterStyle>
<SelectedItemStyle Font-Bold="True" ForeColor="White" BackColor="#669999"></SelectedItemStyle>
<ItemStyle ForeColor="#000066"></ItemStyle>
<HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#006699"></HeaderStyle>
<Columns>
<asp:EditCommandColumn ButtonType="PushButton" HeaderText="Edit" Edittext="Edit" UpdateText="Update" CancelText="Cancel" />
<asp:BoundColumn DataField="ID" HeaderText="ID" Visible="false"></asp:BoundColumn>
<asp:BoundColumn DataField="Proposal_Year" HeaderText="Proposal Year"></asp:BoundColumn>
<asp:BoundColumn DataField="Number" HeaderText="Number"></asp:BoundColumn>
<asp:BoundColumn DataField="Client" HeaderText="Client"></asp:BoundColumn>
<asp:BoundColumn DataField="Project_Title" HeaderText="Project"></asp:BoundColumn>
<asp:BoundColumn DataField="Project_Manager" HeaderText="PM"></asp:BoundColumn>
<asp:BoundColumn DataField="Office" HeaderText="Office"></asp:BoundColumn>
<asp:BoundColumn DataField="Discipline" HeaderText="Discipline"></asp:BoundColumn>
<asp:BoundColumn DataField="F/Q/P" HeaderText="F/Q/P"></asp:BoundColumn>
<asp:BoundColumn DataField="Start_Date" HeaderText="Start Date"></asp:BoundColumn>
<asp:BoundColumn DataField="End_Date" HeaderText="End Date"></asp:BoundColumn>
<asp:BoundColumn DataField="Submitted" HeaderText="Submitted"></asp:BoundColumn>
<asp:BoundColumn DataField="Folder" HeaderText="Folder"></asp:BoundColumn>
<asp:BoundColumn DataField="Job_Number" HeaderText="Job Number"></asp:BoundColumn>
</Columns>
<PagerStyle HorizontalAlign="Left" ForeColor="#000066" BackColor="White" Mode="NumericPages"></PagerStyle>
</asp:datagrid></form>

Thanks again!

Cheers

Eugh

|||

OleDB doesn't understand the @. parameters, change them to ? instead.

WHERE something=?

|||

I'm not familiar with the way you're providing the UPDATE parameters in your code. Also, I use C#, so the syntax may be different. Following is a sample code from what I used to do in ASP.NET 1.x. Maybe you can get some ideas from it and translate it to your code. Just keep in mind that with OLE-db (Access), the order of parameter specified in your UPDATE statement is all that matters, not the actual placeholder specified:

OleDbCommand myCmd =new OleDbCommand();

myCmd.Connection = oleDbConnection1;

myCmd.CommandText ="UPDATE TableName SET Date_Upd = ?, Name_Last = ? WHERE Rec_ID = ?";

myCmd.Parameters.Add("Date_Upd", OleDbType.Date).Value =DateTime.Now;

myCmd.Parameters.Add("Name_Last", OleDbType.Char).Value = Name_Last.Text;

myCmd.Parameters.Add("Rec_ID", OleDbType.Integer).Value = Session["REC_ID"].ToString();

myCmd.CommandType =CommandType.Text;

myCmd.Connection.Open();

myCmd.ExecuteNonQuery();

myCmd.Connection.Close();

|||

Good to know that Access doesnt understand the @. parameters, I would have been stuck there for a while. Just to see my DB update I changfed the UPDATE statement to....

strSQL = "UPDATE tbl-Pnumber_list SET Project_Title = 'success' WHERE ID = 1"

I didnt change anything else, should that have worked? It didnt.

Also, thanks for the example it helps clear some things up for me.

|||Looks okay, assuming your ID is a numeric field. Also, I'm not 100% sure about using single-quote versus double-quote when you specify a text within Access SQL statement. You can probably play with that to verify.|||Try the same statement again with tbl-Pnumber_list in []'s, `'s, or "'s (I'm not sure how Access quotes it's identifiers). The - causes some RDMSs a fit because, well... It's hard to know you aren't trying to say something strange like... tbl (minus) Pnumber_list, which well, doesn't make a lot of sense.|||

Yeah, wadda know... putting [] around tbl-Pnumber_list worked like a champ. Man it feels great to get outta this rut, but it sure hits ya in the gut knowing how simple the solution was. I'll just make sure I wont tell my boss what it was

Thanks to both of you!

Remember me, because I'm sure I'll be begging for some help again soon.

Cheers!

Eugh