Would like to be able to assist with a sample guys, but this is quite a big
project and it would be a heck of a lot of work to strip it down to a basic
reproducible sample
But it IS a real problem (for me anyway), the biggest pain is the fact that
there is no error generated.
I have only suffered from "(a) not show up at all, (b) only show up the
first time they're called," (
http://www.aspfaq.com/show.asp?id=2188)
The problem is definately compounded by the use of the * in the select - so
even if the table has only 3 fields its still better to list them
individually in the select, although that still won't be enough in the case
of memo fields ( not sure what the SQL upsizer converted those to at the
moment)
The ASPFAQ article doesn't indicate its limited to just one database type -
but our code has been running for about 2 years on Access without problem
( with rather to much use of the * in SELECT's it has to be said).
Have converted it to MSDE over the last 3 weeks, and had numerous instances
of fields not being picked up.
Using lastest MDAC - 2.8 I think
And yes I have seen it with what was an Access date/Time field -
rs("FullMemberUntil") in sample #2
Also as I said the order of storing the recordset variables is important -
seems it must follow the left to right order of how it would appear in
Access Table display
I think Rahul may need to have 2 differnet set of recordset to var
assignment statments to match each of his 2 SQL statements
samples
I have refered to the field types by their Access names, don't have access
to what the SQL upsizer converted them to at the moment
* #1 Despite specifically calling out all the fields used, had to resort to
For/Next to get ALL vars
* text_n ( Access memo fields) head_n and image_n (Access text fields)
*
dim fieldlist
fieldlist =
"PageTitle,EditorType,head_1,head_2,head_3,head_4, head_5,image_1,image_2,ima
ge_3,image_4,image_5,text_1,text_2,text_3,text_4,t ext_5"
sql = "SELECT " & fieldlist & " FROM [pages] WHERE CompanyID = " & CompanyID
& " AND PageNo= " & PageNo
set rs_pages = Conn.execute(sql)
if rs_pages.EOF then ' there is no page in database, so create an empty one
sharpish, and re enter at the top of the page
response.redirect "CreateBlankPage.asp?CompanyID=" & CompanyID & "&pageno="
& pageno
end if
for i = 1 to 5
textblock(i) = trim(rs_pages("text_" & i ) & " ")
headblock(i) = trim(rs_pages("head_" & i ) & " ")
imgcapblock(i) = trim(rs_pages("image_" & i ) & " ")
next
pagetitle = rs_pages("PageTitle")
* #2
* This line "if FullMemberUntil >= Now then" used to be "if
rs("FullMemberUntil") >= Now" - but had to be converted to what you see
below to work in MSDE
* "FullMemberUntil" is a Access dateTime field
sql = "SELECT Company.*, Locality.*, Countries.* "
sql = sql & "FROM Countries INNER JOIN (Locality INNER JOIN Company ON
Locality.LocalityID = Company.LocalityID) ON Countries.CountryID =
Locality.CountryID "
sql = sql & " WHERE CompanyID=" & CompanyID
Set rs = conn.Execute(sql)
' do not change the order of these statements
comments = trim(rs("comments") & "") #### a meno field from
company table
GMcustomerID = rs("GMcustomerID") #### a number field from company
FullMemberUntil = rs("FullMemberUntil") #### a date/time field from
company
county = rs("county") #### a text
field from Locality
region = rs("region") #### a text
field from Locality
countryname = rs("countryname") #### a text field from
Countries
' do not change the order of these statements
if FullMemberUntil >= Now then
IsFullMember = True
else
IsFullMember = false
end if
project :
www.index.guestmaster.com
"Ray at <%=sLocation%>" <ra*@ajf8jalskdfna.sefrhja7yasdf.com> wrote in
message news:uy**************@TK2MSFTNGP11.phx.gbl...
I'd like a copy too please. :]
Ray at home
"Bob Barrows" <re*******@yahoo.com> wrote in message
news:OA**************@TK2MSFTNGP12.phx.gbl... only me wrote: You don't mention which database is in use SQL server or Access - I
suspect the former
Having just ported a project across from Access to Sql Server (MSDE
actually), I have to say how suprised I was at the way recordset
variables would just disappear, or not be available shortly after
having opened a recordset.when using SQL server compared to Access/Jet
I have NEVER encountered this problem. Even before I learned to start
using GetRows. Of course, I rarely use Text columns, but you seem to be saying
that it occurs for other dataypes as well ...
If you have a repro (SQL DDL, sample data, asp page) for this problem I
would love to play with it. I have never been able to reproduce this
problem when it has come up in these newsgroups.
Thanks,
Bob Barrows