How do you insert new entries into an Access db table through an ASP.net website?

Question

I need to insert new records into an Access database.
I'm using Visual Studio 2008 and firstly create a asp.net website. I can connect to the information in Access databse using dataview or gridview and can query a particular entry (ie. Proposal No. -brings up all details linking to that proposal).
I can then edit the details of that proposal and this would update the Access Db.
What I need to do is to have a form that simply enters new details for a new customer. ie. Enter name [__] Enter Adress[__]. Then for this to update the database. By using the gridview or dataview I am able to view all fields that exist in the table and edit them. Is there a way that I can get a blank gridview/dataview template (which includes all the fields in the table) and fill it out to then update the database? Thanks

Answer 1

The best way to go is to add a table right below your gridview, and stick the form fields into it.

Alternatively, you can put the form fields in the grid's < asp:FooterTemplate>, but you would have to duplicate them in the < EmptyDataTemplate> to see the ADD form when your grid is empty.

When user clicks the ADD button, insert the form data into the database, and rebind the grid to show the newly inserted row.

EDIT: to answer you comment, you'd have to use the < asp:TemplateFiels> in your gridview in order to put the "insert form" into the FoorterTemplate. i assume you are using < asp:BoundField> now.

Again, i recommend that you put your "insert form" in the table right below the grid.

Read this article for detailed explanation

Answer 2

The usual way of allowing the end user to add a new record to a database is through presenting the user with a set of controls to fill in and/or select from i.e. a number of labelled textbox, listbox, checkbox, etc. controls.

How these are laid out is up to you, and you could lay them out to mimic your gridview. I would recommend creating a user control and then using that in your page.

EDIT:

To answer your comment about how to add the fields as a record to the database, you would do something like the following (this is in C#)

Firstly, set up add a connection string to the connectionstrings section in your web.config. It is generally good practice to set them up here if you're going to be using the same data source throughout your application, as it saves you from having to write the connection string out each time

<connectionStrings> 
<add name="myConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|db1.mdb"
providerName="System.Data.OleDb" />
</connectionStrings>

Then in the code-behind for your page

    protected void AddButton_Click(object sender, EventArgs e)
    {
        AddRecordToDatabase(txtCustomerName.Text, txtPlaceOfBirth.Text); 

        /*
          Where txtCustomerName and txtPlaceOfBirth are the IDs 
          of your Name and Place Of Birth textboxes, respectively.
          You may want to perform some validation on the textboxes before
          performing the insert!
        */
    }

    private void AddRecordToDatabase(string customerName, string placeOfBirth)
    {
        OleDbConnection conn;
        OleDbCommand cmd;

        using (conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString))
        {
            using (cmd = conn.CreateCommand())
            {
                cmd.CommandText = "INSERT INTO Customers(Name, PlaceOfBirth) VALUES (@name, @place_of_birth)";
                cmd.CommandType = CommandType.Text;

                cmd.Parameters.AddWithValue("@name", customerName);
                cmd.Parameters.AddWithValue("@place_of_birth", placeOfBirth);

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }
    }

The using statements will dispose of the objects once finished with (they are essentially Try-Finally blocks).

Depending on the version of Access, it may be possible to return a value after the insert to indicate whether the insert has succeeded or not (I'm only familiar with Access 97 - 2003). If this is possible, you may want to use ExecuteScalar instead of ExecuteNonQuery to obtain the return value and respond accordingly.

As a very general piece of advice, I would not recommend Jet (the database engine that Access uses) as the backend database for an ASP.NET website- have a look at the answers to this question for reasons why. SQL Server 2005 Express is free to use* and is an excellent database choice for web development.

*There are some limitations, check Microsoft Website for details.