Web Wiz - Green Windows Web Hosting

  New Posts New Posts RSS Feed - Update Statement Problem.
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Update Statement Problem.

 Post Reply Post Reply
Author
davidshq View Drop Down
Senior Member
Senior Member


Joined: 29 July 2003
Location: United States
Status: Offline
Points: 299
Post Options Post Options   Thanks (0) Thanks(0)   Quote davidshq Quote  Post ReplyReply Direct Link To This Post Topic: Update Statement Problem.
    Posted: 15 July 2004 at 10:37pm

After a several day break from programming (also known as "returning to sanity") I have reentered the world of ASP.NET programming (also known as "driving oneself insane"), and have written almost another entire file before running into an error I can't fix...
What I am trying to do is take an entry from a database, read it into a form, allow the user to edit the info., then update the database. The reading into works fine, the updating the edited data does not. Here is the code from my code-behind:
Imports System
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports System.Data
Imports System.Data.OleDb

Public Class EditEvent
  Inherits Page
  Protected lblID as Label
  Protected txtMonth as TextBox
  Protected txtDay as TextBox
  Protected txtYear as TextBox
  Protected txtEvent as TextBox
  Protected withEvents cmdSubmit as Button

Private Sub Page_Load(Sender as Object, e as eventargs) Handles MyBase.Load
  Dim con as New OleDBConnection()
  con.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ASP.NET\Databases\TodayInHistory\civilwar.mdb"
  con.Open
  Dim cmd as New OleDBCommand()
  cmd.Connection=con
  cmd.CommandText="Select * from tblToday WHERE ID=" & Request.QueryString("ID")
  Dim reader as OleDBDataReader
  reader = cmd.ExecuteReader()
  reader.Read()
  lblID.Text= reader("ID")
  txtMonth.Text= reader("mMonth")
  txtDay.Text=reader("mDay")
  txtYear.Text=reader("mYear")
  txtEvent.Text=reader("mEvent")
  reader.Close()
  con.Close()
End Sub

Private Sub ButtonClick(Sender as Object, e as EventArgs) Handles cmdSubmit.Click
  Dim UpdateDB as String
  UpdateDB="UPDATE tblToday SET"
  UpdateDB &="ID='" & lblID.Text & "',"
  UpdateDB &="mMonth='" & txtMonth.Text & "',"
  UpdateDB &="mDay='" & txtDay.Text & "',"
  UpdateDB &="mYear='" & txtYear.Text & "',"
  UpdateDB &="mEvent='" & txtEvent.Text & "'"
  UpdateDB &="WHERE ID='" & lblID.Text & "'"
  Dim con as New OleDBConnection()
  con.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ASP.NET\Databases\TodayInHistory\civilwar.mdb"
  con.Open()
  Dim cmd as New OleDBCommand()
  cmd.CommandText=UpdateDB
  cmd.Connection=con
  Dim Updated as Integer
  Updated=cmd.ExecuteNonQuery
  lblID.Text=Updated.ToString()
  con.close()
End Sub
End Class
Here is the error:
System.Data.OleDb.OleDbException: Syntax error in UPDATE statement.
Here is the stack trace:

