Insert issue?

cedwards · June 16th, 2005, 03:58 PM

Here it is:

<p align="center"><span class="text">Additional Locations:
<select name="locations" size="3" multiple id="locations">
         <option value="001">001</option>
         <option value="002">002</option>
         <option value="003">003</option>
         <option value="004">004</option>
         <option value="005">005</option>
</select>

Imar · June 16th, 2005, 04:06 PM

What happens when you modify the loop like this:

Code:

Response.Write("Location is " & Request.Form("locations") & "<br />")

For Each location In Request.Form("locations")
  set cominsert = Server.CreateObject("ADODB.Command")
  cominsert.ActiveConnection = MM_sample_tracking_STRING
  cominsert.CommandText = "INSERT INTO tblsamples (Freezer, Tower, Box, Location, UPN, UPNA, Date_Drawn, Date_Stored, Person_Storing, Total_Vials, Freezing_Method, Comments)  VALUES ('" + Replace(cominsert__varfreezer, "'", "''") + "', '" + Replace(cominsert__vartower, "'", "''") + "', '" + Replace(cominsert__varbox, "'", "''") + "', '" + Replace(cominsert__varlocation, "'", "''") + "', '" + Replace(cominsert__varupn, "'", "''") + "', '" + Replace(cominsert__varupna, "'", "''") + "', '" + Replace(cominsert__vardate_drawn, "'", "''") + "', '" + Replace(cominsert__vardate_stored, "'", "''") + "', '" + Replace(cominsert__varperson_storing, "'", "''") + "', '" + Replace(cominsert__vartotal_vials, "'", "''") + "', '" + Replace(cominsert__varfreezing_method, "'", "''") + "', '" + Replace(cominsert__varcomments, "'", "''") + "') "

'cominsert.CommandType = 1
'cominsert.CommandTimeout = 0
'cominsert.Prepared = true
'cominsert.Execute()
Response.Write("Sql Statement is " & cominsert.CommandText & "<br />")

Next

What do you get? Multiple Sql statements?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

cedwards · June 16th, 2005, 04:28 PM

Sent you an email with the output. Can't seem to get the forum reply to work right now.

cedwards · June 16th, 2005, 04:31 PM

OOPS - forum is working just fine - the screen has been stretched though! Here is the output:

' 'Location is 001, 002, 003, 005, 006
Sql Statement is INSERT INTO tblsamples (Freezer, Tower, Box, Location, UPN, UPNA, Date_Drawn, Date_Stored, Person_Storing, Total_Vials, Freezing_Method, Comments) VALUES ('Cryo-3', '16', 'E', '001, 002, 003, 005, 006', '001', 'A', '06/16/2005', '06/16/2005', 'name', '', 'Control Rate', '')
Sql Statement is INSERT INTO tblsamples (Freezer, Tower, Box, Location, UPN, UPNA, Date_Drawn, Date_Stored, Person_Storing, Total_Vials, Freezing_Method, Comments) VALUES ('Cryo-3', '16', 'E', '001, 002, 003, 005, 006', '001', 'A', '06/16/2005', '06/16/2005', 'name', '', 'Control Rate', '')
Sql Statement is INSERT INTO tblsamples (Freezer, Tower, Box, Location, UPN, UPNA, Date_Drawn, Date_Stored, Person_Storing, Total_Vials, Freezing_Method, Comments) VALUES ('Cryo-3', '16', 'E', '001, 002, 003, 005, 006', '001', 'A', '06/16/2005', '06/16/2005', 'name', '', 'Control Rate', '')
Sql Statement is INSERT INTO tblsamples (Freezer, Tower, Box, Location, UPN, UPNA, Date_Drawn, Date_Stored, Person_Storing, Total_Vials, Freezing_Method, Comments) VALUES ('Cryo-3', '16', 'E', '001, 002, 003, 005, 006', '001', 'A', '06/16/2005', '06/16/2005', 'name', '', 'Control Rate', '')
Sql Statement is INSERT INTO tblsamples (Freezer, Tower, Box, Location, UPN, UPNA, Date_Drawn, Date_Stored, Person_Storing, Total_Vials, Freezing_Method, Comments) VALUES ('Cryo-3', '16', 'E', '001, 002, 003, 005, 006', '001', 'A', '06/16/2005', '06/16/2005', 'name', '', 'Control Rate', '')

I am not sure if this will help or not. Looks like it is looping - but the all of the selections seem to be entered into the database.

Chris

Imar · June 17th, 2005, 01:20 AM

I think this is the problem:

Code:

if(Request.Form("locations") <> "") then cominsert__varlocation = Request.Form("locations")

You assign the value of Request.Form("locations") to cominsert__varlocation. However, the locations is now a form item that can have multiple items. So, you should no longer store it in cominsert__varlocation, but use location in your query instead:

Code:

cominsert.CommandText = "INSERT INTO tblsamples (Freezer, Tower, Box, Location, UPN, UPNA, Date_Drawn, Date_Stored, Person_Storing, 
Total_Vials, Freezing_Method, Comments)  VALUES ('" + Replace
(cominsert__varfreezer, "'", "''") + "', '" + Replace
(cominsert__vartower, "'", "''") + "', '" + Replace
(cominsert__varbox, "'", "''") + "', '" + Replace
(location, "'", "''") + "', '" + Replace
(cominsert__varupn, "'", "''") + "', '" + Replace
(cominsert__varupna, "'", "''") + "', '" + Replace
(cominsert__vardate_drawn, "'", "''") + "', '" + Replace
(cominsert__vardate_stored, "'", "''") + "', '" + Replace
(cominsert__varperson_storing, "'", "''") + "', '" + Replace
(cominsert__vartotal_vials, "'", "''") + "', '" + Replace
(cominsert__varfreezing_method, "'", "''") + "', '" + Replace
(cominsert__varcomments, "'", "''") + "') "

Does that make sense? instead of the comma separated form value (e.g. 001, 002, 003) you know use each location in the loop (e.g. 001, then 002, and in he next iteration 003 and so on).

If you look at the output you posted, you can see that it's executing 6 identical queries. By making the above changes, I think that should be fixed.

Can you please start a new thread if you have more questions? We ruined this one a bit with all those way too long queries.... I can hardly read it anymore....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.