Click here to Skip to main content
15,795,937 members
Please Sign up or sign in to vote.
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:

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
        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")
        End If
Updated 21-Aug-23 8:22am
Andre Oosthuizen 21-Aug-23 13:23pm    
Should 'tblVehicl' not be 'tblVehicle'?

1 solution

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.
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!
Share this answer
Andre Oosthuizen 21-Aug-23 13:59pm    
Must be the coffee, second round today Dave, thanks. :) I saw now that the order is completely wrong! I counted the fields to make sure he had the correct amount of input values, never checked the field names corresponds with the textbox names. +5

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900