Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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

Reply With Quote
  #2 (permalink)  
Old June 14th, 2005, 03:00 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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?
Reply With Quote
  #3 (permalink)  
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
%>

Reply With Quote
  #4 (permalink)  
Old June 15th, 2005, 10:38 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Do you get an error??

Imar
Reply With Quote
  #5 (permalink)  
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.

Reply With Quote
  #6 (permalink)  
Old June 15th, 2005, 12:41 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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.
Reply With Quote
  #7 (permalink)  
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.

Reply With Quote
  #8 (permalink)  
Old June 15th, 2005, 01:09 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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.
Reply With Quote
  #9 (permalink)  
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

Reply With Quote
  #10 (permalink)  
Old June 16th, 2005, 03:39 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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

Imar
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 03:42 PM.


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