GetRows doesn't geddit!

All Forums >> [Web Development] >> ASP and Database

Message

yogaboy -> GetRows doesn't geddit! (2/9/2005 6:55:42)

I've upsized an Access 2003 database into the SQL Desktop Engine on my local box. Finally got the DSN set up (couldn't get the OLEDB to work) and started pulling out data - but it would only get the first few cols of data, then nothing till the last few.

I've done a little bit of tinkering, and it seems that if I use myRS("FieldName"), instead of putting the recordset into an array using GetRows, then I get all the data.

Does anyone know why this would happen?

Here is some 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 " "
      End If
     Response.write "</td>"
    Next
   Response.write "</tr>"
   			   
   Next

Here is the quick alternative to GetRows to test the recordset - this works for any field

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

In the words of Penelope Pitstop - Hayulp!

rdouglass -> RE: GetRows doesn't geddit! (2/9/2005 14:00:21)
quote: '############### '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 .... This to me just looks wrong and/or incomplete. Where is the SQL called? I'd do it like this: <% mySQL = "SELECT * FROM ByteIt_Journal WHERE ([DateActioned] BETWEEN '01-Sep-2004' AND '31-Dec-2004') ORDER BY DateActioned DESC" myDSN = "DSN=XXX;uid=XXX;pwd=XXX;database=XXX" set conntemp=server.createobject("adodb.connection") conntemp.open myDSN set rstemp=conntemp.execute(mySQL) IF rstemp.eof THEN response.write ("ERROR IN:" & mySQL) rstemp.close set rstemp=nothing conntemp.close set conntemp=nothing ELSE alldata=rstemp.getrows 'Do your stuff END IF %> See with what you posted, I don't see where you're "feeding" the SQL. Also you're introducing myRS.getrows when you're using myConn.Open with the SQL. Is that any help?

yogaboy -> RE: GetRows doesn't geddit! (2/10/2005 18:26:23)
Nope, that's my fault! I cut out all the other bits of code to help make it clearer for those wishing to help, and through my clumsy pruning I caused the opposite - sorry!![:)] I have the setting of the recordset etc in my code. 'SETTING RECORDSET OBJECT AND OPENING RECORDSET USING SQL STATEMENT Set myRS = Server.CreateObject("ADODB.Recordset") 'myRS.Open mySQL, myConn set myRS = myConn.execute(mySQL) The code was working properly with the Access database, and it partially works with the SQL. What I have deduced is that it is not the DSN, and I don't think it is anything to do with the SQL engine. If I call for a field using rstemp.Field.Value then I get all the data back, if I use GetRows it misses out chunks of data. I don't get that bit about quote: Also you're introducing myRS.getrows when you're using myConn.Open with the SQL. ??? (but I've changed it now as you can see above)

rdouglass -> RE: GetRows doesn't geddit! (2/11/2005 10:05:28)
A quick check: If you change your SQL query to be just the COUNT of those records, do you get the same count for each (getrows vs. RS)? How 'bout all records (SELECT * FROM table)? Do you get the same results?

yogaboy -> RE: GetRows doesn't geddit! (2/11/2005 16:23:21)
I get the same count for both, and I've tried explicitly declaring each column and using * but same with both. The only difference is that getrows just isn't cutting it![X(] I've found someone else at ASPFree with exactly the same problem on exactly the same day my prob started, and an almost identical setup, so I don't think it's down to my code. I've patched everything, set up logging on so many things, I just think there's a bug somewhere. I'm just going to ditch GetRows and use what works, and maybe I'll find out what caused it later. With the risk of repeating myself, thanks for the help Rdouglass, it is always much appreciated.

rdouglass -> RE: GetRows doesn't geddit! (2/11/2005 16:29:58)
quote: I'm just going to ditch GetRows Maybe you should for the moment, but not altogether. Is there maybe some character(s) in the results that are causing ASP to choke? Cause if you're getting the same COUNT values, the connection (and GetRows) method must be OK. And to be honest, I use getrows all the time and have never run up against that prob.

yogaboy -> RE: GetRows doesn't geddit! (2/11/2005 16:38:35)
Same for me, I'm a getrows convert[:D], but the Ubound and the myRS.Fields.Count come out the same. I've tried things like response.write alldata(4,4) and it returns nothing. I would think that the array takes it's size from the Recordset, rather than adding a field at a time and resetting the ubound (pure speculation, but that would make sense to me!) - perhaps the array isn't being filled properly, or perhaps it's not outputting properly??? It really is a mystery (:-?

Page: [1]

6.152344E-02