[ezywrap]

I'm having a problem updating to my Acess 2003 DB. In the table Records

I keep getting this error

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

Any suggestions? I've been working on this all morning.

Thanks


In the DB table Records

RecordID = Autonumber(key), UserID = Number, PatientID = Number, SequenceNum = Number, DayHours = Number, NightHours= Number , PainDay = Number , PainNight = Number, Date = Text Input mask 99/99/0000;0;_, Comments = Memo

Sub DataUpdate(sender as Object, e as DataGridCommandEventArgs)

Dim strSequence, strDayHours, strNightHours, strPainDay, strPainNight, strDate, strComments as String
Dim intID as String

idLook=Request.QueryString("UserID")
idView=Request.QueryString("PatientID")

strSequence = Ctype(e.Item.Cells(1).Controls(1), Textbox).text
strDate = Ctype(e.Item.Cells(2).Controls(1), Textbox).text
strDayHours = Ctype(e.Item.Cells(3).Controls(1), Textbox).text
strNightHours = Ctype(e.Item.Cells(4).Controls(1), Textbox).text
strPainDay = Ctype(e.Item.Cells(5).Controls(1), Textbox).text
strPainNight = Ctype(e.Item.Cells(6).Controls(1), Textbox).text
strComments = Ctype(e.Item.Cells(7).Controls(1), Textbox).text


intID = EditRecords.DataKeys(e.Item.ItemIndex)

Dim strSQL as String
strSQL = "UPDATE Records SET UserID='" & idLook & _
"', PatientID='" & idView & _
"', SequenceNum='" & strSequence & _
"', DayHours='" & strDayHours & _
"', NightHours='" & strNightHours & _
"', PainDay='" & strPainDay & _
"', PainNight='" & strPainNight & _
"', Date='" & strDate & _
"', Comments='" & strComments & _
"' WHERE RecordID=" & intID & ";"

ExecuteSQLStatement(strSQL)
Response.Write(strSQL)
EditRecords.EditItemIndex = -1
BindDataGrid()

End Sub


<asp:datagrid id="EditRecords" runat="server"
HeaderStyle-BackColor="blue"
HeaderStyle-ForeColor="#ffffff"
HeaderStyle-Horizontalalign="Center"
CellPadding="5"
Font-Size="10pt"
Text-Align="center"
DataKeyField="RecordID"
OnEditCommand="DataEdit"
OnUpdateCommand="DataUpdate"
OnCancelCommand="DataCancel"
AutoGenerateColumns="false"

BorderColor="black">
<columns>
<asp:boundcolumn HeaderText="Record Number"
DataField="RecordID" ReadOnly="true" />
<asp:templatecolumn HeaderText="Sequence Number">
<ItemTemplate>
<center><asp:label Text='<%# Container.DataItem("Sequence")%>'
runat="server"></asp:label></center>
</ItemTemplate>
<EditItemTemplate>
<center><asp:textbox ID="Sequence" width="90"
runat="server" Text='<%# Container.DataItem("Sequence")%>' /></center>
</EditItemTemplate>
</asp:templatecolumn>
<asp:templatecolumn HeaderText="Date">
<ItemTemplate>
<center><asp:label Text='<%# Container.DataItem("Date")%>'
runat="server"></asp:label></center>
</ItemTemplate>
<EditItemTemplate>
<center><asp:textbox ID="Date" width="90"
runat="server" Text='<%# Container.DataItem("Date")%>' /></center>
</EditItemTemplate>
</asp:templatecolumn>
<asp:templatecolumn HeaderText="Hours per Day">
<ItemTemplate>
<center><asp:label Text='<%# Container.DataItem("Hours per Day")%>'
runat="server"></asp:label></center>
</ItemTemplate>
<EditItemTemplate>
<center><asp:textbox ID="HoursDay" width="30"
runat="server" Text='<%# Container.DataItem("Hours per Day")%>' /></center>
</EditItemTemplate>
</asp:templatecolumn>
<asp:templatecolumn HeaderText="Hours per Night">
<ItemTemplate>
<center><asp:label Text='<%# Container.DataItem("Hours per Night")%>'
runat="server"></asp:label></center>
</ItemTemplate>
<EditItemTemplate>
<center><asp:textbox ID="HoursNight" width="30"
runat="server" Text='<%# Container.DataItem("Hours per Night")%>' /></center>
</EditItemTemplate>
</asp:templatecolumn>
<asp:templatecolumn HeaderText="Day Pain Rate">
<ItemTemplate>
<center><asp:label Text='<%# Container.DataItem("Day Pain Rate")%>'
runat="server"></asp:label></center>
</ItemTemplate>
<EditItemTemplate>
<center><asp:textbox ID="DayRate" width="30"
runat="server" Text='<%# Container.DataItem("Day Pain Rate")%>' /></center>
</EditItemTemplate>
</asp:templatecolumn>
<asp:templatecolumn HeaderText="Night Pain Rate">
<ItemTemplate>
<center><asp:label Text='<%# Container.DataItem("Night Pain Rate")%>'
runat="server"></asp:label></center>
</ItemTemplate>
<EditItemTemplate>
<center><asp:textbox ID="NightRate" width="30"
runat="server" Text='<%# Container.DataItem("Night Pain Rate")%>' /></center>
</EditItemTemplate>
</asp:templatecolumn>
<asp:templatecolumn HeaderText="Comments">
<ItemTemplate>
<asp:label Text='<%# Container.DataItem("Comments")%>'
runat="server"></asp:label>
</ItemTemplate>
<EditItemTemplate>
<asp:textbox ID="Comments" width="400"
runat="server" Text='<%# Container.DataItem("Comments")%>' />
</EditItemTemplate>
</asp:templatecolumn>
<asp:templatecolumn>
<ItemTemplate>
<asp:ImageButton CommandName="Edit" AlternateText="Edit"
Text="Edit" ImageUrl="edit2.gif" runat="server" />
</ItemTemplate>
<EditItemTemplate>
<asp:ImageButton CommandName="Update" AlternateText="Update"
Text="Update" ImageUrl="boxcheck.gif" runat="server"/>
<asp:ImageButton CommandName="Cancel" AlternateText="Cancel"
Text="Cancel"
ImageUrl="boxuncheck.gif" runat="server"/>
</EditItemTemplate>
</asp:templatecolumn>

[Amadeus]

Are any oif your fileds numerical in the database? I ask becasue you seem to be entering string (or text) values for all of them...if the field type is numeric, this can throw an error.

Secondly, you may not require the semi colon at the end of the update string.

[ezywrap]

Would you happen to know the syntax of using numeric? Do I convert the variables to numeric? or do I change the variable types in the database?

Thanks for your help

[Amadeus]

The syntax for numbers into a numeric filed would be the same as you've got with the RecordID in the WHERE clause...essentially, no single quotes.