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");
开发者_JAVA技巧 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.
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.
student = txtStudent.Text;
age = txtAge.Text;
address = txtAddress.Text;
section = CBSection.Text;
string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\STI.accdb";
string queryString = "UPDATE Details set StudentName='"+student+"',Age='"+age+"',Address='"+address+"' where Section/Course="+section+"";
OleDbConnection connection = new OleDbConnection(ConnectionString);
OleDbCommand command = new OleDbCommand();
command.CommandType = CommandType.Text;
command.CommandText = queryString;
command.Connection = connection;
connection.Open();
{
try
{
command.ExecuteNonQuery();
MessageBox.Show("StudentName : " + student + "\nAge : " + age + "\nAddress : " + address + "\nSection : " + section + "\nHas been successfully Enrolled");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}*strong text*
精彩评论