ExecuteScalarSql() for SqlServer Compact 3.5 throwing error on multiple queries

Topics: Data Access Application Block
Jul 10, 2008 at 10:31 PM
I'm using enterprise library 4.0 - May 2008. Using SQL Server Compact 3.5

Does the ExecuteScalarSql(string Sql, DbParamter[]) not support multiple queries?

Example:
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.SqlCe;
using Microsoft.Practices.EnterpriseLibrary.ExceptionHandling;
private SqlCeDatabase _dbAccessor = new SqlCeDatabase(Global.LocalDbConnectionString);
.......
protected internal object Create(string QueryFileName, params DbParameter[] Parameters)
{
....
this._dbAccessor.ExecuteScalarSql("insert into Customers([Name])values(@Name);SELECT @@IDENTITY as NewID", Parameters);
}

Keeps throwing this error:
There was an error parsing the query. [ Token line number = 1,Token line offset = 44,Token in error = SELECT ]


As soon as I drop the "SELECT @@IDENTITY as NewID" it works. Is this a limitation? Its not documented in the help for this enterprise library (or I could not find it). I can run this query manually in the query window in for database inside VS.NET 2008, it just won't work with the enterprise library though.

All my connection strings are correct and I can get to the DB fine, its just when I try to run multiple queries. Any ideas?

Chris
Jul 14, 2008 at 6:32 PM
Hi Chris,

This is not a limitation of EntLib; support multiple statements in a single command is not available in SqlServerCE. You can find additional information in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2975568&SiteID=1

This repro does not use EntLib at all but it does throw the same exception.

using System;

using System.Data.Common;

using System.Data.SqlClient;

using System.Data.SqlServerCe;

 

namespace ConsoleApplication3

{

    class Program

    {

        static void Main(string[] args)

        {

            using (DbConnection connection = SqlCeProviderFactory.Instance.CreateConnection())

            {

                connection.ConnectionString = @"Data Source=.\TestDb.sdf";

                connection.Open();

 

                DbCommand command = connection.CreateCommand();

                command.CommandText = @"INSERT INTO Test (name) VALUES ('Test'); select @@identity";

                command.CommandType = System.Data.CommandType.Text;

 

                object result = command.ExecuteScalar();

 

                Console.WriteLine("Return value '{0}'", result);

            }

        }

    }

}


Regards,
Fernando


smittyst0ic wrote:
I'm using enterprise library 4.0 - May 2008. Using SQL Server Compact 3.5

Does the ExecuteScalarSql(string Sql, DbParamter[]) not support multiple queries?

Example:
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.SqlCe;
using Microsoft.Practices.EnterpriseLibrary.ExceptionHandling;
private SqlCeDatabase _dbAccessor = new SqlCeDatabase(Global.LocalDbConnectionString);
.......
protected internal object Create(string QueryFileName, params DbParameter[] Parameters)
{
....
this._dbAccessor.ExecuteScalarSql("insert into Customers([Name])values(@Name);SELECT @@IDENTITY as NewID", Parameters);
}

Keeps throwing this error:
There was an error parsing the query. [ Token line number = 1,Token line offset = 44,Token in error = SELECT ]


As soon as I drop the "SELECT @@IDENTITY as NewID" it works. Is this a limitation? Its not documented in the help for this enterprise library (or I could not find it). I can run this query manually in the query window in for database inside VS.NET 2008, it just won't work with the enterprise library though.

All my connection strings are correct and I can get to the DB fine, its just when I try to run multiple queries. Any ideas?

Chris