Shopping cart error

jackiew · May 4th, 2009, 03:30 PM

I have inherited the job of updating a .NET shopping cart. (They figure since I can program asp, .NET can't be THAT much different...right?) The only person that knew .NET has left, so I have no one to turn to here.

We have a shopping cart that works great, except they want to change the shipping rates. (This is for internal use and the shipping is in flat rates) Before, each order was charged $.50 per order, but now they want to charge $1 for 1-3 items, $2 for 4-5 items and $3 for 6-6+ items. The page would show just for one individual, kinda like the invoice you get after buying something, so this page would only show Jane Smith her Completed order page. I'm struggling through this, and thought I might be on to something, but then an error.

BC30201: Expression expected
on line
shiplbl.text Is (FormatCurrency(("1"),2))

And I'm not sure what expression is expected. I thought to use something like
Dim decItemTotal As Decimal = 0
But, not sure how to call it correctly.

Code:

Function GetShippingTotal(extension as string) As Decimal
 Dim objRdr3 As SqlDataReader
 Dim objConn As SqlConnection
 Dim objCmd As SqlCommand
 
objConn = new SqlConnection("server=(ourserver); User ID=(userIDhere);Password=(passwordHere);database=(ourDatabase);connect timeout=30")
objCmd = new SqlCommand("SELECT Extension, SUM(ItemQuantity) FROM Order GROUP BY Extension = '" & Extension & "'", objConn)
 objConn.Open()
 objRdr3 = objCmd.ExecuteReader()
 
    If objCmd Is "<3" then
     shiplbl.text Is FormatCurrency(("1"),2)
    ElseIf objCmd Is "4" then
     shiplbl.text Is FormatCurrency(("2"),2)
    ElseIf objCmd Is "5" then
     shiplbl.text Is FormatCurrency(("2"),2)
    ElseIf objCmd Is "6" then
     shiplbl.text Is FormatCurrency(("3"),2)
    ElseIf objCmd Is ">6" then
     shiplbl.text Is FormatCurrency(("3"),2)
    End If
 
    objConn.Close()
End Function

Could someone help a totally lost newbie that is poking in the dark?

gbianchi · May 4th, 2009, 05:23 PM

Hello..

Why are you using is for doing things?? I don't remember even sawing that on ASP (but obviously I could be wrong).

Anyway, objcmd helds the command that will return a recordset, but it has no data. It's clear from your query is this rates are only for one user or for several ones. If this is for one user a count with a where would be enough, since you only want the total count of items in the cart.

Also, you would be better reading on how to writte a correct if else. The keyword IS has no use here, if you want to compare a value you simply use:
value < valuetocompare.

Also your logic is simple, something like:

Code:

if qofitems < 4 then
          shiplbl.text = FormatCurrency(("1"),2)
elseif qofitems > 6 then
    shiplbl.text = FormatCurrency(("3"),2)
else
    shiplbl.text = FormatCurrency(("2"),2)
endif

And I don't remember FormatCurrency as a standard function (there is no more functions here, everything is inside a namespace somewhere).

jackiew · May 5th, 2009, 10:41 AM

Thanks for the help. Please understand, I don't know anything about .NET, I was just tossed into it from management. So please be patient with me.

See next post...

jackiew · May 5th, 2009, 11:07 AM

Ok, I found the 'Debug="true" code online and added it. I can see where my error is at now.

I didn't know how to add the new shipping calculations into the function that adds all the items together, so I created a new function for the calculations. My error is occuring in the previous function where I call 'shiplbl.text'. Here are the two blocks of code together...how do I either A) combine them, or B) call the shiplbl.text from the second function?

Code:

'--Get Item Totals
Function GetItemTotal(extension as string) As Decimal
 Dim objConn As SqlConnection
 Dim objCmd As SqlCommand
 Dim lblTotal As String
 Dim decRunningTotal As Decimal = 0
 Dim objRdr As SqlDataReader
 objConn = new SqlConnection("server=(ourserver); User ID=(userIDhere);Password=(passwordHere);database=(ourDatabase);connect timeout=30")
 objCmd = new SqlCommand("SELECT ItemPrice, LogoPrice, ItemQuantity FROM Order WHERE Extension = '" & Extension & "'", objConn)
 objConn.Open()
 objRdr = objCmd.ExecuteReader()
 While objRdr.Read
  decRunningTotal += FormatCurrency(((objRdr.Item("ItemPrice") + objRdr.Item("LogoPrice")) * objRdr.Item("ItemQuantity")),2)
 End While
 
 taxlbl.text = FormatCurrency((decRunningTotal * .07),2)
 'shiplbl.text = FormatCurrency((".50"),2) <--Old shipping calculation
 GrandTotallbl.text = FormatCurrency((decRunningTotal + taxlbl.text + shiplbl.text),2)
 Return decRunningTotal
 objConn.Close()
End Function
 
'--Shipping Price based on ItemQuantity
Function GetShippingTotal(extension as string) As Decimal
 Dim objRdr3 As SqlDataReader
 Dim objConn As SqlConnection
 Dim objCmd As SqlCommand
 Dim QoItems As Decimal = 0
 objConn = new SqlConnection("server=(ourserver); User ID=(userIDhere);Password=(passwordHere);database=(ourDatabase);connect timeout=30")
 objCmd = new SqlCommand("SELECT COUNT(ItemQuantity) FROM Order WHERE Extension = '" & Extension & "'", objConn)
 objConn.Open()
 objRdr3 = objCmd.ExecuteReader()
 
 if QoItems <4 then
         shiplbl.text = FormatCurrency(("1"),2)
 elseif QoItems >5 then
      shiplbl.text = FormatCurrency(("3"),2)
 else
      shiplbl.text = FormatCurrency(("2"),2)
 end if
 
    objConn.Close()
End Function

gbianchi · May 5th, 2009, 11:39 AM

I don't have a clue on what's your idea with all of this.

I don't mean to be rude, we can help you, but I don't think anybody will do the work for you.

In your second function, you took my example, that's good, but you never pass the value you get from the query to QoItems.

Instead of executereader you should be using executescalar, and that value will be QoItems.

And since GetshippingTotal is a function, you should be returning the value somewhere instead of changing a label there.

Try to order the idea of what you need, toss some comments on your code, and we can start working out from there.

Maybe you want to replace the commented line in the first function with a call to the second one??

jackiew · May 5th, 2009, 01:35 PM

How I wish the code was commented, it would have really helped me. And, please, don't do it for me, the guy who left would just do it and I never got the chance to learn anything.

Now, I think I may be on to something here. I've combined the two functions. I don't get errors (which is good) but it doesn't add the shipping correctly. So...help. :)

Code:

Function GetItemTotal(extension as string) As Decimal
 
 Dim objConn As SqlConnection
 Dim objCmd As SqlCommand
 Dim lblTotal As String
 Dim decRunningTotal As Decimal = 0
 Dim objRdr As SqlDataReader
 Dim QoItems As Decimal = 0
 
 objConn = new SqlConnection("server=(ourserver); User ID=(userIDhere);Password=(passwordHere);database=(ourDatabase);connect timeout=30")
 objCmd = new SqlCommand("SELECT ItemPrice, LogoPrice, ItemQuantity FROM Order WHERE Extension = '" & Extension & "'", objConn)
 objConn.Open()
 objRdr = objCmd.ExecuteReader()
 While objRdr.Read
  decRunningTotal += FormatCurrency(((objRdr.Item("ItemPrice") + objRdr.Item("LogoPrice")) * objRdr.Item("ItemQuantity")),2)
 End While
 
 if QoItems <4 then
          shiplbl.text = FormatCurrency(("1"),2)
 elseif QoItems >5 then
          shiplbl.text = FormatCurrency(("3"),2)
 else
          shiplbl.text = FormatCurrency(("2"),2)
 end if
 taxlbl.text = FormatCurrency((decRunningTotal * .07),2)
 GrandTotallbl.text = FormatCurrency((decRunningTotal + taxlbl.text + shiplbl.text),2)
 Return decRunningTotal
 Return QoItems
 
 objConn.Close()
End Function

I tried to change the SELECT statement from

Code:

objCmd = new SqlCommand("SELECT ItemPrice, LogoPrice, ItemQuantity FROM Order WHERE Extension = '" & Extension & "'", objConn)

to

Code:

objCmd = new SqlCommand("SELECT COUNT(ItemQuantity), ItemPrice, LogoPrice FROM Order GROUP BY Extension = '" & Extension & "'", objConn)

I'm pretty sure I need to have the COUNT() in my SELECT statement, but I get this error.

Incorrect syntax near the keyword 'FROM'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'FROM'.
Source Error:

Line 94: objCmd = new SqlCommand("SELECT COUNT(ItemQuantity), ItemPrice, LogoPrice, FROM Order WHERE Extension = '" & Extension & "'", objConn)
Line 95: objConn.Open()
Line 96: objRdr = objCmd.ExecuteReader()
Line 97:
Line 98: While objRdr.Read

Am I using COUNT() correctly? Do I need to replace the WHERE with the GROUP BY?

gbianchi · May 5th, 2009, 02:21 PM

First of all, you are still never using QoItems for anything!!! (it's zero at the beggining and you never change it).

Second, no function can have two returns!

And third, you canÂ´t have a group by with some fields, and show others fields.. Did you try the querys in your database until you get the correct result you need???

jackiew · May 6th, 2009, 11:00 AM

Quote:

Originally Posted by gbianchi

First of all, you are still never using QoItems for anything!!! (it's zero at the beggining and you never change it).

Is this what you mean?

Code:

 While objRdr.Read
  QoItems += FormatNumber(objRdr.Item("ItemQuantity") + (objRdr.Item("ItemQuantity")+1),0)
 
  QoItems = objRdr.Item("QoItems")
 
  Response.Write("QoItems = " & QoItems & "<BR>")
 
  if QoItems <4 then
           shiplbl.text = FormatCurrency(("1"),2)
  elseif QoItems >5 then
      shiplbl.text = FormatCurrency(("3"),2)
  else
      shiplbl.text = FormatCurrency(("2"),2)
  end if
 
 End While

Though, my Response.write doesn't return a value, so I know its wrong, but am I on the right track?

gbianchi · May 6th, 2009, 11:25 AM

Response.write has no use here, if you need to see a value better debug and see what is happening there...

You are still using a reader, so shiplbl.text will have the last value.

jackiew · May 6th, 2009, 12:43 PM

Hey! I'm getting close!!!

When my order has 6 or more items, I have the shipping show up as $3.00 - Yeah!!!

But, when my order has 5 or less items, it still shows $3.00 for shipping.
(I'm so close that I think I may actually get this done! You have no clue how relieved I am right now)

So my question, What did I do wrong that the shipping shows only $3.00?
Any direction is appreciated.

Code:

Function GetItemTotal(extension as string) As Decimal
 Dim objConn As SqlConnection
 Dim objCmd As SqlCommand
 Dim lblTotal As String
 Dim decRunningTotal As Decimal = 0
 Dim objRdr As SqlDataReader
 
 objConn = new SqlConnection("server=(ourserver); User ID=(UserID);Password=(Password);database=(ourDatabase);connect timeout=30")
 objCmd = new SqlCommand("SELECT ItemPrice, LogoPrice, ItemQuantity FROM Order WHERE Extension = '" & Extension & "'", objConn)
 objConn.Open()
 
 Dim QoItems as integer = Ctype(objCmd.ExecuteScalar(), integer)
 
  if QoItems <4 then
           shiplbl.text = FormatCurrency(("1"),2)
  elseif QoItems >5 then
        shiplbl.text = FormatCurrency(("3"),2)
  else
        shiplbl.text = FormatCurrency(("2"),2)
  end if
 
 objRdr = objCmd.ExecuteReader()
 While objRdr.Read
  decRunningTotal += FormatCurrency(((objRdr.Item("ItemPrice") + objRdr.Item("LogoPrice")) * objRdr.Item("ItemQuantity")),2)
 End While
 
 taxlbl.text = FormatCurrency((decRunningTotal * .07),2)
 GrandTotallbl.text = FormatCurrency((decRunningTotal + taxlbl.text + shiplbl.text),2)
 Return decRunningTotal
 Return QoItems 
 
 objConn.Close()
End Function