ASP Programming
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Programming Languages - More > ASP Programming
Reload this Page

Pls Help - Before I go insane ! Unusual Recordset Problem !

Discuss Pls Help - Before I go insane ! Unusual Recordset Problem ! in the ASP Programming forum on Dev Shed.
Pls Help - Before I go insane ! Unusual Recordset Problem ! ASP Programming forum discussing Active Server Pages coding techniques and problem solving methods. Use VBScript or Jscript to make dynamic web applications.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Page 1 of 2 1 2 >
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!
  #1  
Old September 9th, 2003, 03:54 AM
Jonny5uk Jonny5uk is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jan 2003
Posts: 62 Jonny5uk User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 9 m 10 sec
Reputation Power: 6
Pls Help - Before I go insane ! Unusual Recordset Problem !
Hi there,

I posted this a while back but have narrowed down the problem, yet still not fixed it.

I have an SQL database with a table I'm trying to display data from, simple enough..... I know that there is data in the table in the database.

I have a simple ASP page with a recordset that does a SELECT * on dbo.tbl_Car_Details. I then have these fields that I'm requesting to be displayed;

Year
Engine Size
Tax
MOT
Location
Specification
Mods
Price
Email
Seller Type
Service History

For some odd reason, they will not all display together, price, MOT, seller_type and service_history won't display. And yet if I remove certain fields, for example if I remove Location, Price will then display. And if I remove email, Tax will then display. However this is not a consistent ruling. I.e. if I remove specification, nothing re-appears.

I have checked the column names and they all match.


If anyone has any ideas what might be causing this, it would be massively appreciated as I've been stuck for days and days now and its turning into a bit of a nightmare !

I have posted my code below, but don't think there's too much wrong with it.

Here's the url if anyone would like a look..

http://www.uprated.com/search_cars_results.asp?index=13

Any help would be great !

Kind regards

Jon

----------------------------------------------------------------

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/jon.asp" -->
<%
Dim rsDetails
Dim rsDetails_numRows

Set rsDetails = Server.CreateObject("ADODB.Recordset")
rsDetails.ActiveConnection = MM_jon_STRING
rsDetails.Source = "SELECT * FROM dbo.tbl_Car_Details"
rsDetails.CursorType = 0
rsDetails.CursorLocation = 2
rsDetails.LockType = 1
rsDetails.Open()

rsDetails_numRows = 0
%>
<html>
<body>
<table width="684" height="22" border="1" cellpadding="0" cellspacing="0" class="searchtxt">
<tr>
<td width="127">Year</td>
<td width="551"><%=(rsDetails.Fields.Item("Car_Year_Manufacture").Value)%></td>
</tr>
<tr>
<td>Engine Size</td>
<td><%=(rsDetails.Fields.Item("Car_Engine_Size").Value)%></td>
</tr>
<tr>
<td>Tax</td>
<td><%= (rsDetails.Fields.Item("Car_Tax").Value) %></td>
</tr>
<tr>
<td>MOT</td>
<td><%= (rsDetails.Fields.Item("Car_MOT").Value) %></td>
</tr>
<tr>
<td>Location</td>
<td><%=(rsDetails.Fields.Item("Car_Location").Value)%></td>
</tr>
<tr>
<td>Specification</td>
<td><%=(rsDetails.Fields.Item("Car_Specification").Value)%></td>
</tr>
<tr>
<td>Mods</td>
<td><%=(rsDetails.Fields.Item("Car_Modifications").Value)%></td>
</tr>
<tr>
<td>Price</td>
<td><%=(rsDetails.Fields.Item("Car_Price").Value)%></td>
</tr>
<tr>
<td>Email</td>
<td><%=(rsDetails.Fields.Item("Cust_Email").Value)%></td>
</tr>
<tr>
<td>Seller Type</td>
<td><%=(rsDetails.Fields.Item("Car_Seller_Type").Value)%></td>
</tr>
<tr>
<td>Service History</td>
<td><%=(rsDetails.Fields.Item("Car_Service_History").Value) %></td>
</tr>
</table>
</body>
</html>
<%
rsDetails.Close()
Set rsDetails = Nothing
%>
Reply With Quote
  #2  
