Syntax error in UPDATE statement.

Did you try to use Request.Form("packageID")(0) instead?

I have not, the same for ItemID as well?

Just tried it

error:

Request object error 'ASP 0105 : 80004005'

Index out of range

/process.asp, line 954

An array index is out of range

Select allOpen in new window

Yes, like

oConn.Execute("UPDATE enq_items SET item_package="&Request.Form("packageID")(1)&" WHERE item_ID="&Request.Form("itemID")(1))

Select allOpen in new window

The Index starts from 1 not from 0, sorry for my previous wrong answer

But you probably need to set all the selected values, like:

dim i, sql
for i = 1 to Request.Form("packageID").Count
   sql = sql & "UPDATE enq_items SET item_package="&Request.Form("packageID")(i)&" WHERE item_ID="&Request.Form("itemID")(i) & ";"
next
oConn.Execute(sql)

Select allOpen in new window

Thanks for the help

just tried that code, new error:

Microsoft JET Database Engine error '80040e14'

Characters found after end of SQL statement.

/process.asp, line 959

Select allOpen in new window

And please don't forget to prevent a possible SQL injection. do something like:

function S( v )
    S = "'" & Replace( v, "'", "''" ) & "'"
end function

dim i, sql
for i = 1 to Request.Form("packageID").Count
   dim pack, item
   pack = S(Request.Form("packageID")(i))
   item = S(Request.Form("itemID")(i))
   sql = sql & "UPDATE enq_items SET item_package="& pack &" WHERE item_ID=" & item & ";"
next
oConn.Execute(sql)

Select allOpen in new window

I did not realize you were using Access. Probably it does not allow to concatenate multiple queries to one. So, just execute them separately:

function S( v )
    S = "'" & Replace( v, "'", "''" ) & "'"
end function

dim i
for i = 1 to Request.Form("packageID").Count
   dim pack, item
   pack = S(Request.Form("packageID")(i))
   item = S(Request.Form("itemID")(i))
   oConn.Execute "UPDATE enq_items SET item_package="& pack &" WHERE item_ID=" & item 
next

Select allOpen in new window

BTW, do you have to use Access ? I is a very bad choice in my opinion. You could use MS SQL server Express, it is free.

Hi there, I just did a response.write and seen that there are many queries

Sorry, I just want one, not all.

I want to click Add, and that particular line to be updated, not the rest

There are multiple values in the Request.Form("itemID") collection. Do you know why?..
So, you need only the first one?
Then the code should be simplified to:

function S( v )
    S = "'" & Replace( v, "'", "''" ) & "'"
end function
' ----
dim pack, item
pack = S( Request.Form( "packageID" )( 1 ) )
item = S( Request.Form( "itemID" )( 1 ) )
oConn.Execute "UPDATE enq_items SET item_package="& pack &" WHERE item_ID=" & item 

Select allOpen in new window

(I left the validation part)

seem like it is almost done! :)

UPDATE enq_items SET item_package='5434' WHERE item_ID='5434'
Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

/process.asp, line 972

Select allOpen in new window

seems like it has quotes for the number field, tried removing them from the replace field, no deal... ideas?

Ok, since values looks like numeric, lets try to validate them as numeric:

function N( v )
    if Trg.IsNumeric(v) then N = CDbl(v) else N = "null"
end function
' ----
dim pack, item
pack = N( Request.Form( "packageID" )( 1 ) )
item = N( Request.Form( "itemID" )( 1 ) )
oConn.Execute "UPDATE enq_items SET item_package="& pack &" WHERE item_ID=" & item 

Select allOpen in new window

Note the validator returns a null on incorrect input. If you have records with item_ID=null they will be updated.
To prevent that, here is another version:

function N( v )
    if Trg.IsNumeric(v) then N = CDbl(v) else N = Empty
end function
' ----
dim pack, item
pack = N( Request.Form( "packageID" )( 1 ) )
item = N( Request.Form( "itemID" )( 1 ) )
if not(IsEmpty(pack)) and not(IsEmpty(item)) then
   oConn.Execute "UPDATE enq_items SET item_package="& pack &" WHERE item_ID=" & item
end if

