How to Update All Rows of Table SQL

Question

Below is my code:

@{

    Layout = "/_SiteLayout.cshtml";
    var db = Database.Open("MyDatabase");

    var query = "SELECT * FROM Team";
    var Teams = db.Query(query);
 }

    <form>
        <table>
        <tr>
            <td>Team Name</td>
            <td>Played</td>
            <td>Points</td>
        </tr>
  @{  foreach(var Team in Teams){
        <tr>
            <td>@Team.TeamName</td>
            <td><input type="text" value="@Team.Played" name="Played"/></td>
            <td><input type="text" value="@Team.Points" name="Points"/></td>
        </tr>
    }    
   }        
        </table>
    </form>

This is the result:

enter image description here

So what I want to do is update my whole table.

What is the SQL query to do this? I want to update Points and Games Played in my database for all teams once the form is posted.

What language is this? Javascript? – Sam Jan 05 '13 at 10:47 — Sam, Jan 05 '13 at 10:47
It is Razor View Engine. "ASP.NET" – Dawood Awan Jan 05 '13 at 18:16 — Dawood Awan, Jan 05 '13 at 18:16

score 0 · Answer 1 · edited May 23 '17 at 11:56

0

I don't actually understand what exactly you are trying to achieve (update your whole table with what?), but here is some information you might find useful:

SQL Update Tutorial, SQL Update, Update from Select

edited May 23 '17 at 11:56

Community

1
1

answered Jan 05 '13 at 10:46

Sam

1,564
4
23
37

I want to update the rows. for example A user changes Points for Arsenal then the Database should update that for Arsenal. similarly if the user updates all rows then the script should update all rows. So as i do this: var string = Request[Team.Points] it is the same for all. I need a unique value to Request["HERE"] – Dawood Awan Jan 05 '13 at 18:16

score 0 · Accepted Answer · answered Jan 05 '13 at 18:19

Following is My Solution. Anyone have an efficient Solution?

@{

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

var query = "SELECT * FROM Team";
var Teams = db.Query(query);
var InsertQuery = "";
if(IsPost){

   foreach(var Team in Teams){
       var Points = Request[Team.TeamName];
       var TeamId = Team.TeamId.ToString();
       var Played = Request[TeamId];

       var executeQueryString="UPDATE Team Set Points=@0, Played=@1 WHERE TeamId=@2";
       db.Execute(executeQueryString, Points, Played, Team.TeamId);

   }
   Response.Redirect("~/UpdateTable.cshtml");

}

}

<br /><br />


 <form  action="" method="post">
        <table>
        <tr>
            <td><h5>Team Name</h5></td>
            <td><h5>Played</h5></td>
            <td><h5>Points</h5></td>
        </tr>
  @{  foreach(var Team in Teams){
        <tr>
            <td>@Team.TeamName</td>
            <td><input type="text" value="@Team.Played" name="@Team.TeamId"/></td>
            <td><input type="text" value="@Team.Points" name="@Team.TeamName"/></td>
        </tr>
    }    
   }

Unless the version of SQL that webmatrix 2 uses supports Table Valued Parameters I'm pretty sure that it. — Conrad Frix, Jan 07 '13 at 23:28

How to Update All Rows of Table SQL

2 Answers2