I am working on a cart/basket page of an e-commerce site. Specifically: "-" link button to decrease quantity of selected product by 1 in the cart, and later the "+" link button to increase quantity of selected product by 1 in the cart.
For the "-" button I am doing:
- Check the CategoryID of the product selected.
- If the CategoryID = 3 or 4, then:
- Check if the quantity of the selected product is more > 1.
- Quantity = Quantity - 1.
- Reduce the amount of HoursWork (used for booking slot length) for the selected product.
- Product Stock + 1.
- Reduce the weight (used for delivery price).
- Else: label displays "Quantity cannot be changed for Detailing & Valeting services.
I am using a SqlDataReader to get the CategoryID, and then storing it as Integer in variable CategoryID.
I tested by displaying the variable contents in a label, and it collects the correct CategoryID - however the "If CategoryID = "3" or "4" Then... is not working, as all categoryIDs are running the IF section or the statement and not the ELSE section.
Protected Sub lDecrease_Click(ByVal sender As Object, ByVal e As EventArgs)
'get clicked button
Dim lnk As LinkButton = CType(sender, LinkButton)
'Get clicked button row
' Dim row As GridViewRow = CType(lnk.Parent.Parent, GridViewRow)
Dim row As GridViewRow = CType(lnk.NamingContainer, GridViewRow)
'Get row selected
Dim idx As Integer = row.RowIndex
'get CartID
Dim lblCartID As Label = CType(row.Cells(0).FindControl("lblCartID"), Label)
Dim SCCartID As String = lblCartID.Text.ToString
'get ProductID
Dim lblProductID As Label = CType(row.Cells(0).FindControl("lblProductID"), Label)
Dim SCProductID As String = lblProductID.Text.ToString
'get ProductName
Dim lblProduct As Label = CType(row.Cells(0).FindControl("lblProduct"), Label)
Dim SCProduct As String = lblProduct.Text.ToString
'get Size
Dim lblSize As Label = CType(row.Cells(0).FindControl("lblSize"), Label)
Dim SCSize As String = lblSize.Text.ToString
'get Price
Dim lblPrice As Label = CType(row.Cells(0).FindControl("lblPrice"), Label)
Dim SCPrice As String = lblPrice.Text.ToString
'get Quantity
Dim lblQuantity As Label = CType(row.Cells(0).FindControl("lblQuantity"), Label)
Dim SCQuantity As String = lblQuantity.Text.ToString
'get Subtotal
Dim lblSubtotal As Label = CType(row.Cells(0).FindControl("lblSubtotal"), Label)
Dim SCSubtotal As String = lblSubtotal.Text.ToString
'get HoursWork
Dim lblHoursWork As Label = CType(row.Cells(0).FindControl("lblHoursWork"), Label)
Dim SCHoursWork As String = lblHoursWork.Text.ToString
'get Weight
Dim lblWeight As Label = CType(row.Cells(0).FindControl("lblWeight"), Label)
Dim SCWeight As String = lblWeight.Text.ToString
Dim conn As SqlConnection = New SqlConnection(ConnectionString)
' start of category check
Dim cmd4 As SqlCommand = New SqlCommand
cmd4.CommandText = "SELECT products.CategoryID FROM products INNER JOIN Cart on products.ProductID = cart.ProductID WHERE cart.productID=@ProductID"
Dim ProductID2 As SqlParameter = New SqlParameter("@ProductID", SqlDbType.Int, 4)
ProductID2.Value = SCProductID
cmd4.Parameters.Add(ProductID2)
cmd4.Connection = conn
conn.Open()
cmd4.ExecuteNonQuery()
Dim reader As SqlDataReader = cmd4.ExecuteReader
Dim CategoryID As Integer
While reader.Read()
CategoryID = CType(reader.Item("CategoryID"), Integer)
End While
'Testing CategoryID value = success
lblNoStock.Visible = True
lblNoStock.Text = CategoryID
conn.Close()
'Nest IF statement based on Category ID 1,2, cannot be reduced in Quantity
If CategoryID = "3" Or "4" Then
' Run quantity check – And update if possible
Dim exists As Boolean = False
'cart quantity
Dim cmd As SqlCommand = New SqlCommand
cmd.CommandText = "Select Quantity from Cart where CartID = @CartID"
cmd.Connection = conn
' conn.Close()
conn.Open()
'rename cart id 5
Dim CartID5 As SqlParameter = New SqlParameter("@CartID", SqlDbType.Int, 4)
CartID5.Value = SCCartID
cmd.Parameters.Add(CartID5)
'check if more than 1 in cart
exists = (CType(cmd.ExecuteScalar, Integer) > 1)
If exists Then
'show label to say no more in stock
lblNoStock.Visible = True
' lblNoStock.Text = "Available!"
conn.Close()
' update quantity & subtotal
Dim cmd1 As SqlCommand = New SqlCommand
cmd1.CommandText = "UPDATE Cart SET Quantity = Quantity - 1, Subtotal = @Subtotal - @Price WHERE CartID = @CartID"
'update hourswork
Dim cmd2 As SqlCommand = New SqlCommand
cmd2.CommandText = "UPDATE Cart SET HoursWork = (HoursWork - (Select Products.HoursWork FROM Products WHERE Products.ProductID = Cart.ProductID)) WHERE CartID = @CartID"
'UPDATE PRODUCTS STOCK + 1
'"UPDATE Products Set Stock = Stock + 1 WHERE ProductID = @ProductID"
Dim cmd3 As SqlCommand = New SqlCommand
'UPDATE weight
cmd3.CommandText = "UPDATE Cart SET Weight = (Weight - (Select Products.Weight FROM Products WHERE Products.ProductID = Cart.ProductID)) WHERE CartID = @CartID"
cmd1.Connection = conn
cmd2.Connection = conn
cmd3.Connection = conn
conn.Open()
Dim PProductID As SqlParameter = New SqlParameter("@ProductID", SqlDbType.Int, 4)
PProductID.Value = SCProductID
cmd1.Parameters.Add(PProductID)
Dim Subtotal As SqlParameter = New SqlParameter("@Subtotal", SqlDbType.Decimal, 5)
Subtotal.Value = SCSubtotal
cmd1.Parameters.Add(Subtotal)
Dim Price As SqlParameter = New SqlParameter("@Price", SqlDbType.Decimal, 5)
Price.Value = SCPrice
cmd1.Parameters.Add(Price)
Dim CartID As SqlParameter = New SqlParameter("@CartID", SqlDbType.Int, 4)
CartID.Value = SCCartID
cmd1.Parameters.Add(CartID)
Dim CartID2 As SqlParameter = New SqlParameter("@CartID", SqlDbType.Int, 4)
CartID2.Value = SCCartID
cmd2.Parameters.Add(CartID2)
Dim CartID3 As SqlParameter = New SqlParameter("@CartID", SqlDbType.Int, 4)
CartID3.Value = SCCartID
cmd3.Parameters.Add(CartID3)
Try
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()
cmd3.ExecuteNonQuery()
cmd.ExecuteReader()
'show label to quantity updated
lblNoStock.Visible = True
' lblNoStock.Text = "Updated!"
Finally
conn.Close()
'Response.Redirect("Cart2.aspx")
End Try
Else
'show label to say no more in stock
lblNoStock.Visible = True
lblNoStock.Text = "Cannot reduce quantity: Remove from Cart!"
End If
' else
' lblNoStock.Visible = True
' lblNoStock.Text = "Cannot reduce quantity of Detailing/ Valeting services: Remove from Cart!"
Else
'Outer ELSE
'show label to say cannot change quantity
lblNoStock.Visible = True
lblNoStock.Text = "Quantity cannot be changed for Detailing and Valeting services!"
End If
End Sub
I am also having the same issue with the "+" link button to increase quantity of selected product by 1 in the cart. Reduced code:
Protected Sub lIncrease_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim conn As SqlConnection = New SqlConnection(ConnectionString)
Dim exists As Boolean = False
'Check available in Products table STOCK
Dim cmd As SqlCommand = New SqlCommand
cmd.CommandText = "Select Stock from Products where ProductID = @ProductID"
cmd.Connection = conn
conn.Open()
Dim ProductID As SqlParameter = New SqlParameter("@ProductID", SqlDbType.Int, 4)
ProductID.Value = SCProductID
cmd.Parameters.Add(ProductID)
'check if more than 0 in stock
exists = (CType(cmd.ExecuteScalar, Integer) > 0)
If exists Then
conn.Close()
Dim cmd4 As SqlCommand = New SqlCommand
cmd4.CommandText = "SELECT products.CategoryID FROM products INNER JOIN Cart on products.ProductID = cart.ProductID WHERE cart.productID=@ProductID"
Dim ProductID2 As SqlParameter = New SqlParameter("@ProductID", SqlDbType.Int, 4)
ProductID2.Value = SCProductID
cmd4.Parameters.Add(ProductID2)
cmd4.Connection = conn
conn.Open()
cmd4.ExecuteNonQuery()
Dim reader As SqlDataReader = cmd4.ExecuteReader
Dim CategoryID As Integer
While reader.Read()
CategoryID = CType(reader.Item("CategoryID"), Integer)
End While
'Testing CategoryID value = success
' lblNoStock.Visible = True
'lblNoStock.Text = CategoryID
conn.Close()
'NESTED IF STATEMENT
If CategoryID = "3" Or "4" Then
' UPDATE cart QUANTITY & SUBTOTAL based on CartID
cmd1.CommandText = "UPDATE Cart SET Quantity = Quantity + 1, Subtotal = @Subtotal + @Price WHERE CartID = @CartID"
Dim cmd2 As SqlCommand = New SqlCommand
'UPDATE cart HOURSWORK
cmd2.CommandText = "/"
Dim cmd3 As SqlCommand = New SqlCommand
'UPDATE cart HOURSWORK
cmd3.CommandText = "/"
cmd1.Connection = conn
cmd2.Connection = conn
cmd3.Connection = conn
conn.Open()
//Declared Parameters
Try
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()
cmd3.ExecuteNonQuery()
cmd.ExecuteReader()
Finally
conn.Close()
Response.Redirect("Cart2.aspx")
End Try
Else
'Nested ELSE
'show label to say cannot change quantity
lblNoStock.Visible = True
lblNoStock.Text = "Quantity cannot be changed for Detailing and Valeting services!"
End If
Else
'show label to say no more in stock
lblNoStock.Visible = True
lblNoStock.Text = "Sorry out of stock!"
End If
End Sub
Two tables:
CART (CartID, UserID, DateCreated, ProductID, ProductName, Size, Price, Quantity, Subtotal, HoursWork)
PRODUCTS (ProductID, Name, SDescription, Price, Size, Images, Thumbnail, Weight, LDescription, Stock, CategoryID, HoursWork)
Aucun commentaire:
Enregistrer un commentaire