How do I correct syntax error on insert statement for adding data

Question

1.00/5 (1 vote)

See more:

I got an error message when I run program to insert data in database using Access & VB. The error says, "Syntax error in INSERT INTO statement." But when I login it works fine in retrieving username and password in login table

What I have tried:

VB.NET

Imports System.Data
Imports System.Data.OleDb
Public Class AddVehicle
    Dim con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\IECUSER\Documents\CarRental.accdb")
    Dim cmd As New OleDbCommand
    Dim str As String
    Dim da As New OleDbDataAdapter
    Dim ds As New DataSet

        Private Sub Savebtn_Click(sender As System.Object, e As System.EventArgs) Handles Savebtn.Click
        
        con.Open()
        cmd = New OleDbCommand("INSERT INTO tblVehicl[LicenceNo],[Model],[Colour],[Engine],[Class],[DailyRate],[WeeklyRate],[Quantity]" + "Values('" & txtModel.Text & "','" & txtClass.Text & "','" & txtDailyRate.Text & "','" & txtEngine.Text & "','" & txtLicenseNo.Text & "','" & txtQty.Text & "','" & txtWeeklyRate.Text & "','" & cmbColour.SelectedItem & "'", con)
        
        Dim x As String = cmd.ExecuteNonQuery()

        If x > 1 Then
            MsgBox("Saved Successfully")
            clr()
        End If
        con.closed()

Posted 21-Aug-23 7:20am

NyikoB

Updated 21-Aug-23 7:22am

Andre Oosthuizen

v2

Add a Solution

Comments

Andre Oosthuizen 21-Aug-23 13:23pm

Should 'tblVehicl' not be 'tblVehicle'?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Dave Kreskowiak · Answer 1 · 2023-08-21T07:46:00

You can start by not using string concatenation to build the SQL query. Instead, use a parameterized query.

You can also make sure you're spelling your table and column names correctly and making sure you have spaces where appropriate, like just before Value and just after the table name.

It's been a long time since I've done anything in Access, but you may also be required to have parentheses around the list of column names you're inserting data into.

You might want to pass the data from the fields in the correct order to match the columns you specified in the query column list. Just sayin'.

That way you've written this code is error prone, does not accept ' characters in text fields, and is susceptible to SQL injection attacks.

Parameterized queries will solve all of that. With Access queries, the names of the parameters do not matter. What is important though is you mass the parameters in the exact order you specified in the column list. Below is an example of a parameterized query, but I still consider this to be crappy code because it does not validation of data before adding it to the database.

VB

cmd = New OleDbCommand("INSERT INTO tblVehicle ([LicenceNo], [Model], [Colour], [Engine], [Class], [DailyRate], [WeeklyRate], [Quantity]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
cmd.Parameters.AddWithValue("licenseNo", txtLicenseNo.Text)
cmd.Parameters.AddWithValue("model", txtModel.Text)
cmd.Parameters.AddWithValue("colour", cmdColour.SelectedItem)
cmd.Parameters.AddWithValue("engine", txtEngine.Text)
cmd.Parameters.AddWithValue("class", txtClass.Text)
cmd.Parameters.AddWithValue("dailyRate", txtDailyRate.Text)  ' This is bad because you're storing a number as text!
cmd.Parameters.AddWithValue("weeklyRate", txtWeeklyRate.Text)  ' This is bad because you're storing a number as text!
cmd.Parameters.AddWithValue("quantity", txtQty.Text)  ' This is bad because you're storing a number as text!