Tricky logic (for me)

cedwards · April 3rd, 2006, 11:14 PM

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.

Imar · April 4th, 2006, 01:11 PM

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?

cedwards · April 4th, 2006, 03:26 PM

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.

Imar · April 4th, 2006, 03:51 PM

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?

cedwards · April 4th, 2006, 10:02 PM

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

Imar · April 5th, 2006, 04:47 PM

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