Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | 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 April 3rd, 2006, 11:14 PM
Authorized User
 
Join Date: May 2005
Location: , WI, .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default Tricky logic (for me)

Hi all,

haven't been here in a bit - but I have run into a logic problem I just can't wrap my head around. I am using DW8 and access along with plain old ASP to do a web survey. Everything in the survey works well - however at the end of the survey the survey respondent should be able to click a button and receive a coupon code (these codes are kept in a separate table)

My question is; how can I have the coupon codes append to the individual respondents record (each record does have an id field and there is a cookie for the id field as well) and how can I make sure that no two coupons are used more than once.

Essentially - someone completes the survey and then clicks the receive my gift code - then the gift code would be pulled from the table of codes and it would update to a field in the respondents row of information. I tried to think it through with some sort of BOF or first record type of statement but couldn't get it together.

Unfortunately the survey is to go live sometime Tuesday night (USA Central Time) Any ideas would be appreciated. Thanks in advance.

Reply With Quote
  #2 (permalink)  
Old April 4th, 2006, 01:11 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: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi Chris ,

You can use the AddNew method of a recordset, insert a new record and the get its ID. To make this work, the Id column for the Coupon should be set to Autonumber. The following (untested) code should gie you an idea.
Code:
Dim rsCoupon
Set rsCoupon = Server.CreateObject("ADODB.Recordset")
rsCoupon.Open "SELECT * FROM Coupon WHERE Id = -1", myConnection
rsCoupon.AddNew()
rsCoupon.Update()
Dim myNewCouponId
myNewCouponId = rsCoupon("Id")
This code opens an empty recordset. Since you don't need previous records, I use WHERE Id = -1 to make sure no useless records are returned. Although you get no records, you do get the meta data, so you can call AddNew. If you then call Update, the record is inserted in the database, and you can get the ID of the newly created record by querying the Id column. Since Id is an autonumber, you can be sure no other user gets the same ID.

Once you have to ID in the myNewCouponId variable, you can do with it whatever you want, like insert it in the current user's record.

Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
While typing this post, I was listening to: Wherever I May Roam by Metallica (Track 5 from the album: Metallica) What's This?
Reply With Quote
  #3 (permalink)  
Old April 4th, 2006, 03:26 PM
Authorized User
 
Join Date: May 2005
Location: , WI, .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Imar,

Helpful as always! I went about it a bit differently and was able to make it work. I had a hard time explaining what I needed to do and I really do appreciate your quick response. If anyone is interested here is the code I used to grab the data from first record in a table:
<%
'select the individual record
Dim rscoupon
Dim rscouponnumRows
Set rscoupon = Server.CreateObject("ADODB.Recordset")
rscoupon.ActiveConnection = MM_name_STRING
rscoupon.Source = "SELECT ID, couponcode FROM tblcoupons order by ID"
rscoupon.CursorType = 0
rscoupon.CursorLocation = 2
rscoupon.LockType = 1
rscoupon.Open()

allRecords = rsgift.GetRows ' convert rs to an array
recCount = UBound( allRecords, 2 ) + 1
CONST choiceCount = 1
choices = ""
chosen = 0
      info1 = allRecords( 0, choose ) 'autoincremental number
      info2 = allRecords( 1, choose ) 'data I want
rsgift_numRows = 0
%>

'and now for the DW update and delete code

<%
if(info2 <> "") then Command1__varcoupon = info2

if(Request.Cookies("something") <> "") then Command1__varid = Request.Cookies("something")

%>
<%
'updates the other table with the appropriate data
set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_name_STRING
Command1.CommandText = "UPDATE tblother SET couponcode = '" + Replace(Command1__varcoupon, "'", "''") + "' WHERE ID='" + Replace(Command1__varid, "'", "''") + "' "
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
'deletes the data from the lookup table
Command1.CommandText = "DELETE from tblcoupons WHERE ID= " & CLng(info1) 'CLng converts number back to long integer
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
%>
'don't forget to close the rs!
<%
rscoupon.Close()
Set rscoupon = Nothing
%>

Let me know if you have any questions.

Reply With Quote
  #4 (permalink)  
Old April 4th, 2006, 03:51 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: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

I am not sure I understand how this works.

It looks like you're retrieving the last record from the table tblCoupons, right? Where does choose get a value?

And when do you insert a new ID in the table?

Somehow, this looks different than what I imagined you needed....

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
While typing this post, I was listening to: The Order Of Death by Public Image Ltd. (Track 8 from the album: This Is What You Want... This Is What You Get) What's This?
Reply With Quote
  #5 (permalink)  
Old April 4th, 2006, 10:02 PM
Authorized User
 
Join Date: May 2005
Location: , WI, .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Imar,

There are many ways I could hae accomplished what I needed to do - the way I ended up doing it was efficient and effective. I essentially had a table of values that I needed to add to a different table - once those values were used I needed to make sure that they weren't used again - I could have added an ID field to the table of values or I could have added the value to the main table (that is the method I chose) either way it would have resulted in one value for one user and no user ever receiving the same value as any other user. It is confusing to explain - I really appreciate your help! You have been an unbelievable help on this forum for a long time! Thanks for all of yo hard work and I would guess that there have been many others who have appreciated the help that you have given.

Chris

Reply With Quote
  #6 (permalink)  
Old April 5th, 2006, 04:47 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: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

You're welcome. Glad it's working now...

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
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
Tricky Queries balesh.mind SQL Server 2005 5 April 12th, 2008 10:29 PM
A tricky question mike_remember ASP.NET 1.0 and 1.1 Professional 3 October 27th, 2006 08:17 AM
Tricky Query BSkelding MySQL 5 August 31st, 2005 08:59 AM
Mixing Data access logic and business logic polrtex BOOK: Professional Jakarta Struts 0 December 15th, 2003 07:19 PM
Very tricky challenge 12th_Man SQL Language 3 June 20th, 2003 06:45 PM



All times are GMT -4. The time now is 07:09 PM.


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