locked
Link from index page to details page displays all record not the one i select. RRS feed

  • Question

  • User1650462628 posted

    Heyo newbie needs help

    I have an index page for a list of records (at this point only 2 records) from a database which has a link to a generic details page. My problem is when I click the link on 1 of the records I am redirected to the details page however all records are displayed.

    <a href="viewCRdetails.cshtml?id=@row.cr_issue_no">@row.cr_title</a>

    Do I need to add a where clause in the SQL statement?

    Wednesday, August 6, 2014 9:10 PM

Answers

  • User895691971 posted

    Obviously, SQL statement returns the result that you ask it to. 

    SELECT * FROM table_name

    ...would return all of the columns. That is the job of the * and since there is no WHERE clause, this would trigger SQL to include all of the rows too. In other words, entire table to be extracted from the Database. 

    Since you need to only 1 record to be shown from the 2 results in the Database. You can/must add a WHERE clause to your query to extract result depending on a condition. For example 

    SELECT * FROM table_name WHERE column_name = value

    This way, you add a condition to the SQL statement to select all columns (*) but only from the rows that have the column_name the value provided in the query.

    @{
        // Database variable initialization, connection
        var db = Database.Open("ISSUE_XXX");
        // sqlQuery variable string, note the usage of "@0" and
        // the usage of parameter Request["id"] in the data variable. 
        var sqlQ = "SELECT * FROM XXXX_REQUEST where cr_issue_no = @0";
        // extraction of the Data. 
        var data = db.Query(sqlQ, Request["id"]);
    }

    That is what other developers do to provide results to a particular users in their profile. They load just a particular data for example like the following query

    SELECT * FROM UserProfiles WHERE UserId = 10

    The row for the user whose ID is 10 would be loaded and provided only other rows would be rejected and there won't be any more data in the Collection of data returned. Don't get confused by the term "Collection", you'll learn it on your way. Until then I would forward you to the Database tutorial on the ASP.NET Web Pages where you will learn the basics about the "Using database" in an ASP.NET website. 

    http://www.asp.net/web-pages/tutorials/data/5-working-with-data

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 7, 2014 7:15 PM
  • User895691971 posted

    Thanks guys I am slowly getting somewhere but still struggling :(

    I have tried the above and I get no results this time.

    @{
      var db = Database.Open("ISSUE_XXX");
      var sqlQ = "SELECT * FROM XXXX_REQUEST where cr_issue_no = @0";
      var data = db.Query(sqlQ, Request["id"]);
    }
    
    
    
    <div id="crlist">
        <table class="table" border="1" width="75%" align="center">       
           
            @foreach(var row in data) 
            {
                <tr>
              <td width ="12%">
                <center><a href="viewCRdetails.cshtml?id=@row.cr_issue_no">@row.cr_title</a></center>
                </td>
            
                </tr>   
                        }  

    I will run though the links provided to try and clarify in my dense brain

    Using the Request requires you to have passed a form variable or a query string to the current Request. If nothing shows up, maybe you're not having the Variable. Or maybe that was an example or something. If you believe you have set the value somewhere, please use that value to extract the result by passing the value in the parameter. But first make sure, the page loading is not the first time. Because the first time the URL might not fit the Query String and you will have to face this. Change the code to this

    @{
      var id = Request["id"];
      if(id == null) {
         // if value is null, then change the id to 1.
         id = 1;
      }
      var db = Database.Open("ISSUE_XXX");
      var sqlQ = "SELECT * FROM XXXX_REQUEST where cr_issue_no = @0";
      var data = db.Query(sqlQ, Request["id"]);
    }

    Now if the page is the first run (no id in the Url) you will get the results for the first data. Otherwise you can also use this block to ask the user about which data he wants to get the result for and so on.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 7, 2014 8:28 PM

All replies

  • User-1716253493 posted
    Yes, you need to select data based id query string in detail page.
    Wednesday, August 6, 2014 9:25 PM
  • User1650462628 posted

    like this? in the actual SQL statement or a separate line elsewhere

    @{
      var db = Database.Open("ISSUE_XXX");
    var sqlQ = "SELECT * FROM XXXX_REQUEST where cr_issue_no = @cr_issue_no";
    var data = db.Query(sqlQ);
    }
     
    Wednesday, August 6, 2014 9:58 PM
  • User1713851783 posted

    Probably you should follow a good tutorial like this one: Introduction to Working with a Database in ASP.NET Web Pages (Razor) Sites.

    Thursday, August 7, 2014 2:28 AM
  • User-821857111 posted

    like this? in the actual SQL statement or a separate line elsewhere

    @{
      var db = Database.Open("ISSUE_XXX");
    var sqlQ = "SELECT * FROM XXXX_REQUEST where cr_issue_no = @cr_issue_no";
    var data = db.Query(sqlQ);
    }

    No - like this:

    @{
        var db = Database.Open("ISSUE_XXX");
        var sqlQ = "SELECT * FROM XXXX_REQUEST where cr_issue_no = @0";
        var data = db.Query(sqlQ, Request["id"]);
    }

    See the article that GmGregori linked to.

    Thursday, August 7, 2014 3:00 AM
  • User895691971 posted

    Obviously, SQL statement returns the result that you ask it to. 

    SELECT * FROM table_name

    ...would return all of the columns. That is the job of the * and since there is no WHERE clause, this would trigger SQL to include all of the rows too. In other words, entire table to be extracted from the Database. 

    Since you need to only 1 record to be shown from the 2 results in the Database. You can/must add a WHERE clause to your query to extract result depending on a condition. For example 

    SELECT * FROM table_name WHERE column_name = value

    This way, you add a condition to the SQL statement to select all columns (*) but only from the rows that have the column_name the value provided in the query.

    @{
        // Database variable initialization, connection
        var db = Database.Open("ISSUE_XXX");
        // sqlQuery variable string, note the usage of "@0" and
        // the usage of parameter Request["id"] in the data variable. 
        var sqlQ = "SELECT * FROM XXXX_REQUEST where cr_issue_no = @0";
        // extraction of the Data. 
        var data = db.Query(sqlQ, Request["id"]);
    }

    That is what other developers do to provide results to a particular users in their profile. They load just a particular data for example like the following query

    SELECT * FROM UserProfiles WHERE UserId = 10

    The row for the user whose ID is 10 would be loaded and provided only other rows would be rejected and there won't be any more data in the Collection of data returned. Don't get confused by the term "Collection", you'll learn it on your way. Until then I would forward you to the Database tutorial on the ASP.NET Web Pages where you will learn the basics about the "Using database" in an ASP.NET website. 

    http://www.asp.net/web-pages/tutorials/data/5-working-with-data

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 7, 2014 7:15 PM
  • User1650462628 posted

    Thanks guys I am slowly getting somewhere but still struggling :(

    I have tried the above and I get no results this time.

    @{
      var db = Database.Open("ISSUE_XXX");
      var sqlQ = "SELECT * FROM XXXX_REQUEST where cr_issue_no = @0";
      var data = db.Query(sqlQ, Request["id"]);
    }
    
    
    
    <div id="crlist">
        <table class="table" border="1" width="75%" align="center">       
           
            @foreach(var row in data) 
            {
                <tr>
              <td width ="12%">
                <center><a href="viewCRdetails.cshtml?id=@row.cr_issue_no">@row.cr_title</a></center>
                </td>
            
                </tr>   
                        }  

    I will run though the links provided to try and clarify in my dense brain

    Thursday, August 7, 2014 7:52 PM
  • User895691971 posted

    Thanks guys I am slowly getting somewhere but still struggling :(

    I have tried the above and I get no results this time.

    @{
      var db = Database.Open("ISSUE_XXX");
      var sqlQ = "SELECT * FROM XXXX_REQUEST where cr_issue_no = @0";
      var data = db.Query(sqlQ, Request["id"]);
    }
    
    
    
    <div id="crlist">
        <table class="table" border="1" width="75%" align="center">       
           
            @foreach(var row in data) 
            {
                <tr>
              <td width ="12%">
                <center><a href="viewCRdetails.cshtml?id=@row.cr_issue_no">@row.cr_title</a></center>
                </td>
            
                </tr>   
                        }  

    I will run though the links provided to try and clarify in my dense brain

    Using the Request requires you to have passed a form variable or a query string to the current Request. If nothing shows up, maybe you're not having the Variable. Or maybe that was an example or something. If you believe you have set the value somewhere, please use that value to extract the result by passing the value in the parameter. But first make sure, the page loading is not the first time. Because the first time the URL might not fit the Query String and you will have to face this. Change the code to this

    @{
      var id = Request["id"];
      if(id == null) {
         // if value is null, then change the id to 1.
         id = 1;
      }
      var db = Database.Open("ISSUE_XXX");
      var sqlQ = "SELECT * FROM XXXX_REQUEST where cr_issue_no = @0";
      var data = db.Query(sqlQ, Request["id"]);
    }

    Now if the page is the first run (no id in the Url) you will get the results for the first data. Otherwise you can also use this block to ask the user about which data he wants to get the result for and so on.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 7, 2014 8:28 PM
  • User1650462628 posted

    Thank you, I managed to work it out with your guidance. The problem was I had tried so many things the code got messed up

    Sunday, August 17, 2014 9:56 PM