I have the following table Test with the column listed bellow:
FileId - Int (Primary Key)
Name - VarChar
DisplayId - Int (Foreign Key that is listed in Display table).
1 - Kim - null
2- steve - null
3 - Mike - null
Display - Table
Id - Int (Primary Key)
Namex - VarChar
1 - white
2 - Black
I'm trying to create the following chtml page based from the table.
I would like to display all the data from the Test table such as below:
Kim ----------- and next to each name have a drop down that allow you to select white or black and once
you click Submit the column DisplayId will be populated with either 1 or 2 based from their selection from either white or black.
Steve ---------
Mike ----------
And have a button that say Submit.
var db = Database.Open("Classifieds");
if (IsPost && !Request["buttonDelete"].IsEmpty()){
pictureId = Request["Id"];
var updateSql = "UPDATE Test Set DisplayId =@0 from Test JOIN Display ON Test.DisplayId = Display.Id Where Id =@1";
db.Execute(updateSql, Request["Namex"], Request["id"]);
Response.Redirect("~/AdminGallery.cshtml");
}
var sql2 = "SELECT * from Display";
var ty = db.Query(sql2);
<form method="post">
<legend>Create a test</legend>
<div><label for="Name">Name:</label>
</div>
<div>
</div>
<div>
<select name="Id">
<option value="0">--Select to display</option>
@foreach(var cat in ty){
<option value="@cat.Id"/>@cat.Namex<br/>
}
</select>
</div>
<div>
<p><input type="submit" name="buttonDelete" value="Delete Picture" /></p></div>
</form>
I would like to display all the data from the Test table such as below:
Kim ----------- and next to each name have a drop down that allow you to select white or black and once
you click Submit the column DisplayId will be populated with either 1 or 2 based from their selection from either white or black.
It's not a simple task the one you want to accomplish.
Instead, if you are a beginner developer, maybe the best way is to learn
this tutorial (chapter Updating Data in a Database) and begin from simpler tasks.
Marked as answer by yguyon on Dec 05, 2012 01:07 AM
yguyon
Member
7 Points
23 Posts
Get data from two related table and update one table... Just need some help.....
Dec 03, 2012 05:21 PM|LINK
I have the following table Test with the column listed bellow:
FileId - Int (Primary Key)
Name - VarChar
DisplayId - Int (Foreign Key that is listed in Display table).
1 - Kim - null
2- steve - null
3 - Mike - null
Display - Table
Id - Int (Primary Key)
Namex - VarChar
1 - white
2 - Black
I'm trying to create the following chtml page based from the table.
I would like to display all the data from the Test table such as below:
Kim ----------- and next to each name have a drop down that allow you to select white or black and once
you click Submit the column DisplayId will be populated with either 1 or 2 based from their selection from either white or black.
Steve ---------
Mike ----------
And have a button that say Submit.
var db = Database.Open("Classifieds"); if (IsPost && !Request["buttonDelete"].IsEmpty()){ pictureId = Request["Id"]; var updateSql = "UPDATE Test Set DisplayId =@0 from Test JOIN Display ON Test.DisplayId = Display.Id Where Id =@1"; db.Execute(updateSql, Request["Namex"], Request["id"]); Response.Redirect("~/AdminGallery.cshtml"); } var sql2 = "SELECT * from Display"; var ty = db.Query(sql2); <form method="post"> <legend>Create a test</legend> <div><label for="Name">Name:</label> </div> <div> </div> <div> <select name="Id"> <option value="0">--Select to display</option> @foreach(var cat in ty){ <option value="@cat.Id"/>@cat.Namex<br/> } </select> </div> <div> <p><input type="submit" name="buttonDelete" value="Delete Picture" /></p></div> </form>Afzaal.Ahmad...
Contributor
2661 Points
1040 Posts
Re: Get data from two related table and update one table... Just need some help.....
Dec 03, 2012 05:33 PM|LINK
If you want to get the data from the tables You might use INNER JOIN or the JOIN if you are not using SQLCE
Now the thing you want to do is to update the values in the database table. For that you will need the update clause!
Now to update this. Please try to use the cariables.
Also in the Query you should use variables Not the values.
If you use the JOIN statement than you will be able to get the values from the tables (both) in just one query.
http://www.w3schools.com/sql/sql_join_inner.asp
Hope this helps!
~~! FIREWALL !~~
wavemaster
Participant
1295 Points
1131 Posts
Re: Get data from two related table and update one table... Just need some help.....
Dec 04, 2012 06:06 PM|LINK
Maybe there is something you can use in this tutorial.
GmGregori
Contributor
5470 Points
737 Posts
Re: Get data from two related table and update one table... Just need some help.....
Dec 04, 2012 10:04 PM|LINK
It's not a simple task the one you want to accomplish.
If you are an intermediate developer you could look at this new Mike Brind's article: Inline Editing With The WebGrid.
Instead, if you are a beginner developer, maybe the best way is to learn this tutorial (chapter Updating Data in a Database) and begin from simpler tasks.