Shopping Cart not updating until refresh

[Winshent]

I am having problems with adding items to my shopping cart. The problem
occures when adding items that already exists in the cart.

When a user adds to cart, they are automatically redirected to the
cart,
where they can update quantities.

If the item already exists in the cart, i want to update the quantity
by adding 1 to the quantity of that item. The code all works fine when
i step thru the code, however when running it without debugging.. the
quantity does
not is not updated until the cart page is refreshed..

I can only think that the record in the cart table has not been
updated, by the time that the cart is being read to load the cart page.
I have tried clearing the cache in between adding items, but this did
not solve the problem.

The AddItem function calls the UpdateItem function if the item already
exists in the cart. Otherwise it continues to add the item.

Here is my code:

'################################################# ###########

Public Function AddItem( _
ByVal CartID As String, _
ByVal ProductID As Integer, _
ByVal ProductDetailID As Integer, _
ByVal Quantity As Integer, _
ByVal SizeID As String, _
ByVal ColourID As String, _
ByVal UnitCost As Decimal) As String

Dim SQL As String

' First test to see if the item already exists in the cart.
' if so then we just need to update the quantity

sql = "SELECT Sum(SC.Quantity) AS Quantity FROM tblShoppingCart AS SC
" & _
"WHERE SC.CartID=@CartID And SC.ProductDetailID=@ProductDetailID
" & _
"GROUP BY SC.ProductDetailID "

Dim paramsG As OleDbParameter() = { _
New OleDbParameter("@CartID", OleDbType.VarWChar, 50), _
New OleDbParameter("@ProductDetailID", OleDbType.Integer)}

paramsG(0).Value = CartID
paramsG(1).Value = ProductDetailID

Dim rdrG As OleDbDataReader
Dim blnUpdate As Boolean
Dim intQuantity As Integer
Try
rdrG = GetData(paramsG, SQL)
rdrG.Read()
If rdrG.HasRows Then
intQuantity = CInt(rdrG("Quantity"))
blnUpdate = True
End If
Catch ex As Exception
Throw New _
Lilipro.WebModules.AppException("Error occurred in
ShoppingCart.AddItem", ex)
Finally
rdrG.Close()
End Try

If blnUpdate Then
UpdateItem(CartID, ProductID, ProductDetailID, intQuantity + 1)
Else
' If item does not exist the add the item
SQL = "INSERT INTO tblShoppingCart " & _
"( CartID, ProductID, ProductDetailID, Quantity, SizeID, ColourID,
UnitCost ) " & _
"VALUES ( @CartID, @ProductID, @ProductDetailID, @Quantity, @SizeID,
@ColourID, @UnitCost )"

Dim rdr As OleDbDataReader
Dim params As OleDbParameter() = { _
New OleDbParameter("@CartID", OleDbType.VarWChar, 50), _
New OleDbParameter("@ProductID", OleDbType.Integer), _
New OleDbParameter("@ProductDetailID", OleDbType.Integer), _
New OleDbParameter("@Quantity", OleDbType.Integer), _
New OleDbParameter("@SizeID", OleDbType.Integer), _
New OleDbParameter("@ColourID", OleDbType.Integer), _
New OleDbParameter("@UnitCost", OleDbType.Decimal)}

params(0).Value = CartID
params(1).Value = ProductID
params(2).Value = ProductDetailID
params(3).Value = Quantity
params(4).Value = SizeID
params(5).Value = ColourID
params(6).Value = UnitCost

Try
rdr = GetData(params, SQL)
rdr.Close()

