Help with code fragment

[tmcneil]

I have a piece of code where I loop through a recordset and would like to set the value of a variable, like "selimagefile1". However, my variables increase by one, selimagefile1, selimagefile2 and so forth. So, how do I set the variables if my code looks like this and will not compile correctly....

'Get data from ANN_SCHEDULE_IMAGE
'
Set cn = GetDBConnection()
Set SQLStmt1 = Server.CreateObject("ADODB.Command")
Set RS1 = Server.CreateObject("ADODB.Recordset")
SQLStmt1.CommandText = "SELECT * FROM ANN_SCHEDULE_IMAGE " & _
                                       " WHERE ANN_SCHEDULE_ID = '" & ann_schedule_id & "'"
                                       
SQLStmt1.CommandType = 1
Set SQLStmt1.ActiveConnection = cn
RS1.Open SQLStmt1
count1 = 1
Do While Not RS1.EOF
    "selimagefile" & count1 = RS1("image_name")  'error here
    "selcaption" & count1   = RS1("image_caption") 'error here
    count1 = count1 + 1
    RS1.MoveNext
Loop
RS1.Close

Thanks,
Todd

 

[MakeItSo]

You cannot handle variables like this.
I suggest, you dim your "selimagefile" as a variant (array) and use "Redim preserve" to make it grow dynamically:

Dim selimagefile as Variant, selcaption as Variant
...
selimagefile=array(1)
selcaption=array(1)
...
count1 = [b]0[/b]
Do While Not RS1.EOF
    Redim Preserve selimagefile(count1+1)
    Redim Preserve selimagefile(count1+1)
    selimagefile(count1) = RS1("image_name")
    selcaption(count1)   = RS1("image_caption")
    count1 = count1 + 1
    RS1.MoveNext
Loop

Hope this helps.
;-)

Cheers,
Andy

[blue]Help us, join us, participate
IAHRA - International Alliance of Human Rights Advocates[/blue]

 

[guitarzan]

You cannot handle variables like this.

Acutally, you can... see the Execute function on this page:

http://www.4guysfromrolla.com/webtech/030300-1.shtml

HOWEVER, I would strongly recommend the method Andy shows, that is, using arrays.

 

[tmcneil]

Well,

I'd like to make it as easy as possible and tried Execute(), but I don't think I am doing it right....

Execute("selimagefile" & count1+1 = RS1("image_name"))
Execute("selcaption" & count1+1 = RS1("image_caption"))

??

 

[tmcneil]

Sorry, I submitted my previous post before I was done. i get a, Type mismatch: 'Execute' error when using the above code. I could use the array code, but am not sure how I would store the variables again since I still would have to set selimagefile1, selimagefile2 and selimagefile3. I just don't see how using an array would get me around that as I would still have to do a loop to set them.

Todd

 

[guitarzan]

Try this (not tested):

Execute("selimagefile" & count1+1 [COLOR=red]& " = " &[/color] RS1("image_name"))

 

[guitarzan]

...I still would have to set selimagefile1, selimagefile2 and selimagefile3.

Why? If you use an array, you would access them as selimagefile(0), selimagefile(1), etc... or, better yet:

For x = LBound(selimagefile) to UBound(selimagefile)
	'Do something with selimagefile(x)
	'Do something with selcaption(x)
Next

Arrays will make the code more dynamic. Are you ONLY dealing with three different sets of images? Could there be more, now or in the future? Do you want to change your code every time a new image is added?

Of course, I don't know how you're using selimagefile1, selimagefile2, etc...

 

[tmcneil]

Well, after careful consideration, I used a Case statement to do what I wanted.

count1 = 1
Do While Not RS1.EOF
    Select Case count1
        Case 1
	        selimagefile1 = RS1("image_name") & ".jpg"
            selcaption1   = RS1("image_caption")
            imageFiles = selimagefile1 & ",,"
            captionValues = selcaption1 & ",,"
        Case 2
	        selimagefile2 = RS1("image_name") & ".jpg"
            selcaption2   = RS1("image_caption")
            imageFiles = selimagefile1 & "," & selimagefile2 & ","
            captionValues = selcaption1 & "," & selcaption2 & ","
        Case 3
            selimagefile3 = RS1("image_name") & ".jpg"
            selcaption3   = RS1("image_caption")
            imageFiles = selimagefile1 & "," & selimagefile2 & "," & selimagefile3
            captionValues = selcaption1 & "," & selcaption2 & "," & selcaption3
    End Select
    count1 = count1 + 1
    RS1.MoveNext
Loop
RS1.Close

It's not elegant, but it's effective to get what I need.

Todd

 

[MakeItSo]

There is another possibility:

If you add a "Microsoft Script Control 1.0" to your form, you can
* put your code together into a string variable
* send it to the ScriptControl
* and run it

Example: I have just programmed a little "File Property Manager" to display and edit file properties. As I haven't found a way to address the SummaryProperties of an Office doc by index rather than by name (e.g. SummaryProperties.Item("Author") rather than "SummaryProperties.Author", I have decided to apply exactly such a thingy.
I have two listboxes, "filez" and "propz" (the latter with to columns).
"Label1" displays the name of the property that is just being edited by the user, text1 contains the new content.

Private Sub UpdateProps_Click()
Dim strcode As String, pfad As String

pfad = Form1.File1.Path
If Right(pfad, 1) <> "\" Then pfad = pfad & "\"
    strcode = "Sub Updateme()" & vbNewLine
    strcode = strcode & "Dim obfFile" & vbNewLine
    strcode = strcode & "Set objFile = CreateObject(" & Chr(34) & "DSOFile.OleDocumentProperties" & Chr(34) & ")" & vbNewLine
    strcode = strcode & "objFile.Open " & Chr(34) & pfad & Form2.filez & Chr(34) & vbNewLine
    strcode = strcode & "objFile.SummaryProperties." & Label1.Caption & "=" & Chr(34) & Text1.Text & Chr(34) & vbNewLine
    strcode = strcode & "objFile.Save" & vbNewLine & "objFile.Close" & vbNewLine & "Set objFile = Nothing" & vbNewLine & "Set objProperty = Nothing" & vbNewLine
    strcode = strcode & "End Sub" & vbNewLine
    Debug.Print strcode
    ScriptControl1.AddCode strcode
    ScriptControl1.Run "Updateme"
    
    filez_Click

End Sub

By the way, this is a typical debug.Print of what I send to the script control:

Sub Updateme()
Dim obfFile
Set objFile = CreateObject("DSOFile.OleDocumentProperties")
objFile.Open "k:\Tools_Makros_Liste.xls"
objFile.SummaryProperties.Category="Compilation of tools and macros"
objFile.Save
objFile.Close
Set objFile = Nothing
Set objProperty = Nothing
End Sub

The property ".Category" is dynamically added and runs just as normal code. You could even load some code stored in a text file into the script control that way and execute it during runtime.

Would that be something?


[blue]Help us, join us, participate
IAHRA - International Alliance of Human Rights Advocates[/blue]