3 Replies - 4274 Views - Last Post: 25 July 2006 - 04:15 PM Rate Topic: -----

#1 ezywrap  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 24-July 06

Updating Datagrid Question

Posted 25 July 2006 - 11:20 AM

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>

Is This A Good Question/Topic? 0
  • +

Replies To: Updating Datagrid Question

#2 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Updating Datagrid Question

Posted 25 July 2006 - 11:43 AM

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.
Was This Post Helpful? 0
  • +
  • -

#3 ezywrap  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 24-July 06

Re: Updating Datagrid Question

Posted 25 July 2006 - 02:15 PM

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
Was This Post Helpful? 0
  • +
  • -

#4 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Updating Datagrid Question

Posted 25 July 2006 - 04:15 PM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1