Old September 9th, 2003, 07:23 AM
Vlince Vlince is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Canada, Quebec, Montreal
Posts: 410 Vlince User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
try the code in the attachment file I've provided...

Hope this helps!
Sincerely

Vlince
Attached Files
File Type: asp tmp.asp (2.0 KB, 288 views)
Reply With Quote
  #3  
Old September 9th, 2003, 07:46 AM
Jonny5uk Jonny5uk is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jan 2003
Posts: 62 Jonny5uk User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 9 m 10 sec
Reputation Power: 6
re...
Hi Vlince,

Thanks for your reply,

Unfortunately I'm getting this error....

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/tmp.asp, line 20

Set rsDetails = objConn.Execute strSql



Any ideas?

Many thanks

Jon !!!
Reply With Quote
  #4  
Old September 9th, 2003, 07:56 AM
Vlince Vlince is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Canada, Quebec, Montreal
Posts: 410 Vlince User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Make a Response.Write of your SQL Query...like this:

strSql = "SELECT * FROM tbl_Car_Details"
'FOR DEBUG ONLY
'Response.Write strSql & "<hr>"
'Response.End

Then paste the Query in your database and execute it.
See if it works

Perhaps you *might* have to add the *dbo.* in front but I doubt it, anyway try it and see!

Hope this helps!
Sincerely

Vlince
Reply With Quote
  #5  
Old September 9th, 2003, 09:03 AM
Jonny5uk Jonny5uk is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jan 2003
Posts: 62 Jonny5uk User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 9 m 10 sec
Reputation Power: 6
re...
Hi Vlince,

Thanks again for your reply,

Without meaning to sound completely stupid, I'm not too sure what you mean in your instructions.

For example do I replace

strSql = "SELECT * FROM tbl_Car_Details"

with

strSql = "SELECT * FROM tbl_Car_Details"
'FOR DEBUG ONLY
'Response.Write strSql & "<hr>"
'Response.End

And do you mean create a new query in my database? and then paste the above code and run it?

Sorry I'm just not quite sure what you mean, if you could give me the instructions in slightly more 'simpleton terms' that would be much appreciated !

Thanks again

Jon
Reply With Quote
  #6  
Old September 9th, 2003, 09:40 AM
Vlince Vlince is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Canada, Quebec, Montreal
Posts: 410 Vlince User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
What I meant was, since I forgot to put it in the file example, to add these 3 lines of code :

'FOR DEBUG ONLY
'Response.Write strSql & "<hr>"
'Response.End


You'll notice that the 3 lines are commented and that they are only there so that *YOU* can debug.

Example, assuming you have an error like the one you shown:

Microsoft VBScript compilation error '800a0401'
Expected end of statement
/tmp.asp, line 20


The error is on line 20, and that line is:
Set rsDetails = objConn.Execute strSql

That doesn't necessarily mean that the error is caused by the line 20.
It could mean that when you tried to execute your SQL Query, that the query wasn't good/correct. If that is the case then your database *is* the one throwing the error.

That's why its considered good practice to *prepare* yourself for debugging.
Now all you need to do is *UNcomment* the 2 lines under the :
'FOR DEBUG ONLY section.

This will, because of the Response.Write strSql, print on/in your browser the actual SQL Query **you are about to execute**
The Response.End's job is to *stop* the execution of the rest of the page...

Now, you can COPY/PASTE the result that you see on/in your browser(that is the SQL Query of course) and paste it inside your database.

What do I mean by pasting it...
I mean, for example, if your using SQL Server, then open QueryAnalyzer and paste the Query result it there.
Then run/execute it, see if the results are ok...if you have a mistake then QueryAnalyzer will tell you.
And if you do, then you'll know that your Query inside your ASP page is wrong! you'll then be able to fix it.

Now if you're using Access, then simply create a *new query* then go to the SQL pane(cause in Access, you can create a new query in Design View or SQL view).
Chose the SQL view, then paste the results in there, again, run/execute it and see if the Query works and most importantly if it returns what you're expecting.

