VB.NET SQL ACCESS Insert Statement

Question

net and i'm trying to insert data into access database using sql, i have the code below, when i try to execute, it prompts me an error message and highlighting con.open() i don't understand why it's not working, can anyone guide me. Thank

 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles AddBut.Click

        Dim dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source= c:\Databse\Company_db.accdb"
        Dim dbSource = "Data Source= C:\Databse\Company_db.accdb"

        Dim empNum As String
        Dim empFname As String
        Dim empLname As String
        Dim empDept As String
        Dim empStat As String
        Dim empYears As String


        empNum = eNumText.Text
        empFname = empFnameText.Text
        empLname = empLnameText.Text
        empDept = Deptd.Text
        empStat = Statd.Text
        empYears = yearstext.Text




        Dim sql = "INSERT INTO tbl_empinfo (EmpID, FirstName, LastName, Department, Status, Years " & _
           ") " & _
           "Values(empNum, empFname, empLname, empDept, empStat, empYears)"

        con.ConnectionString = dbProvider & dbSource
        Using cmd = New OleDb.OleDbCommand(sql, con)
            con.Open()
            cmd.Parameters.AddWithValue("EmpID", empNum)
            cmd.Parameters.AddWithValue("FirstName", empFname)
            cmd.Parameters.AddWithValue("LastName", empLname)
            cmd.Parameters.AddWithValue("Department", empDept)
            cmd.Parameters.AddWithValue("Status", empStat)
            cmd.Parameters.AddWithValue("Years", empYears)
            cmd.ExecuteNonQuery()

        End Using


        con.Close()
    End Sub

Have you misspelled C:\Databse\? Perhaps C:\Database\

Fionnuala
– Fionnuala

2014-04-01 09:17:11 +00:00
Commented Apr 1, 2014 at 9:17 — Fionnuala
– Fionnuala, Commented Apr 1, 2014 at 9:17
@Remou nope that's really is exactly my folder's name :(

user3476579
– user3476579

2014-04-01 09:24:11 +00:00
Commented Apr 1, 2014 at 9:24 — user3476579
– user3476579, Commented Apr 1, 2014 at 9:24

Steve · Accepted Answer · 2014-04-01 09:46:47Z

3

Standard problem, when you see a Syntax error in an otherwise fine SQL statement, look for RESERVED KEYWORDS for the underlying database.

In your case the word POSITION is a reserved keyword for MS-ACCESS.
Put it between square brackets

Dim sql = "INSERT INTO tbl_empinfo (EmpID, FirstName, LastName, Department, " & _ 
          "[Position], Status, Years) " & _ 
          "Values(empNum, empFname, empLname, empDept, empStat, empYears)"

However, you have another error in that query. You have 7 fields to insert but you pass only 6 parameters, missing just the parameter for the POSITION field.

You need to fix also the connection string. You write

  con.ConnectionString = dbProvider & dbSource

but this result in an invalid file name

  "Provider=Microsoft.ACE.OLEDB.12.0; Data Source= 
   c:\Databse\Company_db.accdbData Source= C:\Databse\Company_db.accdb"

(line splitted for readability)

edited Apr 1, 2014 at 9:46

answered Apr 1, 2014 at 8:54

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Codemunkeee Over a year ago

+1 for this. @OP please also see this link for reserved keywords. technet.microsoft.com/en-us/library/ms189822.aspx

user3476579 Over a year ago

i tried to follow your advise and have this pastebin.com/J1kFBFT9 but it's still giving me the exact error "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: Not a valid file name." message and highlighting con.Open() I still don't get it why?

Steve Over a year ago

Possible typo in the folder name? C:\Databse, still missing the 7th parameter

user3476579 Over a year ago

@Steve nope Sir, i have the exact Path as Databse, i have updated my Parameters from my code above sorry about that, but it's still giving me the same error :(

Steve Over a year ago

I see you define the connection string with con.ConnectionString = dbProvider & dbSource, but the variable dbProvider already contains the DataSource part, try fixing that leaving just one Data Source part

|

Sarjit Delivala · Accepted Answer · 2014-04-01 10:28:36Z

0

I consider that you have initialized the object of "OleDbConnection".

Try to get the Connection String from the Property window of your database (From Server Explorer Window right click on your database select properties option).

And when you are opening the connection use following code:

    If con.State = ConnectionState.Closed Then
        con.Open()
    End If

Hope it helps you.

answered Apr 1, 2014 at 10:28

Sarjit Delivala

5773 gold badges13 silver badges25 bronze badges

Collectives™ on Stack Overflow

VB.NET SQL ACCESS Insert Statement

2 Answers 2

6 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Related