Select allOpen in new window

Thanks for that

error:

Microsoft VBScript runtime error '800a01a8'

Object required: 'Trg'

/process.asp, line 965

Select allOpen in new window

Try this instead:

function N( v )
   on error resume next
    if IsNumeric(v) then N = CDbl(v) else N = Empty
end function

Select allOpen in new window

its processing, but I dont think anything is happening...

just blank, even with response.write of the sql

Please post your current setup.

Sorry for delay, needed to sleep

function N( v )
   on error resume next
    if IsNumeric(v) then N = CDbl(v) else N = Empty
end function

dim pack, item
pack = N( Request.Form( "packageID" )( 1 ) )
item = N( Request.Form( "itemID" )( 1 ) )
if not(IsEmpty(pack)) and not(IsEmpty(item)) then
   Response.Write("UPDATE enq_items SET item_package="& pack &" WHERE item_ID=" & item)
   oConn.Execute "UPDATE enq_items SET item_package="& pack &" WHERE item_ID=" & item
end if

Select allOpen in new window

Where does the function part suppose to go on the page?

Under <% %> or under <script></script> ?

It's to be executed on the server side, so - <% %>

Still running it and no result

I guess you need try to debug trace your code to find out what is wrong. Do you know how?
In a case you don't:
If you don't have it already download Visual Studio (Community version should be free) https://visualstudio.microsoft.com/
Enable debugging in the IIS, ASP feature
You can just put a keyword "stop" to your script, it will create a breakpoint.
Attach the VS to the application pool process (w3wp.exe), Script mode.

All I am doing is running your exact code, no variation

Once submitted, it doesn't run anything

I am putting response.writes in between the lines of code to find where its stopping, it doesn't even get to function

I've attached the sample file I tested your issue with. Could you please test it out to verify that at least this simplified version works maybe?
0102.asp

Thanks I will have a look through it

Cheers

As you can see its grabbing multiple packageIDs & itemIDs, how do i get it to grab just 1 of each?

this pretty much tells you whats going on, you have multiple IDs coming through and you only want one...WHICH one do you want? How do you determine that?

the multiple IDs are coming through because you named all of your elements the same thing...why? it's better to have unique NAME attributes on your form elements so this kind of thing doesnt happen.

OK after a day of trying to figure out what it was, I changed the name of thee request form and it worked, not sure why 'package' didnt work, perhaps a reserve name?

OK, have implemented it and it works well, however when there is multiple itemIDs it chooses the first one only, not the corresponding one

Is there way to tell the submitting code to look at a specific itemID and NOT the first ?

pack=N(Request.Form("packageID")(1)) <--works
item=N(Request.Form("itemID")(1)) <--not works, is it something to do with the (1), does that mean select first?

Select allOpen in new window

What do you call "corresponding" ? I am still not sure why multiply values of the same name come from the browser. Do you have more than one HTML segments you originally posted on your page? As Big Monty mentioned, you have to name the UI controls unequally or if that difficult just enclose them to each own form element.

I think I know what you mean, however...

How would i give a UI to input fields that in a loop ?

Suppose "i" holds the current loop index, then name the elements with a suffix, like

<input type=hidden name="itemID<%=i%>" value="5280"><select name="packageID<%=i%>"> 
....
<input type=submit name="package<%=i%>" value='Add'>

Select allOpen in new window

Thanks for that, is there anything I would need to change in the submit form for it to be identified?

When the user clicks on a submit-type button only its name will be sent. All other submit-type buttons will be ignored.
You could use that fact to find out which button is clicked and take the corresponding values from the combos.
Something like this:

<input type=submit name="package_submit_<%=i%>" value='Add'>

............................................

' find out which submit was clicked
  dim name
  For Each name In Request.Form
     if Left( name, 15 ) = "package_submit_" then i = CInt(Mid( name, 16 ) )
  next

' if found, the i variable will contain the suffix

   dim pack, item
   pack =  N( Request.Form( "packageID" & i ) )
   item =  N( Request.Form( "itemID" & i ) )

Select allOpen in new window

Note that I used a different name ( "package_submit_<%=i%>" ) for the submit-type button to simplify the logic of its name parsing