Wierd ADO problem with ASP

[JC]

OK, what's up with this -- never seen anything like this before:

I have an ASP page that queries the database. When I do a select *
from a table and try to get the values, I must get them out of the ADO
recordset in the EXACT order that the columns are in in the database.
If I get them out of order, the code works, but the value returned is
blank.

Any ideas?

Thanks

[Mario Vargas]

JC,

Can you show a code sample? Maybe I can help.

Mario Vargas

"JC" <cole...@gardencitynet.com> wrote in message
news:ef8bfc04.01121...@posting.google.com...

[JC]

Thanks for the reply.

OK, when I get the fields out of the DB in this order (this is the
EXACT order they are in in the DB), it works and all values are
returned...If I modify the order in which I get the data of the
recordset, the values will be empty (but not null). Any ideas?

Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseServer
rs.Open sql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

sSQL = "select * from BEVNET_Products where " & sWhere
openrs rs, sSQL
bIsUpdateMode = (bPK and not(sAction = "insert" and sForm =
"BEVNET_Products") and not rs.eof)
'-------------------------------

'-------------------------------
' Load all fields into variables from recordset or input parameters
'-------------------------------
if bIsUpdateMode then
fldProduct_ID = GetValue(rs, "Product_ID")

'-------------------------------
' Load data from recordset when form displayed first time
'-------------------------------
if sBEVNET_ProductsErr = "" then
fldName = GetValue(rs, "Name")
fldBrand_ID = GetValue(rs, "Brand_ID")
fldReview = GetValue(rs, "Review")
fldIngredients = GetValue(rs, "Ingredients")
fldNutrition = GetValue(rs, "Nutrition")
fldURL = GetValue(rs, "URL")
fldContainer = GetValue(rs, "Container")
fldAvailability = GetValue(rs, "Availability")
fldRating = GetValue(rs, "Rating")
fldType_ID = GetValue(rs, "Type_ID")
fldIMG_URL = GetValue(rs, "IMG_URL")
fldOrder_URL = GetValue(rs, "Order_URL")
fldOrder_Site_Name = GetValue(rs, "Order_Site_Name")
fldInsertedDate = GetValue(rs, "InsertedDate")
fldLast_Updated = GetValue(rs, "Last_Updated")
fldActive_YN = GetValue(rs, "Active_YN")
fldViewable_YN = GetValue(rs, "Viewable_YN")
end if
SetVar "BEVNET_ProductsInsert", ""
Parse "BEVNET_ProductsEdit", False

Here's the wrapper function GetValue, just so you know what is going
on there:

function GetValue(rs, strFieldName)
on error resume next
if rs is nothing then
GetValue = ""
elseif (not rs.EOF) and (strFieldName <> "") then
res = rs(strFieldName)
if isnull(res) then
res = ""
end if
if VarType(res) = vbBoolean then
if res then res = "1" else res = "0"
end if
GetValue = res
else
GetValue = ""
end if
if bDebug then response.write err.Description
on error goto 0
end function

"Mario Vargas" <angs...@hotmail.com> wrote in message news:<OSFmRVPiBHA.1592@tkmsftngp02>...

[Mario J. Vargas]

Hi, JC,

Your SQL query in sSQL might be returning an empty recordset OR causing an
error your code might be ignoring because your sWhere string might be empty
and you have
sSQL = "select * from ... where " & sWhere
So, when you call, for instance, (I'm assuming that sBEVNET_ProductsErr is
checking for an error and somewhere at the top of your code you have an On
Error Resume Next statement or On Error Goto somewhere where it will assign
something to this variable):

If sBEVNET_ProductsErr = "" Then

fldName = GetValue(rs, "Name")

...
End If

and look at the definition of GetValue, you will see that if the recordset
is empty and the given field name is not an empty string
...

elseif (not rs.EOF) and (strFieldName <> "") then
res = rs(strFieldName)

...
else
GetValue = "" ' This guy returns an empty string...
end if

The GetValue function will return an empty string as I commented above
because the condition evaluates to false. Also, the sBEVNET_ProductsErr
might contain something and since the variables in the If
sBEVNET_ProductsErr = "" Then ... End If statement are already declared AND
empty, it might explain why you are seeing empty values. Try typing
Response.Write "Hey there: " & sBEVNET_ProductsErr after the EndIf.

Also, to help you debug, write something similar in each
If...Elseif...Else...EndIf statement group in the GetValue function that
uniquely identifies each of these and prints out the value of res or what
you are about to return. If might help you figure out exactly what is
working in the function.

I might be barking at the wrong tree, but I hope this helps. I'm still
thinking hard on this. Let me know how you do.

Mario

"JC" <cole...@gardencitynet.com> wrote in message

news:ef8bfc04.01122...@posting.google.com...

[Val Mazur]

Hi,

It looks like we need to see your code.

Thanks,

Val

>.
>

[Robert C. Wafle]

JC,
Seems like its been a while, but have you solved this problem?

My guess is that you are using a text data type.. if you don't need text,
and varchar will suffice, your problems should go away. Also, because each
time you read data in from the recordset there is extra overhead, you could
create a local variable for each field, read the fields into the local
variables at the beginining of your loop.. and get speed and simplicity
(access the variables in any order you like)..

"JC" <cole...@gardencitynet.com> wrote in message
news:ef8bfc04.01121...@posting.google.com...