1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

ASP - Cannot Retrieve record from a 'single - record' recordset

Discussion in 'Software Development' started by trmnorm, Oct 29, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. trmnorm

    trmnorm Thread Starter

    Joined:
    Oct 29, 2004
    Messages:
    4
    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.
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    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?
     
  3. trmnorm

    trmnorm Thread Starter

    Joined:
    Oct 29, 2004
    Messages:
    4
    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
     
  4. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Is this supposed to be Max([CID]) or Max([SID])
     
  5. trmnorm

    trmnorm Thread Starter

    Joined:
    Oct 29, 2004
    Messages:
    4
    It's Max([CID])

    query return CID = 302
     
  6. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    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>"
     
  7. trmnorm

    trmnorm Thread Starter

    Joined:
    Oct 29, 2004
    Messages:
    4
    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
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/290145

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice