nawfal nawfal - 2 months ago 15
C# Question

How to update MS Access database

I need a simple program to update MS Access database fields. I followed an online tutorial which was simple and had the code working. But it doesnt seem to work anymore when I reimplement it. Here's my code.

public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

OleDbConnection conn;
OleDbDataAdapter da;
DataSet ds;
OleDbCommandBuilder cb;
DataRow row;

private void Form1_Load(object sender, EventArgs e)
{
conn = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source = C:\\test.mdb");
da = new OleDbDataAdapter("select* from user", conn);
ds = new DataSet();

conn.Open();
da.Fill(ds, "user");
conn.Close();
}

private void button1_Click(object sender, EventArgs e)
{
cb = new OleDbCommandBuilder(da);
row = ds.Tables["user"].Rows[0];

row[3] = "hello";

da.Update(ds, "user");
}
}


user
is the table name of my database. What I tried to do is update the field row[0] (first row) and column[3] (4th column) with the string
hello
.. The error i get is
Synatx error in FROM clause
. After some internet reading, I found
user
has to be in square brackets. So I made it this.

public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

OleDbConnection conn;
OleDbDataAdapter da;
DataSet ds;
OleDbCommandBuilder cb;
DataRow row;

private void Form1_Load(object sender, EventArgs e)
{
conn = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source = C:\\test.mdb");
da = new OleDbDataAdapter("select* from [user]", conn);
ds = new DataSet();

conn.Open();
da.Fill(ds, "user");
conn.Close();
}

private void button1_Click(object sender, EventArgs e)
{
cb = new OleDbCommandBuilder(da);
row = ds.Tables["user"].Rows[0];

row[3] = "hello";

da.Update(ds, "user");
}
}


When I do this, I get a new error,
Syntax error in UPDATE statement
. I did a lot of internet reading but none seems to address this. They all have used
Update
command differently. I know only this way. What's wrong in my code? Especially since this worked before. Or isn't this way of updating a proper technique? Please help me with the code and not technical terms which I don't follow. Or any other procedure to update in ms access?

Thanks.

Answer

I've never tried to access an Access database with a .NET DataSet before, but I think you could replace the code in button1_Click with something like this:

private void button1_Click(object sender, EventArgs e)
{
   conn.Open();

   string query = "UPDATE [user] SET [columnname] = ? WHERE id = ?";
   var accessUpdateCommand = new OleDbCommand(query, conn);
   accessUpdateCommand.Parameters.AddWithValue("columnname", "hello");
   accessUpdateCommand.Parameters.AddWithValue("id", 123); // Replace "123" with the variable where your ID is stored. Maybe row[0] ?
   da.UpdateCommand = accessUpdateCommand;
   da.UpdateCommand.ExecuteNonQuery();

   conn.Close();
}

Yes, I know you'd be losing some of the benefits of the DataSet, but research suggests that the regular OleDbDataAdapter.Update function doesn't play well with Access.

Comments