VB.NET School Assignment? Project Due Tomorrow? Chat LIVE With A Programming Expert!

Programming Help

Web Development



What's Here?

  • Members: 300,360
  • Replies: 825,824
  • Topics: 137,426
  • Snippets: 4,419
  • Tutorials: 1,147
  • Total Online: 1,700
  • Members: 106
  • Guests: 1,594

Welcome to Dream.In.Code
Become a VB.NET Expert!

Join 300,360 VB.NET Programmers for FREE! Get instant access to thousands of VB.NET experts, tutorials, code snippets, and more! There are 1,700 people online right now. Registration is fast and FREE... Join Now!




syntax error for UPDATE ms access

 

syntax error for UPDATE ms access, Syntax error in string in query expression
skyline85

24 Jun, 2009 - 08:41 PM
Post #1

New D.I.C Head
*

Joined: 24 Jun, 2009
Posts: 6


CODE
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

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
User is offlineProfile CardPM
+Quote Post


Bort

RE: Syntax Error For UPDATE Ms Access

25 Jun, 2009 - 03:05 AM
Post #2

VBort.NET
Group Icon

Joined: 18 Sep, 2006
Posts: 1,353



Thanked: 27 times
Dream Kudos: 350
My Contributions
You need to add & "'" to the end of your CommandText line.
User is offlineProfile CardPM
+Quote Post

skyline85

RE: Syntax Error For UPDATE Ms Access

25 Jun, 2009 - 06:06 PM
Post #3

New D.I.C Head
*

Joined: 24 Jun, 2009
Posts: 6
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

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()
User is offlineProfile CardPM
+Quote Post

Bort

RE: Syntax Error For UPDATE Ms Access

26 Jun, 2009 - 12:34 AM
Post #4

VBort.NET
Group Icon

Joined: 18 Sep, 2006
Posts: 1,353



Thanked: 27 times
Dream Kudos: 350
My Contributions
Sorry Sky, but you lost me. What do you mean by a 'false' messagebox appears?
User is offlineProfile CardPM
+Quote Post

skyline85

RE: Syntax Error For UPDATE Ms Access

28 Jun, 2009 - 07:37 PM
Post #5

New D.I.C Head
*

Joined: 24 Jun, 2009
Posts: 6
QUOTE(Bort @ 26 Jun, 2009 - 12:34 AM) *

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..
User is offlineProfile CardPM
+Quote Post

Bort

RE: Syntax Error For UPDATE Ms Access

29 Jun, 2009 - 12:18 AM
Post #6

VBort.NET
Group Icon

Joined: 18 Sep, 2006
Posts: 1,353



Thanked: 27 times
Dream Kudos: 350
My Contributions
I'm going to guess that it is this line:

CODE

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


Try just:

CODE

MsgBox("Record Updated.")
User is offlineProfile CardPM
+Quote Post

skyline85

RE: Syntax Error For UPDATE Ms Access

29 Jun, 2009 - 01:09 AM
Post #7

New D.I.C Head
*

Joined: 24 Jun, 2009
Posts: 6
QUOTE(Bort @ 29 Jun, 2009 - 12:18 AM) *

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

CODE

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


Try just:

CODE

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~ icon_up.gif

User is offlineProfile CardPM
+Quote Post

Bort

RE: Syntax Error For UPDATE Ms Access

29 Jun, 2009 - 01:59 AM
Post #8

VBort.NET
Group Icon

Joined: 18 Sep, 2006
Posts: 1,353



Thanked: 27 times
Dream Kudos: 350
My Contributions
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:

CODE

NameOfDataSet.AcceptChanges()


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

CODE

ucmd.Connection.Close()


HTH
Bort

This post has been edited by Bort: 29 Jun, 2009 - 02:01 AM
User is offlineProfile CardPM
+Quote Post

skyline85

RE: Syntax Error For UPDATE Ms Access

29 Jun, 2009 - 07:24 PM
Post #9

New D.I.C Head
*

Joined: 24 Jun, 2009
Posts: 6
CODE
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

CODE
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 Jun, 2009 - 07:25 PM
User is offlineProfile CardPM
+Quote Post

skyline85

RE: Syntax Error For UPDATE Ms Access

1 Jul, 2009 - 08:01 PM
Post #10

New D.I.C Head
*

Joined: 24 Jun, 2009
Posts: 6
anyone willing to help me?..i still work for it..

thanx in advance.
User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic

Time is now: 11/7/09 07:51PM
Invision Power Board v2.1.7 © 2009  IPS, Inc.
Licensed to: MediaGroup1

Live VB.NET Help!

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter Fan Us On Facebook

VB.NET Tutorials

Reference Sheets

VB.NET Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month