Error inserting looped values into database table 1

[visageuk]

Hello,

I am trying to insert the following data into an Access database. The data is sent to the next page like this:

default_basket.asp?basketContentID=2&basketContentID=5&action=basket_confirm&catID=1&prodID=3&prodType=k&prodPrice=10&sessionID=603709016

I'm using the following script in the receiving page. It writes the information to screen ok when debugging, but when I try to execute sqlString it gives the following error message: Too few parameters. Expected 3.


Script:

Dim sqlString

For i = 1 To Request.QueryString("basketContentID").Count

sqlString = "INSERT INTO tbl_basket_contents ( basket_prodID, basket_contentID, basket_sessionID ) VALUES ( "

' Insert the values
sqlString = sqlString & """" & Request.QueryString("prodID") & """, "
sqlString = sqlString & """" & Request.QueryString("basketContentID")(i) & """, "
sqlString = sqlString & """" & Request.QueryString("sessionID") & """"

' Close the VALUES (...) bit
sqlString = sqlString & " )"

'Debug Option
'Response.Write sqlString & "</br>"

'Execute the query
Con.Execute sqlString

Next

 

[FesterSXS]

You have 2 basketContentID values in your querystring (2 and 5). These values will be separated by a comma in the QueryString collection and this comma will then be carried through into your SQL string making it think it has 4 values instead of the 3 that it is expecting.



Tony

http://www.SpiraxSarco.com

 

[visageuk]

How do I fix that?

 

[FesterSXS]

What is the datatype of the basketContentID field in your table? Is it supposed to have more than one value? Are you supposed to have more than one basketContentID value in your QueryString?

Please can you post the value of sqlString that is displayed on screen when you enable your debug option.

Tony

http://www.SpiraxSarco.com

 

[visageuk]

The datatypes are all set to "number".

Yes to both, basketContentID will always have a "n" amount in the QueryString and they should all be different values.

To explain - the customer is buying a hamper containing various products, each with an option; eg. a jar of jam, but they have the option to choose whether they want strawberry or blackcurrant!

basketprodID is the hamper and basketContentID is the option they have chosen.

This is the debug output of the for...next loop.

INSERT INTO tbl_basket_contents ( basket_prodID, basket_contentID, basket_sessionID ) VALUES ( "3", "2", "603709017" )
INSERT INTO tbl_basket_contents ( basket_prodID, basket_contentID, basket_sessionID ) VALUES ( "3", "5", "603709017" )

 

[FesterSXS]

sorry - I misread your original code so ignore my previous posts. I will have a closer look at it for you now.


Tony

http://www.SpiraxSarco.com

 

[FesterSXS]

If your datatypes are all numbers then you need to remove the quotes around the values in the SQL string so it looks like this:

INSERT INTO tbl_basket_contents (basket_prodID,basket_contentID,basket_sessionID) VALUES (3,2,603709017)

Tony

http://www.SpiraxSarco.com

 

[visageuk]

I think that this would achieve that effect. But it returns
the following error: The function expects a string as input.


Dim sqlString

For i = 1 To Request.QueryString("basketContentID").Count

sqlString = "INSERT INTO tbl_basket_contents ( basket_prodID, basket_contentID, basket_sessionID ) VALUES ( " &_
" " & Request.QueryString( prodID ) & ", " &_
" " & Request.QueryString( basketContentID )(i) & ", " &_
" " & Request.QueryString( sessionID ) & " )"

'Debug Option
Response.Write sqlString & "</br>"

'Execute the query
'Con.Execute sqlString

Next

 

[FesterSXS]

nearly - you still need your quotes around the QueryString names. Also, no need for the spaces.

sqlString = "INSERT INTO tbl_basket_contents (basket_prodID,basket_contentID,basket_sessionID) VALUES (" & _
  Request.QueryString("prodID") & "," & _
  Request.QueryString("basketContentID")(i) & "," & _
  Request.QueryString("sessionID") & ")"

Tony

http://www.SpiraxSarco.com

 

[visageuk]

Thanks VERY MUCH. That worked!!

Nice one,

Andy

 

[FesterSXS]

glad to help - thanks for the star

Tony

http://www.SpiraxSarco.com