syntax error for UPDATE ms access

Syntax error in string in query expression

Page 1 of 1

9 Replies - 5033 Views - Last Post: 01 July 2009 - 09:01 PM Rate Topic: -----

#1 skyline85  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-June 09

syntax error for UPDATE ms access

Post icon  Posted 24 June 2009 - 09:41 PM

ucmd.CommandText = "UPDATE myDatabase level = '" & TextBox1.Text & "' ,area='" & TextBox2.Text & "' WHERE id='" & Label14.Text()

			ucmd.CommandType = CommandType.Text

			ucmd.Connection = cn2

			ucmd.ExecuteNonQuery()  <---------------------------has been highlight in yellow(error)

			MsgBox(TextBox1.Text = "Record updated.")

		Else

			MsgBox("Enter the required values:")



(sorry for my bad english)

hallo to all..

i am new comer for vb.net....i am doing some basic programming..so far i can do for SELECT query, however for UPDATE these some mistake arise..the error appear seem like "Syntax error in string in query expression 'id='10'"

thank you for ur time =)

full code

Private Sub calculateArea_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


		cn2.ConnectionString = strConnectionString2

		cn2.Open()

		If Label14.Text <> "" Then


			ucmd.CommandText = "UPDATE myDatabase SET level= '" & TextBox1.Text & "' ,area='" & TextBox2.Text & "' WHERE id='" & Label14.Text()


			'MsgBox(cmdUpdate.CommandText)

			ucmd.CommandType = CommandType.Text

			ucmd.Connection = cn2

			ucmd.ExecuteNonQuery()

			MsgBox(TextBox1.Text = "Record updated.")

		Else

			MsgBox("Enter the required values:")

		End If

		ucmd.Dispose()
		

	End Sub


Is This A Good Question/Topic? 0
  • +

Replies To: syntax error for UPDATE ms access

#2 Bort  Icon User is offline

  • Ill-informed Mongoloid
  • member icon

Reputation: 404
  • View blog
  • Posts: 2,983
  • Joined: 18-September 06

Re: syntax error for UPDATE ms access

Posted 25 June 2009 - 04:05 AM

You need to add & "'" to the end of your CommandText line.
Was This Post Helpful? 0
  • +
  • -

#3 skyline85  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-June 09

Re: syntax error for UPDATE ms access

Posted 25 June 2009 - 07:06 PM

thank you very much..

one thing, these already no error but then, when i click the button to run the update query, "False" massagebox appear...y?..where is it come from...the data is not updated..is there any suggestion to me...

thanx again for your time~ =)

this is my code

If LabelId.Text <> "" Then

			ucmd.CommandText = "UPDATE myDatabase SET level = '" & TextBox1.Text & "' ,area='" & TextBox2.Text & "' WHERE 'id'='" & LabelId.Text & "'"


			'MsgBox(cmdUpdate.CommandText)

			ucmd.CommandType = CommandType.Text

			ucmd.Connection = cn

			ucmd.ExecuteNonQuery()

			MsgBox(TextBox1.Text = "Record updated.")

		Else

			MsgBox("Enter the required values:")

		End If

		ucmd.Dispose()

Was This Post Helpful? 0
  • +
  • -

#4 Bort  Icon User is offline

  • Ill-informed Mongoloid
  • member icon

Reputation: 404
  • View blog
  • Posts: 2,983
  • Joined: 18-September 06

Re: syntax error for UPDATE ms access

Posted 26 June 2009 - 01:34 AM

Sorry Sky, but you lost me. What do you mean by a 'false' messagebox appears?
Was This Post Helpful? 0
  • +
  • -

#5 skyline85  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-June 09

Re: syntax error for UPDATE ms access

Posted 28 June 2009 - 08:37 PM

View PostBort, on 26 Jun, 2009 - 12:34 AM, said:

Sorry Sky, but you lost me. What do you mean by a 'false' messagebox appears?



sorry,

actually, my UPDATE query is not working..when i click the button, MsgBox("False") appear even i never put that msgbox..it should be MsgBox(TextBox1.Text = "Record updated.") or MsgBox("Enter the required values:")..

i just wondering where it come from and why the UPDATE query is not work..

thank again for your time..
Was This Post Helpful? 0
  • +
  • -

#6 Bort  Icon User is offline

  • Ill-informed Mongoloid
  • member icon

