Get data from two related table and update one table... Just need some help..... RSS

• • Reply
  

  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>

  
  
  

• • Reply
  

  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!

  Please "Marks As Answer" if any answer helped you out!
  ~~! FIREWALL !~~
• • Reply
  

  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.

• • Reply
  

  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

  yguyon

  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.

  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.

   

  • Marked as answer by yguyon on Dec 05, 2012 01:07 AM