locked
Access Database Doesn`t Update Records anymore RRS feed

  • Question

  • User-1909396868 posted

    Hey Guys, 

    i am facing a problem with access database .. i have employee update page which was working fine and updating records but now whenever i try to update any record it does nothing .. which is strange as i didn`t change any permissions or anything. 

    Any ideas will help :) 

    Thanks and Regards

    Mahmoud

    Tuesday, May 29, 2012 3:02 PM

Answers

  • User-1199946673 posted

    thats my update method is there anything wrong?

    There's nothing wrong with your update method, the only problem is the order of the parameters. OleDb parameters are not recognized by name, but by their position. The order you add the parameters tot the parameter collection should be the same as the order the parameters first appear in the command. In this case, empID is the last parameter in the command (in the WHERE Clause), so it should also be the last parameter that you add in the parametercollection, however, you add this parameter first....

    By the way, using the AddWithValue method makes you code much shorter and readable

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 3, 2012 1:57 PM

All replies

  • User-851967432 posted

    What did change? ...before you say "nothing" check windows updates, table schemas, linked tables, etc...

    Tuesday, May 29, 2012 3:04 PM
  • User-1909396868 posted

    Hey Adam,

    Thanks for your reply

    I looked for the windows updates .. nothing changed relating to the access database engine ... i checked the update method it works fine and the update command is executed but no changes in the database.

    Tuesday, May 29, 2012 4:23 PM
  • User-851967432 posted

    Has the PK changed or is there a PK on the table?

    Wednesday, May 30, 2012 7:39 AM
  • User-1909396868 posted

    No nothing changed in the primary key ... i debugged my update method and checked my update statement .. all values are set .. and the method finishes the update .. but no changes in the database .. it was working fine couple of weeks ago .. really don`t know what is the problem ... 

    Wednesday, May 30, 2012 5:39 PM
  • User3866881 posted

    Access Database Doesn`t Update Records anymore

    Hard to say according to your current info……Maybe I think you should offer us more info about that——

    1)What's the diagram of your Access?

    2)What's the primary key?

    3)What's the codes of aspx?

    4)What's the codes of cs?

    PS:If possible,please direct send your proj(Demo version)to v-dedong@microsoft.com for more analyze……I'll reply to you if I'm free:-)But please notice it that your email's title must be entitled with the subject of your current issue……

    Reguards!

    Wednesday, May 30, 2012 9:13 PM
  • User-1909396868 posted
    Sorry for the late reply 
    Primary key is the EmpID
    //Update Employee Method
            public void UpdateEmployee(int EMP_ID,string Name,string Nationality,string Leaving_date,string Entry_date,string QID,string Pass_No,string Pass_ExpDate,string Permit_No,string Permit_ExpDate,string Status,string Sponsor,string Comp,string Salary,string MobQ,string MobV)
            {
    
                Con.Open();
                string UpdateEmp = "Update Employees SET empName=@EMPNAME,empNationality=@EMPNAT,empPassportNo=@EMPPASSNO,empPassportExpDate=@EMPPASSDATE,empResidentPermitNo=@EMPPERMITNO,empResidentExpDate=@EMPPERMDATE,empLeavingDate=@EMPLEAVE,empEntryDate=@EMPENTER,empSponsore=@EMPSPONSOR,empStatus=@EMPSTATUS,empMobileNo=@EMPMOBILEQ,empMobileNoVod=@EMPMOBILEV,empQID=@EMPQID,empSalary=@EMPSAL,compName=@COMPNAME WHERE empID = @EMPID";
                OleDbCommand Upd = new OleDbCommand(UpdateEmp, Con);
                OleDbParameter EMPID = new OleDbParameter("@EMPID", OleDbType.Numeric);
                EMPID.Value = EMP_ID;
                Upd.Parameters.Add(EMPID);
                OleDbParameter EMPNAME = new OleDbParameter("@EMPNAME", OleDbType.VarChar);
                EMPNAME.Value = Name;
                Upd.Parameters.Add(EMPNAME);
                OleDbParameter EMPNAT = new OleDbParameter("@EMPNAT", OleDbType.VarChar);
                EMPNAT.Value = Nationality;
                Upd.Parameters.Add(EMPNAT);
                OleDbParameter EMPPASSNO = new OleDbParameter("@EMPPASSNO", OleDbType.VarChar);
                EMPPASSNO.Value = Pass_No;
                Upd.Parameters.Add(EMPPASSNO);
                OleDbParameter EMPPASSDATE = new OleDbParameter("@EMPPASSDATE", OleDbType.VarChar);
                EMPPASSDATE.Value = Pass_ExpDate;
                Upd.Parameters.Add(EMPPASSDATE);
                OleDbParameter EMPPERMITNO = new OleDbParameter("@EMPPERMITNO", OleDbType.VarChar);
                EMPPERMITNO.Value = Permit_No;
                Upd.Parameters.Add(EMPPERMITNO);
                OleDbParameter EMPPERMDATE = new OleDbParameter("@EMPPERMDATE", OleDbType.VarChar);
                EMPPERMDATE.Value = Permit_ExpDate;
                Upd.Parameters.Add(EMPPERMDATE);
                OleDbParameter EMPLEAVE = new OleDbParameter("@EMPLEAVE", OleDbType.VarChar);
                EMPLEAVE.Value = Leaving_date;
                Upd.Parameters.Add(EMPLEAVE);
                OleDbParameter EMPENTER = new OleDbParameter("@EMPENTER", OleDbType.VarChar);
                EMPENTER.Value = Entry_date;
                Upd.Parameters.Add(EMPENTER);
                OleDbParameter EMPSPONSOR = new OleDbParameter("@EMPSPONSOR", OleDbType.VarChar);
                EMPSPONSOR.Value = Sponsor;
                Upd.Parameters.Add(EMPSPONSOR);
                OleDbParameter EMPSTATUS = new OleDbParameter("@EMPSTATUS", OleDbType.VarChar);
                EMPSTATUS.Value = Status;
                Upd.Parameters.Add(EMPSTATUS);
                OleDbParameter EMPMOBILEQ = new OleDbParameter("@EMPMOBILEQ", OleDbType.VarChar);
                EMPMOBILEQ.Value = MobQ;
                Upd.Parameters.Add(EMPMOBILEQ);
                OleDbParameter EMPMOBILEV = new OleDbParameter("@EMPMOBILEV", OleDbType.VarChar);
                EMPMOBILEV.Value = MobV;
                Upd.Parameters.Add(EMPMOBILEV);
                OleDbParameter EMPQID = new OleDbParameter("@EMPQID", OleDbType.VarChar);
                EMPQID.Value = QID;
                Upd.Parameters.Add(EMPQID);
                OleDbParameter EMPSAL = new OleDbParameter("@EMPSAL", OleDbType.VarChar);
                EMPSAL.Value = Salary;
                Upd.Parameters.Add(EMPSAL);
                OleDbParameter COMPNAME = new OleDbParameter("@COMPNAME", OleDbType.VarChar);
                COMPNAME.Value = Comp;
                Upd.Parameters.Add(COMPNAME);
    
                Upd.ExecuteNonQuery();
                Upd.Dispose();
                Con.Close();
    
            }

    thats my update method is there anything wrong?

    Sunday, June 3, 2012 1:12 PM
  • User-1199946673 posted

    thats my update method is there anything wrong?

    There's nothing wrong with your update method, the only problem is the order of the parameters. OleDb parameters are not recognized by name, but by their position. The order you add the parameters tot the parameter collection should be the same as the order the parameters first appear in the command. In this case, empID is the last parameter in the command (in the WHERE Clause), so it should also be the last parameter that you add in the parametercollection, however, you add this parameter first....

    By the way, using the AddWithValue method makes you code much shorter and readable

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 3, 2012 1:57 PM
  • User-1909396868 posted

    Thank you So Much Hans that did it :) .. and thanks for the tip too :) ... thanks for all the help guys ... 

    Sunday, June 3, 2012 2:05 PM