Reputation: 404
  • View blog
  • Posts: 2,983
  • Joined: 18-September 06

Re: syntax error for UPDATE ms access

Posted 29 June 2009 - 01:18 AM

I'm going to guess that it is this line:

MsgBox(TextBox1.Text = "Record updated.")



Try just:

MsgBox("Record Updated.")


Was This Post Helpful? 0
  • +
  • -

#7 skyline85  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-June 09

Re: syntax error for UPDATE ms access

Posted 29 June 2009 - 02:09 AM

View PostBort, on 29 Jun, 2009 - 12:18 AM, said:

I'm going to guess that it is this line:

MsgBox(TextBox1.Text = "Record updated.")



Try just:

MsgBox("Record Updated.")



MsgBox("Record Updated.") work! thanx u..

but...

database is not update with the value that i have inserted into the TextBox1.Text and TextBox2.Text..

is there other way to modified data by request input from user using textbox?

million thanxs~ :^:
Was This Post Helpful? 0
  • +
  • -

#8 Bort  Icon User is offline

  • Ill-informed Mongoloid
  • member icon

Reputation: 404
  • View blog
  • Posts: 2,983
  • Joined: 18-September 06

Re: syntax error for UPDATE ms access

Posted 29 June 2009 - 02:59 AM

I'm going to assume that you will be deploying the database with your application, correct? If this is the case, in the Properties window in VB (with the database file selected in Solution Explorer window), make sure the 'Copy to Output directory' option is set to 'Copy if Newer'. this will make sure any changes you make to data while debugging is saved to the database, otherwise the changes will get as far as the dataset, but not the database itself.

Also, after the ExecuteNonQuery line, try adding this:

NameOfDataSet.AcceptChanges()



EDIT:
Oh, one other thing, don't forget to close the connection to the database with...

ucmd.Connection.Close()



HTH
Bort

This post has been edited by Bort: 29 June 2009 - 03:01 AM

Was This Post Helpful? 0
  • +
  • -

#9 skyline85  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-June 09

Re: syntax error for UPDATE ms access

Posted 29 June 2009 - 08:24 PM

Private Sub bindingNavigatorButton_save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button_save.Click

		'Try
		'Me.Validate()
		'Me.myDatabaseBindingSource.EndEdit()
		'Me.myDatabaseTableAdapter.Update(Me.myDatabaseDataSet.myDatabase)
		'MsgBox("Update successful")

		'Catch ex As Exception
		'MsgBox("Update failed")
		'End Try

		If TextBox1.Text <> "" And TextBox2.Text <> "" Then


			'ucmd.CommandText = "UPDATE myDatabase SET area = '" & TextBox2.Text & "WHERE id = " & Label14.Text & ""


			ucmd.CommandText = "UPDATE myDatabase SET level = '" & TextBox1.Text & "' ,area='" & TextBox2.Text & "' WHERE 'id'='" & Label14.Text & "'"

			ucmd.CommandType = CommandType.Text

			ucmd.Connection = cn2

			ucmd.ExecuteNonQuery()

			myDatabaseDataSet.AcceptChanges()

			ucmd.Connection.Close()


			MsgBox("Record Updated.")

		Else

			MsgBox("Enter the required values:")

		End If

		'ucmd.Dispose()



	End Sub


also, i have change 'Copy to Output directory' option to 'Copy if Newer'

am i doing it right?

ermm..still not work...

actually, i need to create a form that retrieve data that i have already display at the main form..i did it

Public Sub New(ByVal nameText As String, ByVal blockText As String, ByVal areaText As String, ByVal levelText As String, ByVal totalText As String, ByVal idText As String)

		InitializeComponent()

		Label9.Text = nameText
		Label10.Text = blockText
		Label11.Text = areaText
		Label12.Text = levelText
		Label13.Text = totalText
		Label14.Text = idText
		'TextBox1.Text = levelText
		'TextBox2.Text = areaText


then, i open new connection from that second form to do UPDATE..

just because it cant...so i try to do it at the main form only..but still not work...huhuuu~

This post has been edited by skyline85: 29 June 2009 - 08:25 PM

Was This Post Helpful? 0
  • +
  • -

#10 skyline85  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-June 09

Re: syntax error for UPDATE ms access

Posted 01 July 2009 - 09:01 PM

anyone willing to help me?..i still work for it..

thanx in advance.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1