Visual C#: Inserting an Access Database Record
 

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.

Re: Visual C#: Inserting an Access Database Record
 

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.

Re: Visual C#: Inserting an Access Database Record
 

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.

Re: Visual C#: Inserting an Access Database Record
 

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.