So that's why the 'FOR DEBUG ONLY section is there...not that its a *must* be it's good practice to have it there, just in case something goes wrong.

Hope this helps!
Sincerely

Vlince
Reply With Quote
  #7  
Old September 9th, 2003, 10:12 AM
Jonny5uk Jonny5uk is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jan 2003
Posts: 62 Jonny5uk User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 9 m 10 sec
Reputation Power: 6
re...
Hi Vlince,

Thanks for the detailed reply, got it this time !!

Anyways tried what u said with the debugging bit and it printed out 'SELECT * FROM dbo.tbl_Car_Details'

This is correct as I'm simply trying to display results directly from my dbo.tbl_Car_Details, and am not using a query, however I have pasted this into the Query Analyser and it shows all the data.

However is mentioned, when i try to show them in my recordset, they don't all show?

I'm just trying to work out why it won't show simple fields that have data in them, in the database.

any other ideas ?

Hope so !

Kind regards

Jon
Reply With Quote
  #8  
Old September 9th, 2003, 10:51 AM
Vlince Vlince is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Canada, Quebec, Montreal
Posts: 410 Vlince User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Well...do you still get that error message ???

Microsoft VBScript compilation error '800a0401'
Expected end of statement
/tmp.asp, line 20
Reply With Quote
  #9  
Old September 9th, 2003, 11:06 AM
Jonny5uk Jonny5uk is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jan 2003
Posts: 62 Jonny5uk User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 9 m 10 sec
Reputation Power: 6
re..
Yup, its still there,

Not sure why, I've hidden the other lines i.e.

strSql = "SELECT * FROM dbo.tbl_Car_Details"
'FOR DEBUG ONLY
'Response.Write strSql & "<hr>"
'Response.End

It should work, as was working before with my original recordset, well was showing some of the data, not all of it tho.

Not sure what the problem is......
Reply With Quote
  #10  
Old September 9th, 2003, 12:24 PM
Vlince Vlince is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Canada, Quebec, Montreal
Posts: 410 Vlince User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Try this then:
Set rsDetails = objConn.Execute(strSql)

Notice the two parenthesis ( and )

If that doesn't help then try putting this:

<%
. . .Beginning code. . .
. .
.
On Error Resume Next

Set rsDetails = objConn.Execute strSql


If Err.Number<>0 Then
Response.Write "Number: " & Err.number & "<br>"
Response.Write "Source: " & Err.Source& "<br>"
Response.Write "Description: " & Err.Description& "<br>"
Response.End
End If


.
. .
. . .Ending code. . .
%>

But try the parenthesis thing first...

Hope this helps!
Sincerely

Vlince
Reply With Quote
  #11  
Old September 9th, 2003, 02:24 PM
Jonny5uk Jonny5uk is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jan 2003
Posts: 62 Jonny5uk User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 9 m 10 sec
Reputation Power: 6
re
Hi Vlince,

Thanks for the reply, the (strSql) worked fine, and recordset is now showing, however I'm still missing the fields that are in the table.

I'm beginning to think that the issue might be my database or something? Although this is strange as when run the Query Analyzer in SQL Server, the data shows fine and when I do it in the recordset test area in dreamweaver it also displays fine, but still misses some fields that contain data when I test it live.

Hmmmmm, any other thoughts ?

Thanks

Jon
Reply With Quote
  #12  
Old September 9th, 2003, 02:36 PM
Vlince Vlince is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Canada, Quebec, Montreal
Posts: 410 Vlince User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Can you show the entire code you have again...

Thanks

vlince
Reply With Quote
  #13  
Old September 9th, 2003, 02:40 PM
Jonny5uk Jonny5uk is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jan 2003
Posts: 62 Jonny5uk User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 9 m 10 sec
Reputation Power: 6
code...
Yup sure,

Here it is......

Hope it helps...

------------------------------------------------------

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<!--#include file="Connections/jon.asp" -->

<%
Dim strSql
Dim objConn
Dim rsDetails

