• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

What I am doing of wrong????

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
amolabravo
Asked:
amolabravo
  • 8
  • 5
  • 2
  • +2
1 Solution
 
Dave_GreeneCommented:
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
 
CJ_SCommented:
What is the error you get?
0
 
DennisBorgCommented:
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
Industry Leaders: 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!

 
CJ_SCommented:
Dave_Greene,
No, the rs references the current selected record. The rs.movenext is correctly called.
0
 
Dave_GreeneCommented:
Well I guess you guys are right then... I'll take your word for it...  but what UGLY code...
0
 
DennisBorgCommented:
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
 
amolabravoAuthor Commented:
Dave Greene, instead of doing your comments study ADO before answering as an "EE expert"...
0
 
CJ_SCommented:
amolabravo,
every expert makes mistakes. It's being human. Please add more information about the problem you are facing.

regards,
CJ

0
 
amolabravoAuthor Commented:
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
 
nigelroweCommented:
Yes its a bit difficult to see what the problem is here







0
 
CJ_SCommented:
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
 
DennisBorgCommented:
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
 
DennisBorgCommented:
>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
 
CJ_SCommented:
I overlooked the on error resume next :-/

Like has been said, weird code.
0
 
DennisBorgCommented:
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
 
DennisBorgCommented:
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
 
DennisBorgCommented:
>I overlooked the on error resume next :-/
>
>Like has been said, weird code.

It was easy to overlook. :-)


-Dennis Borg
0
 
DennisBorgCommented:
Thank you, amolabravo. Glad you got it working.


-Dennis Borg
0

Featured Post

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!

  • 8
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now