Dim vdate as string
定義一個變量
數據庫表DGMOCPPT中定義PT014 datetime (排配日期)
vdate = Right(Excel.worksheets(j).cells(3, 1).Value, Len(Excel.worksheets(j).cells(3, 1).Value) - 3)
現在
vdate='2009/10/27白' '日期+班別cnn.Execute "delete from DGMOCPPT where convert(char(10),PT014,121)= " & CStr(Format(Left(vdate, 10), "yyyy-mm-dd")) & " and PT018='" & strUserName & "' and PT017='" & Right(vdate, 1) & "'"
執行這段的時候報錯:從字元字串轉換成datetime 時,轉換失敗!
請問該怎樣改?
定義一個變量
數據庫表DGMOCPPT中定義PT014 datetime (排配日期)
vdate = Right(Excel.worksheets(j).cells(3, 1).Value, Len(Excel.worksheets(j).cells(3, 1).Value) - 3)
現在
vdate='2009/10/27白' '日期+班別cnn.Execute "delete from DGMOCPPT where convert(char(10),PT014,121)= " & CStr(Format(Left(vdate, 10), "yyyy-mm-dd")) & " and PT018='" & strUserName & "' and PT017='" & Right(vdate, 1) & "'"
執行這段的時候報錯:從字元字串轉換成datetime 時,轉換失敗!
請問該怎樣改?
cnn.Execute "delete from DGMOCPPT where convert(char(10),PT014,121)= " & CStr(Format(Left(vdate, 10), "yyyy-mm-dd")) & " and PT018='" & strUserName & "' and PT017='" & Right(vdate, 1) & "'"
--打个断点,把那个拼好的sql语句 ,复制出来!
在看看,错误在哪?
cnn.Execute "delete from DGMOCPPT where convert(char(10),PT014,121)= '" & CStr(Format(Left(vdate, 10), "yyyy-mm-dd")) & "'
and PT018='" & strUserName & "' and PT017='" & Right(vdate, 1) & "'"
創建一個表DGMOCPPT
create table DGMOCPPT(PT001 varchar(10) , PT002 char(20) ,PT003 varchar(50) ,PT004 varchar(30),PT005 varchar(50),PT006 nvarchar(50),PT007 float,PT008 varchar(20),PT009 char(4),PT010 float ,PT011 float,PT012 nvarchar(20),PT013 nvarchar(100),PT014 datetime,PT015 nvarchar(20),PT016 nchar(10),PT017 datetime ,PT018 nvarchar(50) )
在數據庫中單獨增加一筆數據,
報表代碼:
rstTemp = "insert into DGMOCPPT values('" & PT001 & "','" & PT002 & " ','" & PT003 & " ','" & PT004 & " ','" & PT005 & " ','" & PT006 & " ','" & PT007 & " ','" & PT008 & " ','" & PT009 & " ','" & PT010 & " ',"
rstTemp = rstTemp + "'" & PT011 & " ','" & PT012 & " ','" & PT013 & " ',"
rstTemp = rstTemp + "'" & Left(vdate, 10) & "','" & Right(vdate, 1) & "',
'" & strUserName & "' ,'" & mdate & "','" & PI019 & "' ) "也就是下面的
insert into DGMOCPPT values('SHZ-F','757629 ','420-000542 ','DUKE ','711271 ',
'1-4 ','2500 ','配完半成品 ','NO ','2 ',' ',
'10/27-2000 10/29-1481 ',' ','2009/10/27','白','系統管理員' ,'2009/11/02','255' )
出現
訊息 8152,層級 16,狀態 4,行 2
字串或二進位資料會被截斷。
陳述式已經結束。左看右看也沒發現那個有問題!!
PT012 nvarchar(20)定義短了。換成50就可以了