[trmnorm]

ASP using an access db. I have create a recordset that will return a single record and I'm trying to retrieve that record. When I try to get the record it won't retrieve that value. This is only happening when it is one record in the recordset. The recordset is not at BOF or EOF. Do anyone have any solutions or other alternatives. Need help ASAP, I have been working on this for several days. Below is the code:

Set rsHotFix2 = Server.CreateObject("ADODB.Recordset")
rsHotFix2.ActiveConnection = MM_AMT_DTS_STRING
rsHotFix2.Source = "SELECT Max([CID]) AS LastCID, SID FROM TCO_Contractors Group By LastCID, SID HAVING (TCO_Contractors.SID= "&rsHirerachy__MMColParam&")"
rsHotFix2.CursorType = 0
rsHotFix2.CursorLocation = 2
rsHotFix2.LockType = 1
rsHotFix2.Open()

if rsHotFix2.bof and rsHotFix2.eof then

response.write "Recordset Is Empty"
else
response.write "Recordset Is Not Empty"
response.write rsHotFix2.Fields(0) & "<BR>"
response.write rsHotFix2.Fields("LastCID") & "<BR>"
response.write rsHotFix2.Fields.Item("LastCID") & "<BR>"
response.write rsHotFix2("LastCID") & "<BR>"


end if


This is what's display on the page
Recordset Is Not Empty



Notice it does display the CID value.

[Rockn]

response.write "Recordset Is Not Empty"
response.write rsHotFix2.Fields(0) & "<BR>"
response.write rsHotFix2.Fields("LastCID") & "<BR>"
response.write rsHotFix2.Fields.Item("LastCID") & "<BR>"
response.write rsHotFix2("LastCID") & "<BR>"

Why are you writing out the same thing in 4 different lines of code? Your recordset is only supposed to return the highest numbered CID from the table matching one criteria of SID HAVING (TCO_Contractors.SID= "&rsHirerachy__MMColParam&")" Can you put in a fixed value into your criteria for a known good value in the table or recordset and see if it returns the correct CID then?

[trmnorm]

Thanks Rockn for the replay...

I'm writing it out four different ways, because neither way returns me a value when I know the value should be CID=302. None of these ways of retrieving the value are working.

I have used a known SID=3002 that's not making a difference it's still returns a recordset, but it won't let me retrieve the value...

I'm only having this problem when it's a single record in the recordset. I have never seen this happen before.

trmnorm

[Rockn]

Is this supposed to be Max([CID]) or Max([SID])

[trmnorm]

It's Max([CID])

query return CID = 302

[Rockn]

Have you tried a different cursor type? Has it ever worked. If there is only one record in the table there BOF and EOF are going to be the same. Try:

If Not rsHotFix2.eof then
response.write "Recordset Is Empty"
else
response.write "Recordset Is Not Empty"
response.write rsHotFix2.Fields(0) & "<BR>"
response.write rsHotFix2.Fields("LastCID") & "<BR>"
response.write rsHotFix2.Fields.Item("LastCID") & "<BR>"
response.write rsHotFix2("LastCID") & "<BR>"

[trmnorm]

Once again thank you so much for your timely response and help.

I have fix the problem by finding another way to skin the cat. And yes I did try changing the cursortype as well as the locktype.

So here is the fix, for anyone else who my experience this problem:

I just combine the select statement and the insert statement into a subquery and executed it. And it worked perfectly.

If flagOneContractor = "yes" Then

'response.write "Yes One Record"

cmdIns.CommandText = "INSERT INTO SurveyAreaSupported ( SID, CID, AreaSupported ) SELECT TCO_Contractors.SID, TCO_Contractors.CID, '" & rsGetAS("AreaSupported") & "' AS Expr FROM TCO_Contractors WHERE (((TCO_Contractors.SID)="&pkex&"))"

Else

'response.write "More than one Record"
cmdIns.CommandText = "INSERT INTO SurveyAreaSupported (SID, CID, AreaSupported) VALUES ("&pkex&"," & rsGetNames2("CID") & ",'" & rsGetAS("AreaSupported") & "')"

End If