[OleDbException (0x80040e14): Syntax error in UPDATE 
statement.] System.Data.OleDb.OleDbCommand.ExecuteCommandText
ErrorHandling(Int32 hr) +41 System.Data.OleDb.OleDbCommand.ExecuteCommandText
ForSingleResult(tagDBPARAMS dbParams, Object& execute
Result) +122 System.Data.OleDb.OleDbCommand.ExecuteCommandText
(Object& executeResult) +92 System.Data.OleDb.OleDbCommand.ExecuteCommand
(CommandBehavior behavior, Object& executeResult) +65 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal
(CommandBehavior behavior, String method) +112 System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66 EditEvent.ButtonClick(Object Sender, EventArgs e) +346 System.Web.UI.WebControls.Button.OnClick(EventArgs e)
+83 System.Web.UI.WebControls.Button.System.Web.UI.
IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent
(IPostBackEventHandler sourceControl, String
eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent
(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277 Any ideas are appreciated.
Back to Top
Leeb65 View Drop Down
Groupie
Groupie


Joined: 05 December 2003
Location: Germany
Status: Offline
Points: 62
Post Options Post Options   Thanks (0) Thanks(0)   Quote Leeb65 Quote  Post ReplyReply Direct Link To This Post Posted: 16 July 2004 at 2:22am

  Dim UpdateDB as String
  UpdateDB="UPDATE tblToday SET "
  UpdateDB &="ID='" & lblID.Text & "', "
  UpdateDB &="mMonth='" & txtMonth.Text & "', "
  UpdateDB &="mDay='" & txtDay.Text & "', "
  UpdateDB &="mYear='" & txtYear.Text & "', "
  UpdateDB &="mEvent='" & txtEvent.Text & "' "
  UpdateDB &="WHERE ID='" & lblID.Text & "'"

Try that with the spaces.

 

Lee


Back to Top
Mart View Drop Down
Senior Member
Senior Member
Avatar

Joined: 30 November 2002
Status: Offline
Points: 2304
Post Options Post Options   Thanks (0) Thanks(0)   Quote Mart Quote  Post ReplyReply Direct Link To This Post Posted: 16 July 2004 at 2:49am

Change the Page_Load sub to this:

Private Sub Page_Load(Sender as Object, e as eventargs) Handles MyBase.Load
  If Not Page.IsPostBack Then
  Dim con as New OleDBConnection()
  con.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ASP.NET\Databases\TodayInHistory\civilwar.mdb"
  con.Open
  Dim cmd as New OleDBCommand()
  cmd.Connection=con
  cmd.CommandText="Select * from tblToday WHERE ID=" & Request.QueryString("ID")
  Dim reader as OleDBDataReader
  reader = cmd.ExecuteReader()
  reader.Read()
  lblID.Text= reader("ID")
  txtMonth.Text= reader("mMonth")
  txtDay.Text=reader("mDay")
  txtYear.Text=reader("mYear")
  txtEvent.Text=reader("mEvent")
  reader.Close()
  con.Close()
  End If
End Sub

Otherwise when you click the update button it will fill the text boxes with their old values from the database then update.

Back to Top
Leeb65 View Drop Down
Groupie
Groupie


Joined: 05 December 2003
Location: Germany
Status: Offline
Points: 62
Post Options Post Options   Thanks (0) Thanks(0)   Quote Leeb65 Quote  Post ReplyReply Direct Link To This Post Posted: 16 July 2004 at 2:52am

Doh , I didn't see that!!

 

Lee


Back to Top
davidshq View Drop Down
Senior Member
Senior Member


Joined: 29 July 2003
Location: United States
Status: Offline
Points: 299
Post Options Post Options   Thanks (0) Thanks(0)   Quote davidshq Quote  Post ReplyReply Direct Link To This Post Posted: 16 July 2004 at 9:06am
Thanks for the advice guys. I added the If Not Page.IsPostBack clause and also placed spaces after the commas...But it still didn't work, so I commented out the first line which told it to update ID b/c ID is actually never changed...But this didn't seem to change anything so then I placed a space after the UPDATE...SET clause and finally it....threw a different error! Now it is telling me "Data type mismatch in criteria expression."
Here's the stack trace:
[OleDbException (0x80040e07): Data type mismatch in criteria expression.]
   System.Data.OleDb.OleDbCommand.
ExecuteCommandTextErrorHandling(Int32 hr) +41
   System.Data.OleDb.OleDbCommand.
ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +122
   System.Data.OleDb.OleDbCommand.ExecuteCommandText
(Object& executeResult) +92
   System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavio r behavior, Object& executeResult) +65
   System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(Command Behavior behavior, String method) +112
   System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66
   EditEvent.ButtonClick(Object Sender, EventArgs e) +308
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +83
   System.Web.UI.WebControls.Button.System.Web.UI.
IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1277
Thanks again for the help.
Back to Top
Leeb65 View Drop Down
Groupie
Groupie


Joined: 05 December 2003
Location: Germany
Status: Offline
Points: 62
Post Options Post Options   Thanks (0) Thanks(0)   Quote Leeb65 Quote  Post ReplyReply Direct Link To This Post Posted: 16 July 2004 at 9:54am

UpdateDB="UPDATE tblToday SET"
  UpdateDB &="ID='" & lblID.Text & "',"
  UpdateDB &="mMonth=" & txtMonth.Text & ", "
  UpdateDB &="mDay=" & txtDay.Text & ", "
  UpdateDB &="mYear=" & txtYear.Text & ", "
  UpdateDB &="mEvent='" & txtEvent.Text & "' "
  UpdateDB &="WHERE ID=" & lblID.Text

an integer field must not be ID='1', but should be ID=1.

 

Lee


Back to Top
davidshq View Drop Down
Senior Member
Senior Member


Joined: 29 July 2003
Location: United States
Status: Offline
Points: 299
Post Options Post Options   Thanks (0) Thanks(0)   Quote davidshq Quote  Post ReplyReply Direct Link To This Post Posted: 16 July 2004 at 10:08am

Thanks Lee, that worked beautifully. I'm sure I'll be back with more questions though...This ASP.NET script is far from finished. Its a Today in History Script and so far I have it so that it:
1. Displays all Events.
2. Displays all Events occurring on a given day.
3. Can edit current events.
I still have to add:
1. The ability to add new events.
2. The ability to delete events.
3. Password protected administration.
Respectfully,
David.

Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.


Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.