vb.net Syntax error in INSERT INTO Statement with access

Question

I'm getting an error during my insert can someone take a look? Table: enter image description here

VB.NET

   Dim Name As String = txtName.Text
    Dim JoinDate As String = dpJoinDate.Value
    Dim DOB As String = dpDOB.Value
    Dim ParentsName As String = txtParentsName.Text
    Dim School As String = txtSchool.Text
    Dim STD As String = txtSTD.Text
    Dim Address As String = txtAddress.Text
    Dim EMail As String = txtEMail.Text
    Dim Mobile1 As String = txtMobile1.Text
    Dim Mobile2 As String = txtMobile2.Text
    Dim DurationStart As Date = dpDurationStart.Value
    Dim DurationEND As Date = dpDurationEND.Value
    Dim Fees As Decimal = Decimal.Parse(0.0)
    Dim MaterialFees As Decimal = Decimal.Parse(0.0)
    Dim LateFees As Decimal = Decimal.Parse(0.0)
    Dim NextRenewal As Date = dpNextRenewal.Value
    Dim Centre As String = cbCentre.Text
    Dim Coach As String = cbCoach.Text
    Dim picture As String = lblFileName.Text

    Try
        Fees = Decimal.Parse(txtFees.Text)
    Catch

    End Try

    Try
        MaterialFees = Decimal.Parse(txtMaterialFees.Text)
    Catch

    End Try

    Try
        LateFees = Decimal.Parse(txtLateFees.Text)
    Catch

    End Try

    Dim Cmd As OleDbCommand
    Dim SQL As String
    Dim objCmd As New OleDbCommand

    Dim Con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=./AcademyDatabase.accdb;Persist Security Info=False;")

    SQL = "INSERT INTO Student (FullName,JoinDate,DOB,ParentsName,School,STD,Address,EMail,Mobile1,Mobile2,DurationStart,DurationEND,Fees,MaterialFees,LateFees,NextRenewal,Centre,Coach,Image,DropOut) VALUES ('" _
        & Name & "','" _
        & JoinDate & "','" _
        & DOB & "','" _
        & ParentsName & "','" _
        & School & "','" _
        & STD & "','" _
        & Address & "','" _
        & EMail & "','" _
        & Mobile1 & "','" _
        & Mobile2 & "','" _
        & DurationStart & "','" _
        & DurationEND & "','" _
        & Fees & "','" _
        & MaterialFees & "','" _
        & LateFees & "','" _
        & NextRenewal & "','" _
        & Centre & "','" _
        & Coach & "','" _
        & picture & "'," _
        & "0)"
    Cmd = New OleDbCommand(SQL, Con)

    Con.Open()
    objCmd = New OleDbCommand(SQL, Con)
    Dim rowCount As Integer = 0

    Try
        rowCount = objCmd.ExecuteNonQuery()
    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try

Sql:

"INSERT INTO Student (FullName,JoinDate,DOB,ParentsName,School,STD,Address,EMail,Mobile1,Mobile2,DurationStart,DurationEND,Fees,MaterialFees,LateFees,NextRenewal,Centre,Coach,Image,DropOut) VALUES ('','3/13/2014','1/1/1900','','fadsasdffdas','','','','','','1/1/1900','1/1/1900','0','0','0','1/1/1900','','','',0)"

what is the error message you're getting?

Ezi
– Ezi

2014-03-13 18:44:14 +00:00
Commented Mar 13, 2014 at 18:44 — Ezi
– Ezi, Commented Mar 13, 2014 at 18:44

Steve · Accepted Answer · 2014-03-13 18:53:40Z

IMAGE is a reserved keyword. If you want to use it for a column name, then your need to encapsulate it with square brackets

"INSERT INTO Student " & _ 
"(FullName,JoinDate,DOB,ParentsName,School,STD,Address," & _
"EMail,Mobile1,Mobile2,DurationStart,DurationEND,Fees," & _
"MaterialFees,LateFees,NextRenewal,Centre,Coach,[Image],DropOut) VALUES  ...."

If you are still able to do so, I suggest to change the name of that column to a NON reserved keyword, otherwise you will alway have this problem when you try to use that column.

Said that, please, read about parameterized queries. Your code has a big problem and it is called SQL Injection (not to mention the parsing problems for strings, date and decimals)

 SQL = "INSERT INTO Student " & _ 
       "(FullName,JoinDate,DOB,ParentsName,School,STD,Address," & _
       "EMail,Mobile1,Mobile2,DurationStart,DurationEND,Fees," & _
       "MaterialFees,LateFees,NextRenewal,Centre,Coach,[Image],DropOut) " & _
       "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0)"
 Con.Open()
 objCmd = New OleDbCommand(SQL, Con)
 objCmd.Parameters.AddWithValue("@p1", Name)
 objCmd.Parameters.AddWithValue("@p2", JoinDate)
 .... add the other missing parameters with their values.....
 objCmd.Parameters.AddWithValue("@p18", picture)

 Dim rowCount As Integer = 0
 rowCount = objCmd.ExecuteNonQuery()

Collectives™ on Stack Overflow

vb.net Syntax error in INSERT INTO Statement with access

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related