Looking for old forum posts and information? View the old forum archive here ยป
Welcome to Vortx Community Forum, where you can ask questions and receive answers from the staff at Vortx and other members of the community.

If you had a user account on our previous forums website, you will need to register a new account here.

A static copy of our previous forums website is available online for reference. Click here to access the forum archive.

Learn more about...

AspDotNetStorefront
DotFeed

Adding A Custom Report

It's been a while since I have added a custom report, maybe I am missing something easy.  I need to add the following report, but I get a Syntax Error

(Exception=Incorrect syntax near '100000'.)

It runs fine in SQL, but when I put it in the Run SQL screen in ASPDNSF I get the error.

INSERT INTO CustomReport  ('Bulk Product List', 'Pricing for Products 5Lbs and Over', 'Select SKU +''+SKUSuffix as "SKU",SKUSuffix as "Variant",Product.Name as "Description", ProductVariant.Name as "Size",Price as "Price"   From Product left outer join ProductVariant on Product.ProductID=ProductVariant.ProductID    Where SKU >='100000' and SKU <= '300999' and Product.Published = '1' and SKUSuffix >=' 008'   Order By SKU')

 

Any ideas?

Thanks!
Penny

 

asked Mar 10 in MultiStore by pennypatt (485 points)

1 Answer

0 votes
 
Best answer

HI Penny, there are a few things wrong... watch your use of ' vs "

Notice the SQL query surrounded by ' ' has within it a mix of " and '  (single quotes) but ' is the bound quote so you cannot use '

So for the SKU conditions, make sure to escape the ' with a double ' quote... also, not sure if it will work since SKU is a string and you are treating as a number...

Also your insert statement is missing Values etc.

Nevertheless here it is fixed up:

 

INSERT INTO CustomReport(Name, Description, SQLCommand) VALUES ('Bulk Product List', 'Pricing for Products 5Lbs and Over', 'Select SKU +''''+SKUSuffix as "SKU",SKUSuffix as Variant,Product.Name as Description, ProductVariant.Name as Size,Price as Price   From Product left outer join ProductVariant on Product.ProductID=ProductVariant.ProductID    Where SKU >=''100000'' and SKU <= ''300999'' and Product.Published = ''1'' and SKUSuffix >='' 008''   Order By SKU')

 
Thank you,
Jan
 
Compunix, LLC (Phoenix, AZ)
AspDotNetStorefront Development Partner and Reseller since 2005
------------------------------------------------------------------------------------------------------
 AspDotNetStorefront add-ons and plugins : http://www.ecommercecartmods.com
  * Searching Filtering, Single Signons, Reviews Ratings, Reports, Integrations
 Complete Automotive Solution : http://www.autopartsshoppingcart.com
  * Auto + Aftermarket Parts solution
 Unlimited Custom Development : http://www.compunix.us/t-unlimited-custom-development.aspx
  * Unlimited with Quick turn-arounds!
------------------------------------------------------------------------------------------------------
 

 

answered Mar 10 by jsimacek (6,045 points)
selected Mar 10 by pennypatt
Jan,

Yes, I see now what I was doing wrong with the quotes.  I actually had the insert statement in it when I started and must have accidentally taken it out when I was troubleshooting the issue.

 

Anyway, it runs perfectly now - than you for your help.

Have a great weekend!

Penny
Thank you, you too!
...