Mad recordset problems 1

[RevsEd]

Hi,

wonder if anyone can help me - before insanity sets in.

Basically, I have an ASP site working fine with an MS Access database backend.

I've just upsized to SQL Server and now my recordsets aren't working as expected. If I open my recordset and then go to display a value (esp. bits which I'm using as booleans) there's nothing there.

example:

Response.Write (rs.fields("myfield&quot.value)

won't output anything, yet the below works:

iVal = rs.fields("myfield&quot.value
Response.Write (iVal)

It's as running the value through a function means the record isn't read, yet setting a variable to the value forces the read and it's there. I've tried client and server positions for the cursor and just about anything/everything else. I've tried opening the rs, or executing it on the con, all with the same results. I've checked MDAC with the MS ComChecker and it's fine. I'm now at a complete loss - code that worked fine in Access is failing in the weirdest manner.

Anyone got any ideas or seen similar I'd really appeciate the input.

Thanks

Darren

 

[shaddow]

Can you test this code.

Set con=Server.CreateObject("ADODB.Connection")
dbPath=Server.MapPath("mydatabase.mdb")
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
set rs=server.CreateObject("ADODB.Recordset")
sql="select * from mytable"
rs.Open sql,con,3,3
Response.Write rs("myfield")

________
George, M

 

[RevsEd]

Thanks for the response.

I noticed the rs.Open sql,con,3,3 elsewhere,
so was going to try that tonight as well -
I generally don't bother with the 3,3.

Thanks again, will let you know how it goes
tomorrow.

Darren

 

[shaddow]

That saves you a lot of trouble using rs object. It enables all the features(properties,methods) of the rs object.
Also i dont think that it's the problem.
What i want you to do it's to make just an asp who does eaxctly same thing and nothign more. You can try my code or anyother simple code.

________
George, M

 

[RevsEd]

Yes, it's something I should have done myself - I just got sidetracked by the fact that it used to run fine hitting Access and has now all gone to pot. I've tried to write the code with no specific back end in mind, and now to upsize to SQL Server I thought I'd probably have an easy job, not get lost in strange results.

Thanks again for your time

Darren

 

[shaddow]

Normally it shouldnt be any code changes then the connection object and maybe some SQL querry changes.

________
George, M

 

[RevsEd]

Thanks... problem sorted. Completely my fault. After running your simple test (which read the record correctly) and trying hard to make it fall over (by changing the field values) I realised it had to be my code. On much closer examination I realised the page was only calling my code when a form was posted, else it was skipping the code block and picking up the values from a cookie.

One other question though -- I now notice that the SQL Bit column is still returning True/False to ADO, when I expected to have to translate the 1/0 via a function. Is this common functionality of ADO and I can rely on it, or should I translate 1 to True anyway?

Thanks again for putting me on the straight and narrow.


Darren

 

[shaddow]

My opinion it's to turn that bit column to an tinyint or something smaler and use 1 or 0 as value other then true or false.
Bit values are anyway stored as tinyint as i remember so you wont save space for those values.

________
George, M

 

[RevsEd]

Not after saving space, TinyInt is fine, just wondered on the functionality of ADO knowing it's a True/False... which really isn't what I expected at all?

 

[shaddow]

Usualy bit field types are treated like this(true/false).
Anyway 0 should be false and any other value should be true.

________
George, M