Excel export raises error

hi,

exporting products or orders to excel gives me following error always:
Number of query values and destination fields are not the same.
System.Data.OleDb.OleDbException: Number of query values and destination fields are not the same. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at NopSolutions.NopCommerce.BusinessLogic.ExportImport.ExcelHelper.ExecuteCommand(String Command) in F:\Own\NopCommerce\Solution\Solution\Libraries\Nop.BusinessLogic\ExportImport\ExcelHelper.cs:line 171 at NopSolutions.NopCommerce.BusinessLogic.ExportImport.ExportManager.ExportProductsToXLS(String FilePath, ProductCollection products) in F:\Own\NopCommerce\Solution\Solution\Libraries\Nop.BusinessLogic\ExportImport\ExportManager.cs:line 761 at NopSolutions.NopCommerce.Web.Administration.Modules.ProductsControl.btnExportXLS_Click(Object sender, EventArgs e) in F:\Own\NopCommerce\Solution\Solution\NopCommerceStore\Administration\Modules\Products.ascx.cs:line 142


XML export works. Is this some kind of bug or something wrong with my setup?


Best regards,
Sven

Try adding write permissions to the files/ExportImport directory?

E

I got the same issue and it's related with regional settings. In my case a comma is the decimal point and this is a problem for the current code on ExportProductsToXLS.
My solution requires code modifications and uses parameterized SQL. Something like this:

string query = @"INSERT INTO [Products] (Name, ...)  VALUES (?, ...)";
OleDbCommand cmd = new OleDbCommand(query);
OleDbParameter paramName = new OleDbParameter("@Name", OleDbType.VarChar, maxStringLength);
paramName.Value = p.Name;
cmd.Parameters.Add(paramName);
...

This is the error i get trying to export to excel:

Number of query values and destination fields are not the same

Hi all,

here is my solution.

I replaced all double quotes with single quotes and added missing single quotes to all INSERT values.

1) Run Visual Web Developer 2008 Express Edition
2) Open Project -> NopCommerce.sln
3) Solution Explorer -> open Nop.BusinessLogic/ExportImport/ExportManager.cs
4) Search "public static void ExportProductsToXLS"
5) Replace foreach cycle with code below...
6) Build Nop.BusinessLogic
7) Go to folder ...NopCommerce\Libraries\Nop.BusinessLogic\bin\Debug\
8) Replace files Nop.BusinessLogic.dll, Nop.BusinessLogic.pdb, Nop.BusinessLogic.xml on your web /bin directory
9) Now XLS export works fine ;-)

Code:

