这是我写的存储过程:
ALTER PROCEDURE dbo.SearchKyxm
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
@ddl_ziduan varchar(50),
@searchKey varchar(50)
AS
/* SET NOCOUNT ON
declare
create table #tb
*/
if(@ddl_ziduan='kyxmmc')
begin
select * from kyxm where kyxmmc like '%'+@searchKey+'%'
end
if(@ddl_ziduan='cbdwmc')
begin
select * from kyxm where cbdwmc like '%'+@searchKey+'%'
end
if(@ddl_ziduan='pzh')
begin
select * from kyxm where pzh like '%'+@searchKey+'%'
end
if(@ddl_ziduan='xmzt')
begin
select * from kyxm where xmzt like '%'+@searchKey+'%'
end
if(@ddl_ziduan='qsny')
begin
select * from kyxm where qsny like '%'+cast(@searchKey as datetime)+'%'
end
if(@ddl_ziduan='zzny')
begin
select * from kyxm where zzny like '%'+cast(@searchKey as datetime)+'%'
end
RETURN存储过程能够保存,但是在页面里运行时
却提示:
从字符串向 datetime 转换时失败。
说明: 执行当前 Web 请求期间,出现未处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.Data.SqlClient.SqlException: 从字符串向 datetime 转换时失败。源错误:
行 202: db.AddInParameter(cmd, "@ddl_ziduan", DbType.String, ddl_ziduan);
行 203: db.AddInParameter(cmd, "@searchKey", DbType.String, searchKey);
行 204: return db.ExecuteDataSet(cmd);
行 205: }
行 206:
源文件: F:\science\DataBusiness\Kyxm.cs 行: 204 堆栈跟踪:
[SqlException (0x80131904): 从字符串向 datetime 转换时失败。]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2300
System.Data.SqlClient.SqlDataReader.HasMoreRows() +154
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +211
System.Data.SqlClient.SqlDataReader.Read() +9
System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) +174
System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +175
System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +195
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +199
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +82
Microsoft.Practices.EnterpriseLibrary.Data.Database.DoLoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames) +711
Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames) +97
Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String tableName) +68
Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteDataSet(DbCommand command) +96
DataBusiness.Kyxm.Search(String ddl_ziduan, String searchKey) in F:\science\DataBusiness\Kyxm.cs:204
control_Search.Button1_Click(Object sender, EventArgs e) in f:\science\scienceWeb\control\Search.ascx.cs:43
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +75
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +97
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4919
ALTER PROCEDURE dbo.SearchKyxm
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
@ddl_ziduan varchar(50),
@searchKey varchar(50)
AS
/* SET NOCOUNT ON
declare
create table #tb
*/
if(@ddl_ziduan='kyxmmc')
begin
select * from kyxm where kyxmmc like '%'+@searchKey+'%'
end
if(@ddl_ziduan='cbdwmc')
begin
select * from kyxm where cbdwmc like '%'+@searchKey+'%'
end
if(@ddl_ziduan='pzh')
begin
select * from kyxm where pzh like '%'+@searchKey+'%'
end
if(@ddl_ziduan='xmzt')
begin
select * from kyxm where xmzt like '%'+@searchKey+'%'
end
if(@ddl_ziduan='qsny')
begin
select * from kyxm where qsny like '%'+cast(@searchKey as datetime)+'%'
end
if(@ddl_ziduan='zzny')
begin
select * from kyxm where zzny like '%'+cast(@searchKey as datetime)+'%'
end
RETURN存储过程能够保存,但是在页面里运行时
却提示:
从字符串向 datetime 转换时失败。
说明: 执行当前 Web 请求期间,出现未处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.Data.SqlClient.SqlException: 从字符串向 datetime 转换时失败。源错误:
行 202: db.AddInParameter(cmd, "@ddl_ziduan", DbType.String, ddl_ziduan);
行 203: db.AddInParameter(cmd, "@searchKey", DbType.String, searchKey);
行 204: return db.ExecuteDataSet(cmd);
行 205: }
行 206:
源文件: F:\science\DataBusiness\Kyxm.cs 行: 204 堆栈跟踪:
[SqlException (0x80131904): 从字符串向 datetime 转换时失败。]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2300
System.Data.SqlClient.SqlDataReader.HasMoreRows() +154
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +211
System.Data.SqlClient.SqlDataReader.Read() +9
System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) +174
System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +175
System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +195
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +199
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +82
Microsoft.Practices.EnterpriseLibrary.Data.Database.DoLoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames) +711
Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames) +97
Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String tableName) +68
Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteDataSet(DbCommand command) +96
DataBusiness.Kyxm.Search(String ddl_ziduan, String searchKey) in F:\science\DataBusiness\Kyxm.cs:204
control_Search.Button1_Click(Object sender, EventArgs e) in f:\science\scienceWeb\control\Search.ascx.cs:43
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +75
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +97
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4919
解决的办法是在掉用储存过程前先判断参数类型,如果为DateTime类型,则自己先转换。
刚才看到一份资料
说是varchar to datetime 之间 是隐式转换,也就是不需要显示转换但是 datetime to varchar 可以用convert(data_type,exrpession,style)来转换
可以通过style的设置设置显示的格式