Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    11

    Unanswered: Next fun error of the day...

    Converting a (previously working) website to use SQL Server data instead of an Access database. This code worked perfectly before and returns no results now:

    Dim rsItemsInCart, ItemsInCart
    ItemsInCart = Empty

    Set rsItemsInCart = Database.Execute("SELECT * FROM CartItems, Products WHERE CartID='" & CartID & "' AND CartItems.ProductID=Products.ProductID")

    If Not rsItemsInCart.EOF Then
    ItemsInCart = rsItemsInCart.GetRows
    rsItemsInCart.Close
    Set rsItemsInCart = Nothing
    End If

    Any help is greatly appreciated.

    - AtaxiA

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Have you tried running the query in the analyzer?

  3. #3
    Join Date
    Apr 2004
    Posts
    11
    Yes, it checks out. I was wondering if maybe the GetRows uses a different format when accessing SQL databases.

    The strange thing is that no errors appear whatsoever. The variables just come up blank.

    (Thanks again)

    - AtaxiA

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    It ccould be something to do with the cursor type of the recordset.

    Possibly the default cursor for SQL behaves differently to that for MSAccess.

    Trying this

    Code:
    Set rsItemsInCart = New ADODB.Recordset
    strItemsInCart = "SELECT * FROM CartItems, Products WHERE " _
      & "CartID='" & CartID & "' AND CartItems.ProductID=Products.ProductID"
    rsItemsInCart.Open strItemsInCart , Database, adOpenStatic, adLockReadOnly, adCmdText

  5. #5
    Join Date
    Apr 2004
    Posts
    11
    The query is running now. I also rewrote it using a JOIN statement instead of the funky WHERE statement that was in there before, but it returns the exact same results.

    It seems to ignore some fields (see lines 10 and 11). Here's a dump of the data from one record.

    Line0 - 60646
    Line1 - {6B184F6A-5459-4DF9-901C-9B7CFF26041E}
    Line2 - 175
    Line3 - 5
    Line4 -
    Line5 -
    Line6 -
    Line7 -
    Line8 -
    Line9 -
    This is where the second table in the join query starts
    Line10 - (nothing displays, but "175" is in the database)
    Line11 - (nothing displays, but "3 ring binder" is in the database)
    Line12 -
    Line13 - Our 3-Ring vinyl binder with crest on the front is the ideal item for storing all your paperwork.
    Line14 - 1.5
    Line15 - test
    Line16 - 1145
    Line17 -
    Line18 - 1145m_3rb.jpg
    Line19 -
    Line20 - 5.99
    Line21 - 0
    Line22 - False
    Line23 - True
    Line24 - False
    Line25 -
    Line26 - -1
    Line27 - False

    Thanks again,
    - Ataxia

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Yeah, I was going to suggest a join but only after you got it going.

    I suspect your problem with "ignored fields" is more something to do with the actual structure of the returned results....

  7. #7
    Join Date
    Apr 2004
    Posts
    11
    I hate working on other people's code...

    This project was dumped on me at work, and the last guy (who was fired) apparently didn't believe in commenting his code. That makes it MUCH more difficult. Such is the life of a programmer...

    I'm almost tempted to suggest they start from scratch.

    Thanks again for taking a look.

    - AtaxiA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •