Hi there,
I have one strange error, if I read recordset value once there is the expected, but when I read this value second time there is Null.

Windows Server 2003 Standart, MS SQL Express 2005
It is in default.asp (standart IIS)

Im including code here, code where is the problem is typed in bold:

<%
dim cnn,stcnn,rstemp,strstemp

select case ucase(request("chview"))
case "INFO", "CALENDAR", "CAUSE", "NEWS", "HISTORY","LINKS","CONTACT"
session("view")=request("chview")
end select


set cnn = server.createobject("ADODB.Connection")
cnn.open application("database")
query="SELECT * FROM Documents WHERE ViewText='"+session("view")+"' and IsSelectionRoot='True' ORDER BY UID desc;"
set rstemp=cnn.execute(query)

a=0
session("docid")="0"
Odkazy=""

do until rstemp.eof
odkazy=odkazy+"<a href=""default.asp?docid="+cstr(rstemp(0))+""">"+r stemp(2)+"</a><br>"
a=a+1
if a=1 then
session("docid")=cstr(rstemp(0))
end if
rstemp.movenext
loop
if a=1 then
odkazy=""
end if
rstemp.close
cnn.close
%>


'Some HTML code

<%
if session("docid")="0" then
Response.write "Not exists."
else

set cnn = server.createobject("ADODB.Connection")
cnn.open application("database")

query="SELECT * FROM Documents WHERE UID='"+session("docid")+"';"
set rstemp=cnn.execute(query)

select case ucase(rstemp(1))
case "STT"
set stcnn = server.createobject("ADODB.Connection")
stcnn.open application("database")

stquery="SELECT * FROM Statics WHERE StaticID='"+cstr(rstemp(0))+"';"
set strstemp=stcnn.execute(stquery)

'if rstemp(4)=true then
'response.write "<h1>"+strstemp(1)+"</h1>"
'end if

'here it fails:
'(your first reading is successful, but second and any other gets null)

response.write vartype(strstemp(3)) 'Responses 8 (text)
response.write vartype(strstemp(3)) 'Responses 1 (null)

strstemp.close
stcnn.close

end select

rstemp.close
cnn.close

end if

%>


Thanks you for any suggestion.

JVV

__________________
JVV

Are you refreshing the page? Is that what you mean by "second time..."? What exactly do you mean by second reading?

Best to write the queries to the page to see exactly what is getting sent to the database on each pass.

Response.Write stQuery & "<br>" etc.

Then you can see if any of your variables are getting wiped out on the next passes.

Hi, I mean second reading that I want get second value from recordset array (as bolded lines in code).


This is first reading: response.write vartype(strstemp(3)) 'Responses 8 (text)
This is second reading: response.write vartype(strstemp(3)) 'Responses 1 (null)

Table has 5 column (vchar,vchar,vchar,text,boolean) and it is unindexed.

I'm programming for five years and I know only one object which operates this way - if you are getting data from winsock object by the GetData method - you can get ti only one time, if you call it once more, it replies the next piece of buffer...

I was thinking about it today in work and I research that if I dont touch the Boolean type cluster of array (the last one) => (vchar,vchar,vchar,boolean,text), values in array stay in normal. So I try to move the boolean column in database from last to one-before-last position so last is text SQL type.
And its work. Huh - I dont understand it but it looks that boolean value cant be last in recordset...


I believe that it is clear enough.

So I dont have problem now - it is workarounded, but I wonder about that last value in table cant be boolean or what? What Im doing wrong?
Thank you.

JVV

FYI: first and second reading is in the same file, same time, same procedure run over two lines that are neaby by it self. As it is in first post in code wrote in bold.

JVV

Hi,

I don't understand why its working or why it wasn't working and I'm still not sure I understand what you're doing but it looks like you're reading the 4th value in the recordset strsTemp(3). I've never really needed to get particular records except in very rare cases.

Hey, I'm glad its working.

štěstí

Hi,
If you dont need to get particular values from recordset so how do you get values from response to sql query?

I think it is needed when you are working with recordset to access the result array...

I use the boolean value in array as a flag for show some data from record.

JVV

In your SQL statement you are selecting * so I can't really see the column names. When you want to get a particular value you do this.

Say this was your SQL statement:

SELECT Column1, Column2, Column3 FROM Table1

set strstemp=stcnn.execute(stquery)

Do While NOT strstemp.EOF

Response.Write Column1 & " " & Column2 & " " & Column3 & "<br>"

strstemp.MoveNext

Loop

Sometimes I know I'm only going to get back one record so in that case I will use the array value. If the recordset returns just one value, then to access that value as you probably already know is to do this.

MyValue = strstemp(0)

That's going to be the only record in a one value recordset.

I hope I'm not telling you something you already know. I just never had a case where I needed to do what you're doing.

Actually, I'm looking back up at your code and you are using the value from set rstemp=cnn.execute(query) to use in the next query. But you're right, rstemp(0) is only going to give you the first record since you're selecting * in the SQL statement and the first record is going to be the value of the first column. That sounds very iffy to me when you could just SELECT Column1 and set a variable equal to it like - myvalue = rstemp("Column1")

Please excuse me from saying so but your code looks very confusing to me. Just trying to keep the array numbers correct for me is hard that's why I find it easier to just assign the value to a variable and then test for the value like you're doing with either a CASE statement of IF statement.

JÁ am tvůj druh

How do you think that the value from recordset is driven to variable with name of column???

I was test it by this way now and it isnt working...

my code is:


response.write "<div>"
stcnn.open application("database")

stquery="SELECT StaticID, StaticName, StaticAbstract, ShowHeaders, StaticText FROM Statics WHERE StaticID='"+cstr(rstemp(0))+"';"
set strstemp=stcnn.execute(stquery)

response.write StaticID 'it is doing nothing as well as variable called column1

if strstemp(3)=true then
response.write "<h1>"+strstemp(1)+"</h1>"
end if
response.write strstemp(4)


strstemp.close
stcnn.close

response.write "</div>"


If I change the list of selected columns to * the result is the same in this case, because all columns is listed in their accordance...

I already know that you can use the column name over its number...

But problem was (when last column was 'ShowHeaders' the boolean) if I read it in the 'IF' clause other values goes null so if the 'ShowHeaders' was False, the server responses <div></div>, if the value is True server responses <div><h1></h1></div>.

If you experimentally read first value for example 2 it resolves the right value stored in database in column 2 but all other columns from recordset goes NULL so if you read the same value two times (repeating the command), the same command replies <text value> first and the NULL as the second in scope of two lines...

As I told before the solution is store bools in not last position.


Yes the code is very confusing but it is better to me store it in numbers because since I use text values as parameters it will be more confusing for me...



JVV

Hah, yeah I was going to say that you would probably be confused by my code.

Everyone has their own way.

Richard

It is very strange error, but can be workarounded

Hey guy, in every way I want to thank you for your help!

Jan

JVV