Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    63
    Rep Power
    16

    Please Help ! - Recordset not displaying all required fields


    Hi there,

    I have searched high and low on google and these forums, but not found any answers so am really hoping someone might be able to help me!

    Basically I've created a search page, with a search results page for a second hand cars. This uses ASP and SQL server 2000.

    My search page allows you to search for a make, model and location and these results then get passed by a form to the results page. I then have a filtered recordet looking at a query in SQL to show a certain car by its location.

    Anyways this all works fine, but my recordset is mystifying me ! When i test it in dreamweaver, (by entering a value in the recordset test area) it shows all the data.

    However, when I run the page live, some fields are not being displayed, even though they are in the database query and are displaying in the data tests in dreamweaver and Enterprise manager. I have created a simple test results page with table and it seems that the missing fields, are being 'greyed out'. By this I mean the data is not actually showing, and the table column where it should be is being greyed out(see page link below)

    Whats even stranger is that when I remove a couple of fields, for example Car_Tax, one of the fields that was originally displayed, will then be displayed, i.e. Car Price, will then appear.

    As I mentioned I can't for the life of me work out why this is, my query is simply looking at 3 tables, 1 table pulls make, the other model, and the 3rd pulls other relevant info, such as Car_Price, Car_Tax etc.

    As I mentioned the query works fine when tested in dreamweaver's recordset test area, and also tests fine when I run it in SQL enterprise manager.


    I don't think my code is wrong as it displays the recordset fields, just not all of them. I have also removed the filter on the recordset to show all, but it still greys out certain fields that should be showing.

    If anyone has ever had this happen to them, or has any ideas, it would be much appreciated as I'm really stuck and on a deadline !!

    Also if you want to see the search page its here

    http://www.uprated.com/search_cars.asp

    and results page is found after you do a simple search

    I can also post the code, or the SQL if that helps.

    Many thanks

    Jon
    Last edited by Jonny5uk; September 7th, 2003 at 11:15 AM.
  2. #2
  3. No Profile Picture
    Average Intelligence
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2003
    Location
    Ohio/Chicago
    Posts
    678
    Rep Power
    16
    can't help ya dude, no code....
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    63
    Rep Power
    16

    re.. the code


    Hi there, sorry, here is the code if it helps?

    Hope so !

    Jon

    P.S I also have the SQL if it would help if I posted that?


    <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
    <!--#include file="Connections/jon.asp" -->
    <%
    Dim rsDetails__MMColParam
    rsDetails__MMColParam = "1"
    If (Request.Form("location") <> "") Then
    rsDetails__MMColParam = Request.Form("location")
    End If
    %>
    <%
    Dim rsDetails
    Dim rsDetails_numRows

    Set rsDetails = Server.CreateObject("ADODB.Recordset")
    rsDetails.ActiveConnection = MM_jon_STRING
    rsDetails.Source = "SELECT * FROM dbo.qry_Search_Results WHERE Car_Location = '" + Replace(rsDetails__MMColParam, "'", "''") + "'"
    rsDetails.CursorType = 0
    rsDetails.CursorLocation = 2
    rsDetails.LockType = 1
    rsDetails.Open()

    rsDetails_numRows = 0
    %>

    <table width="1395" height="22" border="1" cellpadding="0" cellspacing="0" class="searchtxt">
    <tr>
    <td>Image</td>
    <td>Make</td>
    <td>Model</td>
    <td>Year</td>
    <td>Engine Size</td>
    <td>Tax</td>
    <td>Location</td>
    <td width="162">Specification</td>
    <td width="119">Price</td>
    <td width="99">Email</td>
    <td width="100">Seller Type</td>
    </tr>
    <tr>
    <td width="40"><img src="<%=(rsDetails.Fields.Item("Car_Thumb_Pic").Value)%>"></td>
    <td width="123"><%=(rsDetails.Fields.Item("Car_Make").Value)%></td>
    <td width="126"><%=(rsDetails.Fields.Item("Car_Model").Value)%></td>
    <td width="192"><%=(rsDetails.Fields.Item("Car_Year_Manufacture").Value)%></td>
    <td width="159"><%=(rsDetails.Fields.Item("Car_Engine_Size").Value)%></td>
    <td width="114"><%=(rsDetails.Fields.Item("Car_Tax").Value)%></td>
    <td width="139"><%=(rsDetails.Fields.Item("Car_Location").Value)%></td>
    <td><%=(rsDetails.Fields.Item("Car_Specification").Value)%></td>
    <td><%=(rsDetails.Fields.Item("Car_Price").Value)%></td>
    <td><%=(rsDetails.Fields.Item("Cust_Email").Value)%></td>
    <td><%=(rsDetails.Fields.Item("Car_Seller_Type").Value)%></td>
    </tr>
    </table>
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    in Orbit mostly
    Posts
    148
    Rep Power
    16

    Full SQL and ALL results?


    Are you sure you have the correct results in "SELECT * FROM dbo.qry_Search_Results"?
    I am guessing that the 3 tables merged (using qry_Search_Results) might not fill in all you want, or not with the exact names you are using...
    I often use the method described in this tutorial to display all the results during troubleshooting, including automatically showing ALL the column names as the database sees them (and not how I THOUGHT I named them...).
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    63
    Rep Power
    16

    re..


    Thanks for the reply OldJacques,

    Unfortunately I don't think it has a problem pulling the data as when I test the query in dreamweaver recordset test area, all the data is displaying fine, and when I run the query in SQL, it is also displaying fine.

    When I drag the recordset fields onto the page and test them on a live server, some of them simply aren't displaying and I just can't figure out why, as they are displaying in the test data!?

    I've also checked my field names but they are fine too.

    It just really baffles me, if anyone has any other suggestions or ideas, it would be much appreciated as I'm in a bit of a hole right now....

    Thanks

    Jon
  10. #6
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    in Orbit mostly
    Posts
    148
    Rep Power
    16
    Well, you may want to give the tutorial solution/suggestion a try, as the code is "drop in" easy, and would only take a minute.
    The fact that you are combining results from three tables would seem to be complicating something, and doing a query on a query seems to hide the actual problem.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    63
    Rep Power
    16

    re....trying suggestion...


    Hi OldJacques

    Have tried the tutorial with the getrows solution, however it now seems to show even less results that before?

    For example its still not showing the original fields, but a few more have also vanished now, when I run the code.


    Here's the implemented code I used... (the bits I changed)

    <%
    myDSN="dsn=uprateddsn;uid=userid;pwd=password"
    mySQL="select * from dbo.qry_Search_Results where Car_Location='kent'"
    showblank="&nbsp;"
    shownull="-null-"

    Any ideas why the fields aren't showing with your code?

    I'm thinking I must have some conflicting fields, or something is wrong with my query. The trouble is, I'm not an expert in SQL and I really need to pull the data from the 3 tables for my query.

    If you have any other ideas, please pass them my way !

    Kind regards

    Jon
  14. #8
  15. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    in Orbit mostly
    Posts
    148
    Rep Power
    16

    further digging - or digging deeper


    I would try the following for troubleshooting:

    mySQL="select * from dbo.qry_Search_Results" ' to get all the results available
    showblank="-blank-" ' to indicate that the script thinks it is a BLANK field, even if you think or know otherwise
    shownull="-null-"

    If the fields aren't showing with a SELECT *, then the problem would seem to lie in the qry_Search_Results query, which I imagine is a stored procedure. You can list the original text of the Stored Procedure using "sp_helptext qry_Search_Results"

    In order to troubleshoot that query though, it is necessary to know the SQL being used and the fields being called (if you are using * instead of specifying them). Do you have the URL of the troubleshooting page using "GetRows"? I find it to be a good idea during troubleshooting to always echo the SQL statement at the end of the page, so I am always double checking what I am doing...
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    63
    Rep Power
    16

    re.. troubleshooting


    Hi OldJacques,

    Thank you for such a quick reply !

    Anyways I've tried it again using Select * this time and implementing the '-blank- feature and it is still giving the same results, and it would appear that the script seems to think that the fields that are missing are '-blank- when I know they should not be.

    I think the problem is that my SQL query was basically converted from one in access. As I mentioned I'm not an expert in SQL and I'm presuming that Queries and Stored Procedures are different things?

    I'm therefore wondering (if the above statement is correct) whether I should pull out a tutorial on stored procedures and create one for my query and maybe this might sort out the problem?

    If you have any thoughts on this one, please let me know!

    Thanks again

    Jon
  18. #10
  19. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    in Orbit mostly
    Posts
    148
    Rep Power
    16
    What is "qry_Search_Results"?
    If it isn't a Stored Procedure, what is the SQL code that it uses (and even if it IS SP).
    How has it been created?

    Learning Stored Procedures will be useful anyway, but I still have no way of understanding if it comes into play in THIS case...
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    63
    Rep Power
    16

    re...


    qry_Search_Results was built in the 'views' area of SQL Server. It seems to have a pretty similar query building interface to that of access, so its simply a 'add you tables' then build the relationships and tick the field boxes you require for the query and then you can test it/run it in the same area.

    From looking at some tutorials, its definitely not a stored procedure. I should also point out that I didn't build the query hence I'm having to find out about it, as another guy did it, but is away at the moment.

    I will post the SQL code at lunch time, so you might be able to take a look if you feel it will help, (am out of the office at the moment)

    Either way, thank you for the pointers and advice so far !

    Kind regards

    Jon
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2004
    Posts
    1
    Rep Power
    0

    Solved


    I realize this is an old thread, but I wanted to post the solution here in case anyone else has this problem.

    After pulling my hair out for a couple of days trying to figure out this issue, I was able to get it fixed by changing the ADO recordset object CursorType to "2" (dynamic). The default of "0" is a forward-only cursor so if you try to access the fields in a different order than they are listed in from the SQL query (even using 'SELECT *'), then you'll just get an empty string for the value.

    St00pid auto-generated code.

    Hope this helps...
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    63
    Rep Power
    16

    Re


    Hey thanks for posting this,

    I managed to fix the problem some time ago, just by re-doing the table, but never really worked out what the problem was.

    Never realised I had to query the fields in the right order !

    Will remember this next time !

    Many thanks


    Jon
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Posts
    1
    Rep Power
    0

    Another possibility - different field types


    Hi,

    I also realize that this is an old thread, but I've been so stuck on this for at least 4 hours, and I couldn't find an answer anywhere, I thought to write down my solution for others who might encounter this as well.

    Basically I used (recordset).GetRows to retrieve each row of the database query result, and found that one of the fields in the 2D array returned by GetRows, was always empty when in fact I knew that it was not. The problem was that the 5th and 6th fields were created using the Text datatype in the SQL 2000 database. One of the fields would show in the array, but not the other one. Luckily, I didn't have to use a Text field for the 6th field so I changed it to a VarChar datatype, and now the array field is showing the correct result (it's no longer empty).

    Does anyone know why this happens and if there's a way to have GetRows properly populate all array fields if there are various fields declared as Text?

    thanks
  28. #15
  29. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,560
    Rep Power
    4550
    As long as you do the getrows right after you create the recordset I think it should work, I have often used getrows with text columns.
    ======
    Doug G
    ======
    I've never been able to appreciate the sublime arrogance of folks who feel they were put on earth just to save other folks from themselves .." - Donald Hamilton
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo