locked
the message is displayed but replies did not. please look at the sql query and join where AdvoFId of AdvoFReply table is used. RRS feed

  • Question

  • User1493762548 posted

    Hi all,

     I am working on the forum like this Asp.net webpages.there are three tables ,advocates,advoForum and advoFReply. the advoForum redirect as

    <a href="~/Advocate/ViewForum.cshtml/?AdvoFId=@row.AdvoFId"> @row.MessageHeader</a>

    the code of viewforum is here please check sql query why the replies are not displayed .Note: there are two replies in AdvoFReply table .

    here is code:

    @{
       
      
     
         if (!WebSecurity.IsAuthenticated)
          {
              Response.Redirect("~/Account/Login");
          }
     
     
        
        var AdvoFId= Request["AdvoFId"];
        var db = Database.Open("PhotoGallery");
        
        var p= @"SELECT AdvoFId ,MessageHeader,MessageBody,AdvoName,newFileName FROM AdvoForum INNER JOIN Advocates
                                  ON AdvoForum.AdvoId =Advocates.AdvoId  where AdvoFId=AdvoFId " +" ORDER BY AdvoForum.UpdateTime desc"; 
                   var forum = db.QuerySingle(p) ;
     
        if (IsPost)
        {
            var Reply = Request["Reply"].Trim();
            
            if (Reply.IsEmpty()) 
            {
                Response.Redirect(Href("~/Advocate/ViewForum"));
            }
     
             
                 
         
        var UserId=WebSecurity.CurrentUserId;
                 
         
        var advo= db.QuerySingle("SELECT * FROM Advocates WHERE UserId = @0",UserId);
        var AdvoId=advo.AdvoId;
            db.Execute("INSERT INTO AdvoFReply ( Reply,AdvoId,AdvoFId,ReplyTime) VALUES (@0, @1, @2,@3)",
              Reply,AdvoId,AdvoFId,DateTime.Now);
                 }
                 
        var pageSize = 6; 
        var totalPages = 0; 
        var count = 0;
        var page = UrlData[0].AsInt(1);
        var offset = (page -1) * pageSize;
        
       
         var sql = @"Select Count(*) From AdvoFReply INNER JOIN Advocates
                                  ON AdvoFReply.AdvoId = Advocates.AdvoId where AdvoFReply.AdvoFId=@0 ";
                                  
                     count = (int)db.QueryValue(sql,AdvoFId);
        totalPages = (int)Math.Ceiling((double)count/pageSize); 
        
         sql = @"SELECT * FROM AdvoFReply INNER JOIN Advocates
                                  ON AdvoFReply.AdvoId =Advocates.AdvoId "+" where AdvoFReply.AdvoFId=@0" +" ORDER BY ReplyTime desc"+
                   " OFFSET @1 ROWS FETCH NEXT @2 ROWS  ONLY;";
                   
                 
                   var result = db.Query(sql, offset, pageSize,AdvoFId) ;
        
         
        }
     
    <div class="fluid">
        <div class="bg-info ">
            <p>Page @page of @totalPages</p> 
            
            <div class="bg-success">
            <form method="post" action="">
                <fieldset >
                    <legend> Reply To Message</legend>
                    <ol>
                        <li>
                            <label for="Reply">Reply:</label><br/>
                            <textarea  id=""name="Reply" title="Reply" rows="2" cols="70" placeholder="start writing here!"></textarea>
                        </li>
                        
                    </ol>
                    
                        <input type="submit" title="Add Post" value=" Post" />
                    
     
                </fieldset>
            </form>
       </div>
             <div class="media">
      <a class="media-left bg-warning" href="">
        <img class="media-object" src="~/AdvoPics/@forum.newFileName" alt="Profile picture">
          
      </a>
     <div class="media-body text-center text-danger ">
        <h4 class="media-heading text-center bg-warning">@forum.AdvoName &nbsp @forum.MessageHeader </h4>
       <p>@forum.MessageBody</p>
       
         </div>
    </div>
             
            
        @foreach(var row in result){
            <div class="media">
      <a class="media-left bg-warning" href="~/Advo/@row.AdvoId">
        <img class="media-object" src="~/AdvoPics/@row.newFileName" alt="Profile picture">
          
      </a>
     <div class="media-body text-center bg-success">
        <h4 class="media-heading text-center bg-warning"> @row.AdvoName</h4>
          @row.Reply
       <p><span class="glyphicon glyphicon-time"></span>&nbsp @row.UpdateTime.ToString("f")</p>&nbsp <br/>
        
         </div>
    </div>
             
            
     
     
        }
    </div>
     
     
    </div>
     
        
     
     
          
                      <br/> 
        @{
            for (var i = 1; i <= totalPages; i++){
                <a href="/ViewForum/@i"><button class="btn-info">@i</button></a>
            }
        }
     
     
    

    Friday, July 8, 2016 10:23 AM

