Visual C#: Inserting an Access Database Record

[SeekAnswers]

Good day, I have been trying to add a new record to the Access Database that has already been connected to the Visual Basic Application (Using Visual Studio 2005) that I have been developing, using the DataSource and the DataAdapter objects, though I'm able to add an Insert query and run it, but the record is not saved in the related database at all.

Can you give me some hints and directions as how to solve the above mentioned problem, thanks.

[binteron]

This is the exact problem I am having. Autogenerated code with DataGridView control doesn't save data to the table? Help please if you can.

[Venjense]

Access doesnt have transactional and stored proc support and I really really really think you should migrate to MSDE aka SQL express. But if you have to use Access....

[ripped off MSDN]

private static OleDbDataAdapter CreateCustomerAdapter(
    OleDbConnection connection)
{
    OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
    OleDbCommand command;
    OleDbParameter parameter;

    // Create the SelectCommand.
    command = new OleDbCommand("SELECT * FROM dbo.Customers " +
        "WHERE Country = ? AND City = ?", connection);

    command.Parameters.Add("Country", OleDbType.VarChar, 15);
    command.Parameters.Add("City", OleDbType.VarChar, 15);

    dataAdapter.SelectCommand = command;

    // Create the UpdateCommand.
    command = new OleDbCommand(
        "UPDATE dbo.Customers SET CustomerID = ?, CompanyName = ? " +
        "WHERE CustomerID = ?", connection);

    command.Parameters.Add(
        "CustomerID", OleDbType.Char, 5, "CustomerID");
    command.Parameters.Add(
        "CompanyName", OleDbType.VarChar, 40, "CompanyName");

    parameter = command.Parameters.Add(
        "oldCustomerID", OleDbType.Char, 5, "CustomerID");
    parameter.SourceVersion = DataRowVersion.Original;

    dataAdapter.UpdateCommand = command;

    //do the same for the insert and delete commands on the data adapter

    return dataAdapter;
}

The problem with this is that it can introduce SQL Injection attacks if used on a website. If you don't know what this is, it goes something like this (old customer id parameter has to be larger for this lets say its char 20 instead of 5)

oldCustomerid = "1;delete customer;";

oops!

Even a poorly written stored procedure would pass this along, but atleast if the customerid was a number, the type would be a number and you would get an error.

If you do use a DBMS, don't do "Select * from..." there is a hidden cost to this. There is additional overhead to get the table column schema first, then the query gets resubmitted as "Select field1, field2, ... fieldn from..."

Lastly, explicitely open the connection so it reminds you to close the connection so there are no memory leaks! If you dont close the connection the objects wont be freed until the GC comes along whenever that may be.

[binteron]

I figured out my problem. I had included the database file in the project. Therefore it was overwritten each time I compiled the project, making it look like the database wasn't being updated. I created an external database and it works flawlessly now.
Thanks for the responses.

[facadie]

I figured out my problem. I had included the database file in the project. Therefore it was overwritten each time I compiled the project, making it look like the database wasn't being updated. I created an external database and it works flawlessly now.
Thanks for the responses.

hi how did you do it?=) cause I'm using microsoft access to store the data and i was wondering how can i make the the database to be updated?

[DdoubleD]

I figured out my problem. I had included the database file in the project. Therefore it was overwritten each time I compiled the project, making it look like the database wasn't being updated. I created an external database and it works flawlessly now.
Thanks for the responses.

hi how did you do it?=) cause I'm using microsoft access to store the data and i was wondering how can i make the the database to be updated?

If including the Access database with the project, there is a property you can set for this in the project that prevents compiles from overwriting your updated database. Just select the .mdb in the project (Solution Explorer), and change the Copy to Output Directory property to be "Copy if newer" option.

[kei1412]

public void SymTable(string var)
        {
            SQLOpen();
            SQLCommand = new OleDbCommand("INSERT INTO SymbolTable (Variable) VALUES ('"+var+"')", SQLConnection);
            SQLCommand.Dispose();
            SQLClose();
        }

having problem with this code in inserting data in fields