foreach (Product p in products)
                {
                    ProductVariantCollection productVariants = p.ProductVariants;
                    foreach (ProductVariant pv in productVariants)
                    {
                        StringBuilder sb = new StringBuilder();
                        sb.Append("INSERT INTO [Products] (Name, ShortDescription,FullDescription,ProductTypeID,TemplateID,ShowOnHomePage,MetaKeywords,MetaDescription,MetaTitle,AllowCustomerReviews,AllowCustomerRatings,Published,SKU,ManufacturerPartNumber,IsDownload,DownloadID,UnlimitedDownloads,MaxNumberOfDownloads,HasSampleDownload,SampleDownloadID,IsShipEnabled,IsFreeShipping,AdditionalShippingCharge,IsTaxExempt,TaxCategoryID,ManageInventory,StockQuantity,MinStockQuantity,LowStockActivityID,NotifyAdminForQuantityBelow,OrderMinimumQuantity,OrderMaximumQuantity,DisableBuyButton,Price,OldPrice,Weight, Length, Width, Height, CreatedOn) VALUES (");
                        string name = p.Name;
                        if (name.Length > maxStringLength)
                            name = name.Substring(0, maxStringLength);
                        sb.Append("'"); sb.Append(name.Replace('"', '\'')); sb.Append("',");
                        string shortDescription = p.ShortDescription;
                        if (shortDescription.Length > maxStringLength)
                            shortDescription = shortDescription.Substring(0, maxStringLength);
                        sb.Append("'"); sb.Append(shortDescription.Replace('"', '\'')); sb.Append("',");
                        string fullDescription = p.FullDescription;
                        if (fullDescription.Length > maxStringLength)
                            fullDescription = fullDescription.Substring(0, maxStringLength);
                        sb.Append("'"); sb.Append(fullDescription.Replace('"', '\'')); sb.Append("',");
                        sb.Append("'"); sb.Append(p.ProductTypeID); sb.Append("',");
                        sb.Append("'"); sb.Append(p.TemplateID); sb.Append("',");
                        sb.Append("'"); sb.Append(p.ShowOnHomePage); sb.Append("',");
                        string metaKeywords = p.MetaKeywords;
                        if (metaKeywords.Length > maxStringLength)
                            metaKeywords = metaKeywords.Substring(0, maxStringLength);
                        sb.Append("'"); sb.Append(metaKeywords.Replace('"', '\'')); sb.Append("',");
                        string metaDescription = p.MetaDescription;
                        if (metaDescription.Length > maxStringLength)
                            metaDescription = metaDescription.Substring(0, maxStringLength);
                        sb.Append("'"); sb.Append(metaDescription.Replace('"', '\'')); sb.Append("',");
                        string metaTitle = p.MetaTitle;
                        if (metaTitle.Length > maxStringLength)
                            metaTitle = metaTitle.Substring(0, maxStringLength);
                        sb.Append("'"); sb.Append(metaTitle.Replace('"', '\'')); sb.Append("',");
                        sb.Append("'"); sb.Append(p.AllowCustomerReviews); sb.Append("',");
                        sb.Append("'"); sb.Append(p.AllowCustomerRatings); sb.Append("',");
                        sb.Append("'"); sb.Append(p.Published); sb.Append("',");
                        string SKU = pv.SKU;
                        if (SKU.Length > maxStringLength)
                            SKU = SKU.Substring(0, maxStringLength);
                        sb.Append("'"); sb.Append(SKU.Replace('"', '\'')); sb.Append("',");
                        string manufacturerPartNumber = pv.ManufacturerPartNumber;
                        if (manufacturerPartNumber.Length > maxStringLength)
                            manufacturerPartNumber = manufacturerPartNumber.Substring(0, maxStringLength);
                        sb.Append("'"); sb.Append(manufacturerPartNumber.Replace('"', '\'')); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.IsDownload); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.DownloadID); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.UnlimitedDownloads); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.MaxNumberOfDownloads); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.HasSampleDownload); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.SampleDownloadID); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.IsShipEnabled); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.IsFreeShipping); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.AdditionalShippingCharge); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.IsTaxExempt); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.TaxCategoryID); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.ManageInventory); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.StockQuantity); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.MinStockQuantity); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.LowStockActivityID); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.NotifyAdminForQuantityBelow); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.OrderMinimumQuantity); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.OrderMaximumQuantity); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.DisableBuyButton); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.Price); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.OldPrice); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.Weight); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.Length); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.Width); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.Height); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.CreatedOn); sb.Append("'");
                        sb.Append(")");
                        excelHelper.ExecuteCommand(sb.ToString());
                    }
                }

Still get some error when text came like warm's  and ' in the end of text so i have made some changes code is here


