?
Solved

What I am doing of wrong????

Posted on 2001-07-19
18
Medium Priority
?
247 Views
Last Modified: 2008-03-17
What I am doing of wrong???


Public Property Get prova(ByVal cnstr As String, ByRef myArray() As Variant, ByRef condition As Variant, ByRef RowColor 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
 
  rs.Open "select * from filtro", cn
 
  For x = 1 To UBound(myArray)
     For y = 1 To UBound(myArray)
    condition = myArray(x, 1) & x
    RowColor = myArray(1, y) & y
   
   
 
 
    rs.Filter = condition
   
   
If Not rs.EOF Then
   While Not rs.EOF
     
 
     strtemp = strtemp & "<tr bgcolor=" & RowColor & ">" & _
     strtemp = strtemp & "<TD>" & rs(1) & "<td>" & _
     strtemp = strtemp & "<tr>"
 

     rs.MoveNext
  Wend
End If
   
      Next y
  Next x
   
   

   
prova = strtemp


Clean_Up:
  CtxSetComplete
  Exit Property

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


Testing client:

Private Sub Command1_Click()
Dim cnstr
cnstr = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=BancaRete;Data Source=MI-RIGEST19"

Dim var As String
Dim dd As String

Dim oggetto As BanCareteDAL.Vista
Set oggetto = New BanCareteDAL.Vista
 

Dim bravo(2, 2) As Variant


'ReDim bravo(1, 1)

bravo(1, 0) = "Descrizione like '%a%'"
bravo(0, 1) = "red"

bravo(2, 0) = "Descrizione like '%e%'"
bravo(0, 2) = "blue"

Dim condizione As Variant
Dim colore As Variant

var = oggetto.prova(cnstr, bravo(), condizione, colore)

end sub
0
Comment
Question by:amolabravo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
  • +2
18 Comments
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6298269
This line isn't going to get you anywhere
<<strtemp = strtemp & "<TD>" & rs(1) & "<td>" & _>>
...you are never incrementing "rs(1)" it is always set to the first record.
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 6298278
What is the error you get?
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6298295
Dave Greene:

>This line isn't going to get you anywhere
>
><<strtemp = strtemp & "<TD>" & rs(1) & "<td>" & _>>
>...you are never incrementing "rs(1)" it is always set to
>the first record.

"rs(1)" is a reference to the second field. It is the equivalent of:

   rs.Fields(1)

The Fields collection is the default property of the Recordset object, which is why you don't have to explicitly address it (i.e. why you don't have to include the ".Fields" part).

The first field has an index of zero (0).

The line which reads "rs.MoveNext" is the line which points the recordset to the next record.


-Dennis Borg
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Expert Comment

by:CJ_S
ID: 6298297
Dave_Greene,
No, the rs references the current selected record. The rs.movenext is correctly called.
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6298305
Well I guess you guys are right then... I'll take your word for it...  but what UGLY code...
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6298312
amolabravo:

Why don't you take a little time and describe the problem to us?

What is it supposed to do?

What is it doing that it shouldn't? What is it not doing that it should?

I'm sorry; I simply don't have the time to figure out both what it is supposed to do, and what is wrong, and then also figure out the solution.

It's sort-of like going to the doctor and saying, "Doc! I don't feel good. What is wrong with me?" I'm sure the doctor would ask you several questions to find out the symptoms, instead of blindly performing exploratory surgery.

-Dennis Borg
0
 

Author Comment

by:amolabravo
ID: 6298320
Dave Greene, instead of doing your comments study ADO before answering as an "EE expert"...
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 6298323
amolabravo,
every expert makes mistakes. It's being human. Please add more information about the problem you are facing.

regards,
CJ

0
 

Author Comment

by:amolabravo
ID: 6298327
I would like the Rows of the table whose Rs match a certain condition change color accordingly...now when I print from the client I get False and not the content of strtemp..thank you DennisBorg
0
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6298362
Yes its a bit difficult to see what the problem is here







0
 
LVL 22

Expert Comment

by:CJ_S
ID: 6298380
Add some error handling to check whether it gets in the error handler or not. It is hard to see for us since we do not know wheether the rs is actually filled or not.


Clean_Up:
 CtxSetComplete
 Exit Property

ErrorHandler:
 prova = Err.Description
 Debug.Print Err.Number
 RaiseError MODULE_NAME, "Moduli(" & cnstr & ")"
 GoTo Clean_Up
 
End Property
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6298434
Dave Greene:

Please don't let amolabravo's harsh remarks inhibit you from participating in the future. :-)



