Programming Forums > Application Development > Visual Basic .NET

Intimidat0r · Nov 27th, 2005, 8:35 PM

I am using ASP.NET with VB as the language.

OK, I know I'm connecting to the database properly, as things like SELECT work. But my problem is UPDATE won't work. It says it's a syntax error:

(Toggle Plain Text)

Dim DateUpdated As String = Date.Now.ToString()
Dim objDateCmd As OleDbCommand = New OleDbCommand("UPDATE teacher_info SET date='" & DateUpdated & "' WHERE username='" & User & "'", objConn)

Anyone have any ideas why this is happening? Thanks.

melbolt · Nov 28th, 2005, 8:11 PM

hmm, looks alright from what I can see, can you post the entire code pertaining to the DB?

also, can you put the exact error message you get and at what line it occurs?

Intimidat0r · Nov 30th, 2005, 11:54 PM

error message:

Quote:

Syntax error in UPDATE statement.

exception

Quote:

System.Data.OleDb.OleDbException

relevant code:

(Toggle Plain Text)

Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(DBPath) + ";Jet OLEDB:Database Locking Mode=3")
objConn.Open()
Dim User As String
User = Session.Item("User") ' yes believe me this is set, it works in other places
'Dim objCmd As New OleDbCommand("UPDATE teacher_info SET bulliten='" & Me.txtBulliten.Text & "' WHERE username='" & Session.Item("Username") & "'", objConn)
Dim objCmd As New OleDbCommand("UPDATE teacher_info SET bulliten='" & Bulliten & "' WHERE username='" & User & "'", objConn)
Dim objDataReader As OleDbDataReader = objCmd.ExecuteReader()
Dim DateUpdated As String = Date.Now.ToString()
Dim objDateCmd As OleDbCommand = New OleDbCommand("UPDATE teacher_info SET date='" & DateUpdated & "' WHERE username='" & User & "'", objConn)
Dim objDataDateReader As OleDbDataReader = objDateCmd.ExecuteReader()

stack trace:

(Toggle Plain Text)

[OleDbException (0x80040e14): Syntax error in UPDATE statement.]
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) +65
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +181
   System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +307
   System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +77
   System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +188
   System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +123
   System.Data.OleDb.OleDbCommand.ExecuteReader() +26
   bulliten.cmdSubmitBulliten_Click(Object sender, EventArgs e) in C:\Documents and Settings\Intimidat0r\My Documents\Visual Studio 2005\WebSites\Sierra\controlpanel.aspx.vb:68
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +96
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +116
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +72
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3840

Rory · Dec 2nd, 2005, 2:39 PM

Just being pedantic, but for your date to be a valid SQL date on all locales, you should use:

(Toggle Plain Text)

(New System.data.SqlTypes.SqlDateTime(System.DateTime.Now())).ToSqlString

Rather than just Now.ToString().

I think the error might be that the field name, `date` isn't in quotes, as it's a reserved SQL keyword. Also bulletin is misspelled :p.

EDIT: What's up with the code tag tabbing?

Intimidat0r · Dec 5th, 2005, 9:52 PM

yes that was it, the quotes thing. thanks.

Nov 27th, 2005, 8:35 PM	#1
Intimidat0r Hobbyist Programmer Join Date: May 2005 Location: Don't know, but the padded walls are a nice touch. Posts: 126 Rep Power: 0	update command I am using ASP.NET with VB as the language. OK, I know I'm connecting to the database properly, as things like SELECT work. But my problem is UPDATE won't work. It says it's a syntax error: (Toggle Plain Text) Dim DateUpdated As String = Date.Now.ToString() Dim objDateCmd As OleDbCommand = New OleDbCommand("UPDATE teacher_info SET date='" & DateUpdated & "' WHERE username='" & User & "'", objConn) Dim DateUpdated As String = Date.Now.ToString() Dim objDateCmd As OleDbCommand = New OleDbCommand("UPDATE teacher_info SET date='" & DateUpdated & "' WHERE username='" & User & "'", objConn) Anyone have any ideas why this is happening? Thanks. __________________ Children in the dark cause accidents, and accidents in the dark cause children. http://www.ronincoders.org

Nov 28th, 2005, 8:11 PM	#2
melbolt Professional Programmer Join Date: Feb 2005 Location: PA, USA Posts: 253 Rep Power: 4	hmm, looks alright from what I can see, can you post the entire code pertaining to the DB? also, can you put the exact error message you get and at what line it occurs? __________________ I have never let my schooling interfere with my education. -Mark Twain- Xbox live gamertag: melbolt

Dec 2nd, 2005, 2:39 PM	#4
Rory Expert Programmer Join Date: Jan 2005 Location: London Posts: 542 Rep Power: 4	Just being pedantic, but for your date to be a valid SQL date on all locales, you should use: (Toggle Plain Text) (New System.data.SqlTypes.SqlDateTime(System.DateTime.Now())).ToSqlString (New System.data.SqlTypes.SqlDateTime(System.DateTime.Now())).ToSqlString Rather than just Now.ToString(). I think the error might be that the field name, `date` isn't in quotes, as it's a reserved SQL keyword. Also bulletin is misspelled :p. EDIT: What's up with the code tag tabbing?

Dec 5th, 2005, 9:52 PM	#5
Intimidat0r Hobbyist Programmer Join Date: May 2005 Location: Don't know, but the padded walls are a nice touch. Posts: 126 Rep Power: 0	yes that was it, the quotes thing. thanks. __________________ Children in the dark cause accidents, and accidents in the dark cause children. http://www.ronincoders.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Programming Forums > Application Development > Visual Basic .NET

update command