Translation From VB

Report · Dec 27, 2007

Could anyone help me with this translation to CFML please?

Report · Dec 27, 2007

I understand the code but it looks incomplete. Is the intent to look for duplicate part numbers?

Report · Dec 27, 2007

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.

Report · Dec 27, 2007

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

Report · Dec 27, 2007

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"?

Report · Dec 28, 2007

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

Report · Dec 28, 2007

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/

Report · Dec 28, 2007

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 ?

Report · Dec 28, 2007

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.

Report · Dec 28, 2007

ok I understand what you mean by consolidating the queries only thing is can i use AS CfgDisplay more than once in one query?

Report · Dec 28, 2007

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.

Report · Dec 28, 2007

1

Report · Dec 28, 2007

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

Report · Dec 28, 2007

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.

Report · Dec 28, 2007

where can i get examples of this?

Report · Dec 28, 2007

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.

Report · Dec 28, 2007

For an introduction to databases you might try the books "SQL Queries for Mere Mortals" or "Database Design for Mere Mortals"

Report · Dec 28, 2007

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.

Adobe Community

Translation From VB