'Create the SQL Query
strSql = "SELECT * FROM dbo.tbl_Car_Details"
'FOR DEBUG ONLY
'Response.Write strSql & "<hr>"
'Response.End
'NOTE: I've removed the *dbo.* cause it's not necessary


'Create the connection object
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open MM_jon_STRING

'Create an IMPLICIT recordset object...
Set rsDetails = objConn.Execute (strSql)

'Check to see if it has reached EOF
If rsDetails.EOF Then
'Close recordset and connection object
rsDetails.Close
Set rsDetails = nothing

objConn.Close
Set objConn = nothing

'Write error message to user...
Response.Write "No records found..."
Response.End
End If

'Continue with the process...
%>
<html>
<body>
<table width="684" height="22" border="1" cellpadding="0" cellspacing="0" class="searchtxt">
<tr>
<td width="127">Year</td>
<td width="551"><%=rsDetails("Car_Year_Manufacture")%></td>
</tr>
<tr>
<td>Engine Size</td>
<td><%=rsDetails("Car_Engine_Size")%></td>
</tr>
<tr>
<td>Tax</td>
<td><%=rsDetails("Car_Tax")%></td>
</tr>
<tr>
<td>MOT</td>
<td><%=rsDetails("Car_MOT")%></td>
</tr>
<tr>
<td>Location</td>
<td><%=rsDetails("Car_Location")%></td>
</tr>
<tr>
<td>Specification</td>
<td><%=rsDetails("Car_Specification")%></td>
</tr>
<tr>
<td>Mods</td>
<td><%=rsDetails("Car_Modifications")%></td>
</tr>
<tr>
<td>Price</td>
<td><%=rsDetails("Car_Price")%></td>
</tr>
<tr>
<td>Email</td>
<td><%=rsDetails("Cust_Email")%></td>
</tr>
<tr>
<td>Seller Type</td>
<td><%=rsDetails("Car_Seller_Type")%></td>
</tr>
<tr>
<td>Service History</td>
<td><%=rsDetails("Car_Service_History")%></td>
</tr>
</table>
</body>
</html>
<%
'Close recordset and connection object
rsDetails.Close()
Set rsDetails = Nothing

objConn.Close
Set objConn = nothing
%>
Reply With Quote
  #14  
Old September 9th, 2003, 04:28 PM
OldJacques's Avatar
OldJacques OldJacques is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2003
Location: in Orbit mostly
Posts: 148 OldJacques User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Have you tried the GetRows troubleshooting test I suggested yesterday on the Table? That really has helped me a couple times in the past, and worth using as a tool.

And using ALL the records (no Where statement) so that you can see all the results that you currently have in the DB.

From the looks of the name, tbl_Car_Details is a table and not a view formed by an SQL statement, is that correct?

And just as a suggestion, I often add conditional troubleshooting debugging code using a few lines like:

' at the beginning of the file
debuggin = 0
If Request.QueryString("debuggin") <> "" Then debuggin = Request.QueryString("debuggin") End If

and then where I need
If debuggin = 1 Then Response.Write "sql is:" & sql & ":<br />" & VbCrLf
or
If debuggin >= 2 Then Response.Write "conn.properties has a value of:" & conn.properties & ":<br />" & VbCrLf

So that if I want to check, I can change the value at the beginning of the file (for several runs), or I can append an ?debuggin=2 to the URL on the fly as well.
Reply With Quote
  #15  
Old September 10th, 2003, 03:22 AM
Jonny5uk Jonny5uk is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jan 2003
Posts: 62 Jonny5uk User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 9 m 10 sec
Reputation Power: 6
Finally Fixed !
Hi guys,

Just wanted to say a big thanks to everybody who helped me out on this one, especially to Vlince and OldJacques.

It seems that they problem lay in my database, still not entirely sure what it was, but after re-creating the table in question and changing the relationships slightly, the missing fields are now displaying in the asp page when i test it live.

Anyways, many thanks again for the input.

Kind regards

Jon
Reply With Quote
Reply
Page 1 of 2 1 2 >

Viewing: Dev Shed Forums > Programming Languages - More > ASP Programming > Pls Help -