Update statement not working

Reply

Jusdavis

None

0 Points

5 Posts

Update statement not working

Jun 24, 2013 09:50 AM|Jusdavis|LINK

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>

Reply

Ajay.puram

Member

60 Points

26 Posts

Re: Update statement not working

Jun 24, 2013 12:39 PM|Ajay.puram|LINK

Hi ,

Could you please elaborate whats the issue..;)

Cheeers,

AJ

Reply

Rion William...

All-Star

114593 Points

18503 Posts

MVP

Re: Update statement not working

Jun 24, 2013 12:46 PM|Rion Williams|LINK

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?)

Blog | Twitter | Stack Overflow | LinkedIn

Reply

DonnieS

Member

99 Points

275 Posts

Re: Update statement not working

Jun 24, 2013 12:58 PM|DonnieS|LINK

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"); }

Reply

Jusdavis

None

0 Points

5 Posts

Re: Update statement not working

Jun 25, 2013 10:22 AM|Jusdavis|LINK

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:

Reply

Jusdavis

None

0 Points

5 Posts

Re: Update statement not working

Jun 25, 2013 10:40 AM|Jusdavis|LINK

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.

Reply

DonnieS

Member

99 Points

275 Posts

Re: Update statement not working

Jun 25, 2013 11:14 AM|DonnieS|LINK

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)

Reply

dblaire

Member

690 Points

185 Posts

Re: Update statement not working

Jun 25, 2013 04:13 PM|dblaire|LINK

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

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

Update statement not working[Answered] RSS

7 replies

Shortcuts

Related Links

Update statement not working

Re: Update statement not working

Re: Update statement not working

Re: Update statement not working

Re: Update statement not working

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

Re: Update statement not working

Re: Update statement not working

Re: Update statement not working