SQL2000 recordset returns 'undefined' intermittently

[clegg]

I am connecting to an SQL 2000 database with OLE. My
connection is fine and records are returned, but certain
field names seem to cause empty or undefined records to
return. If I then move the recordset request that returned
undefined to a different position, it will return a value,
but everything below it will return undefined!

The problem variable in the code below is "Hours".

This is driving me nuts. I had the same problem in a
different script with the word 'Name'. I had to fix it by
changing the field name to B_name in the database.

I can't figure it out, please help.

the code is below,

clegg

//define Connect and create the database object
Connect = Server.CreateObject("ADODB.Connection");

//open the stream to the database
Connect.Open(FilePath);

//create the record set
rs = Server.CreateObject("ADODB.RecordSet");
rs.Open(sql,Connect);


//******** if I move these three lines below the 'Hours'
recordset request, they will return undefined ***//
breakfast = String(rs("Hour_breakfast"));
lunch = String(rs("Hour_lunch"));
dinner = String(rs("Hour_dinner"));

//******* With this call here it will return undefined ***/
hours = String(rs("Hours"));

rs.Close();
Connect.Close();

[Bob Barrows]

Hmm, "Hours" does not seem to be a reserved word
([url]http://www.aspfaq.com/show.asp?id=2080[/url]), but you might try bracketing it
(Select ..., [Hours], ... ) in your query just to make sure.

Just to be sure, Hours is not a Text column is it? How about the other three
columns that are giving you the problem?

Try doing a
Response.Write(rs.GetString(,";","<BR>"));
(almost forgot you were using jscript ... ) just as a quick check on the
contents of your recordset.

If this does not help, show us the Select clause of your SQL statement
(you're not using "select *", I hope)

HTH,
Bob Barrows

clegg wrote:

> I am connecting to an SQL 2000 database with OLE. My
> connection is fine and records are returned, but certain
> field names seem to cause empty or undefined records to
> return. If I then move the recordset request that returned
> undefined to a different position, it will return a value,
> but everything below it will return undefined!
>
> The problem variable in the code below is "Hours".
>
> This is driving me nuts. I had the same problem in a
> different script with the word 'Name'. I had to fix it by
> changing the field name to B_name in the database.
>
> I can't figure it out, please help.
>
> the code is below,
>
> clegg
>
> //define Connect and create the database object
> Connect = Server.CreateObject("ADODB.Connection");
>
> //open the stream to the database
> Connect.Open(FilePath);
>
> //create the record set
> rs = Server.CreateObject("ADODB.RecordSet");
> rs.Open(sql,Connect);
>
>
> //******** if I move these three lines below the 'Hours'
> recordset request, they will return undefined ***//
> breakfast = String(rs("Hour_breakfast"));
> lunch = String(rs("Hour_lunch"));
> dinner = String(rs("Hour_dinner"));
>
> //******* With this call here it will return undefined ***/
> hours = String(rs("Hours"));
>
> rs.Close();
> Connect.Close();

[Aaron Bertrand - MVP]

> THE REASON (as far as I can tell):

>
> When a forward only cursor accesses the db it copies the
> recordset into local memory and makes all calls to the
> recordset from there.
> The static cursor on the other hand takes it's values
> straight from the database as it needs them.
> I believe that at the time the forward only cursor was
> copying the data to local memory, something interrupts it
> and it misses a few values, leaving some vars undefined.
> since it then uses this memory, OOPS undefined.
> Static just asks for the data till it gets it.

I've never experienced this ...