foreach (Product p in products)
                {
                    ProductVariantCollection productVariants = p.ProductVariants;
                    foreach (ProductVariant pv in productVariants)
                    {
                        StringBuilder sb = new StringBuilder();
                        sb.Append("INSERT INTO [Products] (Name, ShortDescription,FullDescription,ProductTypeID,TemplateID,ShowOnHomePage,MetaKeywords,MetaDescription,MetaTitle,AllowCustomerReviews,AllowCustomerRatings,Published,SKU,ManufacturerPartNumber,IsDownload,DownloadID,UnlimitedDownloads,MaxNumberOfDownloads,HasSampleDownload,SampleDownloadID,IsShipEnabled,IsFreeShipping,AdditionalShippingCharge,IsTaxExempt,TaxCategoryID,ManageInventory,StockQuantity,MinStockQuantity,LowStockActivityID,NotifyAdminForQuantityBelow,OrderMinimumQuantity,OrderMaximumQuantity,DisableBuyButton,Price,OldPrice,Weight, Length, Width, Height, CreatedOn) VALUES (");
                        string name = p.Name;
                        if (name.Length > maxStringLength)
                            name = name.Substring(0, maxStringLength);
                        sb.Append("'"); sb.Append(name.Replace('"', '\'').Replace("'s", "&quto;s").Replace("'","&quto;")); sb.Append("',");
                        string shortDescription = p.ShortDescription;
                        if (shortDescription.Length > maxStringLength)
                            shortDescription = shortDescription.Substring(0, maxStringLength);
                        sb.Append("'"); sb.Append(shortDescription.Replace('"', '\'').Replace("'s", "&quto;s").Replace("'", "&quto;")); sb.Append("',");
                        string fullDescription = p.FullDescription;
                        if (fullDescription.Length > maxStringLength)
                            fullDescription = fullDescription.Substring(0, maxStringLength);
                        sb.Append("'"); sb.Append(fullDescription.Replace('"', '\'').Replace("'s", "&quto;s").Replace("'", "&quto;")); sb.Append("',");
                        sb.Append("'"); sb.Append(p.ProductTypeID); sb.Append("',");
                        sb.Append("'"); sb.Append(p.TemplateID); sb.Append("',");
                        sb.Append("'"); sb.Append(p.ShowOnHomePage); sb.Append("',");
                        string metaKeywords = p.MetaKeywords;
                        if (metaKeywords.Length > maxStringLength)
                            metaKeywords = metaKeywords.Substring(0, maxStringLength);
                        sb.Append("'"); sb.Append(metaKeywords.Replace('"', '\'')); sb.Append("',");
                        string metaDescription = p.MetaDescription;
                        if (metaDescription.Length > maxStringLength)
                            metaDescription = metaDescription.Substring(0, maxStringLength);
                        sb.Append("'"); sb.Append(metaDescription.Replace('"', '\'')); sb.Append("',");
                        string metaTitle = p.MetaTitle;
                        if (metaTitle.Length > maxStringLength)
                            metaTitle = metaTitle.Substring(0, maxStringLength);
                        sb.Append("'"); sb.Append(metaTitle.Replace('"', '\'')); sb.Append("',");
                        sb.Append("'"); sb.Append(p.AllowCustomerReviews); sb.Append("',");
                        sb.Append("'"); sb.Append(p.AllowCustomerRatings); sb.Append("',");
                        sb.Append("'"); sb.Append(p.Published); sb.Append("',");
                        string SKU = pv.SKU;
                        if (SKU.Length > maxStringLength)
                            SKU = SKU.Substring(0, maxStringLength);
                        sb.Append("'"); sb.Append(SKU.Replace('"', '\'')); sb.Append("',");
                        string manufacturerPartNumber = pv.ManufacturerPartNumber;
                        if (manufacturerPartNumber.Length > maxStringLength)
                            manufacturerPartNumber = manufacturerPartNumber.Substring(0, maxStringLength);
                        sb.Append("'"); sb.Append(manufacturerPartNumber.Replace('"', '\'')); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.IsDownload); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.DownloadID); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.UnlimitedDownloads); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.MaxNumberOfDownloads); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.HasSampleDownload); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.SampleDownloadID); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.IsShipEnabled); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.IsFreeShipping); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.AdditionalShippingCharge); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.IsTaxExempt); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.TaxCategoryID); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.ManageInventory); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.StockQuantity); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.MinStockQuantity); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.LowStockActivityID); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.NotifyAdminForQuantityBelow); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.OrderMinimumQuantity); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.OrderMaximumQuantity); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.DisableBuyButton); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.Price); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.OldPrice); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.Weight); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.Length); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.Width); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.Height); sb.Append("',");
                        sb.Append("'"); sb.Append(pv.CreatedOn); sb.Append("'");
                        sb.Append(")");
                        excelHelper.ExecuteCommand(sb.ToString());
                    }
                }

Another things guys why don't u put Nepal in Country List?

Nepal?

thanks
Khem Raj