首页 新闻 搜索 专区 学院

求高手解决SQL语句问题

0
悬赏园豆:15 [已解决问题] 解决于 2012-05-20 10:33

错误消息:用户代码未处理SqlCeException

数据转换失败。 [ OLE DB status value (if known) = 2 ]

代码如下:

public static List<Emp_Salary> Emp_Salary_Select(string SalaryName, int State)
       {
           List<Emp_Salary> list = new List<Emp_Salary>();

           using (SqlCeConnection con = new SqlCeConnection(Data_Share.LocalConStr))
           {
               con.Open();

               SqlCeCommand com = con.CreateCommand();
               com.CommandType = CommandType.Text;
               com.Connection = con;

               string SQL = @"SELECT [ESID]
      ,[ESName]
      ,[ESValue]
      ,[ESunitPrice]
      ,[ESDesc]
      ,[ESetUser]
      ,[ESetTime]
      ,[ESState]
      ,[EEndUser]
      ,[EEndTIme]
  FROM [TBL_EmployeeSalary]
  WHERE 1=1";
               if (!string.IsNullOrEmpty(SalaryName))
               {
                   SQL += " and [ESName] like ('%'+@ESName+'%')";
                   com.Parameters.Add("@ESName", DbType.String);
                   com.Parameters["@ESName"].Value = SalaryName;
               }

               if (State >= 0)
               {
                   SQL += " and [ESState]=@ESState";
                   com.Parameters.Add("@ESState", DbType.Boolean);
                   com.Parameters["@ESState"].Value = State == 1;
               }

               com.CommandText = SQL;
               using (IDataReader ireader = com.ExecuteReader())
               {
                   Guid gd_tp = default(Guid);
                   double db_tp = 0D;
                   DateTime dt_tp = default(DateTime);
                   bool bl_tp = false;
                   while (ireader.Read())
                   {
                       Emp_Salary salary = new Emp_Salary();
                       salary.ESID = Guid.TryParse(ireader["ESID"].ToString(),out gd_tp) ? gd_tp : default(Guid);
                       salary.ESName = ireader["ESName"].ToString();
                       salary.ESValue = double.TryParse(ireader["ESValue"].ToString(), out db_tp) ? db_tp : 0D;
                       salary.ESUnitPrice = ireader["ESunitPrice"].ToString();
                       salary.ESDesc = ireader["ESDesc"].ToString();
                       salary.ESetUser = Data_SysUser.Sys_User_SelectSingle(Guid.TryParse(ireader["ESetUser"].ToString(), out gd_tp) ? gd_tp : default(Guid));
                       salary.ESetTime = DateTime.TryParse(ireader["ESetTime"].ToString(), out dt_tp) ? dt_tp : default(DateTime);
                       salary.ESState = bool.TryParse(ireader["ESState"].ToString(), out bl_tp) ? bl_tp : false;
                       salary.EEndUser = Data_SysUser.Sys_User_SelectSingle(Guid.TryParse(ireader["EEndUser"].ToString(), out gd_tp) ? gd_tp : default(Guid));
                       salary.EEndTime = DateTime.TryParse(ireader["EEndTIme"].ToString(), out dt_tp) ? dt_tp : default(DateTime);
                       list.Add(salary);
                   }
                   ireader.Close();
               }



               con.Close();
            }

           return list;
       }


SQL脚本

--USE [Sync_ProjectManagementDB]
--GO
/****** 对象:  Table [dbo].[TBL_EmployeeSalary]    脚本日期: 05/19/2012 17:00:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
--newsequentialid()  SQL 2005 sp2中才有该函数
CREATE TABLE [dbo].[TBL_EmployeeSalary](
    [ESID] [uniqueidentifier] NOT NULL DEFAULT (newsequentialid()),
    [ESName] [varchar](50) NULL,
    [ESValue] [money] NULL,
    [ESunitPrice] [varchar](20) NULL,
    [ESDesc] [varchar](200) NULL,
    [ESetUser] [uniqueidentifier] NULL,
    [ESetTime] [datetime] NULL,
    [ESState] [bit] NULL,
    [EEndUser] [uniqueidentifier] NULL,
    [EEndTIme] [datetime] NULL,
    [LastEditDate] [datetime] NULL CONSTRAINT [DF_TBL_EmployeeSalary_LastEditDate]  DEFAULT (getutcdate()),
    [CreationDate] [datetime] NULL CONSTRAINT [DF_TBL_EmployeeSalary_CreationDate]  DEFAULT (getutcdate()),
PRIMARY KEY CLUSTERED 
(
    [ESID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
--SET ANSI_PADDING OFF
--GO
--ALTER TABLE [dbo].[TBL_EmployeeSalary]  WITH CHECK ADD  CONSTRAINT [FK_TBL_EmployeeSalary_TBL_SysUser_EEndUSer] FOREIGN KEY([EEndUser])
--REFERENCES [dbo].[TBL_SysUser] ([UNID])
--GO
--ALTER TABLE [dbo].[TBL_EmployeeSalary] CHECK CONSTRAINT [FK_TBL_EmployeeSalary_TBL_SysUser_EEndUSer]
--GO
--ALTER TABLE [dbo].[TBL_EmployeeSalary]  WITH CHECK ADD  CONSTRAINT [FK_TBL_EmployeeSalary_TBL_SysUser_ESetUser] FOREIGN KEY([ESetUser])
--REFERENCES [dbo].[TBL_SysUser] ([UNID])
--GO
--ALTER TABLE [dbo].[TBL_EmployeeSalary] CHECK CONSTRAINT [FK_TBL_EmployeeSalary_TBL_SysUser_ESetUser]

在CE中出问题的脚本:

SELECT [ESID]
      ,[ESName]
      ,[ESValue]
      ,[ESunitPrice]
      ,[ESDesc]
      ,[ESetUser]
      ,[ESetTime]
      ,[ESState]
      ,[EEndUser]
      ,[EEndTIme]
  FROM [TBL_EmployeeSalary]
  WHERE 1=1 and [ESName] like ('%'+@ESName+'%')
IceS的主页 IceS | 初学一级 | 园豆:197
提问于:2012-05-19 17:06
< >
分享
最佳答案
0

脚本在数据库查询分析器里能运行成功吗?

收获园豆:15
artwl | 专家六级 |园豆:16526 | 2012-05-19 18:22

在SQL管理器中可以运行成功,在CE中用参数化模糊查询报错。不用参数直接拼接SQL不报错(不安全)。

IceS | 园豆:197 (初学一级) | 2012-05-20 08:17

@IceS: 

SQL += " and [ESName] like ('%'+@ESName+'%')";

换为:

SQL += " and [ESName] like ('%@ESName%')";
artwl | 园豆:16526 (专家六级) | 2012-05-20 08:44

@artwl: 那个不行,这样就查询 @ESName 字符串了.

IceS | 园豆:197 (初学一级) | 2012-05-20 10:34

@IceS: 上面的是有问题,你可以改成这样:

SQL += " and [ESName] like @ESName";
com.Parameters.Add("@ESName", DbType.String);
com.Parameters["@ESName"].Value = "%"+SalaryName+"%";
artwl | 园豆:16526 (专家六级) | 2012-05-20 10:39

@artwl: OK,问题解决,谢谢

IceS | 园豆:197 (初学一级) | 2012-05-20 15:28
其他回答(1)
0

因为sqlserverce不支持char函数,暂时的解决办法是字符拼接。拼接前替换检查单引号

IceS | 园豆:197 (初学一级) | 2012-05-20 10:33
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册