Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Dreamweaver (all versions) section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 14th, 2005, 09:09 AM
Authorized User
 
Join Date: May 2005
Location: , WI, .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default Insert issue?

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

 
Old June 14th, 2005, 03:00 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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?
 
Old June 15th, 2005, 08:35 AM
Authorized User
 
Join Date: May 2005
Location: , WI, .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
%>

 
Old June 15th, 2005, 10:38 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Do you get an error??

Imar
 
Old June 15th, 2005, 12:27 PM
Authorized User
 
Join Date: May 2005
Location: , WI, .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old June 15th, 2005, 12:41 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
 
Old June 15th, 2005, 12:56 PM
Authorized User
 
Join Date: May 2005
Location: , WI, .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old June 15th, 2005, 01:09 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
 
Old June 16th, 2005, 03:34 PM
Authorized User
 
Join Date: May 2005
Location: , WI, .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old June 16th, 2005, 03:39 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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

Imar




Similar Threads
Thread Thread Starter Forum Replies Last Post
Sql Insert -- date format issue feets Access VBA 2 June 12th, 2007 12:11 PM
Insert Issue into Acces DB brawny4 Visual Basic 2005 Basics 2 February 21st, 2007 08:43 PM
Ch10 - issue with insert in log table Cata BOOK: Professional SQL Server 2005 Administration ISBN: 0-470-05520-0 0 February 15th, 2007 05:17 PM
trigger to insert current date on insert kev_79 SQL Server 2000 3 January 23rd, 2006 05:58 PM
Insert Issue revisited cedwards Dreamweaver (all versions) 13 June 21st, 2005 03:02 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.