SQL Question (once more, with feeling)

# Thread: SQL Question (once more, with feeling)

1. Senior Member
Join Date
Dec 1969
Posts
583

## SQL Question (once more, with feeling)

I&#039;ve been trying to get the total weight of the items that a user may have in his shopping cart. My goal is to times the weight of each item in the products table by the quantity of that item that the user have is his shopping cart.<BR><BR>So far I have (with some ASP removed for clarity):<BR><BR>SELECT SUM(tblProducts.productweight * tblCart.quantity) as total_weight "<BR>FROM tblProducts, tblCart "<BR>WHERE tblCart.orderid=&#039;" & OrderID & "&#039; "<BR>AND tblCart.productid = tblProducts.productid"<BR><BR>In the simple example I&#039;ve tried so far (with one, 18 pound item in the shopping cart) I get an error.<BR><BR>The value that the precessding SQL code generates for a 1 pound item times 18, I get &#039;1818&#039;. It seems as though the SQL takes 18, multiplies it by 1 and then concatenates the result to itself.<BR><BR>Any help would be most appreciated.<BR><BR>- Conrad

2. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: SQL Question (once more, with feeling)

Ahhhh ha<BR><BR>My bet is that one or other of the fields in not numeric

3. Senior Member
Join Date
Dec 1969
Posts
583

## RE: SQL Question (once more, with feeling)

That&#039;s what I thought, but no, they&#039;re both numeric.<BR><BR>As a side note: it&#039;s Access 2000.

4. Senior Member
Join Date
Dec 1969
Posts
6,476

## RE: SQL Question (once more, with feeling)

Did you check to see if you are getting a "Double Loop" on your recordset?<BR><BR>IF you have a<BR>Do while not rs.eof (Or something simular...)<BR>Maybe for some odd reason it&#039;s looping 2 times<BR>therefor writing the number 18 to the page 2 times....<BR><BR>"That is if your 100% sure both fields are numeric...<BR><BR>Best way to check that "Is not in database", but in asp/vbs....<BR><BR>Response.Write "quantity datatype = " & varType( rs("quantity") ) & "&#060;/br&#062;"<BR>Response.Write "productweight datatype = " & varType( rs("productweight") ) & "&#060;/br&#062;"<BR><BR>Then you will know for sure what kind of variable its returning and go from there...<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
583

## RE: SQL Question (once more, with feeling)

I don&#039;t do any looping, I just assaign the value to a variable.<BR><BR>As for whether or not the fields are number, I looked at the tables in design mode. They&#039;re numeric.<BR><BR>Man, when things suck, they really blow. ;)<BR><BR>Any other ideas?<BR><BR>Thanks for the help.<BR><BR>- Conrad

6. Senior Member
Join Date
Dec 1969
Posts
6,476

## RE: SQL Question (once more, with feeling)

I Think you missed what I said to try...<BR><BR>Do the varType test from ASP To see what datatype is returned from the recordset...<BR><BR>Is it being returned as a string, or as a numeric value?<BR><BR>And post exact sql as you have it in code<BR>as well as the rest of the code that writes everything to the page...<BR><BR>We need to see the full code...<BR>And the results of the VarType test...<BR>

7. Senior Member
Join Date
Dec 1969
Posts
583

## Here it is

OK, two interesting things happened with the following code:<BR><BR>Response.Write "&#060;font color=&#039;red&#039;&#062;" & varType(oRecordset.Fields("productprice")) & "&#060;/font&#062;&#060;br /&#062;"<BR>Response.Write "&#060;font color=&#039;blue&#039;&#062;" & varType(oRecordset.Fields("quantity")) & "&#060;/font&#062;&#060;br /&#062;"<BR><BR>First off, it didn&#039;t display what type of variable they were returned as. It just displayed the values returned.<BR><BR>Secondly, the values returned where quite unexpected.<BR><BR>The quantity returned a value of 3, even though the value in the database is only 1.<BR><BR>The price returned a value of 18186, even though the value in the database is 18.<BR><BR>It seems as though the problem is in the table join, but where did the &#039;6&#039; come from? Why wasn&#039;t the value returned &#039;181818&#039;?<BR><BR>Thanks again.<BR><BR>Note: the font color was just for clarification.

8. Senior Member
Join Date
Dec 1969
Posts
6,476

## Ok good start.

Lets try something...<BR><BR>Lets do a normal select and add a loop to see what and how many records are returning....<BR><BR>(We might need to use an inner join to make sure of the table joins are coorect)<BR><BR>For now...<BR><BR>SELECT tblProducts.productweight, tblCart.quantity as total_weight " <BR>FROM tblProducts, tblCart " <BR>WHERE tblCart.orderid=&#039;" & OrderID & "&#039; " <BR>AND tblCart.productid = tblProducts.productid" <BR><BR>Do while not rs.EOF<BR>Response.Write "&#060;font color=&#039;red&#039;&#062;" & varType(oRecordset.Fields("productprice")) & "&#060;/font&#062;&#060;br /&#062;"<BR>Response.Write "&#060;font color=&#039;blue&#039;&#062;" & varType(oRecordset.Fields("quantity")) & "&#060;/font&#062;&#060;br /&#062;"<BR>rs.MoveNext<BR>Loop<BR><BR><BR>Plug that in and see if your getting multiple records...<BR>

9. Senior Member
Join Date
Dec 1969
Posts
6,476

## RE: Ok good start.

forgot...<BR>For other test on the variable, i made a mistake<BR><BR>Use<BR>TypeName(oRecordset.Fields(" productprice"))<BR><BR>Not<BR>Vartype....<BR>My mistake, was thinking of something else...<BR><BR>Im still betting its the sql Join on the tables returning more than 1 record...<BR>

10. Senior Member
Join Date
Dec 1969
Posts
583

## Number of records

It seems as if two records are returned. Whether I have one, two, three, or more items in the cart. It also does not appear to matter how many of any given item I have. It should be noted that each item, no matter how many, only occupy a single line. As items are added or removed I simply change the quantity.

#### Posting Permissions

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