locked
Update statement not working RRS feed

  • Question

  • User-1676023341 posted

    Hi there I am tottaly baffeled with this. I have a page that seems to work but just doesnt actually update the row:

    I get no error. Below is the source code - can somebody please help??

    @{
    if (!WebSecurity.IsAuthenticated) {
            Response.Redirect("~/Account/Login?returnUrl="
                + Request.Url.LocalPath);
        }    
     
        Layout = "~/_SiteLayout.cshtml";
        Page.Title = "Food Palette Portal";
        
        var ID = "";
        var Type = "";
        var Name = "";
        var Season = "";
        var Colour = "";
     
        if(!IsPost){
            if(!Request.QueryString["ID"].IsEmpty() && Request.QueryString["ID"].IsInt()) {
                ID = Request.QueryString["ID"];
                var db = Database.Open("FoodPalette");
                var dbCommand = "SELECT * FROM FruitVeg WHERE ID = @0";
                var row = db.QuerySingle(dbCommand, ID);
     
                if(row != null) {
                    //ID = row.ID.ToString();
                    Type = row.Type;
                    Name = row.Name;
                    Season = row.Season;
                    Colour = row.Colour;
                     }
                else{
                    Validation.AddFormError("No Fruit/Veg was selected.");
                    // Use the following line instead for versions of ASP.NET Web Pages 2 earlier
                    // than the RC release.
                    //ModelState.AddFormError("No movie was selected.");
                }
            }
            else{
                Validation.AddFormError("No Fruit/Veg was selected.");
                // Use the following line instead for versions of ASP.NET Web Pages 2 earlier
                // than the RC release.
                //ModelState.AddFormError("No movie was selected.");
            }
        }
     
        if(IsPost){
            Validation.RequireField("Type", "You must enter a Type");
            Validation.RequireField("Name", "Name is required");
            Validation.RequireField("Season", "No Season was submitted!");
            Validation.RequireField("Colour", "No Colour was submitted!");
     
            ID = Request.Form["ID"];
            Type = Request.Form["Type"];
            Name = Request.Form["Name"];
            Season = Request.Form["Season"];
            Colour = Request.Form["Colour"];
                    
            if(Validation.IsValid()){
                var db = Database.Open("FoodPalette");
                var updateCommand = "UPDATE FruitVeg SET Type=@1, Name=@2, Season=@3, Colour=@4 WHERE ID=@0";
                db.Execute(updateCommand, ID, Type, Name, Season, Colour);
                //Response.Redirect("~/Account/ViewFruitVeg");
            }
        }
    }
     
    <!DOCTYPE html>
    <html>
      <head>
       <meta charset="utf-8" />
     
          <a href="~/Account/ViewJobs">Back to view Fruit/Vegetables</a>
         <p></p>
       <title>Edit Fruit/Vegetables Details</title>
        <style>
          .validation-summary-errors{
            border:2px dashed red;
            color:red;
            font-weight:bold;
            margin:12px;
          }
        </style>
      </head>
    </head>
    <body>
      <h1>Edit Fruit/Vegetables Details</h1>
        @Html.ValidationSummary()
      <form method="post">
    <table>
        <fieldset>
          <legend>Job Details</legend>
    <tr><td>
          <p><label for="Type">Type:</label>
    </td><td>
             <input type="text" name="Type" value="@Type" /></p>
    <tr><td>
          <p><label for="Name">Name:</label>
    </td><td>
             <input type="text" name="Name" value="@Name" /></p>
    <tr><td>
          <p><label for="Season">Season:</label>
    </td><td>
             <input type="text" name="Season" value="@Season" /></p>
        <tr><td>
          <p><label for="Colour">Colour:</label>
    </td><td>
             <input type="text" name="Colour" value="@Colour" /></p>
    <tr><td>
        
          <input type="hidden" name="FruitVegID" value="@ID" />
     
          <p><input type="submit" name="buttonSubmit" value="Submit Changes" /></p>
     
        </table>
        </fieldset>
     
      </form>
      <p><a href="~/Account/ViewFruitVeg">Return to Fruit/Veg listings</a></p>
    </body>
    </html>

    Monday, June 24, 2013 9:50 AM

