全部
基础类
应用实例
新技术前沿

日期赋空值显示为1900-1-1？真不爽！!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Nightyanlovecsharp 2008-03-26 10:33:27

/*用存储过程向用户通讯录信息表中插入数据*/
CREATE PROC InsertUserConInfo(
@ConTableName varchar(30),
@ConName varchar(30),
@ConAddress varchar(50),
@ConPhone varchar(20),
@ConEmail varchar(40),
@ConBirthday datetime,
@ConGroup varchar(20),
@ConMark varchar(50)
)
AS
DECLARE @str_sql varchar(500)
SET @str_sql='INSERT INTO '+ @ConTableName+'(ConName,ConAddress,ConPhone,ConEmail,ConBirthday,ConGroup,ConMark)
VALUES('''+@ConName+''','''+@ConAddress+''','''+@ConPhone+''','''+@ConEmail+''','''+convert(char(10),@ConBirthday,120)+''','''+@ConGroup+''','''+@ConMark+''')'
EXEC(@str_sql)
为什么我执行写成这样:
InsertUserConInfo 'table1','user1','','','','','',''
运行成功后再查询结果全都显示为1900-1-1.
但是我又这样写InsertUserConInfo 'table2','user2','','','',null,'',''
运行命令语法没问题，可是就不向表中插入数据。
没办法，我只有手动去企业管理器插入空的日期数据才没问题。
可是我要用程序来实现啊！怎么一个回事？解决一下！！！！！！！！！！！！！！！！！

...全文

170 点赞收藏 11

写回复

11 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

wzy_love_sly 2008-03-26

260831750,

回复赞

Nightyanlovecsharp 2008-03-26

能加你qq吗，楼上的大侠！告诉我吧！

回复赞

wzy_love_sly 2008-03-26

--null结果
INSERT INTO a(ConName,ConAddress,ConPhone,ConEmail,ConBirthday,ConGroup,ConMark)
VALUES('b','c','d','e',null,'f','g')
--'2008-01-02'结果
INSERT INTO a(ConName,ConAddress,ConPhone,ConEmail,ConBirthday,ConGroup,ConMark)
VALUES('b','c','d','e','2008-01-02','f','g')

ok了请结帖，我下了，谢谢

回复赞

wzy_love_sly 2008-03-26

CREATE PROC InsertUserConInfo( 

@ConTableName varchar(30), 

@ConName varchar(30), 

@ConAddress varchar(50), 

@ConPhone varchar(20), 

@ConEmail varchar(40), 

@ConBirthday datetime, 

@ConGroup varchar(20), 

@ConMark varchar(50) 

) 

AS

DECLARE @str_sql varchar(500)

SET @str_sql='INSERT INTO '+ @ConTableName+'(ConName,ConAddress,ConPhone,ConEmail,ConBirthday,ConGroup,ConMark)  

VALUES('''+@ConName+''','''+@ConAddress+''','''+@ConPhone+''','''+@ConEmail+''','

if @ConBirthday is null

set @str_sql=@str_sql+'null'

else 

set @str_sql=@str_sql+''''+convert(char(10),@ConBirthday,120)+''''

set @str_sql=@str_sql+','''+@ConGroup+''','''+@ConMark+''')' 

EXEC(@str_sql)

这个ok了，上面有点错误

回复赞

wzy_love_sly 2008-03-26

CREATE PROC InsertUserConInfo( 

@ConTableName varchar(30), 

@ConName varchar(30), 

@ConAddress varchar(50), 

@ConPhone varchar(20), 

@ConEmail varchar(40), 

@ConBirthday datetime, 

@ConGroup varchar(20), 

@ConMark varchar(50) 

) 

AS

DECLARE @str_sql varchar(500)

SET @str_sql='INSERT INTO '+ @ConTableName+'(ConName,ConAddress,ConPhone,ConEmail,ConBirthday,ConGroup,ConMark)  

VALUES('''+@ConName+''','''+@ConAddress+''','''+@ConPhone+''','''+@ConEmail+''','

if @ConBirthday is null

set @str_sql=@str_sql+'null'

else 

set @str_sql=@str_sql+'''convert(char(10),@ConBirthday,120)'''

set @str_sql=@str_sql+','''+@ConGroup+''','''+@ConMark+''')' 

EXEC(@str_sql)

这样看看，该行了

回复赞

wzy_love_sly 2008-03-26

哦看懂了

回复赞

Nightyanlovecsharp 2008-03-26

3楼的有问题啊！
IsNull只需要2个参数！

回复赞

Nightyanlovecsharp 2008-03-26

我想默认设置为空，也就时我插入数据为''时候让其在数据库中显示为空，也就是没内容
因为我要用到TextBox控件在程序中，要从TextBox.Text传入到表中。
究竟改怎么写

回复赞

wzy_love_sly 2008-03-26

DECLARE @str_sql varchar(500) 

SET @str_sql='INSERT INTO '+ @ConTableName+'(ConName,ConAddress,ConPhone,ConEmail,ConBirthday,ConGroup,ConMark)  

VALUES('''+@ConName+''','''+@ConAddress+''','''+@ConPhone+''','''+@ConEmail+''','''+isnull(@ConBirthday,convert(char(10),@ConBirthday,120),null)+''','''+@ConGroup+''','''+@ConMark+''')' 

EXEC(@str_sql)

回复赞