We are excited to announce that the IIS.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

Some fields not displaying from query [Answered]RSS

7 replies

Last post Sep 06, 2018 05:48 PM by LCarlSeiler

  • Some fields not displaying from query

    Aug 31, 2018 02:27 PM|LCarlSeiler|LINK

    We have some old code on our site that is exposed to some longtime clients upon login. For years, parts of it haven't returned the right data, and we've just rolled with it. The other day, I got the assignment from the boss to figure out why it reads that way. As someone who has worked on LAMP stack and now Node and just barely dabbled in .NET for the web despite having some C# and VBA stuff I occasionally do in-house, I don't quite know where to start, except that it's obvious that two of the fields in the query aren't being mapped from the recordset object to the ASP engine for display.  That is, when I run the query from the stored procedure, it returns the desired columns with the desired names, but when it comes to displaying on the web site, they simply show up blank or apparently return "Empty" strings.

    So, the code has this on the page:

     <% 
        Set objConnection = Server.CreateObject("ADODB.Connection")  
        objConnection.Open "DSN=OurDSN"
        SQLQuery = "exec sp_thepayments " & Request.querystring("claimid")
        Set rsCustomersList = objConnection.Execute(SQLQuery)
         %>

    I have also tried using the rs.Open instead of Execute:

    <% 
    Set objConnection = Server.CreateObject("ADODB.Connection")
    objConnection.Open "DSN=OurDSN"
    SQLQuery = "exec sp_thepayments " & Request.querystring("claimid")
    'Set rsCustomersList = objConnection.Execute(SQLQuery)
    Set rsCustomersList = Server.CreateObject("ADODB.recordset")
    rsCustomersList.Open SQLQuery, objConnection
    %>

    It works the same way.

    Then, later the table is created:

    <tbody>
    <%  Do Until rsCustomersList.EOF%>
          <tr>
            <td class="amount"><% if ISNULL(rsCustomersList("clmpmt_amt")) then %>
        <p>$0.00 <% else %> <%= formatcurrency (rsCustomersList("clmpmt_amt"))%> <% end if %> </td>
            <td><% if ISNULL(rsCustomersList("clmpmt_date")) then %>
        <p><% else %> <%= formatdatetime (rsCustomersList("clmpmt_date"),2)%> <% end if %> </td>
            <td><%= rsCustomersList("chk_nbr")%></td>
            <td><% if ISNULL(rsCustomersList("chk_date")) then %><p><% else %> <%= formatdatetime (rsCustomersList("chk_date"),2)%> <% end if %></td>
            <td><% strtype = rsCustomersList("clmpmt_type")%>
        <%If rsCustomersList("clmpmt_type") = "c" Then %>
        Customer<% Else %><%Response.Write(strtype)%>Vendor<% End If %>
        <%If IsEmpty(rsCustomersList("clmpmt_type")) Then%>
        &bull; rsCustomersList(&quot;clmpmt_type&quot;) is Empty
        <% Else %>
        &quot;<%= strtype%>&quot;
        <% End If %>
        </td>
            <td><%= rsCustomersList("wt_item")%></td>
            <td><%= rsCustomersList("claimobj_desc")%></td>
            <td><%= rsCustomersList("loss_name")%></td>
          </tr>
        <% rsCustomersList.MoveNext
         Loop %>
     </tbody>

    So, what I'm finding through reading is that it's basically setting up a recordset object which is called rsCustomersList in this case. It processes each member of the recordset using the Do Until...Loop. On each loop, it creates a row in the table. The <%= %> seems to be a shortcut or alternative for the Response.Write() method. Each item in the rsCustomersList("...") style is a value in a column in the recordset represented by the column name in quotes for that particular row as it loops. Apparently, you can address the value of a column for that recordset row by specifying rsCustomersList("clmpmt_date") for example. However, what I'm finding is that it isn't able to display the values of Customer or Vendor based on the If rsCustomersList("clmpmt_type") = "c" Then... As you can see, it appears that strtype [and rsCustomersList("clmpmt_type") for that matter] are always empty. I added some test code in there, to verify that it is indeed empty. The code is working, I am just not getting the output I expect.

    So, my first thought was that the query is not returning a value for that column, but tests on the SQL server for that stored procedure reveal that it is indeed working as intended. The SQL query is correct as written. The spelling of the column matches, too. Finally, I thought that maybe the stored procedure it is calling is not the one I'm looking at (maybe a different server instance or database), but no when I edit the stored procedure, the website displays different data, so I am working on the correct SQL query on the server. I thought maybe that it has something to do with the logic in the VBScript that is supposed to convert the "c" to Customer and everything else to Vendor, but no I'm having the same problem with the <%= rsCustomersList("loss_name")%> not displaying even when the SQL query in the stored procedure is returning values for "loss_name".

    Anyone have any ideas or can point me how to debug this?

  • Re: Some fields not displaying from query

    Sep 03, 2018 02:08 AM|deepakpanchal10|LINK

    Hi LCarlSeiler,

    You had mentioned that,"Anyone have any ideas or can point me how to debug this?"

    All you had tried is like testing the query and procedure in SQL and with in code.

    I suggest you to put a break point and check the SQL Query that passed at run time with parameters.

    Check and verify that it is correct.

    Then after check that 'rsCustomersList("clmpmt_amt")' contains the value.

    Try to only execute the VBA code for testing purpose in VBA Editor.

    Print the data in immediate window to check whether it fetch the correct data or not.

    Also try to store the data in variable for testing purpose and try to use the variable in code to display the data.

    Regards

    Deepak

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue.
    If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
  • Re: Some fields not displaying from query

    Sep 04, 2018 02:02 PM|LCarlSeiler|LINK

    Thank you very much for your reply

    deepakpanchal10

    Hi LCarlSeiler,

    You had mentioned that,"Anyone have any ideas or can point me how to debug this?"

    All you had tried is like testing the query and procedure in SQL and with in code.

    That's correct as I don't know any other strategies at this point.

    I suggest you to put a break point and check the SQL Query that passed at run time with parameters.

    Check and verify that it is correct.

    Then after check that 'rsCustomersList("clmpmt_amt")' contains the value.

    I actually have to admit that I do not know how to do any of that.  I'm not even sure what tools I should be using to work on this problem.  I'm currently using MSSMS and a text editor to edit it.  So that is why, as you see in my post, I mainly go through and edit the code to print the variables on the site in the ASP file--and the ones not showing are empty at that point.  I just don't know WHY they are empty, as I said, the query appears to be returning values when I run it, but appears not to be when it runs on the site. I have tried bringing it into Visual Studio, but there are so many gaps in my knowledge.  I am currently trying to go through this: https://weblogs.asp.net/dixin/debugging-classic-asp-with-visual-studio to see if I can get that going.

    Try to only execute the VBA code for testing purpose in VBA Editor.

    Print the data in immediate window to check whether it fetch the correct data or not.

    Also try to store the data in variable for testing purpose and try to use the variable in code to display the data.

    In a VBA editor, as in a VBA in Access?  I imported it to VBA, and I'm trying to decruft all the HTML, but will continue that direction as well.  Thank you for this tip. 

    I will continue to work on this and report back.

  • Re: Some fields not displaying from query

    Sep 04, 2018 05:29 PM|LCarlSeiler|LINK

    Thank you.  I replied earlier, but my reply appears to have gotten stuck with the moderators (perhaps because I included a link to asp.net blogs).  Anyway, I've been pursuing the VBA editor track.   It takes a little bit of rewriting, but I was able to put the code into ACCESS and run it.  I added a bunch of Debug.Print() lines to show me what is happening. What I'm finding is that again, for some fields (but not all) it simply is not showing anything.  Even though when I run the same stored procedure as a pass-through query in Access I see all the columns.  I put a break in the VBA editor and stopped it after it had run one row in the recordset.   I've tried using both the index value and the column name with no luck.  To top it off, it's not the same fields or indexes I was having problems with on the web server.

    This is the dialog from the immediate window:

    ? strLoss

    ? len(strLoss)
    0
    ? IsNull(strLoss)
    False
    ? IsEmpty(strLoss)
    False

    So, it appears that it is returning a non-empty, zero-length string.

    This is the code I'm using in my VBA testing:

    'Set objConnection = Server.CreateObject("ADODB.Connection")
    Dim objConnection
    Set objConnection = CreateObject("ADODB.Connection")
    strCnxn = "DSN=myDSN;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=myDatabase"
    objConnection.Mode = adModeRead
    objConnection.Open strCnxn
    Dim SQLQuery As String
    Dim strAdj As String
    Dim strLoss As String

    SQLQuery = "exec sp_payments 202686"
    Set rsCustomersList = objConnection.Execute(SQLQuery)
    'Set rsCustomersList = Server.CreateObject("ADODB.recordset")
    'rsCustomersList.Open SQLQuery, objConnection
    Do Until rsCustomersList.EOF
    'This code works in VBA but not on the web server. It correctly returns the word 'Customer'
    If rsCustomersList.Fields(4) = "c" Then
    Debug.Print ("Customer")
    Else
    Debug.Print ("Vendor")
    End If
    If IsEmpty(rsCustomersList.Fields(4)) Then
    Debug.Print ("rsCustomersList(&quot;clmpmt_type&quot;) is Empty")

    End If

    strAdj = rsCustomersList.Fields(2).Value
    Debug.Print ("Adjustment Description: " & strAdj)

    strLoss = rsCustomersList.Fields(3)
    Debug.Print ("Loss name: " & strLoss)


    Debug.Print ("------ END of RECORD --------")

    rsCustomersList.MoveNext
    Loop
    rsCustomersList.Close
    Set rsCustomersList = Nothing
    Set objConnection = Nothing
    ASPTest = 0

    I'm basically where I am the same predicament that I am with the web server version. The stored procedure seems to run fine. I use the same stored procedure in the code and the code is unable to see/address the columns/fields. In this case they are not empty but zero-length strings, but this may be a difference between VBScript and VBA.  Anyway, the bottom line is that it doesn't seem to be something with the ASP technology but something I'm missing entirely here as I encounter the same problem with VBA

  • Re: Some fields not displaying from query

    Sep 05, 2018 07:00 AM|deepakpanchal10|LINK

    Hi LCarlSeiler,

    I can see that you got the similar kind of output in VBA but at present I am not aware about your stored procedure and your data in a table.

    If possible then try to post a stored procedure and some sample data.

    I will try to make a test on my side to check the result.

    Is it a string type data or any other data type?

    Other thing I suggest you to directly try to execute the query from code instead of stored procedure to check the output whether it makes any difference or not.

    Regards

    Deepak  

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue.
    If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
  • Re: Some fields not displaying from query

    Sep 05, 2018 02:14 PM|LCarlSeiler|LINK

    I'll work on extracting some sample data for you.

    Create PROCEDURE [dbo].[spPayments] @claim_id int = NULL AS
    select co.claim_id, co.claimobj_desc, adj_desc, loss_name, cp.clmpmt_type, clmpmt_amt, cp.clmpmt_date, cp.chk_nbr, cp.chk_date, w.wt_item
    from claim_objects co, adjustments, loss_codes, claimpayments cp, weights w
    where co.claim_id = @claim_id and co.claimobj_id *= cp.claimobj_id and co.adj_code *= adjustments.adj_code and co.loss_code *= loss_codes.loss_code and co.wt_code *= w.wt_code
    
    GO

    The data that isn't showing in VBA is of type text and varchar(50).  The data that isn't showing in the IIS/ASP is char(1) and varchar(50).  I know text is deprecated.

    I'm actually not sure how to best attach the sample data.  I started working on a script to export the schema and data, and was going to trim out the unneeded data, but some of the tables are so huge that I'm going to have to try a different strategy.  I was trying to avoid a SELECT INTO.

  • Re: Some fields not displaying from query

    Sep 05, 2018 04:32 PM|LCarlSeiler|LINK

    deepakpanchal10

    Other thing I suggest you to directly try to execute the query from code instead of stored procedure to check the output whether it makes any difference or not.

    Good suggestion.  Does not make any difference at least in the VBA version.  I get same variables displaying and missing.

    However, digging further into it, I find that if I do it entirely in Access, using linked tables and a saved MS Access query instead of a stored procedure or built query it works. 

    'This works exactly as intended:

    'This works exactly as intended
        Dim dbs As Database
        Set dbs = CurrentDb
        'qryclaimPayments below is a saved query in MS Access
        'The query is the same as the stored procedure with tables simply renamed and pointing to linked tables in Access
        Set rsCustomersList = dbs.OpenRecordset("qryClaimPayments", dbOpenDynaset)
        
        Debug.Print ("This is without stored procedure")
        
        Do Until rsCustomersList.EOF
            'This code works in VBA but not on the web server.  It correctly returns the word 'Customer'
            If rsCustomersList.Fields(4) = "c" Then
                Debug.Print ("Customer")
            Else
                Debug.Print ("Vendor")
            End If
            If IsEmpty(rsCustomersList.Fields(4)) Then
                Debug.Print ("rsCustomersList(&quot;clmpmt_type&quot;) is Empty")
            
            End If
    
            strAdj = rsCustomersList.Fields(2).Value
            Debug.Print ("Adjustment Description: " & strAdj)
            
            strLoss = rsCustomersList.Fields(3)
            Debug.Print ("Loss name: " & strLoss)
            
            Dim x As String
            x = rsCustomersList.Fields(4)
            Debug.Print ("Field 4: " & x)
            
            Dim y As String
            y = rsCustomersList.Fields(5)
            Debug.Print ("Field 5: " & y)
            
            
            Debug.Print ("------ END of RECORD --------")
            
            rsCustomersList.MoveNext
        Loop
        rsCustomersList.Close
        Set rsCustomersList = Nothing
        Set dbs = Nothing
        Debug.Print ("**** Using Linked Tables and saved Query ****")

  • Re: Some fields not displaying from query

    Sep 06, 2018 05:48 PM|LCarlSeiler|LINK

    deepakpanchal10

    Is it a string type data or any other data type?

    I never did send the sample data as I wasn't sure if I should past it all in message or link to it.  Either way, I have solved it.  Your question about data types got me really looking at it.  And I also was able to find a post on StackOverflow where a user was having a similar problem in Excel.  They solved it by explicitly casting  all of the text/string values in TSQL.  So I just cast everything to varchar(50) except the char(1).  I ended up with the query:

    select co.claim_id, CAST(co.claimobj_desc AS varchar(50)) claimobj_desc, CAST(adj_desc AS varchar(50)) AS adj_desc, CAST(loss_name AS varchar(50)) AS loss_name, CAST(cp.clmpmt_type AS char(1)) AS clmpmt_type, clmpmt_amt, cp.clmpmt_date, cp.chk_nbr, cp.chk_date, CASt(w.wt_item AS varchar(50)) AS wt_item
    from claim_objects co
    LEFT JOIN claimpayments cp ON co.claimobj_id = cp.claimobj_id 
    LEFT JOIN adjustments on co.adj_code = adjustments.adj_code 
    LEFT JOIN loss_codes ON co.loss_code = loss_codes.loss_code 
    LEFT JOIN weights w ON co.wt_code = w.wt_code
    where co.claim_id = @claim_id 
    

    That worked in VBA in Access, and after a little testing I was able to get it working in IIS/ASP.  The confounding issue was that it wasn't always the same strings having problems.  But, in the end, that was it.