1,105,229 Community Members

Visual C#: Inserting an Access Database Record

Member Avatar
SeekAnswers
Newbie Poster
20 posts since Mar 2006
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
binteron
Newbie Poster
7 posts since Jun 2006
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
Venjense
Light Poster
33 posts since Oct 2003
Reputation Points: 2 [?]
Q&As Helped to Solve: 5 [?]
Skill Endorsements: 5 [?]
 
0
 

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.

Member Avatar
binteron
Newbie Poster
7 posts since Jun 2006
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
facadie
Junior Poster in Training
57 posts since Sep 2009
Reputation Points: -9 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
-1
 

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?

Member Avatar
DdoubleD
Posting Shark
984 posts since Jul 2009
Reputation Points: 315 [?]
Q&As Helped to Solve: 235 [?]
Skill Endorsements: 5 [?]
 
-1
 

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.

Member Avatar
kei1412
Newbie Poster
7 posts since Aug 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 
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

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article