Insert issue?

cedwards · June 14th, 2005, 09:09 AM

Hi -

This is probably easy - I am using a Command object to insert 4 fields of data into a recordset - this recordset will be update with additional information later on- what I would like to happen is that if those four fileds are already located in the database it would not insert them.

Basically some type of if then statement - I am unsure on how to do it though.

Here is the insert code:

<%

if(Request.Cookies("freezer") <> "") then com_insert__varnamefreezer = Request.Cookies("freezer")

if(Request.Cookies("tower") <> "") then com_insert__varnametower = Request.Cookies("tower")

if(Request.Cookies("box") <> "") then com_insert__varnamebox = Request.Cookies("box")

if(Request.Form("location") <> "") then com_insert__varnameloc = Request.Form("location")

%>
<%

set com_insert = Server.CreateObject("ADODB.Command")
com_insert.ActiveConnection = MM_sample_tracking_STRING
com_insert.CommandText = "INSERT INTO tblsamples (Freezer, Tower, Box, Location) VALUES ('" + Replace(com_insert__varnamefreezer, "'", "''") + "', '" + Replace(com_insert__varnametower, "'", "''") + "', '" + Replace(com_insert__varnamebox, "'", "''") + "', '" + Replace(com_insert__varnameloc, "'", "''") + "') "
com_insert.CommandType = 1
com_insert.CommandTimeout = 0
com_insert.Prepared = true
com_insert.Execute()

%>

Thans for any help!

Chris

Imar · June 14th, 2005, 03:00 PM

You could fire an additional SELECT query to see if the item exists before you insert it.

Something along these lines would work:

Code:

Dim rsCheck
Set rsCheck = myConnection.Execute("SELECT 1 FROM MyTable WHERE Column1 = 'SomeValue' AND Column2 = 'SomeOtherValue'")
If rsCheck.EOF Then
  ' Record does no exist, so go ahead and insert a new one
Else
  ' Do whatever you need to do when the record exists
End If
rsCheck.EOF
Set rsCheck = Nothing

Does this help??

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Butterflies & Hurricanes by Muse (Track 10 from the album: Absolution) What's This?

cedwards · June 15th, 2005, 08:35 AM

Hi Imar,

Thanks again - I new it was easy - but I am still learning asp - and am tackling things that are a bit more complicated than I expect - here is the code - all pretty much Dreamweaver with a few modifications:

<%
Dim rsedit__varnamefreezer
rsedit__varnamefreezer = "Cryo-3"
If (Request.Cookies("freezer") <> "") Then
  rsedit__varnamefreezer = Request.Cookies("freezer")
End If
%>
<%
Dim rsedit__varnametower
rsedit__varnametower = "01"
If (Request.Cookies("tower") <> "") Then
  rsedit__varnametower = Request.Cookies("tower")
End If
%>
<%
Dim rsedit__varnamebox
rsedit__varnamebox = "a"
If (Request.Cookies("box") <> "") Then
  rsedit__varnamebox = Request.Cookies("box")
End If
%>
<%
Dim rsedit__varnamelocation
rsedit__varnamelocation = "001"
If (Request.Form("location") <> "") Then
  rsedit__varnamelocation = Request.Form("location")
End If
%>
<%
Dim rsedit
Dim rsedit_numRows

Set rsedit = Server.CreateObject("ADODB.Recordset")
rsedit.ActiveConnection = MM_sample_tracking_STRING
rsedit.Source = "SELECT Freezer, Tower, Box, Location FROM tblsamples WHERE Freezer = '" + Replace(rsedit__varnamefreezer, "'", "''") + "' AND Tower = '" + Replace(rsedit__varnametower, "'", "''") + "' AND Box = '" + Replace(rsedit__varnamebox, "'", "''") + "' AND Location = '" + Replace(rsedit__varnamelocation, "'", "''") + "'"
rsedit.CursorType = 0
rsedit.CursorLocation = 2
rsedit.LockType = 1
rsedit.Open()

rsedit_numRows = 0
if rsedit.EOF then

set com_insert = Server.CreateObject("ADODB.Command")
com_insert.ActiveConnection = MM_sample_tracking_STRING
com_insert.CommandText = "INSERT INTO tblsamples (Freezer, Tower, Box, Location) VALUES ('" + Replace(rsedit__varnamefreezer, "'", "''") + "', '" + Replace(rsedit__varnametower, "'", "''") + "', '" + Replace( rsedit__varnamebox, "'", "''") + "', '" + Replace(rsedit__varnamelocation, "'", "''") + "') "
com_insert.CommandType = 1
com_insert.CommandTimeout = 0
com_insert.Prepared = true
com_insert.Execute()

Else

