Click here to Skip to main content
15,921,028 members
Please Sign up or sign in to vote.
4.33/5 (2 votes)
See more:
I am using ms access as a database and getting error in update statement.
Can somebody help me out.?
C#
String userid = username.Text;
                 String password = pwd.Text;
                String date1=date12.Text;
                String report1 = report.Text;
                 OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\divyapc\My Documents\CRPDatabase.accdb;
     Jet OLEDB:Database Password=divya6");
                 MessageBox.Show(userid);
                 OleDbCommand cmmd = new OleDbCommand("UPDATE trialtable SET userid=@userid, password = @password,testreport= @testreport,date12= @date12 where userid = @userid)", conn);
                 cmmd.Parameters.Add("@USERID", OleDbType.VarChar).Value = userid;
                 cmmd.Parameters.Add("@PASSWORD", OleDbType.VarChar).Value = password;
                 cmmd.Parameters.Add("@TESTREPORT", OleDbType.VarChar).Value = report1;
                 cmmd.Parameters.Add("@DATE12", OleDbType.VarChar).Value = date1;
                 conn.Open();

                  int temp = cmmd.ExecuteNonQuery();
                     if (temp > 0)
                     {
                         username.Text = null;
                         pwd.Text = null;
                         MessageBox.Show("Record Successfuly updated");
                     }
                     else
                     {
                         MessageBox.Show("Record Fail to Added");
                     }

                     conn.Close();
                 }
Posted
Updated 17-Feb-14 0:16am
v2
Comments
Krunal Rohit 17-Feb-14 6:16am    
what error ?
Diya Bh. 17-Feb-14 6:19am    
syntax error in update statement
R-a-v-i-k-u-m-a-r 17-Feb-14 6:17am    
What error you are getting?

Solution 1 seems a good start.

Moreover, if userid column in your database is primary key, I doubt you will be able to update it.

Maybe you can try:
C#
OleDbCommand cmmd = new OleDbCommand("UPDATE trialtable SET password = @password, testreport = @testreport, date12 = @date12 where userid = @userid", conn);


Hope this helps.
 
Share this answer
 
v3
Comments
Diya Bh. 17-Feb-14 6:25am    
its just the trial page for update statement.
There might be error in your naming conventions of you query parameters. Use following one instead,
SQL
cmmd.Parameters.Add("@userid", OleDbType.VarChar).Value = userid;
cmmd.Parameters.Add("@password", OleDbType.VarChar).Value = password;
cmmd.Parameters.Add("@testreport", OleDbType.VarChar).Value = report1;
cmmd.Parameters.Add("@date12", OleDbType.VarChar).Value = date1;

-KR
 
Share this answer
 
Comments
Diya Bh. 17-Feb-14 6:21am    
again it is giving the same error.
Krunal Rohit 17-Feb-14 6:23am    
remove one bracket, which is at end of your query.
-KR
Diya Bh. 17-Feb-14 6:26am    
I removed ...but still the same error.
Krunal Rohit 17-Feb-14 6:27am    
can you paste the error here ?
here is the solution:

String userid = username.Text;
                String password = pwd.Text;
               String date1=date12.Text;
               String report1 = report.Text;
                OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\divyapc\My Documents\CRPDatabase.accdb;
    Jet OLEDB:Database Password=divya6");
                MessageBox.Show(userid);
                OleDbCommand cmmd = new OleDbCommand("UPDATE trialtable SET userid=@userid, [password] = @password,testreport= @report1,date12= @date1 where userid = @userid");
                cmmd.Parameters.Add("@USERID", OleDbType.VarChar).Value = userid;
                cmmd.Parameters.Add("@PASSWORD", OleDbType.VarChar).Value = password;
                cmmd.Parameters.Add("@TESTREPORT", OleDbType.VarChar).Value = report1;
                cmmd.Parameters.Add("@DATE12", OleDbType.VarChar).Value = date1;
                conn.Open();
                cmmd.Connection = conn;

                 int temp = cmmd.ExecuteNonQuery();
                    if (temp > 0)
                    {
                        username.Text = null;
                        pwd.Text = null;
                        MessageBox.Show("Record Successfuly updated");
                    }
                    else
                    {
                        MessageBox.Show("Record Fail to Added");
                    }

                    conn.Close();
                }
 
Share this answer
 
Comments
agent_kruger 18-Feb-14 23:09pm    
yes, "password" is predefined word in the database.
there's an unnecessary bracket before the double quotes at
...@userid)"
i.e: it should be
@userid"
 
Share this answer
 
Comments
Diya Bh. 17-Feb-14 6:23am    
still the same error..
SQL
OleDbCommand cmmd = new OleDbCommand("UPDATE trialtable SET userid=@userid, password = @password,testreport= @testreport,date12= @date12 where userid = @userid)", conn);



Error in your query stmt
SQL
OleDbCommand cmmd = new OleDbCommand("UPDATE trialtable SET userid=@userid, password = @password,testreport= @testreport,date12= @date12 where userid = @userid", conn);
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900