Thread: getting values from tables

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

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.

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?

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

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

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

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)