Results 1 to 5 of 5

Thread: System.Data.OleDb.OleDbException: 'Syntax error in INSERT INTO statement.' Error

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2021
    Posts
    5

    System.Data.OleDb.OleDbException: 'Syntax error in INSERT INTO statement.' Error

    Dear Master,
    need help please..

    i got this error.. as attch

    this is my code..
    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If TextBox1.Text = "" Or
                TextBox2.Text = "" Or
                TextBox2.Text = "" Or
                TextBox4.Text = "" Or
                TextBox5.Text = "" Or
                TextBox6.Text = "" Or
                TextBox7.Text = "" Or
                TextBox8.Text = "" Or
                TextBox12.Text = "" Or
                TextBox13.Text = "" Or
                TextBox14.Text = "" Or
                ComboBox1.Text = "" Or
                ComboBox2.Text = "" Or
                ComboBox3.Text = "" Then
                MsgBox("Pastikan Data Diisi Lengkap !")
            Else
                Call Koneksi()
                Dim SimpanData As String = "insert into Tbl_Karyawan (NoIndukKaryawan,NamaKaryawan,TempatLahirKaryawan,TanggalLahirKaryawan,AlamatKtpKaryawan,AlamatDomisiliKaryawan,TelpRumahKaryawan,TelpSelKaryawan,StatusKaryawan,JenisKelaminKaryawan,AgamaKaryawan,EmailKaryawan,NPWPKaryawan,KTPKaryawan)Value('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "','" & TextBox6.Text & "','" & TextBox7.Text & "','" & TextBox8.Text & "','" & ComboBox1.Text & "','" & ComboBox2.Text & "','" & ComboBox3.Text & "',,'" & TextBox12.Text & "','" & TextBox13.Text & "','" & TextBox14.Text & "')"
                CMD = New OleDb.OleDbCommand(SimpanData, CONN)
                CMD.ExecuteNonQuery()
                MsgBox("Data Berhasil Disimpan")
                Call KondisiAwal()
    
            End If
    
        End Sub
    and i attach my access database as well

    please help master

    thank in advance

    Regards,
    fitriyanto
    Attached Images Attached Images    
    Last edited by Shaggy Hiker; Nov 24th, 2021 at 11:13 PM. Reason: Added CODE tags.

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,772

    Re: System.Data.OleDb.OleDbException: 'Syntax error in INSERT INTO statement.' Error

    It seems to be the same story every time with this sort of error. People tell us that they have a syntax error in their SQL code and then don't show us their SQL code, which suggests that they haven't even looked at it themselves. The VB code that you use to build your SQL code is only of so much use. You need to look at the actual SQL code, i.e. the value of SimpanData at run time, to see what the actual syntax being used is to see where the actual issue is.

    Of course, this is exactly why you don't use VB code to build SQL code this way. Doing so makes it harder to read and thus error-prone, plus it opens you to format issues with dates as well as apostrophes in text values. Most importantly though, it opens you up to SQL injection attacks, meaning that a malicious user could potentially delete the entire contents of your database. ALWAYS use parameters to avoid ALL of these issues. Check out this CodeBank thread of mine for some ADO.NET examples of various scenarios and check out this blog post for specific details relating to using parameters.

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,772

    Re: System.Data.OleDb.OleDbException: 'Syntax error in INSERT INTO statement.' Error

    You also have every value wrapped in single quotes when that should only be done for text values, so you might have encountered a data type mismatch if you hadn't encountered a syntax error. If you use parameters then you don't wrap anything in single quotes, so you can never get it wrong. Just as the only thing that gets wrapped in double quotes in VB is literal Strings, so the only thing that gets wrapped in single quotes in SQL is literal strings.

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    36,765

    Re: System.Data.OleDb.OleDbException: 'Syntax error in INSERT INTO statement.' Error

    Welcome to the forum. I edited your post to wrap the code in [CODE][/CODE] tags so that it formats nicely. You can do that yourself with the # button. Just press the button and paste the code between the tags, or add the code, then go back, highlight it, and press the button.

    As for the question, there's one pretty likely problem, and at least one other. You have a date/time field. You have that wrapped in single quotes, but Access doesn't use single quotes for dates, it uses # marks. So, around the date you should have # rather than '.

    In any case, you should be using parameters rather than concatenating user entered data directly into the query. Concatenating user data leaves you open to SQL injection attacks. Using parameters would not just protect you from that, but would remove the need to worry about what to wrap/not wrap any data in.

    Wrapping the data does look like it is the problem, though. In addition to needing to wrap dates in # rather than single quotes, you also have a couple numeric fields. Those shouldn't be wrapped at all.
    My usual boring signature: Nothing

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,772

    Re: System.Data.OleDb.OleDbException: 'Syntax error in INSERT INTO statement.' Error

    Actually, taking another look at your code after Shaggy added the CODE tags, I can now see that you have two commas between two of the values. I couldn't see that without the formatting tags, which is a perfect example of why you need to use formatting tags. I also have to wonder how you could miss that yourself, which suggest that you made no effort to solve your own problem before asking for help. You won't be able to solve all your problems but you should be able to solve the simple ones... if you try. Regardless, all my previous advice still applies. ALWAYS use parameters and one benefit will be that your code will be easier to read, so things like spurious commas will be easier to spot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width