locked
Inserting into Access Database RRS feed

  • Question

  • User-49348100 posted

    I am trying to insert data into a access database from 2 text boxes but getting error message. Trying to use VB code. Please Help!!!

    Name of Database: test.accdb

    Location: T:\Data Team\Data Request Website

    Table Name: DataRequests

    Fields in Table: FirstName & LastName

        Dim Connection As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;" & _
        "Data Source=T:\Data Team\Data Request Website\test.accdb")
        Connection.Open()
        Dim Command As New OleDbCommand("INSERT INTO datarequests(FirstName," & _
        "LastName)VALUES(@FirstName,@LastName)", Connection)
        Command.Parameters.Add(New OleDbParameter("@FirstName", TextBox1.Text))
        Command.Parameters.Add(New OleDbParameter("@LastName", TextBox2.Text))
        Command.ExecuteNonQuery()

        Connection.Close()
      
        'Clear the text boxes after a new insert
        TextBox1.Text = ""
        TextBox2.Text = ""

    Friday, November 4, 2011 12:08 PM

Answers

All replies

  • User-448512826 posted

    Hi,

    your code looking good..

    paste Error here...

    Thanks....

    Friday, November 4, 2011 1:07 PM
  • User-1889364718 posted

    Hi ,

    Please post exact error message that you are getting .

    regards

    Sree

    Friday, November 4, 2011 1:09 PM
  • User-1199946673 posted

    You're using the Jet.OleDb.4.0 driver, which only works for Access 2003 (.mdb) files. So you need to convert your database into an Access 2003 file. Another option would be to use the ACE 12.0 OleDb driver, then you can use an accdb file. But be aware that this driver is not installed by default on Windows servers, so you need to make sure that this driver is available on you production machine:

    http://www.mikesdotnetting.com/Article/78/AccessDataSource-SqlDataSource-and-connecting-to-Access-databases-in-ASP.NET

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 4, 2011 6:45 PM
  • User-287396071 posted

    Try this

     Dim Connection As OleDbConnection
                Dim Command As OleDbCommand
                Dim icount As Integer
                Dim SQLstr As String
                Try
                    Connection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=T:\Data Team\Data Request Website\test.accdb"")
                    Connection.Open()

                    SQLstr = "INSERT INTO Login VALUES('" & FirstName.Text & "','" & LastName.Text & "')"

                    Command = New OleDbCommand(SQLstr, Connection)
                    icount = Command.ExecuteNonQuery
                    MessageBox.Show(icount)

                Catch ex As Exception

                    Connection.Close()
                End Try

    Monday, December 12, 2011 7:19 AM
  • User-1199946673 posted

                    Connection = New OleDbConnection(""Provider=Microsoft.Jet.Oledb.4.0;" & _
        "Data Source=T:\Data Team\Data Request Website\test.accdb"")
                    Connection.Open()

                    SQLstr = "INSERT INTO Login VALUES('" & FirstName.Text & "','" & LastName.Text & "')"

    This thread is already marked as answered, so why do you answer it again? furthermore, if you look into my previous post that is marked as the answer, you could read that your solution will NOT work, because the Jet Oledb 4.0 driver doesn't work with .accdb (access 2007 and above) files...

    Also, I think you should take a look at this article....

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    Monday, December 12, 2011 8:41 AM