Solved

Urgent

Posted on 2001-09-08
25
251 Views
Last Modified: 2012-08-13
Kind all I have a Rs deriving from a Select * from mytable.
Then I have a 2-D array where
--in the first dimension is stored the name of the file of an image;
-- and in the second is stored a condition(s)/string like the following: "Name = Pippo AND Age > 30". Name and Age are fields of the DB table mytable where the original RS come from.

What I need is code by which I could accomplish the following task: I need the rows of the RS that match the various condition(s) stored in the second D of the array to be associated to the corresponding name of the file of an image (stored in the q dim if the array).

Thanx for your attention and regards to all.

Davide
0
Comment
Question by:Davidex
  • 11
  • 7
  • 2
  • +3
25 Comments
 
LVL 22

Expert Comment

by:CJ_S
ID: 6466662
You could create a new SQL statement each time you grab the information, this way you will be sure that you are only getting the valid items in the recordset.

If you are already sure that you will use all items of the recordset in the program you can use the find method supplied by the ADODB recordset object

rs.Find("Name='PIPO'")

and you will get only the recordset items with their name being PIPO.

Regards,
CJ
0
 
LVL 2

Expert Comment

by:YourBuddyToo
ID: 6466831
To elabrate on CJ S

strCondition = "Name = Pippo AND Age > 30"

rs.Open "Select * from mytable where " & strCondition, connection

or

rs.Find strCondition
0
 

Author Comment

by:Davidex
ID: 6467251
Kind all, maybe I didn't explain myself well. I don't need TO filter the rows contained in my RS: the RS has to remain with ALL the original rows contained in it (if any).

What I need is that those rows of the RS that match one or more group of conditions be associated with the corresponding image.

For ex let suppose to have the
-- myImgae.gif associated to
the conditions: "AGE > 20 AND Income > 3000$";
AND
myImgae1.gif associated to
the conditions: "NUMBEROFSONS > 1 AND MaritalStatus = "Single";

Those rows (if any) of the original RS that match the above conditions have to be associated with the right image.

I think this coild be done by adding to the RS a field containing the right image...

I hope somebody could give his help!!
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 6467385
I think I know what you are getting at, and indeed the best way is by providing the imagefield to the recordset, but to do that you will need to somehow know what image belongs to the record.
How do you know when you retrieve the records? Is that information also in the database?


Regards,
CJ
0
 
LVL 4

Expert Comment

by:wileecoy
ID: 6467447
So Davidex,

What you need is an Update statement so that a field in your table is populated with a specific image file name based on certain conditions being met.

Ex:

Predifined condition and related filename:

NumberOfSons > 1 and MaritalStatus = Single ---- Myimage_1.gif

Age > 20 and Income > 3000$ ---- MyImage_2.gif
....etc

MyImageFile = "MyImage_1.Gif"
MyCondition = "Age > 20 And Income > 3000$"

sqlText = "Update Mytable Set ImageField = " & MyImageFile & " Where " & MyCondition

This will go through and update the ImageField of your table with MyImageFile for all fields meeting MyCondition.

Is this the direction you are trying to go?

Wileecoy.
0
 

Author Comment

by:Davidex
ID: 6467480
Yes CJ_S, the name of the file is stored in the DB in a specific field.
Thanx for your interest in my question. Regards
0
 

Author Comment

by:Davidex
ID: 6467483
The DB is done in a way that the image file(s) are already associated to these conditions/strings as

"NUMBEROFSONS > 1 AND MaritalStatus = "Single";

ONE image x ONE string

Thanx again.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6467784
One thing for sure, at least Davidex is consistent:
Whenever a question is requested on a urgent basis, invariably it belongs to someone who is less than enthusiastic about following up on previous questions.

Davidex, you have 5 questions outstanding, one as old as November of last year.  Please take care of this, before preceeding. Thanks.

Questions Asked 9
Last 10 Grades Given A B A  
Question Grading Record 3 Answers Graded / 3 Answers Received

Anthony
0
 
LVL 11

Expert Comment

by:thman
ID: 6468187

I think Wileecoy is right. You can do this to the exist records. But if your case also needs to consider adding such integrity to new data, I suggest you the following solution.

Let?s assume the table that contains information of people is called Info and the table that contains image filename Images. I suggest you add a field call ImageID to both tables, which is the primary key of table Images, to reduce data redundancy. You open the tables in your application as rsInfo and rsImages respectively. Adding the following to your code.

