Updating Multiple rows in MS Access c#

Question

I am Trying to Update multiple rows in a MS Access DB But every time it gives ERROR IN UPDATE SYNTAX here's my code

cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source =C:\\Users\\Kalpesh\\Desktop\\Entry.accdb");
sqlstr = "Update Members SET Name=@Name, MobileNo=@MobileNo, Password=@Password, IDType=@IDType, IDNo=@IDNo, AmountPaid=@AmountPaid, Address=@Address, Reg Date = "+DateTime.Now.ToShortDateString()+" WHERE UserName = '" + comboBox4.SelectedItem.ToString() + "'",cn);
cmd = new OleDbCommand(sqlstr, cn);
cmd.Parameters.AddWithValue("@Name", textBox19.Text.ToString());
cmd.Parameters.AddWithValue("@MobileNo", textBox14.Text.ToString());
cmd.Parameters.AddWithValue("@Password", textBox17.Text.ToString());
cmd.Parameters.AddWithValue("@IDType", comboBox5.SelectedItem.ToString());
cmd.Parameters.AddWithValue("@IDNo", textBox11.Text.ToString());
cmd.Parameters.AddWithValue("@AmountPaid", textBox16.Text.ToString());
cmd.Parameters.AddWithValue("@Address", richTextBox3.Text.ToString());

cn.Open();
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
MessageBox.Show("Record Updated");
cn.Close();

I am not an expert in Access but Reg Date syntax looks wrong. First, there is a space in identifier. Second, you are probably missing some punctuation around the date literal. — Pavel Zhuravlev
– Pavel Zhuravlev, Commented Apr 17, 2014 at 12:09
Update Working Code sqlstr = "Update Members SET [Name]=@Name, MobileNo=@MobileNo, [Password]=@Password, IDType=@IDType, IDNo=@IDNo, AmountPaid=@AmountPaid, Address=@Address, [Reg Date] = '"+DateTime.Now.ToShortDateString()+"' WHERE UserName = '" + comboBox4.SelectedItem.ToString() + "'"; — Kalpesh
– Kalpesh, Commented Apr 17, 2014 at 12:10

Soner Gönül · Accepted Answer · 2014-04-17 12:13:16Z

4

Name and Password are reserved keywords in MS Access.

If a reserved word is already in use, you can avoid error messages by surrounding each occurrence of the word with brackets ([ ]). However, the best solution is to change the name to a nonreserved word.

You need to use Reg Date with square brackets like [Reg Date] also since it has space. Parameterized your Reg Date and UserName column values as you did for other columns. Like;

... [Reg Date] = @reg WHERE UserName = @user ...
cmd.Parameters.AddWithValue("@reg", DateTime.Now.ToShortDateString());
cmd.Parameters.AddWithValue("@user", comboBox4.SelectedItem.ToString());

Also use using statement to dispose your OleDbConnection and OleDbCommand.

string conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =C:\\Users\\Kalpesh\\Desktop\\Entry.accdb";
using(OleDbConnection cn = new OleDbConnection(conString))
using(OleDbCommand cmd = cn.CreateCommand())
{
  // Do your work..
}

edited Apr 17, 2014 at 12:13

answered Apr 17, 2014 at 12:02

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kalpesh Over a year ago

Hell Yes You were right it adding [] fro name & password & single quot for datetime worked

Tamim Al Manaseer · Accepted Answer · 2014-04-17 12:04:01Z

0

Since there is no clear error message about the syntax problem, I would guess its the lack of single quotes around the date, try this:

RegDate = '"+DateTime.Now.ToShortDateString()+"'

answered Apr 17, 2014 at 12:04

Tamim Al Manaseer

3,7543 gold badges27 silver badges34 bronze badges

Comments

Shell · Accepted Answer · 2014-04-17 12:07:57Z

0

Problem is here

 "Reg Date = "+DateTime.Now.ToShortDateString()+"

Try this

 "[Reg Date] = #" + DateTime.Now.ToShortDateString() + "# "

answered Apr 17, 2014 at 12:07

Shell

6,86911 gold badges43 silver badges75 bronze badges

Collectives™ on Stack Overflow

Updating Multiple rows in MS Access c#

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related