Catch ex As Exception
Throw New _
Lilipro.WebModules.AppException("Error occurred in
ShoppingCart.AddItem", ex)
End Try
End If

End Function

Public Overloads Function UpdateItem( _
ByVal CartID As String, _
ByVal ProductID As Integer, _
ByVal ProductDetailID As Integer, _
ByVal Quantity As Integer) As String

' throw an exception if quantity is a negative number
If Quantity < 0 Then
Throw New Lilipro.WebModules.AppException("Quantity cannot be a
negative number")
End If

Dim SQL As String
SQL = "UPDATE tblShoppingCart " & _
"SET Quantity=@Quantity " & _
"WHERE CartID=@CartID AND ProductID=@Product AND
ProductDetailID=@ProductDetailID "

' Create Instance of Connection and Command Object
Dim params As OleDbParameter() = { _
New OleDbParameter("@Quantity", OleDbType.Integer), _
New OleDbParameter("@CartID", OleDbType.VarWChar, 50), _
New OleDbParameter("@ProductID", OleDbType.Integer), _
New OleDbParameter("@ProductDetailID", OleDbType.Integer) _
}

params(0).Value = Quantity
params(1).Value = CartID
params(2).Value = ProductID
params(3).Value = ProductDetailID

Try
GetData(params, SQL)

Catch ex As Exception
Throw New Lilipro.WebModules.AppException("Error occurred in
ShoppingCart.UpdateItem ", ex)
Finally

End Try

End Function

'################################################# ###########

How can i get round this?

[Kevin Spencer]

Hi Winshent,

Here is the problem:

rdrG.Read()
If rdrG.HasRows Then
The Read method of a DataReader reads the current row and advances to the
next. If the record has only one row, there are no more rows in the
DataReader; therefore, HasRows = false. Since you're using SUM() in your SQL
Statement, the DataReader will always have 0 or 1 row in it.

The solution is to not call the Read() method at all. You're only interested
in finding out if there is 1 row in the DataReader. No need for a round-trip
to the server to fetch data you don't use.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Everybody picks their nose,
But some people are better at hiding it.

"Winshent" <wi*************@yahoo.co.uk> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...I am having problems with adding items to my shopping cart. The problem
occures when adding items that already exists in the cart.

When a user adds to cart, they are automatically redirected to the
cart,
where they can update quantities.

If the item already exists in the cart, i want to update the quantity
by adding 1 to the quantity of that item. The code all works fine when
i step thru the code, however when running it without debugging.. the
quantity does
not is not updated until the cart page is refreshed..

I can only think that the record in the cart table has not been
updated, by the time that the cart is being read to load the cart page.
I have tried clearing the cache in between adding items, but this did
not solve the problem.

The AddItem function calls the UpdateItem function if the item already
exists in the cart. Otherwise it continues to add the item.

Here is my code:

'################################################# ###########

Public Function AddItem( _
ByVal CartID As String, _
ByVal ProductID As Integer, _
ByVal ProductDetailID As Integer, _
ByVal Quantity As Integer, _
ByVal SizeID As String, _
ByVal ColourID As String, _
ByVal UnitCost As Decimal) As String

Dim SQL As String

' First test to see if the item already exists in the cart.
' if so then we just need to update the quantity

sql = "SELECT Sum(SC.Quantity) AS Quantity FROM tblShoppingCart AS SC
" & _
"WHERE SC.CartID=@CartID And SC.ProductDetailID=@ProductDetailID
" & _
"GROUP BY SC.ProductDetailID "

Dim paramsG As OleDbParameter() = { _
New OleDbParameter("@CartID", OleDbType.VarWChar, 50), _
New OleDbParameter("@ProductDetailID", OleDbType.Integer)}

paramsG(0).Value = CartID
paramsG(1).Value = ProductDetailID

Dim rdrG As OleDbDataReader
Dim blnUpdate As Boolean
Dim intQuantity As Integer
Try
rdrG = GetData(paramsG, SQL)
rdrG.Read()
If rdrG.HasRows Then
intQuantity = CInt(rdrG("Quantity"))
blnUpdate = True
End If
Catch ex As Exception
Throw New _
Lilipro.WebModules.AppException("Error occurred in
ShoppingCart.AddItem", ex)
Finally
rdrG.Close()
End Try

If blnUpdate Then
UpdateItem(CartID, ProductID, ProductDetailID, intQuantity + 1)
Else
' If item does not exist the add the item
SQL = "INSERT INTO tblShoppingCart " & _
"( CartID, ProductID, ProductDetailID, Quantity, SizeID, ColourID,
UnitCost ) " & _
"VALUES ( @CartID, @ProductID, @ProductDetailID, @Quantity, @SizeID,
@ColourID, @UnitCost )"

Dim rdr As OleDbDataReader
Dim params As OleDbParameter() = { _
New OleDbParameter("@CartID", OleDbType.VarWChar, 50), _
New OleDbParameter("@ProductID", OleDbType.Integer), _
New OleDbParameter("@ProductDetailID", OleDbType.Integer), _
New OleDbParameter("@Quantity", OleDbType.Integer), _
New OleDbParameter("@SizeID", OleDbType.Integer), _
New OleDbParameter("@ColourID", OleDbType.Integer), _
New OleDbParameter("@UnitCost", OleDbType.Decimal)}

params(0).Value = CartID
params(1).Value = ProductID
params(2).Value = ProductDetailID
params(3).Value = Quantity
params(4).Value = SizeID
params(5).Value = ColourID
params(6).Value = UnitCost

Try
rdr = GetData(params, SQL)
rdr.Close()

Catch ex As Exception
Throw New _
Lilipro.WebModules.AppException("Error occurred in
ShoppingCart.AddItem", ex)
End Try
End If

End Function

Public Overloads Function UpdateItem( _
ByVal CartID As String, _
ByVal ProductID As Integer, _
ByVal ProductDetailID As Integer, _
ByVal Quantity As Integer) As String

' throw an exception if quantity is a negative number
If Quantity < 0 Then
Throw New Lilipro.WebModules.AppException("Quantity cannot be a
negative number")
End If

Dim SQL As String
SQL = "UPDATE tblShoppingCart " & _
"SET Quantity=@Quantity " & _
"WHERE CartID=@CartID AND ProductID=@Product AND
ProductDetailID=@ProductDetailID "

' Create Instance of Connection and Command Object
Dim params As OleDbParameter() = { _
New OleDbParameter("@Quantity", OleDbType.Integer), _
New OleDbParameter("@CartID", OleDbType.VarWChar, 50), _
New OleDbParameter("@ProductID", OleDbType.Integer), _
New OleDbParameter("@ProductDetailID", OleDbType.Integer) _
}

params(0).Value = Quantity
params(1).Value = CartID
params(2).Value = ProductID
params(3).Value = ProductDetailID

Try
GetData(params, SQL)

Catch ex As Exception
Throw New Lilipro.WebModules.AppException("Error occurred in
ShoppingCart.UpdateItem ", ex)
Finally

End Try

End Function

'################################################# ###########

How can i get round this?

[Winshent]

Hi Kevin

Thanks for the response.

I need to use the read method to get the quantity of the item. The
quantity is then updated in the function UpdateItem where it accepts
the variable intQuantity

rdrG.Read()
If rdrG.HasRows Then
intQuantity = CInt(rdrG("Quantity"))
blnUpdate = True
End If If blnUpdate Then
UpdateItem(CartID, ProductID, ProductDetailID, intQuantity + 1)
also, if i change the above to read (which i will do - thanks for the
tip)
If rdrG.HasRows Then
rdrG.Read()

I still have the problem of the cart page not reading the updated
quantity for the item. But, when refreshing the cart page, it reads
correctly.. which proves that the update is being triggerred

[Kevin Spencer]

Not a problem. Just use the following:

If rdrG.Read() Then
intQuantity = CInt(rdrG("Quantity"))
blnUpdate = True
End If

The Read() method returns true if there is data, and false if it is past the
end of the data (or if there is no data).

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Everybody picks their nose,
But some people are better at hiding it.

"Winshent" <wi*************@yahoo.co.uk> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

Hi Kevin

Thanks for the response.

I need to use the read method to get the quantity of the item. The
quantity is then updated in the function UpdateItem where it accepts
the variable intQuantity

rdrG.Read()
If rdrG.HasRows Then
intQuantity = CInt(rdrG("Quantity"))
blnUpdate = True
End If

If blnUpdate Then
UpdateItem(CartID, ProductID, ProductDetailID, intQuantity + 1)

also, if i change the above to read (which i will do - thanks for the
tip)

If rdrG.HasRows Then
rdrG.Read()

I still have the problem of the cart page not reading the updated
quantity for the item. But, when refreshing the cart page, it reads
correctly.. which proves that the update is being triggerred

[Winshent]

Hi Kevin

i dont think i have explained my problem.. the code above all works
fine.. the record is updated if the item exists. as i say this all
works.. what doesnt work is the code that calls it.

this is the code that calls it

'################################################# ###########

Public Sub AddToCart_Click(ByVal sender As Object, _
ByVal e As System.Web.UI.ImageClickEventArgs) _
Handles AddToCart.Click

'some other code

cart.AddItem(CartID, Request.Params("productid"), ProductDetailID, 1,
_
cboSize.SelectedValue, cboColour.SelectedValue,
CDec(lblUnitCost.Text))

Response.Redirect("ShoppingCart.aspx")

End Sub

'################################################# ###########

As you can see from this.. the item is added to the cart.. whether that
be adding a new record.. or updating the quantity field of an existing
record, it then redirects the user to the ShoppingCart.aspx page.
However, when it follows the route of updating the quantity field of an
existing record, the ShoppingCart.aspx does not reflect the update.

The update only shows when you then refresh the ShoppingCart.aspx
page..

ie.. when adding a product for a second time, the shopping cart page
initially still shows a quantity of 1. When i press refresh, it
displays 2.
Its really weird!!

still dont know what to do about this!!