添加smalldatetime类型数据的问题
在c#2005中我新建一个数据集文件，建了一个insert方法(调用的是ins存储过程)
用如下两句添加一行
SqlDateTime dateTime = new SqlDateTime(2007, 10, 5, 11, 20, 30);
da.insert( "eeee999888 ", "ddd 'd ",dateTime.Value, "user_info ");
但提示将字符串转换为 smalldatetime 数据类型时失败
存储过程如下:
create proc ins
@ValueName varchar(20),@valuePassword varchar(20),@valueBirthday smalldatetime,@Table_info varchar(20)
as
begin
declare @sql varchar(100)
set @valuePassword = replace(@valuePassword, ' ' ' ', ' ' ' ' ' ')
set @valueName = replace(@valueName, ' ' ' ', ' ' ' ' ' ')
set @table_info = replace(@table_info, ' ' ' ', ' ')
set @sql = 'insert into ' + @table_info + '(name,password,birthday) ' + ' values( ' + ' ' ' ' + @ValueName + ' ' ' ' + ', ' + ' ' ' ' + @ValuePassword + ' ' ' ' + @valueBirthday + ') '
exec(@sql)
end
数据集文件中关于insert方法的信息如下：
<CommandText> dbo.ins </CommandText>
<Parameters>
<Parameter AllowDbNull= "True " AutogeneratedName= " " DataSourceName= " " DbType= "Int32 " Direction= "ReturnValue " ParameterName= "@RETURN_VALUE " Precision= "10 " ProviderType= "Int " Scale= "0 " Size= "4 " SourceColumnNullMapping= "False " SourceVersion= "Current ">
</Parameter>
<Parameter AllowDbNull= "True " AutogeneratedName= " " DataSourceName= " " DbType= "AnsiString " Direction= "Input " ParameterName= "@ValueName " Precision= "0 " ProviderType= "VarChar " Scale= "0 " Size= "20 " SourceColumnNullMapping= "False " SourceVersion= "Current ">
</Parameter>
<Parameter AllowDbNull= "True " AutogeneratedName= " " DataSourceName= " " DbType= "AnsiString " Direction= "Input " ParameterName= "@valuePassword " Precision= "0 " ProviderType= "VarChar " Scale= "0 " Size= "20 " SourceColumnNullMapping= "False " SourceVersion= "Current ">
</Parameter>
<Parameter AllowDbNull= "True " AutogeneratedName= " " DataSourceName= " " DbType= "DateTime " Direction= "Input " ParameterName= "@valueBirthday " Precision= "16 " ProviderType= "SmallDateTime " Scale= "0 " Size= "4 " SourceColumnNullMapping= "False " SourceVersion= "Current ">
</Parameter>
<Parameter AllowDbNull= "True " AutogeneratedName= " " DataSourceName= " " DbType= "AnsiString " Direction= "Input " ParameterName= "@Table_info " Precision= "0 " ProviderType= "VarChar " Scale= "0 " Size= "20 " SourceColumnNullMapping= "False " SourceVersion= "Current ">
</Parameter>
</Parameters>
[解决办法]
--对于smalldatetime类型的@valueBirthday需要先转换为字符型后，再关联到串中
create proc ins
@ValueName varchar(20),
@valuePassword varchar(20),
@valueBirthday smalldatetime,
@Table_info varchar(20)
as
begin
declare @sql varchar(100)
set @valuePassword = replace(@valuePassword, ' ' ' ', ' ' ' ' ' ')
set @valueName = replace(@valueName, ' ' ' ', ' ' ' ' ' ')
set @table_info = replace(@table_info, ' ' ' ', ' ')
set @sql = 'insert into ' + @table_info + '(name,password,birthday) ' + ' values( ' + ' ' ' ' + @ValueName + ' ' ' ' + ', ' + ' ' ' ' + @ValuePassword + ' ' ' ' + convert(varchar(10),@valueBirthday,120) + ') '
exec(@sql)
end
