Insert text from a text box into and Access 2010 DataBase using VB.Net

Question

I have 3 text boxes I am using as a contact form. I am trying to use VB to take this data and add it into my database. I have run debugging and it says the error is in the INSERT INTO string. ![Screen Grab of Debugging] [1]: https://i.sstatic.net/ufYPs.png

Any ideas?

Imports System

Imports System.Data Imports System.Data.OleDb Partial Class Contact Inherits System.Web.UI.Page

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim Name As String = txtName.Text
    Dim Email As String = tbxEmail.Text
    Dim Comment As String = tbxComment.Text

    Dim objConnection As OleDbConnection = Nothing
    Dim objcmd As OleDbCommand = Nothing

    Dim strSql As String
    Dim dbConn As OleDbConnection = Nothing

    dbConn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = Z:\Documents\Databases\user.accdb")
    dbConn.Open()

    strSql = "INSERT INTO user (username, email, comments) VALUES (?,?,?)"
    objcmd = New OleDbCommand(strSql, dbConn)
    objcmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("@username", Name))
    objcmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("@email", Email))
    objcmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("@comments", Comment))
    objcmd.ExecuteNonQuery()


    dbConn.Close()
    Response.Write("Submitted Successfully")

End Sub

Steve · Accepted Answer · 2014-04-30 10:18:03Z

1

USER is a reserved keyword in MS-Access, To use it as a table name your need to enclose it in square brackets

strSql = "INSERT INTO [user] (username, email, comments) VALUES (?,?,?)"

As a side note, try to use the Using Statement when you work with disposable objects.

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim Name As String = txtName.Text
    Dim Email As String = tbxEmail.Text
    Dim Comment As String = tbxComment.Text

    Using dbConn = New OleDbConnection(".......")
        dbConn.Open()
        Dim strSql = "INSERT INTO [user] (username, email, comments) VALUES (?,?,?)"
        Using objcmd = New OleDbCommand(strSql, dbConn)
            objcmd.Parameters.AddWithValue("@username", Name)
            objcmd.Parameters.AddWithValue("@email", Email)
            objcmd.Parameters.AddWithValue("@comments", Comment)
            objcmd.ExecuteNonQuery()
        End Using
    End Using
    Response.Write("Submitted Successfully")
End Sub

edited Apr 30, 2014 at 10:18

answered Apr 29, 2014 at 21:49

Steve

217k22 gold badges242 silver badges296 bronze badges

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

5 Comments

mredfern79 Over a year ago

Thanks for the reply, I have tried 2 things. If I add the Using statement I end up with build errors, stating both dbconn and objcmd hide a variable in and enclosing block.

mredfern79 Over a year ago

I have tried running without the 'Using statement', just adding square brackets to user [user]. While debugging the following error is thrown Unhandled exception at line 940, column 13 in localhost:1169/… 0x800a139e - JavaScript runtime error: Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed.

Steve Over a year ago

Rewritten all the answer code, all the variables for connection and command are implicitly created inside the using statement block and destroyed at the End Using, so you don't need to declare and initialize them before the block. The second error doesn't seem to be related to this question

mredfern79 Over a year ago

Thank you very much for your help. I realise now I hand;t amended my code to reflect the variables being declared inside the USING statement.

mredfern79 Over a year ago

I should also add the javascript runtime error was caused by the update panel containing my form. I think the error was being thrown as the response.write was being loaded outside of the panel as I failed to add a placeholder

Collectives™ on Stack Overflow

Insert text from a text box into and Access 2010 DataBase using VB.Net

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related