Private WithEvents rsInfo As ADODB.Recordset
Private rsImages As ADODB.Recordset

Private addingNew As Boolean  'Whether it's adding new record to rsInfo
Private isHandling As Boolean 'for avoiding reentrance when an event is handling

Private Sub rsInfo_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    If isHandling Then Exit Sub
    isHandling = True
    If adReason = adRsnAddNew Then
        addingNew = True
    Else
        addingNew = False
    End If
    isHandling = False
End Sub

Private Sub rsInfo_RecordChangeComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal cRecords As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    If isHandling Then Exit Sub
    isHandling = True
    If adReason = adRsnUpdate And addingNew Then
        updateImageField
    End If
    isHandling = False
End Sub

Private Sub updateImageField()
    Dim oldBM As Bookmark
    Dim oldFilter As String
   
    With rsInfo
        Set oldBM = .Bookmark
        oldFilter = .Filter
    End With
   
    With rsImages
        If .State = adStateClosed Then .Open
        .MoveFirst
        While Not .EOF
            With rsInfo
                .Filter = rsImages!Condition & " ImageID = """""
                .MoveFirst
                While Not .EOF
                    If .Bookmark = oldBM Then
                        !ImageID = rsImages!ImageID
                        .Update
                        .Filter = oldFilter
                        .Bookmark = oldBM
                        Exit Sub
                    End If
                    .MoveNext
                Loop
            End With
            .MoveNext
        Loop
    End With
   
    With rsInfo
        .Filter = oldFilter
        .Bookmark = oldBM
    End With
End Sub

Note: A better solution will also consider this integrity constrain: when you make changes to the Condition field of the Images table, you should make changes to table Info too unless you are sure the Images table will never change.

If performance is not your issue and you want to associate the images to the Info table at run time (so that you don?t need to consider the integrity constrains), you can modify the updateImageField to a sub associateImages as bellow:

'Without WithEvent in this case
Private rsInfo As ADODB.Recordset
Private rsImages As ADODB.Recordset

Private Sub associateImages()
    With rsImages
        If .State = adStateClosed Then .Open
        .MoveFirst
        While Not .EOF
            With rsInfo
                .Filter = rsImages!Condition
                .MoveFirst
                While Not .EOF
                    !ImageFile = rsImages!ImageFile
                    .MoveNext
                Loop
            End With
            .MoveNext
        Loop
    End With
   
    rsInfo.Filter = ""
End Sub

0
 
LVL 11

Expert Comment

by:thman
ID: 6468190
The above code is not tested.

I am sorry I missed a " And" before " ImageID=""""".
0
 
LVL 2

Expert Comment

by:YourBuddyToo
ID: 6468837
How about cloning the recordset and applying the filters to the clone?
0
 

Author Comment

by:Davidex
ID: 6469071
Sorry but what you said is not clear at all!!
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 4

Expert Comment

by:wileecoy
ID: 6469238
I'm confused.

Does my previous comment not address the question??

What am I missing?

In a previous comment, Davidex writes:

"Kind all, maybe I didn't explain myself well. I don't need TO filter the rows contained in my RS: the
RS has to remain with ALL the original rows contained in it (if any)."

So filtering isn't an issue.

It seems to me that a sql statement needs to be constructed dynamically, based on the conditions as previously specified.

Davidex, please help me understand how my previous comment doesn't work and I'll make it work.

Wileecoy.
0
 
LVL 11

Expert Comment

by:thman
ID: 6469272
Based on the information we've got, I think Wileecoy's got what Davidex needs. My comment just gave another guess of Davidex's situation.

Hi, Wileecoy, maybe you have to point out the whole view.

0
 

Author Comment

by:Davidex
ID: 6470771
'test
cnstr = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;PWD=;Initial Catalog=myDBname;Data Source=myservername"




Dim oggetto As SIC_DO.Alarms
Set oggetto = New SIC_DO.Alarms

Dim bravo(3, 3) As Variant


bravo(0, 0) = "Nome like '%a%'"
bravo(0, 1) = "aromb.gif"

bravo(1, 0) = "Cognome like '%e%'"
bravo(1, 1) = "afatstar.gif"

bravo(2, 0) = "Eta < 30"
bravo(2, 1) = "aipno.gif"

bravo(3, 0) = "Eta > 40"
bravo(3, 1) = "acube.gif"

res = oggetto.Alarms(cnstr, bravo())
MsgBox (res)

