17 Replies Latest reply on Dec 28, 2007 1:39 PM by JR "Bob" Dobbs-qSBHQ2

    Translation From VB

    mactech999 Level 1
      Could anyone help me with this translation to CFML please?
        • 1. Re: Translation From VB
          Dan Bracuk Level 5
          I understand the code but it looks incomplete. Is the intent to look for duplicate part numbers?
          • 2. Re: Translation From VB
            JR "Bob" Dobbs-qSBHQ2 Level 3
            mactech999,

            My VB is a bit rusty but it appears that your code sample is truncated. The outer do until loop does not appear to be closed.

            Questions:
            1. is rst an ADO recordset?
            2. is this code part of a procedure or function?
            3. Is NoRecs a procedure or function that is being called?
            4. It appears that the purpose of the loop is to make the value of Numrecs that number of distinct PartNo values in the recordset. Is this correct?

            Explaining what this code is trying to do might clarify things.
            • 3. Translation From VB
              mactech999 Level 1
              Sorry about that here is the code

              This Code is part of a Event Procedure.
              on AfterUpdate
              Also In my Coldfusion Code Rst is know as Recordset1

              NumRecs is Declared as a Variant

              Basically what we want is right now we have 20 parts that come down and they each have a description,
              We want to group them by the description and only show the description after the last PartNo
              • 4. Re: Translation From VB
                Dan Bracuk Level 5
                Forget the vb code. It's easier to do it from scratch. What do you mean by:
                "Basically what we want is right now we have 20 parts that come down and they each have a description,
                We want to group them by the description and only show the description after the last PartNo "
                ?

                Specifically, what do you mean by,
                "only show the description after the last PartNo"?
                • 5. Re: Translation From VB
                  mactech999 Level 1
                  ok For a 2005 Honda Civic this is the Result I get:

                  FHO-556 Front Disc Brake Rotor 2 F1 Sport Brake Rotors
                  Use with: SubModel: Hybrid
                  FHO-556 Front Disc Brake Rotor 2 F1 Sport Brake Rotors
                  Use with: 1.7L L4
                  HO-556 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
                  Use with: SubModel: Hybrid
                  HO-556 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
                  Use with: 1.7L L4
                  HO-584 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
                  Use with: 2.0L L4
                  HOD-56 Rear Brake Drum 2 AutoStar Drum & Rotor
                  Use with: 1.7L L4


                  I would like it to be like this

                  FHO-556 Front Disc Brake Rotor 2 F1 Sport Brake Rotors
                  HO-556 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
                  HOD-56 Rear Brake Drum 2 AutoStar Drum & Rotor
                  Use with: 1.7L L4

                  HO-584 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
                  Use with: 2.0L L4

                  FHO-556 Front Disc Brake Rotor 2 F1 Sport Brake Rotors
                  HO-556 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
                  Use with: SubModel: Hybrid
                  • 6. Re: Translation From VB
                    JR "Bob" Dobbs-qSBHQ2 Level 3
                    Take a look at the group attribute of the cfoutput tag. The CF8 documentation includes a sample to demonstrate the use of the group attribute. www.easycfm.com also has a tutorial on this topic.

                    http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_m-o_16.html#1101659
                    http://tutorial150.easycfm.com/
                    • 7. Re: Translation From VB
                      mactech999 Level 1
                      Well you see thats where I have a problem

                      grouping would solve the problem however i am using this to get the display

                      The CfgDisplay is what gives the Description and it is not in Recordset1

                      is there a way of adding it in ?
                      • 8. Re: Translation From VB
                        JR "Bob" Dobbs-qSBHQ2 Level 3
                        Add it as a column in your queries.

                        Two recommendations.
                        1. Use cfqueryparam for all parameters in your cfquery tags.
                        2. I can't give specifics having seen only snippets of your code, but consider consolidating your queries to make fewer calls to your database if possible.
                        • 9. Re: Translation From VB
                          mactech999 Level 1
                          ok I understand what you mean by consolidating the queries only thing is can i use AS CfgDisplay more than once in one query?
                          • 10. Re: Translation From VB
                            JR "Bob" Dobbs-qSBHQ2 Level 3
                            quote:

                            Originally posted by: mactech999
                            can i use AS CfgDisplay more than once in one query?


                            If your database allows it. Give it a try.


                            • 11. Re: Translation From VB
                              Dan Bracuk Level 5
                              quote:

                              Originally posted by: mactech999
                              ok For a 2005 Honda Civic this is the Result I get:

                              FHO-556 Front Disc Brake Rotor 2 F1 Sport Brake Rotors
                              Use with: SubModel: Hybrid
                              FHO-556 Front Disc Brake Rotor 2 F1 Sport Brake Rotors
                              Use with: 1.7L L4
                              HO-556 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
                              Use with: SubModel: Hybrid
                              HO-556 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
                              Use with: 1.7L L4
                              HO-584 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
                              Use with: 2.0L L4
                              HOD-56 Rear Brake Drum 2 AutoStar Drum & Rotor
                              Use with: 1.7L L4


                              I would like it to be like this

                              FHO-556 Front Disc Brake Rotor 2 F1 Sport Brake Rotors
                              HO-556 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
                              HOD-56 Rear Brake Drum 2 AutoStar Drum & Rotor
                              Use with: 1.7L L4

                              HO-584 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
                              Use with: 2.0L L4

                              FHO-556 Front Disc Brake Rotor 2 F1 Sport Brake Rotors
                              HO-556 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
                              Use with: SubModel: Hybrid


                              So in other words, if a part number only shows up once, you want the Use With stuff, otherwise you don't. Hopefully, it's a different field. You can do it all with query of queries. Let's call your database call q1.

                              First, get all the part numbers that only show up once.
                              <cfquery name="q2" dbtype="query">
                              select part_num, part_name, count(part_num)
                              from q1
                              group by part_num, part_name
                              having count(part_num) =1

                              Next get the ones that appear more than once by changing the last line.

                              You should be able to take it from here.


                              • 13. Re: Translation From VB
                                mactech999 Level 1
                                not quite here is my code that i use right now:

                                I would like to group the PartNo by CfgDisplay and only Display the CfgDisplay on the last PartNo

                                like this

                                MI-355 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
                                MID-28 Rear Brake Drum 2 AutoStar Drum & Rotor
                                ES658 Rear Drum Brake Shoe 1 Eurorotor Brake Shoes
                                Use with: 2.4L L4

                                Right now at the moment the Results come out like this:

                                PartNo Position Description Qty Grade

                                MI-355 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
                                Use with: 2.4L L4
                                MID-28 Rear Brake Drum 2 AutoStar Drum & Rotor
                                Use with: 2.4L L4
                                ES658 Rear Drum Brake Shoe 1 Eurorotor Brake Shoes
                                Use with: 2.4L L4
                                • 14. Re: Translation From VB
                                  Level 7
                                  This would be trivial to do with a <cfquery...> and its "group"
                                  parameter. It should be possible to build the record set you need one
                                  way or another. Depending on your system and the requirements you
                                  should be able to either:

                                  1) Build the record set with SQL in a single <cfquery...> using Joins,
                                  Unions, ect.

                                  OR

                                  2) Build the record set from multiple record sets using Query Of Query
                                  with SQL Joins, Unions, ect.

                                  OR

                                  3) Using queryNew(), queryAddRow() and querySetCell() functions to
                                  modify or build the required record set.

                                  You could also achieve grouping with a build your own algorithm with the
                                  right combination of loops and branches to build the output you need.
                                  But I think the former record set solution and <cfoutput> is easier and
                                  more reusable.
                                  • 15. Re: Translation From VB
                                    mactech999 Level 1
                                    where can i get examples of this?
                                    • 16. Re: Translation From VB
                                      Level 7
                                      mactech999 wrote:
                                      > where can i get examples of this?

                                      Examples of which? All the techniques are well discussed in various
                                      formats. You can find all kinds of SQL tutorials and resources. I hear
                                      "Teach yourself SQL in 10 Minutes" by Ben Forta is good. His CFWACK
                                      (ColdFusion Web Application Construction Kit" book and sequal(s) are
                                      also very well received and much bigger.

                                      All the tags and functions are described in the ColdFusion documentation
                                      with executable examples in almost all of them. Then there are
                                      innumerable blogs, tutorials and discussion lists available on the internet.

                                      Finally, if you can phrase your questions a clear format, much help can
                                      be had right here. Asking "How do I write a CMS|Amazon|E-Bay|MySpace
                                      application from the ground up?" is not going to get much of a favorable
                                      result. On the other hand, "This loop produces output A but I need
                                      output B, what should I look for?" Will often get at least one person to
                                      help.




                                      • 17. Re: Translation From VB
                                        JR "Bob" Dobbs-qSBHQ2 Level 3
                                        For an introduction to databases you might try the books "SQL Queries for Mere Mortals" or "Database Design for Mere Mortals"