Amolabravo:

You problem lies in the following lines of code:


    strtemp = strtemp & "<tr bgcolor=" & RowColor & ">" & _
    strtemp = strtemp & "<TD>" & rs(1) & "<td>" & _
    strtemp = strtemp & "<tr>"

You are trying to both append to 'strtemp' *and* use the line continuation characters.

Here is a simplified example of what is happening:

   strTemp = strTemp & "A" & _
   strTemp = strTemp & "B" & _
   strTemp = strTemp & "C"

Part of the value being assigned to strTemp is the expression found in the second line: strTemp=strTemp & "B"

   strTemp = (strTemp = strTemp & "B") <--- will always result in False, because it evaluates the portion I've enclosed in parenthesis to either True or False.

(this has been difficult to articulate clearly ... hopefully you can understand what I'm trying to say anyway)




You would need to revise your code to one of the following:


    strtemp = strtemp & "<tr bgcolor=" & RowColor & ">"
    strtemp = strtemp & "<TD>" & rs(1) & "<td>"
    strtemp = strtemp & "<tr>"

**** OR ****

    strtemp = strtemp & "<tr bgcolor=" & RowColor & ">" & _
              "<TD>" & rs(1) & "<td>" & _
              "<tr>"



-Dennis Borg
0
 
LVL 8

Accepted Solution

by:
DennisBorg earned 580 total points
ID: 6298452
>Add some error handling to check whether it gets in the
>error handler or not. It is hard to see for
>us since we do not know wheether the rs is actually
>filled or not.

Actually, we can know that it will never get to the Err Handler, because of the following two lines:

      On Error GoTo ErrorHandler

      On Error Resume Next

The second line (On Error Resume Next) cancels the first (On Error GoTo ErrorHandler). Also, there is no inline error checking, so any and all errors are simply ignored.

Glad you thought about the err handler, CJ, as I had overlooked this problem until I read your comment.


-Dennis Borg
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 6298468
I overlooked the on error resume next :-/

Like has been said, weird code.
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6298478
To clean up the code a little (will look much better in the VB IDE than in this post):


Public Property Get prova(ByVal cnstr As String, ByRef myArray() As Variant, ByRef condition As Variant,
ByRef RowColor As Variant) As Variant
   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
   cn.Open cnstr
   rs.Open "select * from filtro", cn
   
   strtemp = ""
 
   For x = 1 To UBound(myArray)
      For y = 1 To UBound(myArray)
         condition = myArray(x, 1) & x
         RowColor = myArray(1, y) & y

         rs.Filter = condition
         If Not rs.EOF Then
            While Not rs.EOF
               strtemp = strtemp & "<tr bgcolor=" & RowColor & ">" & _
                         "<TD>" & rs(1) & "<td><tr>"
               rs.MoveNext
            Wend
         End If
      Next y
   Next x

   prova = strtemp

Clean_Up:
   CtxSetComplete
   Exit Property

ErrorHandler:
   Debug.Print Err.Number
   RaiseError MODULE_NAME, "Moduli(" & cnstr & ")"
   GoTo Clean_Up
End Property
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6298483
I forgot to include the ON ERROR statement in my cleaned up version:


To clean up the code a little (will look much better in the VB IDE than in this post):


Public Property Get prova(ByVal cnstr As String, ByRef myArray() As Variant, ByRef condition As Variant,
ByRef RowColor As Variant) As Variant
   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
 
   On Error Goto ErrorHandler

   Set cn = New adodb.Connection
   Set rs = New adodb.Recordset
   cn.Open cnstr
   rs.Open "select * from filtro", cn
   
   strtemp = ""
 
   For x = 1 To UBound(myArray)
      For y = 1 To UBound(myArray)
         condition = myArray(x, 1) & x
         RowColor = myArray(1, y) & y

         rs.Filter = condition
         If Not rs.EOF Then
            While Not rs.EOF
               strtemp = strtemp & "<tr bgcolor=" & RowColor & ">" & _
                         "<TD>" & rs(1) & "<td><tr>"
               rs.MoveNext
            Wend
         End If
      Next y
   Next x

   prova = strtemp

Clean_Up:
   CtxSetComplete
   Exit Property

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

0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6298488
>I overlooked the on error resume next :-/
>
>Like has been said, weird code.

It was easy to overlook. :-)


-Dennis Borg
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6298495
Thank you, amolabravo. Glad you got it working.


-Dennis Borg
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses
Course of the Month10 days, 6 hours left to enroll

765 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