AbleCommerce Shopping Cart Software

I'm a developer for a POS and Inventory Management software company that has many customers that are willing to use AbleCommerce with our Web Store solution. Unfortunately, I'm having a problem with regards to variants. I am using SQL Pass-through to update the database. We cannot use dataport since it does not integrate into our software, and we have to have this run without user input via a scheduler every hour of the day, at each customer's site. Some of our customers have over 130,000 items to manage, many of which use size and color as the variants. We are limiting the variants to only 2 options.

I am filling up the tables properly, exactly as the administrative tool does. There are 4 tables involved for maintaining options: ac_options, ac_optionChoices, ac_productOptions, and ac_productVariants. Each time my software runs an export maintaining the web store data, I update each table, checking for existing records, inserting when necessary, and updating the remaining records with quantities, orderby, options, etc. Additionally, I keep the tables clean by removing unnecessary options.

There are 3 choices for the products form containing options in drop downs (1) , options in drop downs (2), options in grid (3). I can certainly get the first 2 to work beautifully without any problem and it is very nice. Unfortunately, option 3 always gives me an error, varying with the option name: [[ConLib:BuyProductDialogOptionsList]] DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'LEVI 501 SIZE'.

This error can be resolved by going through the administrative tool and going to each product that contains variants and saving one or more of the options. This is not maintainable for our customers as they are going to have thousands of products with variants. I'm assuming that the admin tool triggers a function similar to BuildProductOptions(), upon save. However, the drop downs utilize it properly without anymore interaction.


Thanks,

Bob Ethridge
Development Manager
ARS Business Solutions, LLC
http://www.arss.com

Can you post the code that you are using to create/save options and variants?

Here is the basic code, it is a very long Visual FoxPro program that has many procedures, so here are some relevant pieces:

******** ac_Options Insert upon not locating an existing record
SQLEXEC(.nHandle, "INSERT INTO ac_Options " + ;
"(name,showthumbnails,thumbnailcolumns,thumbnailwidth," + ;
"thumbnailheight,createddate) VALUES " + ;
"(?m.lcOptionName,?m.llFalse,0,0,0,?m.ldDatetime)")

******** ac_OptionChoices Insert
SQLEXEC(.nHandle, "INSERT INTO ac_OptionChoices " + ;
"(name,optionid,orderby ) " + ;
"VALUES (?csrDistColor.Color,?m.lnColorId,?m.lnLoop)")

******** ac_OptionChoices Update
SQLEXEC(.nHandle, "Update ac_OptionChoices Set " + ;
"orderby=?m.lnLoop where optionchoiceid=?tblLocalOptionChoices.optionchoi")

******** ac_ProductOptions Insert
SQLEXEC(.nHandle,"INSERT INTO ac_productOptions " + ;
"(optionid,orderby,productid) values " + ;
"(?m.lnSizeID,1,?m.lnRemoteVariantParentId)")

******** ac_ProductOptions Update
SQLEXEC(.nHandle, "UPDATE ac_ProductOptions SET " + ;
"orderby=" + IIF(SEEK(PADR(ALLTRIM(UPPER(ITEM.Desc1)),30), ;
'csrColorAndSize', 'desc1'), "2", "1") + ;
" WHERE optionid=?m.lnColorID and productid=?m.lnRemoteVariantParentId" )

******** ac_ProductVariants Insert (concatenated result of a built string)
SQLEXEC(.nHandle, "INSERT INTO ac_productVariants (Available,Option1,Option2,Option3,Option4,Option5,Option6,Option7,Option8,InStock,Price,PriceModeId,ProductId,Sku,Weight,WeightModeId,InStockWarningLevel,VariantName,CostOfGoods) VALUES (?m.llTrue,1054,1067,0,0,0,0,0,0,?m.lnqoh,?m.lnprice,1,?m.lnRemoteVariantParentID,?csrColorSize.sku,?item.weight,1,0,null,null)")

******** ac_ProductVariants Update (this is a result of a bunch of comparison code concatenated to shorten the data passed)
SQLEXEC(.nHandle, "UPDATE ac_productVariants SET instock=?m.lnqoh,option1=72,option2=44,price=?m.lnPrice,weight=?m.lnWeight,productid=?m.lnRemoteVariantParentId,available=?m.llTrue,pricemodeid=1,weightmodeid=1 where sku=?csrColorSize.sku")


Thanks,
Bob

Why are you writing the FoxPro routines to do this job. It would be better to use the AbleCommerce API to do this job. For example you can create some web service which can accept the information and can update the database using Able code.

I gave a try by creating the options for a product directly in database without any AbleCode at all and worked perfectly. I create 3 options each with 3 choices in db tables in following hierarchy. First I created three options in ac_Options table, then I created three choices for each in ac_OptionChoices table and at the end I joined these options with product in ac_ProductOptions table. Then I directly visited the related product details page and it worked without any problem. Check the screen capture. May be there is something goes wrong when you try to update your db through queries. Did you modified the BuyProductDialogOptionsList control?

Why are you writing the FoxPro routines to do this job. It would be better to use the AbleCommerce API to do this job. For example you can create some web service which can accept the information and can update the database using Able code.

Using an API is not a bad concept, however, I don't currently have access to it, nor do I have the ability to create and maintain Web Services for each client. The routines would still need the logic in place to determine what data needed to be related, ordered, updated, maintained, etc. So really the language should not matter as much as the data that goes into the database.

I gave a try by creating the options for a product directly in database without any AbleCode at all and worked perfectly. I create 3 options each with 3 choices in db tables in following hierarchy. First I created three options in ac_Options table, then I created three choices for each in ac_OptionChoices table and at the end I joined these options with product in ac_ProductOptions table. Then I directly visited the related product details page and it worked without any problem.

I would like some more details, did you open the tables in SQL Server and manually enter in all new data into ac_Options, ac_OptionChoices, ac_ProductOptions, and ac_ProductVariants?

Thanks,
Bob

I would like some more details, did you open the tables in SQL Server and manually enter in all new data into ac_Options, ac_OptionChoices, ac_ProductOptions, and ac_ProductVariants?

Yes I opened and entered the values in ac_Options, ac_OptionChoices and ac_ProductOptions tables. I haven't touched the ac_ProductVariants table.

Using an API is not a bad concept, however, I don't currently have access to it, nor do I have the ability to create and maintain Web Services for each client.

When you have CommerceBuilder.DLL you have access to it. When you are modifying/using Ablecommerce's aspx pages you are actually using the CommerceBuilder API.

The routines would still need the logic in place to determine what data needed to be related, ordered, updated, maintained, etc. So really the language should matter as much as the data that goes into the database.

Its not the language that matters; Its the API that matters. The details of what goes and what not to the database are shielded from the user by the API. When you use the API you do not directly interact with the database.
Creating a product using API would look like instead of

A CSV import routine sample
viewtopic.php?f=47&t=9720

I understand that you want me to go into the direction of the API. However to complete your test, could you put some variations in that list, such as model (sku) / price variation / quantites and then test it. I want you to try it by writing directly to the ac_productVariants table, or if you have another way, other than using able tools, please let me know.

Thanks,
Bob