How to add data to an access database in visual studio 2012

Question

This is my code I have put behind my submit button, I have two text boxes Name and E-mail that I want to be added to my database that it does not work wonder if anyone can help

Dim Strnm As String = Request.Form("txtname")
        Dim Strem As String = Request.Form("txtemail")

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

        Dim StrSQL As String
        Dim dbConn As OleDbConnection = Nothing

        Dim filepath = "G:\WebSites\WebSite1\App_Data\register_log.ldf"
        dbConn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\registration.accdb")
        dbConn.Open()

        StrSQL = "insert into tblregistration (Name, E-mail) values (?,?)"
        objcmd = New OleDbCommand(StrSQL, dbConn)
        objcmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("@txtname", Strnm))
        objcmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("@txtemail", Strem))


        'close connection
        dbConn.Close()
        Response.Write("submitted successfully")

you did not execute the query: `objcmd.ExecuteNonQuery()`. you should also dispose of the connection and command object. `Using` blocks are good for this — Ňɏssa Pøngjǣrdenlarp, Apr 20 '15 at 11:46
adding parameter with name must have the parameter name in the query.. otherwise just add the parameters without parameter name. — Krish, Apr 20 '15 at 11:56
@krish OleDB just treats named params as placeholders and doesnt actually use them as named params — Ňɏssa Pøngjǣrdenlarp, Apr 20 '15 at 11:57

score 0 · Accepted Answer · edited May 23 '17 at 12:06

You did not execute the query. Your code can be also be shortened with Using blocks which declare and dispose of things:

Dim Strnm As String = Request.Form("txtname")
Dim Strem As String = Request.Form("txtemail")

Dim StrSQL = "insert into tblregistration (Name, E-mail) values (?,?)"

Using dbConn = New OleDbConnection(connection string...),
    objcmd = New OleDbCommand(StrSql, objConnection)

    dbConn.Open()

    objcmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("@txtname", Strnm))
    objcmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("@txtemail", Strem))

    objcmd.ExecuteNonQuery     ' execute the query
End Using             ' close and dispose of dbConn, objcmd


Response.Write("submitted successfully")

There are also 2 Connection objects declared (objConnection and dbConn) in the code.

Using acts like Dim in so far the code can declare and initialize things in one line of code. But mainly it assures that these things are closed and disposed of at the end of the block. The code also uses the constructor to pass the SQL and Connection to dbConn and objCmd when they are created. It only saves a line or two of code, but assumes that they have what they need from the moment they are created.

You can write also use a function to return a Connection object so that you do not have to have the connection string throughout your code. See this answer.

How to add data to an access database in visual studio 2012

1 Answers1