'I tryed this solution...It works but only partially:
'in effect it seems not able to manage the 'situation 'where 'one or more rows match  2 or more 'different conditions.
 

Public Property Get Alarms(ByVal cnstr As String, ByRef myArray() As Variant) As Variant

On Error GoTo ErrorHandler

'On Error Resume Next
Dim cn As adodb.Connection
Dim Rs As adodb.Recordset


Dim condition As Variant
Dim RowColor As Variant


Dim strtemp As Variant
Dim x As Integer
Dim y As Integer

Set cn = New adodb.Connection
Set Rs = New adodb.Recordset


strtemp = ""

cn.Open cnstr
Dim strSQL As String
strSQL = "select * from portafoglioglobalepromotore"
Rs.Open "select * from portafoglioglobalepromotore", cn

'Looping through the 2-D array
RowColor = "White"
For x = 1 To UBound(myArray)
  'For y = 1 To UBound(myArray) -q
 
'Getting the search condition
condition = myArray(x, 0)

'Getting the row color
RowColor = myArray(x, 1)

'Setting the filter condition

Rs.Filter = condition
 
'Now the rs would contain only filtered data
'Loop through the rs and populate the strtemp

If Not Rs.EOF Then
While Not Rs.EOF
strtemp = strtemp & "<tr bgcolor=" & RowColor & ">"
strtemp = strtemp & "<TD>" & Rs(1) & "</td>"
strtemp = strtemp & "</tr>" & vbCrLf
Rs.MoveNext
Wend
End If

'The filter for the recordset is removed. Now the recordset would contain
'all the original data
Rs.Filter = ""
   'Next y
Next x

Alarms = strtemp


Clean_Up:
CtxSetComplete
Exit Property

ErrorHandler:
Debug.Print Err.Number
RaiseError MODULE_NAME, "Alarms(" & cnstr & ")"
GoTo Clean_Up

End Property
0
 

Author Comment

by:Davidex
ID: 6470776
'test
cnstr = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;PWD=;Initial Catalog=myDBname;Data Source=myservername"




Dim oggetto As SIC_DO.Alarms
Set oggetto = New SIC_DO.Alarms

Dim bravo(3, 3) As Variant


bravo(0, 0) = "Nome like '%a%'"
bravo(0, 1) = "aromb.gif"

bravo(1, 0) = "Cognome like '%e%'"
bravo(1, 1) = "afatstar.gif"

bravo(2, 0) = "Eta < 30"
bravo(2, 1) = "aipno.gif"

bravo(3, 0) = "Eta > 40"
bravo(3, 1) = "acube.gif"

res = oggetto.Alarms(cnstr, bravo())
MsgBox (res)

'I tryed this solution...It works but only partially:
'in effect it seems not able to manage the 'situation 'where 'one or more rows match  2 or more 'different conditions.
 

Public Property Get Alarms(ByVal cnstr As String, ByRef myArray() As Variant) As Variant

On Error GoTo ErrorHandler

'On Error Resume Next
Dim cn As adodb.Connection
Dim Rs As adodb.Recordset


Dim condition As Variant
Dim RowColor As Variant


Dim strtemp As Variant
Dim x As Integer
Dim y As Integer

Set cn = New adodb.Connection
Set Rs = New adodb.Recordset


strtemp = ""

cn.Open cnstr
Dim strSQL As String
strSQL = "select * from portafoglioglobalepromotore"
Rs.Open "select * from portafoglioglobalepromotore", cn

'Looping through the 2-D array
RowColor = "White"
For x = 1 To UBound(myArray)
  'For y = 1 To UBound(myArray) -q
 
'Getting the search condition
condition = myArray(x, 0)

'Getting the row color
RowColor = myArray(x, 1)

'Setting the filter condition

Rs.Filter = condition
 
'Now the rs would contain only filtered data
'Loop through the rs and populate the strtemp

If Not Rs.EOF Then
While Not Rs.EOF
strtemp = strtemp & "<tr bgcolor=" & RowColor & ">"
strtemp = strtemp & "<TD>" & Rs(1) & "</td>"
strtemp = strtemp & "</tr>" & vbCrLf
Rs.MoveNext
Wend
End If

'The filter for the recordset is removed. Now the recordset would contain
'all the original data
Rs.Filter = ""
   'Next y
Next x

Alarms = strtemp


Clean_Up:
CtxSetComplete
Exit Property

ErrorHandler:
Debug.Print Err.Number
RaiseError MODULE_NAME, "Alarms(" & cnstr & ")"
GoTo Clean_Up

