SQL not returning all results

Contributing User

Join Date: Nov 2003

Location: UK

Posts: 2,166

Time spent in forums: 3 Weeks 21 h 31 m 35 sec
Reputation Power: 238

SQL not returning all results

I've just converted from access to SQL but when i run my pages with get the info from a record it only displays about half of the information. It seems like it just drops it but the sessions are still running fine. Any ideas? Been racking my head don't know why..
cheers in advance for any help

February 7th, 2005, 10:24 AM

Contributing User

Join Date: Jun 2004

Posts: 3,002

Time spent in forums: 6 Days 19 h 40 m 17 sec
Reputation Power: 105

Make sure you do not have an "On Error Resume Next" statement in your code. If you do, and removing it does not produce an error, check your data fields. In Access you would wrap dates in #...# but do not do this in SQL Server. Failing this you would have to post your code

__________________
selwonk

If I've posted some code above, you might think it looks a bit simplistic. It might be. I'd rather people tried the next step themselves rather than getting a full solution on a plate. That way they learn more!

February 8th, 2005, 04:21 AM

Tonny-Soeroso

Contributing User

Join Date: Jun 2004

Posts: 36

Time spent in forums: 7 h 56 m 27 sec
Reputation Power: 0

set your command timeout to zero

February 8th, 2005, 07:36 AM

Contributing User

Join Date: Feb 2005

Location: Near London

Posts: 112

Time spent in forums: 14 h 37 m 29 sec
Reputation Power: 5

I've done the same thing, upsized an Access2003 to SQL Desktop Engine running locally, and I've got the same problem - it writes the info from the first few cols then nothing until the last few cols.

Using a DSN, IIS5.1 running everything locally on XP. Using ASP to get the data. I've got an .adp file attached to the database and it can see the data.

Tried changing command timeout to 0, but it didn't work.

Here is the code

Code:

   '###############
   'the connection
   Dim myConn
   Set myConn = Server.CreateObject("ADODB.Connection")
   myConn.CommandTimeout = 0
   myConn.Open "accountsSQL"
   '################
   'the sql statement
   SELECT * FROM ByteIt_Journal WHERE ([DateActioned] BETWEEN '01-Sep-2004' AND '31-Dec-2004') ORDER BY DateActioned DESC
   '#################
   'getrows
   
   alldata=myRS.getrows
      
   	numcols=ubound(alldata,1)
   	numrows=ubound(alldata,2)
   
   myRS.Close
   		Set myRS = Nothing
   		myConn.Close
   		Set myConn = Nothing
   '#####################
   'outputting the data
   
   For i = 0 to numrows
     Response.write "<tr>"			   
   			   
     For j = 0 to numcols
      response.write "<td class='tds'>"
      If alldata(j,i) <> "" then
   	 thisfield = alldata(j,i)#
   	 Response.write thisfield
      Else
   	 Response.write "&nbsp;"
      End If
     Response.write "</td>"
    Next
   Response.write "</tr>"
   			   
   Next

Oh, I forgot to mention that I've tried this on another almost identical table and get the same problem, and I've tried expressing every column name with [] delimiters, and the date delimiters are not #.

February 9th, 2005, 06:35 AM

Contributing User

Join Date: Feb 2005

Location: Near London

Posts: 112

Time spent in forums: 14 h 37 m 29 sec
Reputation Power: 5

Instead of using GetRows I've tried using myRS.FieldName and it can pick up all of the data, so it's something to do with getrows.

February 9th, 2005, 07:29 AM

Contributing User

Join Date: Nov 2003

Location: UK

Posts: 2,166

Time spent in forums: 3 Weeks 21 h 31 m 35 sec
Reputation Power: 238

SQL not returning all results

i tried the timeout command but didn't do anything either.

but something i found is that the code works on the main SQL server (which is hosted) but not on my laptop

so i think it's got to be some setting in the SQL Server that I ned to do, but I've searched through for differances but still no joy.

Anyone else have any ideas please?

Regards

