inconsistent retrieval of data

I have created a sql 2000 db and imported my tables from an access database. My web site is using asp. I am getting inconsistent results when I retrieve data from the database. Even though I can see the data in the database some fields will not retrieve to the web site. When I go to the database and erase the field and then create a new field and then put data in, I will then be able to retrieve the data at the web site. The problem is some pages will be able to retrieve while others won't. Also some fields will return data while others won't. The web site worked fine with access.

Does anyone know what may be causing this?

Thank you in advance.Betcha got spaces in the column names...

or at least unique chars like ? in them

Can you show us some code?|||There are no spaces that I can see or unique chars.

RS.Open "Select * FROM tProjects where playCode = '" & playCode & "'", Conn

filePath = RS("filePath")
myVideo = Split(filePath, ",", -1, 1)

RS4.Open "Select * FROM tVideosViewed WHERE hitID="&hitID&" AND contentID="&myVideo(0), Conn

When the RS4.Open fires then I will receive an error and when I investigate it is because the array is blank and filePath is blank even though there is a value in the database.

Originally posted by Brett Kaiser
Betcha got spaces in the column names...

or at least unique chars like ? in them

Can you show us some code?|||Why not create and call stored procedures?

It'll be more effecient as well

Just curious though, do you have sql server client tools?

Can you type that same select in a query analyzer window?

What would it look like?

Also you can look at profiler to see what's being passed to the server..

my guess is the statement is malformed when it gets passed...

do you know how to start a trace?

I still thinks sprocs are the way to go in any case...|||We found the problem. On certain pages we did not have all the locktypes specified. So SQL server gave us random records back. Once That we specified the locktypes it worked fine.

Thanks for suggestions. Luckily it was an easy fix for us

Originally posted by Brett Kaiser
Why not create and call stored procedures?

It'll be more effecient as well

Just curious though, do you have sql server client tools?

Can you type that same select in a query analyzer window?

What would it look like?

Also you can look at profiler to see what's being passed to the server..

my guess is the statement is malformed when it gets passed...

do you know how to start a trace?

I still thinks sprocs are the way to go in any case...|||Help me out here...what did you do exactly?|||Originally posted by Brett Kaiser
Help me out here...what did you do exactly?

We didn't have the locktype and cursor type set for all the recordsets that we had open. There was multiple recordsets on each page. When I added:

RS.CursorType = adOpenDynamic
RS.LockType = adLockOptimistic

RS2.CursorType = adOpenDynamic
RS2.LockType = adLockOptimistic

For each recordset then we received consistent results from SQL server. It was a small thing but access let us get away with it but SQL wouldn't.

Looks like we have found most of the differences from access to SQL but we need to test it for about a week.