Response.Redirect("editsample.asp")
End If
%>
<%
rsedit.Close()
Set rsedit = Nothing
%>

Imar · June 15th, 2005, 10:38 AM

Do you get an error??

Imar

cedwards · June 15th, 2005, 12:27 PM

Hi Imar - No errors - I have tested it numerous times - and have checked the database - nu duplicate records are being added and every new record is being added! I know the code is ugly - but I am getting really used to using dreamweaver (I just switched to DM MX2004).

I do have another question though :>)

I want to be able to insert multiple records based on one field that can be selected multiple times. Basically - I have the tbl where freezer=x Tower=X Box=X and location is the field that will be selected multiple times. I guess what I need is to be able to run a loop of some sort - I have looked around - but haven't found anything very helpful.

Imar · June 15th, 2005, 12:41 PM

Ah, OK, I see now. I thought you posted the code because you were still having problems with it. Glad it's working.
The problem with the ugly code is partially DW's fault. I creates generally very safe code (in terms of failure, not security), which results in quite bloated code.

Writing a loop isn't so hard and writing code that fires inside the loop isn't either. What is hard to understand is your Freezer, Tower and Box stuff. Again, can you please describe in more detail what it is you want. Is location a drop-down on your page? What way can you select multiple items of what kind? How does your table structure look like? What INSERT statement do you want to loop exactly?

Providing these details with your initial question makes it *much* more easier for me to answer these questions, rather than trying to guess what Freezer I should loop to fit in in the location of the Tower....

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

cedwards · June 15th, 2005, 12:56 PM

Hi Imar - wow! quick response on that one.

You have seen my page of 100 buttons already - well each button represent a location (1 through 100) associated with the freezer - tower and box. So location would be a drop-down field - the freezer - tower - box would be captured as cookies. There are also additional fields of data that would be inserted - but the important thing to do is insert the data based on the number of locations selected.

So I could select location 001, 002, 003, 004, 005 and 006. I would want the insert to loop through all of them.

Hope that helps (you can look at the site again if you want - click on any of the 100 buttons and you will get to a page that you can add-edit samples (the location dropdown isn't in there yet but will be soon) It will give you an idea anyway.

As always - thanks for your help.

Imar · June 15th, 2005, 01:09 PM

Quote:

quote:Hi Imar - wow! quick response on that one.

Depends on the time of the day you post your question.... ;)

Anyway, try this simple example; this may just be what you need.

Imar

Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%
  If Request.Form("lstCategory") <> "" Then
    Dim category
      For Each category In Request.Form("lstCategory")
        Response.Write("INSERT INTO SomeTable (SomeColumn, TheCategory) VALUES('SomeValue', " & category & ")<br />")
      Next
    End If
%>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Test Loop Insert</title>
</head>

<body>
  <form method="post" action="PutTheBoxInTheFreezer.asp">
    <select name="lstCategory" multiple="multiple" size="5" style="width: 75px;">
      <option value="1">1</option>
      <option value="2">2</option>
      <option value="3">3</option>
      <option value="4">4</option>
      <option value="5">5</option>
    </select>
    <input type="submit" value="Click me" />
  </form>
</body>
</html>

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

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

OK - I can't seem to get this to work - I am modifying a DM command insert - so that could be the problem: All it does is enter the first number I select - I guess it isn't looping through. Here is the code - any help would be appreciated!

<%

if(Request.Cookies("freezer") <> "") then cominsert__varfreezer = Request.Cookies("freezer")

if(Request.Cookies("tower") <> "") then cominsert__vartower = Request.Cookies("tower")

if(Request.Cookies("box") <> "") then cominsert__varbox = Request.Cookies("box")

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

if(Request.Form("upn") <> "") then cominsert__varupn = Request.Form("upn")

if(Request.Form("upna") <> "") then cominsert__varupna = Request.Form("upna")

if(Request.Form("date_drawn") <> "") then cominsert__vardate_drawn = Request.Form("date_drawn")

if(Request.Form("date_stored") <> "") then cominsert__vardate_stored = Request.Form("date_stored")

if(Request.Form("person_storing") <> "") then cominsert__varperson_storing = Request.Form("person_storing")

if(Request.Form("total_vials") <> "") then cominsert__vartotal_vials = Request.Form("total_vials")

if(Request.Form("freezing_method") <> "") then cominsert__varfreezing_method = Request.Form("freezing_method")

if(Request.Form("comments") <> "") then cominsert__varcomments = Request.Form("comments")

%>
<%
if Request.Form("locations") <> "" Then
  Dim location
    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()
   Next
End If
%>

That is it - let me know if you have any suggestions.

Chris

Imar · June 16th, 2005, 03:39 PM

How does Request.Form("locations") look like? What kind of control is it?

Imar