Click here to Skip to main content
14,422,431 members
Rate this:
Please Sign up or sign in to vote.
See more:
i have to update from vb.net to ms access database but when update btn click its says record saved but immediately say
Syntax error in UPDATE statement


What I have tried:

pro = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:/Praise/Praise/bin/Debug/Database.accdb"
        connString = pro
        myConnection.ConnectionString = connString
        myConnection.Open()
        command = "Update SONGS Set [SONG_ETITLE]'" & TextBox2.Text & "', [SONG_TTITLE]='" & TextBox3.Text & "', [SONG_SONGS1]='" & RichTextBox1.Text & "' where [CODE]=" & TextBox1.Text & ""

        Dim cmd As OleDbCommand = New OleDbCommand(command, myConnection)
        MsgBox("Record Update")
        Try
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            myConnection.Close()
            TextBox1.Enabled = False
            TextBox2.Enabled = False
            TextBox3.Enabled = False
            RichTextBox1.Enabled = False


        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
    End Sub
Posted
Updated 8-Jan-20 5:06am
Comments
Richard MacCutchan 8-Jan-20 13:00pm
   
Add a print statement in your code, or use the debugger, to view the generated statement. The error should be clear.
Member 14621280 9-Jan-20 11:18am
   
'Praise.vshost.exe' (CLR v4.0.30319: Praise.vshost.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_32\mscorlib\v4.0_4.0.0.0__b77a5c561934e089\mscorlib.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Praise.vshost.exe' (CLR v4.0.30319: Praise.vshost.exe): Loaded 'C:\Windows\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities\12.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingProcess.Utilities.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Praise.vshost.exe' (CLR v4.0.30319: Praise.vshost.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Windows.Forms\v4.0_4.0.0.0__b77a5c561934e089\System.Windows.Forms.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Praise.vshost.exe' (CLR v4.0.30319: Praise.vshost.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System\v4.0_4.0.0.0__b77a5c561934e089\System.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Praise.vshost.exe' (CLR v4.0.30319: Praise.vshost.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Drawing\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Praise.vshost.exe' (CLR v4.0.30319: Praise.vshost.exe): Loaded 'C:\Windows\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities.Sync\12.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingProcess.Utilities.Sync.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Praise.vshost.exe' (CLR v4.0.30319: Praise.vshost.exe): Loaded 'C:\Windows\assembly\GAC_MSIL\Microsoft.VisualStudio.Debugger.Runtime\12.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.Debugger.Runtime.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Praise.vshost.exe' (CLR v4.0.30319: Praise.vshost.exe): Loaded 'F:\Praise\Praise\bin\Debug\Praise.vshost.exe'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Praise.vshost.exe' (CLR v4.0.30319: Praise.vshost.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Configuration\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Praise.vshost.exe' (CLR v4.0.30319: Praise.vshost.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Core\v4.0_4.0.0.0__b77a5c561934e089\System.Core.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Praise.vshost.exe' (CLR v4.0.30319: Praise.vshost.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_32\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Praise.vshost.exe' (CLR v4.0.30319: Praise.vshost.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Deployment\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Deployment.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Praise.vshost.exe' (CLR v4.0.30319: Praise.vshost.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Xml\v4.0_4.0.0.0__b77a5c561934e089\System.Xml.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Praise.vshost.exe' (CLR v4.0.30319: Praise.vshost.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Xml.Linq\v4.0_4.0.0.0__b77a5c561934e089\System.Xml.Linq.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Praise.vshost.exe' (CLR v4.0.30319: Praise.vshost.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Data.DataSetExtensions\v4.0_4.0.0.0_
Richard MacCutchan 9-Jan-20 11:40am
   
Very interesting, but where is the printout of the generated UPDATE statement?
Member 14621280 9-Jan-20 11:48am
   
Update SONGS Set [SONG_ETITLE]='HE IS LORD!', [SONG_TTITLE]=',NaRNt Mz;lth;', [SONG_SONGS1]=',HE IS LORD! HE IS LORD!
HE IS RISEN FROM THE DEAD
AND HE IS LORD
EVERY KNEE SHALL BOW
EVERY TONGUE CONFESS
THAT JESUS CHRIST IS LORD.

56546456' where [CODE]=5
Richard MacCutchan 9-Jan-20 12:23pm
   
That does not match the code you have posted which is missing the = sign following Update SONGS Set [SONG_ETITLE].
Member 14621280 9-Jan-20 12:31pm
   
Update SONGS Set =[SONG_ETITLE]

is this correct?
Richard MacCutchan 9-Jan-20 12:46pm
   
No, the equals sign must go between the column name and the value field. Please do not try to guess the syntax, but study the documentation in detail. See SQL UPDATE Statement[^].
Member 14621280 9-Jan-20 12:59pm
   
command = "Update SONGS Set [SONG_ETITLE]='" & TextBox2.Text & "', [SONG_TTITLE]='" & TextBox3.Text & "', [SONG_SONGS1]='" & RichTextBox1.Text & "' where [CODE]=" & TextBox1.Text & ""

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Two things:
1) Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

2) You missed an "=" in the first update clause:
Update SONGS Set [SONG_ETITLE]'" & TextBox2.Text & "',
                              ^
                              |



How many times do we have to tell you the same thing? If you aren't going to listen to us and learn from us, is there any point in our answering your questions? Or even your asking them?
   
Comments
Member 14621280 8-Jan-20 10:14am
   
Sorry OriginalGriff... i am new to the VB.net i am learning.... when u say something in SQL i can't understand the term where you are using? that is the main reason....
Member 14621280 8-Jan-20 10:15am
   
Now that syntax error is gone.. but saving to DB is the problem..... even i am total blank in sql commands .....
OriginalGriff 8-Jan-20 10:20am
   
As I have said before: stop guessing how to do this stuff, and start learning and thinking. It'll be a load quicker in the long run, and you get skills out of it.

And stop concatenating strings!
Member 14621280 8-Jan-20 10:28am
   
Please teach me if u can... i am totally blank because morethan 1 month i have doing this especially this update statements
OriginalGriff 8-Jan-20 10:43am
   
I'm not here to teach you how to use SQL, or VB - that's what your course is for, or one of the many, many books on the subject.

Even if we wanted to - and remember everyone here is a volunteer, we mostly have "Proper Jobs" which pay the rent / mortgage - this is a tiny little text box, and some of the books are 1400 or more pages long! We can't teach you everything you need to know.

What course or book are you using?
Member 14621280 8-Jan-20 10:40am
   
Using con As New SqlConnection(strConnect)
con.Open()
Using com As New SqlCommand("INSERT INTO SONGS (SONG_ETITLE, SONG_TTITLE, SONG_SONGS1) VALUES (@SONG_ETITLE, @SONG_TTITLE, @SONG_SONGS1)", con)
com.Parameters.AddWithValue("@SONG_ETITLE", TextBox2.Text)
com.Parameters.AddWithValue("@SONG_TTITLE", TextBox3.Text)
com.Parameters.AddWithValue("@SONG_SONGS1", RichTextBox1.Text)
com.ExecuteNonQuery()
com.Dispose()
con.Close()
TextBox1.Enabled = False
TextBox2.Enabled = False
TextBox3.Enabled = False
RichTextBox1.Enabled = False



End Using
End Using

i have tried what u said before.... but this error is coming when update "The ConnectionString property has not been initialized."
Member 14621280 8-Jan-20 10:43am
   
<configuration>
<configsections>

<connectionstrings>
<add name="Praise.My.MySettings.DatabaseConnectionString" connectionstring="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:/Praise/Praise/bin/Debug/Database.accdb"
="" providername="System.Data.OleDb">

<startup>
<supportedruntime version="v4.0" sku=".NETFramework,Version=v4.5">




this is my connectionstring
OriginalGriff 8-Jan-20 10:52am
   
:sigh:
What did I say about guessing and thinking?

What does the error message say? Read the words. Read them again. What do you think they mean? Have you looked to see exactly what is in the string your are trying to use to connect? How? What did it show?

This is basic stuff: you are trying to take shortcuts and hoping it all works instead of putting effort into learning how to actually do anything, and that's not a good idea at all.
Get a course, get a good book. Start thinking instead of guessing!
Member 14621280 8-Jan-20 11:04am
   
Thanks for the information

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100