Failed to insert package into Subsonic3 with error "Must declare scalar variable ..."

Question

I ran into the problem of inserting multiple lines into a package with Subsonic3. My development environment includes:

1. Visual Studio 2010, but use .NET 3.5 2. Active Record Mode in SubSonic 3.0.0.4 3. SQL Server 2005 express 4. Northwind sample database 

I use Active Reecord mode to insert the mutiple "Product" into the "Products" table. If I insert rows one by one, either call "aProduct.Add ()" or call "Insert.Execute ()" several times (like the codes below), it works fine.

  private static Product[] CreateProducts(int count) { Product[] products = new Product[count]; for (int index = 0; index < products.Length; ++index) { products[index] = new Product { ProductName = string.Format("cheka-test-{0}", index.ToString()), Discontinued = (index % 2 == 0), }; } return products; } private static void SucceedByMultiExecuteInsert() { Product[] products = CreateProducts(2); // -------------------------------- prepare batch NorthwindDB db = new NorthwindDB(); var inserts = from prod in products select db.Insert.Into<Product>(x => x.ProductName, x => x.Discontinued).Values(prod.ProductName, prod.Discontinued); // -------------------------------- batch insert var selectAll = Product.All(); Console.WriteLine("--- before total rows = {0}", selectAll.Count().ToString()); foreach (Insert insert in inserts) insert.Execute(); Console.WriteLine("+++ after inserting {0} rows, now total rows = {1}", products.Length.ToString(), selectAll.Count().ToString()); } 

but if I use "BatchQuery", for example, the codes below,

  private static void FailByBatchInsert() { Product[] products = CreateProducts(2); // -------------------------------- prepare batch NorthwindDB db = new NorthwindDB(); BatchQuery batchquery = new BatchQuery(db.Provider, db.QueryProvider); var inserts = from prod in products select db.Insert.Into<Product>(x => x.ProductName, x => x.Discontinued).Values(prod.ProductName, prod.Discontinued); foreach (Insert insert in inserts) batchquery.Queue(insert); // -------------------------------- batch insert var selectAll = Product.All(); Console.WriteLine("--- before total rows = {0}", selectAll.Count().ToString()); batchquery.Execute(); Console.WriteLine("+++ after inserting {0} rows, now total rows = {1}", products.Length.ToString(), selectAll.Count().ToString()); } 

then it failed with the exception: " Unhandled exception: System.Data.SqlClient.SqlException: should declare the scalar variable" @ins_ProductName ". Must declare the scalar variable" @ins_ProductName ". "

Please help solve this problem. Many thanks.

Answer 1

I ran into this problem. If you look at the query that he is trying to run, you will see that he is doing something like this (this is not the actual code, but you will get the point):

 exec_sql N'insert into MyTable (SomeField) Values (@ins_SomeField)',N'@0 varchar(32)','@0=SomeValue' 

For some reason, it defines the parameters in the query using "@ins_"+FieldName , but then passes the parameters as ordinals. I have yet to define a template for why / when it does it, but I have lost enough time during this dev loop with SubSonic to try to correctly diagnose the problem.

The implemented work process will include loading the source 3.0.0.4 from github and making changes to line 179. Insert.cs.

Where is he reading

 ParameterName = _provider.ParameterPrefix + "ins_" + columnName.ToAlphaNumericOnly(), 

Change it to

 ParameterName = _provider.ParameterPrefix + Inserts.Count.ToString(), 

seemed to me a trick. I make no warranties regarding this decision for you, expressed or implied. This worked for me, but your mileage may vary.

I should also note that the same logic around "updates", as well as in Update.cs on lines 181 and 194, but I did not have such problems, I do not know yet.

Honestly, I don’t think SubSonic is ready for prime time, and that’s a shame because I really like how Rob set it up. However, this is in my product for better or worse now, so you are doing everything you can with what you have.