Array problem

[jordan11]

Hi,

I have this array, can someone please tell me how I can get it to update my database so the different states selected update one field only with values like this ca,ga,co.
At the moment when it updates it updates each state seperately repeating the values of the other fields e.g if i have 6 states selected the id will be displayed for each state. All I want is 1 id and the 6 states in one field.

This is the code I have at the moment.

arrSelectedItems=Split(Request.form("sel2"),",")
for r=0 to ubound(arrSelectedItems)

txtsql = "INSERT INTO Physicianpreference (physicianID,community,schools,lakes,art,dine,airport,PreferredRegion,PreferredState,StateBirth,OtherConsiderations) " _
& "VALUES ('" & trim(request("physicianid")) &"', '" _
& trim(Request.Form("community")) &"', '" _
& trim(Request.Form("schools")) &"', '" _
& trim(Request.Form("lakes")) &"', '" _
& trim(Request.Form("art")) &"', '" _
& trim(Request.Form("dine")) &"', '" _
& trim(Request.Form("airport")) &"', '" _
& trim(Request.Form("PreferredRegion")) &"', '" _
& arrSelectedItems & "', '" _
& trim(Request.Form("OtherConsiderations")) &"')"


conn.Execute txtsql
next

Thanks

 

[DotNetGnat]

let me warn you that having multiple values in a single field is a bad database design...

but to achieve what you want...you dont have to split the arrSelectedItems and dont use the for loop...just insert it directly...something like this:

  txtsql = "INSERT INTO Physicianpreference (physicianID,community,schools,lakes,art,dine,airport,PreferredRegion,PreferredState,StateBirth,OtherConsiderations) " _
           & "VALUES ('" & trim(request("physicianid")) &"', '" _
           & trim(Request.Form("community")) &"', '" _
           & trim(Request.Form("schools")) &"', '" _
           & trim(Request.Form("lakes")) &"', '" _
           & trim(Request.Form("art")) &"', '" _
           & trim(Request.Form("dine")) &"', '" _
           & trim(Request.Form("airport")) &"', '" _
           & trim(Request.Form("PreferredRegion")) &"', '" _
           & arrSelectedItems & "', '" _
           & trim(Request.Form("OtherConsiderations")) &"')"

-DNG

 

[jordan11]

I agree with you,I originally had the values going to a seperate table but my boss wanted all the values going into one field.

Thanks that done the job.

 

[DotNetGnat]

no problem...was just warning you the troubles you might encounter in future from having this kind of bad db design...anyways...glad to be of help...

-DNG