Trying to Insert Text to Access Database with ExecuteNonQueryin VB

Question

I'm working on this feature were i save text from a textbox on my form to a access database file. With the click on my "store" button I want to save the text in the corresponding textbox fields to the fields in my access database for searching and retrieving purposes. Im doing this in VB

The problem I run into is the INSERT TO error with my "store" button Any help or insight as to why im getting error would be greatly appreciated.

here is my code:

Dim con As System.Data.OleDb.OleDbConnection
Dim cmd As System.Data.OleDb.OleDbCommand
Dim dr As System.Data.OleDb.OleDbDataReader
Dim sqlStr As String

Private Sub btnStore_Click(sender As Object, e As EventArgs) Handles btnStore.Click

    con = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Eric\Documents\SPC1.2.accdb")
    con.Open()

    MsgBox("Initialized")

    sqlStr = "INSERT INTO Master FADV Calll info  (First Name, Last Name, 
       Phone Number, Email, User Id) values (" & txtFirstName.Text & " ' ,'" &
       txtLastName.Text & "','" & txtPhone.Text & "', '" & txtEmail.Text & "', '" &
       txtUserID.Text & "', '" & txtOtherOptionTxt.Text & "','" 
       & txtCallDetail.Text & "');"

    cmd = New OleDb.OleDbCommand(sqlStr, con)
    cmd.ExecuteNonQuery()   <------- **IM RECIVING THE ERROR HERE** 
    MsgBox("Stored")
    ''pass ther parameter as sq; query, con " connection Obj)"

    con.Close()

So I tried to clean up my code a little, I didn't use paramerters like you recommended yet so maybe that is why I am still receiving the error. I figured this would work since it was a little cleaner

sqlStr = "INSERT INTO Master FADV Calll info(First Name, Last Name, Phone Number,
 Email, User Id,Notes) values (@First Name, @Last Name, @Phone Number,@ Email,
 @UserId,@Notes)"


    cmd = New OleDb.OleDbCommand(sqlStr, con)
    cmd.ExecuteNonQuery()'

Now by parameters do you mean something along the lines of this 'cmdInsert.Parameters.Add("@FirstName", Data.SqlDbType.NVarChar).Value = FirstName'

im still getting this crazy error its making me want to bang my head against a wall. I do apologize for bugging you on such a meager issue, but please bear with me. I am receiving this error message here is my code Syntax error (missing operator) in query expression '@First Name'.

but even before I was still getting an input error at the cmd.ExecuteNonQuery() line.

'Dim SQL As String = "INSERT INTO [Master FADV Calll info] Values (@First Name, @Last Name, @Phone Number,)"

    Using cmd As New OleDbCommand(SQL, con)
        cmd.Parameters.AddWithValue("@First Name", txtFirstName.Text)
        cmd.Parameters.AddWithValue("@Last Name", txtLastName.Text)
        cmd.Parameters.AddWithValue("@Phone Number", txtPhone.Text)
        ' etc

        cmd.ExecuteNonQuery()

    End Using'

I fianaly got it to work. here is my code

' con = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Eric\Documents\Fadv.accdb") con.Open()

    Dim SQL As String = "INSERT INTO [info] ([FirstName], [LastName], [Phone#]) Values (?, ?, ?)"


    Using cmd As New OleDbCommand(Sql, con)

        cmd.Parameters.AddWithValue("@p1", txtFirstName.Text)
        cmd.Parameters.AddWithValue("@p2", txtLastName.Text)
        cmd.Parameters.AddWithValue("@p3", txtPhone.Text)
        ' etc

        cmd.ExecuteNonQuery()

        con.Close()
    End Using

'

you have mismatched ticks in your SQL string. Use Parameters and that problem and much worse ones (see SQL injection) will disappear; as a free bonus, you code will be easier to read. [Example](http://stackoverflow.com/a/23377684/1070452) — Ňɏssa Pøngjǣrdenlarp, May 27 '14 at 02:32
may also be missing a JOIN in the SQL too if Master, FADV, Calll, info are tables — Ňɏssa Pøngjǣrdenlarp, May 27 '14 at 03:06
a) if there are spaces in teh table name, you need to escape them with `[...]` b) escape the column names too since they have spaces c) the link in the first comment shows how to do Parameters with OleDb you use '?' in the SQL and "@p...` in the param. With spaces in the names, the SQL processor cant tell where the names start or end. — Ňɏssa Pøngjǣrdenlarp, May 27 '14 at 03:32

score 0 · Answer 1 · answered May 27 '14 at 03:22

0

Your missing a tick after the value and paren...

 Values('" &

Put that in and see if it works... Also as Plutonix has mentioned, use parameterized queries; better approach overall.

answered May 27 '14 at 03:22

Trevor

7,777
6
31
50

score 0 · Accepted Answer · edited May 23 '17 at 10:32

Mismatched ticks are a big problem when concatenating strings to get a SQL statement. A bigger problem is the threat of SQL injection attacks. You also have some very strange table and column names - names with spaces in them must be "escaped" using brackets:

Master FADV Calll info ==> [Master FADV Calll info]

con = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Eric\Documents\SPC1.2.accdb")
con.Open()

Dim SQL As String = "INSERT INTO [Master FADV Calll info] ([First Name], 
        [Last Name], [Phone Number], Email, [User Id], Notes ) 
        Values (?, ?, ?, ?, ?)"

Using cmd As New OleDbCommand(SQL, Con)
   cmd.Parameters.AddWithValue("@p1", txtFirstName.Text )
   cmd.Parameters.AddWithValue("@p2", txtLastName.Text)
   cmd.Parameters.AddWithValue("@p3", txtPhone.Text)
   ' etc

   cmd.ExecuteNonQuery() 
End Using

In OleDB parameters are positional (only), so you have to add the parameter values in the same order as the columns are listed in the SQL.

Using parameters

you avoid SQL injection
less chance of botching the SQL from missing ticks '
code is easier to read and maintain

hey plutonix your so Awesome your technique made everything so uniform, but im still getting this crazy error its making me want to bang my head against a wall. I do apologize for bugging you on such a meager issue, but please bear with me. — Playergoodi, May 27 '14 at 23:30
@user3673701 I missed something in the post but it is mentioned in the comment above: you have to specify column names, but if you have spaces in them, you need to escape them with `[...]`. Thus, `[First Name]` but only `Email` or `Notes`. You **must** add the values with AddWithValue in the same order as those names appear. Also if a column is not Text/string, you must convert your TextBox.Text like this `AddWithValue("@pN", Convert.ToInt32(TextBox.Text))`. Answer updated — Ňɏssa Pøngjǣrdenlarp, May 27 '14 at 23:44

Trying to Insert Text to Access Database with ExecuteNonQueryin VB

2 Answers2