Solved

What I am doing of wrong????

Posted on 2001-07-19
18
240 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
  • 8
  • 5
  • 2
  • +2
18 Comments
 
LVL 8

Expert Comment

by:Dave_Greene
Comment Utility
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
Comment Utility
What is the error you get?
0
 
LVL 8

Expert Comment

by:DennisBorg
Comment Utility
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
 
LVL 22

Expert Comment

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

Expert Comment

by:Dave_Greene
Comment Utility
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
Comment Utility
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
Comment Utility
Dave Greene, instead of doing your comments study ADO before answering as an "EE expert"...
0
 
LVL 22

Expert Comment

by:CJ_S
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 3

Expert Comment

by:nigelrowe
Comment Utility
Yes its a bit difficult to see what the problem is here







0
 
LVL 22

Expert Comment

by:CJ_S
Comment Utility
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
Comment Utility
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 145 total points
Comment Utility
>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
Comment Utility
I overlooked the on error resume next :-/

Like has been said, weird code.
0
 
LVL 8

Expert Comment

by:DennisBorg
Comment Utility
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
Comment Utility
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
Comment Utility
>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
Comment Utility
Thank you, amolabravo. Glad you got it working.


-Dennis Borg
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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 …
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 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

14 Experts available now in Live!

Get 1:1 Help Now