End Property
0
 
LVL 11

Expert Comment

by:thman
ID: 6471884
I don't understand your situation. Do you want a row appears more than once or not if it matches two or more conditions?

If you do, you are doing it.

If you do not, can you tell me what's the rule to define which condition should be applied to?
0
 

Author Comment

by:Davidex
ID: 6471960
Kind thman, first thank you for caring this way about my question.
In effect I don't want to have 'a row appears more than once if it matches two or more conditions'.
Let me first permit to resume the situation: for ONE 'condition-string', ie: "Name like '%a%'" or for ex. "AGE > 20 AND Income > 3000$"; I Have associated a specif image file.
what I need is the following: let suppose I have the condition number 1: "Name like '%a%'" and the condition number 2: "AGE > 20 AND Income > 3000$".
Let suppose I associated to the first condition the "image.gif" file and to the second condition the "image2.gif".
Now both the conditions are applied to my RS..
What I need is that, if it happens that a certain row of my RS or a certain group of rows of my RS match both the condition number 1 and the condirtion number 2, I would need that that row or those rows be associated with BOTH the "image.gif" and the "image1.gif" files.
Now in effect a different thing happens, that is: a row appears more than once if it matches two or more conditions'.

I hope to have been clear. Sorry if I didn't before.
With regards

Davide
0
 
LVL 11

Accepted Solution

by:
thman earned 300 total points
ID: 6472307
Okay, Davide, now I think I understand your situation except not clear with the reason you apply the images to bgcolor. (I don't think you would like this to happen: if a group of rows match more than one conditions, you want them to appear in group to be associated to more than one images. That will be rather confusing because no one can predict what the thing would be if a row could be in different groups.)

So the thing is that you have to put the RS in the outer iteration and the array in the inner iteration. There could be a third iteration in the inner iteration to determine whether the current row is satified with the condition.

The code will be sort of the below:
'----
Dim tmpBM As Variant
Dim found As Boolean
Dim ImageFile As String
With Rs
    .MoveFirst
    Do While Not .EOF
        tmpBM = .Bookmark
        strtemp = strtemp & "<tr bgcolor=white><TD>"
        For x = 1 To UBound(myArray)
            condition = myArray(x, 0)
           
            ImageFile = myArray(x, 1)
           
            'if your row have an unique ID to identify himself,
            'better to combine the condition with it like this:
            '.Filter = condition & " and UniqueID=" & !UniqueID
            'if this is the case, you don't need to iterate to
            'find whether it is in the filtered recordset.
            'Just get the RecordCount property.
            .Filter = condition
           
            found = False
           
            Do While Not Rs.EOF
                If .Bookmark = tmpBM Then
                    found = True
                    Exit Do
                End If
            Loop
           
            If found Then
                strtemp = strtemp & "<IMG>" & ImageFile & "</IMG>"
            End If
        Next x
        strtemp = strtemp & "</TD>"
       
        .Filter = ""
        .Bookmark = tmpBM
        'anyway, you want to show the current row, don't you?
        strtemp = strtemp & "<TD>" & Rs(1) & "</td>"
        strtemp = strtemp & "</tr>" & vbCrLf
        .MoveNext
    Loop
End With
'-----

The code hasn't been tested. There could be errors even syntax errors like I did in the prior comments. I am sure you can figure them out if there're any.

Don't thank me. Thank God. God be kind to everyone.

Bless,
thman
0
 
LVL 11

Expert Comment

by:thman
ID: 6472326
Sorry, I found an error just after I committed the comment. At least one.

"<IMG>" & ImageFile & "</IMG>" should be "<IMG SRC=" & ImageFile & "></IMG>"
0
 

Author Comment

by:Davidex
ID: 6473514
Thanx a lot thman!!
0
 

Author Comment

by:Davidex
ID: 6473612
Thanx a lot thman!!
0
 

Author Comment

by:Davidex
ID: 6473654
just a thing thman:

it seems that the following code enters in a infinite loop



Do While Not RS.EOF
               If .Bookmark = tmpBM Then
                   found = True
                   Exit Do
               End If
           Loop
0
 
LVL 11

Expert Comment

by:thman
ID: 6474901
Oh, I forgot to call movenext.

Thank you a lot too.
0
 

Author Comment

by:Davidex
ID: 6485138
Thanks a lot to you thman! Your code have been of great help!!
With my Best Regards

Davide
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now