DeveloperBarn

todd2006 · #1 February 1st, 2009, 12:15 PM

Hi,

I am giving discount codes to special customers. So I will be giving a specified codes from the table. The number of the codes will be different each time.

someone will get 2 codes someone will get 45 or someone will get 75.

so my query will be like this
strnum=trim(request.form("noofcodes")

Select top strnum * from codes

how do i save them in different variables.

Can i create the variables on fly and store the codes in them

can someone tell me how I make this happen

todd

todd2006 · #2 February 2nd, 2009, 12:17 AM

So what I want to do is get the 50 or 75 codes from one table and add it to another table with the USERID

I have the userid stored into a variable.

dr_rock · #3 February 2nd, 2009, 12:44 AM

Use a count statement to fint the number of rows then use a select statement to pull the codes,

Code:

strSQL = "select count(ID) from wherever where something = true;select code from wherever where something = true"

Create an array with userID and Code fields, Ubound rowcount value then run through your recordset of codes and mix with the USERID

Code:

Redim arrList(1,rst(0))

Set rst = rst.NextRecordset
For intCount = 0 to rst.EOF
   arrList(0,intCount) = UserID
   arrList(1,intCount) = rst("code")
  rst.MoveNext
Loop

Then you can stick it in a table or do whatever you like in the array.

Does that kind of sound like what you are looking for?

todd2006 · #4 February 2nd, 2009, 01:16 AM

I tried it like this

Code:

sqltext = "select top 3 * from Codes"
Set rs = Conn.Execute(sqltext)

strnewadd ="select * from Add_Codes;"
Set rsnewAdd = Server.CreateObject("ADODB.Recordset")
With rsnewAdd
.Source="Add_Codes"
.CursorType = 2
.LockType = 3
End With
rsnewAdd.Open strnewadd, Conn

For I = 0 to rs.Fields.Count - 1
    redim preserve ARR(I)
  	rsnewAdd.AddNew
	rsnewAdd("Reg_Id") = sID
	rsnewAdd("Free_Codes") = rs.Fields(I).Name
	
	rsnewAdd.Update

Next

I tried for 3 codes it didnt add the codes in the free_Codes field

what am i doing wrong

todd2006 · #5 February 2nd, 2009, 01:34 AM

Ok this code only adds one code and not 3

Code:

sqltext = "select top 3 DiscountCode from Codes"
Set rs = Conn.Execute(sqltext)

strnewadd ="select * from Add_Codes;"
Set rsnewAdd = Server.CreateObject("ADODB.Recordset")
With rsnewAdd
.Source="Add_Codes"
.CursorType = 2
.LockType = 3
End With
rsnewAdd.Open strnewadd, Conn

For I = 0 to rs.Fields.Count - 1
   	rsnewAdd.AddNew
	rsnewAdd("Reg_Id") = sID
	rsnewAdd("Free_Codes") = rs.Fields(I)
	rsnewAdd.Update

Next

can someone tell me what i am doing wrong in the loop

dr_rock · #6 February 2nd, 2009, 02:14 AM

How about this?

Code:

sqltext = "select top 3 DiscountCode from Codes"
Set rs = Conn.Execute(sqltext)
sqltextoutput = ""

For I = 0 to rs.Fields.Count - 1

  sqltextoutput = sqltextoutput & "Insert Into Add_Codes (Reg_Id,Free_Codes) values ("&sID&","&rs.Fields(I)&");"

Next

Response.write(sqltextoutput)
If len(sqltextoutput) > 0 Then Conn.Execute(sqltextoutput)

todd2006 · #7 February 2nd, 2009, 02:23 AM

Ok i used this code and it worked

Code:

Set oCodesRs = oConn.Execute("SELECT TOP " & strnum & "* FROM codes;")

If Not oCodesRs.EOF Then
 Do While Not oCodesRs.EOF 
  sCode = oCodesRs("code")

  oConn.Execute("INSERT INTO othertable (UserID,Code) VALUES(" & iUserID & ",'" & sCode & "');")

 oCodesRs.MoveNext
 Loop
End If

how do i delete the codes that i inserted from the codes table

because i dont want to give the same codes to a different person

dr_rock · #8 February 2nd, 2009, 06:33 PM

Code:

sqltext = "select top 3 DiscountCode from Codes"
Set rs = Conn.Execute(sqltext)
sqltextoutput = ""

For I = 0 to rs.Fields.Count - 1

  sqltextoutput = sqltextoutput & "Insert Into Add_Codes (Reg_Id,Free_Codes) values ("&sID&","&rs.Fields(I)&");Delete From DiscountCode Where ID = " & rs.Fields("ID") &";"

Next

Response.write(sqltextoutput)
If len(sqltextoutput) > 0 Then Conn.Execute(sqltextoutput)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Spliced tables, paged results	dr_rock	SQL Code Samples	0	September 16th, 2008 02:19 AM
Pulling from multiple tables...	bryceowen	SQL Development	2	September 15th, 2008 07:57 PM
Form for multiple tables and queries	nboscaino	Microsoft Access	1	August 21st, 2008 06:55 PM
finding values	todd2006	ASP Development	2	June 23rd, 2008 02:03 AM
Permissions on Tables, Stored Procedures, etc.	theChris	Microsoft SQL Server	2	March 24th, 2008 11:49 AM

DeveloperBarn

getting values from tables

getting values from tables

Similar Threads