Next fun error of the day...

ataxia1 · #1 (**permalink**) 04-01-04, 23:18

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

rokslide · #2 (**permalink**) 04-01-04, 23:39

Have you tried running the query in the analyzer?

ataxia1 · #3 (**permalink**) 04-02-04, 00:09

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

rokslide · #4 (**permalink**) 04-02-04, 00:22

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

ataxia1 · #5 (**permalink**) 04-02-04, 11:05

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

rokslide · #6 (**permalink**) 04-02-04, 16:42

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....

ataxia1 · #7 (**permalink**) 04-05-04, 10:47

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

Internet Services

Web Development

Digital Marketing

Consumer Tech