February 9th, 2005, 09:29 AM

Contributing User

Join Date: Feb 2005

Location: Near London

Posts: 112

Time spent in forums: 14 h 37 m 29 sec
Reputation Power: 5

It sounds like we have a very similar problem and I'm hoping if you solve yours it will help solve mine!

What are you using to get the data - ASP, PHP, .NET? Are you using a DSN or a OLEDB string?

February 9th, 2005, 09:34 AM

Contributing User

Join Date: Nov 2003

Location: UK

Posts: 2,166

Time spent in forums: 3 Weeks 21 h 31 m 35 sec
Reputation Power: 238

SQL not returning all results

i'm using ASP with a DSN connection on with an XP Pro O/S.

February 9th, 2005, 11:59 AM

Contributing User

Join Date: Nov 2003

Location: UK

Posts: 2,166

Time spent in forums: 3 Weeks 21 h 31 m 35 sec
Reputation Power: 238

SQL not returning all results

Has anyone else got any ideas that'll help us please.....

#10

February 10th, 2005, 10:20 AM

Contributing User

Join Date: Feb 2005

Location: Near London

Posts: 112

Time spent in forums: 14 h 37 m 29 sec
Reputation Power: 5

Mark - are you using GetRows to return the data?

I used the following code to test what was happening, call it Page 2 - page 1 uses GetRows.

Page 2

Code:

For i = 0 to myRS.Fields.Count -1
		Response.write "<th>" & myRS(i).Name & "</th>"
	Next

	While NOT myRS.EOF
	
		Response.write "<tr>"
		  For i = 0 to myRS.Fields.Count -1
	
		     Response.write "<td>" & myRS(i) & "</td>"
			
		  Next
		Response.write "</tr>"
		
		myRS.MoveNext
	Wend

I also set up another DSN, identical to the first, and I setup up some logs to see what happened with the connections.

Firstly, Page1 using GetRows does not return all of the data, just the first 2 cols then the last 4 cols, but all the rows are there.

Page2 using the code above returns all the records.

I then swapped the DSN connection that each page used.

Same as before, Page1 didn't return everything, Page2 did. This suggests it's not the connection.

Interestingly, looking at the logs, even though the 2 DSNs are apparently identical in all but name, the 2nd one is quicker and far more efficient! I don't know enough to be able to analyse the logs further or give any idea as to why this happens.

Having done all this (god, do I feel like an overworked geek!

I think it has nothing to do with the DSN or the database, but everything to do with IIS5.1. I might setup a 2003 server and test the code/database on that as it will have IIS6. I'm also going to check my IIS5.1 settings and see if there's anything on there to do.

Iain

#11

February 10th, 2005, 02:29 PM

Contributing User

Join Date: Jun 2004

Posts: 3,002

Time spent in forums: 6 Days 19 h 40 m 17 sec
Reputation Power: 105

Have you patched SQL Server and installed the latest MDAC download?

#12

February 10th, 2005, 05:45 PM

Contributing User

Join Date: Feb 2005

Location: Near London

Posts: 112

Time spent in forums: 14 h 37 m 29 sec
Reputation Power: 5

I'm running XP Pro sp2, MDAC 2.8 and service pack 3a for SQL Desktop Engine.

#13

February 11th, 2005, 06:35 AM

Contributing User

Join Date: Nov 2003

Location: UK

Posts: 2,166

Time spent in forums: 3 Weeks 21 h 31 m 35 sec
Reputation Power: 238

SQL not returning all results

I checked my MDAC I'm running 2.8 SP1 for XP SP2 using the Component Checker

Is there any other patches you might think I could be missing?

Cause everyone who has this problem seems to be on XP with SP2....

#14

February 11th, 2005, 06:53 AM

Contributing User

Join Date: Jun 2004

Posts: 3,002

Time spent in forums: 6 Days 19 h 40 m 17 sec
Reputation Power: 105

I can't find any known issues for SP2. I'll do some extra digging

#15

February 11th, 2005, 06:56 AM