Answers

  • User-821857111 posted

    sql = @"SELECT * FROM AdvoFReply INNER JOIN Advocates
    ON AdvoFReply.AdvoId =Advocates.AdvoId "
    +" where AdvoFReply.AdvoFId=@0" +" ORDER BY ReplyTime desc"+
    " OFFSET @1 ROWS FETCH NEXT @2 ROWS ONLY;"; var result = db.Query(sql, offset, pageSize,AdvoFId) ;

    You have to pass parameter values in to the Database.Query method in exactly the same order as they appear in the SQL statement:

    sql = @"SELECT * FROM AdvoFReply 
          INNER JOIN Advocates 
          ON AdvoFReply.AdvoId = Advocates.AdvoId  
          where AdvoFReply.AdvoFId=@0  
          ORDER BY ReplyTime desc 
          OFFSET @1 ROWS FETCH NEXT @2 ROWS ONLY"; 
    
    var result = db.Query(sql, AdvoFId, offset, pageSize) ;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 8, 2016 12:17 PM
  • User-821857111 posted

    it should be implemented in some way in paging also.
    Yes, so change the code that generates the links:

    <a href="/ViewForum/@i?AdvoFld=@AdvoFld"><button class="btn-info">@i</button></a>
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 8, 2016 1:16 PM

All replies

  • User-821857111 posted

    sql = @"SELECT * FROM AdvoFReply INNER JOIN Advocates
    ON AdvoFReply.AdvoId =Advocates.AdvoId "
    +" where AdvoFReply.AdvoFId=@0" +" ORDER BY ReplyTime desc"+
    " OFFSET @1 ROWS FETCH NEXT @2 ROWS ONLY;"; var result = db.Query(sql, offset, pageSize,AdvoFId) ;

    You have to pass parameter values in to the Database.Query method in exactly the same order as they appear in the SQL statement:

    sql = @"SELECT * FROM AdvoFReply 
          INNER JOIN Advocates 
          ON AdvoFReply.AdvoId = Advocates.AdvoId  
          where AdvoFReply.AdvoFId=@0  
          ORDER BY ReplyTime desc 
          OFFSET @1 ROWS FETCH NEXT @2 ROWS ONLY"; 
    
    var result = db.Query(sql, AdvoFId, offset, pageSize) ;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 8, 2016 12:17 PM
  • User1493762548 posted

    very very thanks mikes,

    it  started working as desired .but when go to next page error is Cannot perform runtime binding on a null reference.

    i think AdvoFId is null .it should be implemented in some way in paging also.

    Friday, July 8, 2016 12:52 PM
  • User-821857111 posted

    it should be implemented in some way in paging also.
    Yes, so change the code that generates the links:

    <a href="/ViewForum/@i?AdvoFld=@AdvoFld"><button class="btn-info">@i</button></a>
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 8, 2016 1:16 PM
  • User1493762548 posted

    i changed the code in link as :

    @{
            for (var i = 1; i <= totalPages; i++){
                <a href="~/Advocate/ViewForum/@i?AdvoFld=@AdvoFld"><button class="btn-info">@i</button></a>
            }
        }

    the following error occur:
     CS0103: The name 'AdvoFld' does not exist in the current context
    if i change the code as:
     <a href="~/Advocate/ViewForum/@i?AdvoFld=@row.AdvoFld"><button class="btn-info">@i</button></a>
    
    the error is same as :
     CS0103: The name 'row' does not exist in the current context

    where as both are defined in the upper code.AdvoFId is global whereas the row is in foreach block.
     
    Friday, July 8, 2016 1:35 PM
  • User-821857111 posted
    @for (var i = 1; i <= totalPages; i++){ 
         <a href="~/Advocate/ViewForum/@i?AdvoFld=@Request["AdvoFId"]"><button class="btn-info">@i</button></a> 
    }



    Friday, July 8, 2016 6:45 PM