Answers

  • User281315223 posted

    Have you tried placing a breakpoint within your code to ensure that the contents of your Validation.IsValid block is being executed? 

    if(Validation.IsValid()){
                var db = Database.Open("FoodPalette");
                var updateCommand = "UPDATE FruitVeg SET Type=@1, Name=@2, Season=@3, Colour=@4 WHERE ID=@0";
                db.Execute(updateCommand, ID, Type, Name, Season, Colour);
                //Response.Redirect("~/Account/ViewFruitVeg");
    }

    Your syntax aside from that appears correct - I would just make sure that it is actually being executed properly. (Is the correct database being targeted / possible connection string issues?)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 24, 2013 12:46 PM
  • User483055775 posted

    I know that trying to update your ID field (I assume thats your unique key into your table) is NOT going to be allowed.  Other than that, you update command line likes just like mine.  To be precise, in your db.execut(updatecommand.... remove the "ID, " from that line.

     

    DonnieS

     

     

     

                     if(Validation.IsValid()){            

     var db = Database.Open("FoodPalette");            

    var updateCommand = "UPDATE FruitVeg SET Type=@1, Name=@2, Season=@3, Colour=@4 WHERE ID=@0";            

    db.Execute(updateCommand, ID, Type, Name, Season, Colour);             //Response.Redirect("~/Account/ViewFruitVeg");         }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 24, 2013 12:58 PM
  • User-1676023341 posted

    I fixed it by addingin the ID into theform as follows:

    <tr><td>
          <p><label for="ID">ID:</label>
    </td><td>
             <input type="text" name="ID" value="@ID" readonly/></p>
    <tr><td>
    This seemed to fix the problem but now I have to have the ID visible which was not my intention...anyway beggers cant be choosers.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 25, 2013 10:40 AM

All replies

  • User741437230 posted

    Hi  , 

    Could you please elaborate whats the issue..;)

    Cheeers,

    AJ

    Monday, June 24, 2013 12:39 PM
  • User281315223 posted

    Have you tried placing a breakpoint within your code to ensure that the contents of your Validation.IsValid block is being executed? 

    if(Validation.IsValid()){
                var db = Database.Open("FoodPalette");
                var updateCommand = "UPDATE FruitVeg SET Type=@1, Name=@2, Season=@3, Colour=@4 WHERE ID=@0";
                db.Execute(updateCommand, ID, Type, Name, Season, Colour);
                //Response.Redirect("~/Account/ViewFruitVeg");
    }

    Your syntax aside from that appears correct - I would just make sure that it is actually being executed properly. (Is the correct database being targeted / possible connection string issues?)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 24, 2013 12:46 PM
  • User483055775 posted

    I know that trying to update your ID field (I assume thats your unique key into your table) is NOT going to be allowed.  Other than that, you update command line likes just like mine.  To be precise, in your db.execut(updatecommand.... remove the "ID, " from that line.

     

    DonnieS

     

     

     

                     if(Validation.IsValid()){            

     var db = Database.Open("FoodPalette");            

    var updateCommand = "UPDATE FruitVeg SET Type=@1, Name=@2, Season=@3, Colour=@4 WHERE ID=@0";            

    db.Execute(updateCommand, ID, Type, Name, Season, Colour);             //Response.Redirect("~/Account/ViewFruitVeg");         }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 24, 2013 12:58 PM
  • User-1676023341 posted

    Thanks DonnieS, but now I am getting this:

    An SqlCeParameter with ParameterName '4' is not contained by this SqlCeParameterCollection.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

    Exception Details: System.IndexOutOfRangeException: An SqlCeParameter with ParameterName '4' is not contained by this SqlCeParameterCollection.

    Source Error: 

    Line 58:             var db = Database.Open("FoodPalette");
    Line 59:             var updateCommand = "UPDATE FruitVeg SET Type=@1, Name=@2, Season=@3, Colour=@4 WHERE ID=@0";
    Line 60: db.Execute(updateCommand, Type, Name, Season, Colour); Line 61:             Response.Redirect("~/Account/ViewFruitVeg");
    Line 62: }


    Source File: c:\Users\Justin\Documents\My Web Sites\Food Palette\Account\EditFruitVeg.cshtml    Line: 60 

    Stack Trace: 

    Tuesday, June 25, 2013 10:22 AM
  • User-1676023341 posted

    I fixed it by addingin the ID into theform as follows:

    <tr><td>
          <p><label for="ID">ID:</label>
    </td><td>
             <input type="text" name="ID" value="@ID" readonly/></p>
    <tr><td>
    This seemed to fix the problem but now I have to have the ID visible which was not my intention...anyway beggers cant be choosers.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 25, 2013 10:40 AM
  • User483055775 posted

    What I see here is you are telling the system to prepare  for 5 parameters ( in the line Update UPDATE FruitVeg SET Type=@1, Name=@2, Season=@3, Colour=@4 WHERE ID=@0, (items 0,1,2,3,4) but only providing 4 (Type, Name, Season, Colour) - the execute line should be

    db.Execute(updatecommand, Type, Name, Season, Colour, ID)

     

     

    Tuesday, June 25, 2013 11:14 AM
  • User-1980594115 posted

    Have the form field "ID" as a hidden field and eliminate the label:

    <input type="hidden" name="ID" value="@ID"/>

    Tuesday, June 25, 2013 4:13 PM