loop and store automatically (cool question i think) [Archive]

maxpouliot

08-25-2006, 08:15 PM

hi, i wonder if a pro can help me with this

i want to loop through all the fields (this is why i use the * in the following query)

"SELECT * FROM cas WHERE id=814"

This returns 1 result but my table has 152 fields (i know... this is too much!) What i would like is to have some code that makes a variable for each field that i have automatically

for example, if i had a table with 3 fields (field1,field2,field3), normally i could this

field1=rs("field1")
field2=rs("field2")
field3=rs("field3")

but is it possible to automate this step?

the code would have to be like :

loop through all the fields
nameOfTheField = valueOfTheField
end loop

am i clear enough?

-------------

i just solved half of my problem with this code :

Dim rs
SQL="SELECT * FROM cas WHERE id=" & idCas
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open SQL,Conn
intfieldcount = rs.fields.count
For intthisfield = 0 To intfieldcount -1
rs(intthisfield).name = rs(intthisfield).value
Next

The only thing that doesn't work is the following line :
rs(intthisfield).name = rs(intthisfield).value

i want to create a variable that has the name of the field and then put the value in it

degsy

08-31-2006, 04:01 PM

Not sure what you are trying to do.

If you want to loop through recordset field names then use a For Each Loop
http://www.w3schools.com/ado/ado_recordset.asp

If you want to turn a variable value into a variable name then you need to use the Execute function
http://www.attention-to-details.com/newslog/389-eval-and-execute-commands-in-asp.asp

e.g.
http://computer-helpforum.com/asp/ADO_Recordsets/rs_fields_execute.asp

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim rs
Dim rs_numRows

Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = MM_TEST_STRING
rs.Source = "SELECT * FROM ajax_user"
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 1
rs.Open()

rs_numRows = 0
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Loop Through rs Fields Execute Var</title>
</head>

<body>
<%
'database
' id user_name user_email
' 1 John john@domain.com
' 2 Paul paul@domain.com
counter = 1
Do Until rs.EOF
For Each field In rs.Fields
'var = fieldname_counter
'otherwise the variable value will be overwritten during the loop
Execute(field.Name & "_" & counter & " = """ & field.Value & """")

If counter <> tempCount Then
Response.Write "counter: " & counter & " "
tempCount = counter
End If
Response.Write field.Name & " - " & field.Value & " "
Next
Response.Write " "
counter = counter + 1
rs.MoveNext
Loop
Response.Write " New Variables "
Response.Write "user_name_1 = " & user_name_1 & " "
Response.Write "user_name_2 = " & user_name_2 & " - user_email_2 = " & user_email_2 & " "

%>
</body>
</html>
<%
rs.Close()
Set rs = Nothing
%>