Prev: Mapi email problem (and Apology)
Next: MAPI Issue
From: albertleng on 6 Apr 2010 22:30
Hi.

I'm using ADO connection and recordset to insert records from Table1
to table2.
The database is SQL Server 2005 and the connection is established
before the following the code snippet with
"csvSQL". The code works without any error but the result is not what
i want.
The issue is that when aTIME1 is "" and is inserted to table2, TIME1
of Table2 becomes "1901-1-1 00:00:00".
FYI, TIME1 is of type datetime and allow null. What i expect is when
aTIME1 is "" and inserted to Table2, TIME1 should be null.
Please help. Thanks.

Set csvRecord = New ADODB.Recordset
csvRecord.CursorLocation = adUseClient
csvRecord.CursorType = adOpenDynamic
csvRecord.LockType = adLockOptimistic

csvRecord.Open "SELECT * FROM TABLE1 ORDER BY PCVUE_TIME2 ASC",
csvSQL
totalRecord = csvRecord.RecordCount

If Not (csvRecord.EOF) And Not (csvRecord.BOF) Then
csvRecord.MoveLast
Do Until csvRecord.EOF
If IsNull(csvRecord("TIME1")) Then
aTIME1 = ""
Else
aTIME1 = csvRecord("TIME1")
End If
If IsNull(csvRecord("TIME2")) Then
aTIME2 = ""
Else
aTIME2 = csvRecord("TIME2")
End If
If IsNull(csvRecord("DATA1")) Then
aDATA1 = ""
Else
aDATA1 = csvRecord("DATA1")
End If
If IsNull(csvRecord("DATA2")) Then
aDATA2 = ""
Else
aDATA2 = csvRecord("DATA2") '*
End If
If IsNull(csvRecord("DATA3")) Then
aDATA3 = ""
Else
aDATA3 = csvRecord("DATA3")
End If
If IsNull(csvRecord("DATA4")) Then
aDATA4 = ""
Else
aDATA4 = csvRecord("DATA4")
End If
If IsNull(csvRecord("DATA5")) Then
aDATA5 = ""
Else
aDATA5 = csvRecord("DATA5")
End If
If IsNull(csvRecord("DATA6")) Then
aDATA6 = ""
Else
aDATA6 = csvRecord("DATA6")
End If
If IsNull(csvRecord("TIME3")) Then
aTIME3 = ""
Else
aTIME3 = csvRecord("TIME3")
End If
If IsNull(csvRecord("DATA7")) Then
aDATA7 = ""
Else
aDATA7 = csvRecord("DATA7")
End If
If IsNull(csvRecord("TIME4")) Then
aTIME4 = ""
Else
aTIME4 = csvRecord("TIME4")
End If


If InStr(1, aDATA2, "*") > 0 Then
pointerFound = True
End If


If pointerFound And dataReady Then
sSQL = "INSERT INTO
TABLE2(TIME1,TIME2,DATA1,DATA2,DATA3," & _
"DATA4,DATA5,DATA6,TIME3,DATA7,PCVUE_TIME3) VALUES
('" & _
aTIME1 & "','" & aTIME2 & "','" & aDATA1 & "','" &
_
aDATA2 & "','" & aDATA3 & "','" & aDATA4 & "','" &
_
aDATA5 & "','" & aDATA6 & "','" & aTIME3 & "','" &
aDATA7 & "','" & aTIME4 & "')"
csvSQL.Execute sSQL
End If

If pointerFound Then
dataReady = True
If totalRecord = csvRecord.AbsolutePosition Then
If InStr(aDATA2, "*") <= 0 Then
csvRecord("DATA2") = aDATA2 & "*"
csvRecord.Update
End If
End If
csvRecord.MoveNext
Else
csvRecord.MovePrevious
End If
Loop
End If
From: Helmut Meukel on 7 Apr 2010 03:33
"albertleng" <albertleng(a)gmail.com> schrieb im Newsbeitrag
news:88507fa7-a896-4c89-bc7d-a312a4da66d9(a)10g2000yqq.googlegroups.com...
> Hi.
>
> I'm using ADO connection and recordset to insert records from Table1
> to table2.
> The database is SQL Server 2005 and the connection is established
> before the following the code snippet with
> "csvSQL". The code works without any error but the result is not what
> i want.
> The issue is that when aTIME1 is "" and is inserted to table2, TIME1
> of Table2 becomes "1901-1-1 00:00:00".
> FYI, TIME1 is of type datetime and allow null. What i expect is when
> aTIME1 is "" and inserted to Table2, TIME1 should be null.
> Please help. Thanks.
>


Why on earth do you expect TIME1 to be null?
How is aTIME1 declared? (data type?).

If you would read in Online Help about VBs Date data type and its
internal repesentation and about the corresponding data type of
SQL Server then you wouldn't ask this question.

Helmut.

From: Patrice on 7 Apr 2010 04:40
Hello,

> The issue is that when aTIME1 is "" and is inserted to table2, TIME1
> of Table2 becomes "1901-1-1 00:00:00".

This is how SQL Server converts an empty string :

SELECT CAST('' AS SMALLDATETIME) gives 1901-01-01...

> FYI, TIME1 is of type datetime and allow null. What i expect is when
> aTIME1 is "" and inserted to Table2, TIME1 should be null.

Then use NULL rather than an empty string when the value in your CSV file is
an empty string.

--
Patrice



From: albertleng on 7 Apr 2010 08:27
On Apr 7, 3:33 pm, "Helmut Meukel" <NoS...(a)NoProvider.de> wrote:
> "albertleng" <albertl...(a)gmail.com> schrieb im Newsbeitragnews:88507fa7-a896-4c89-bc7d-a312a4da66d9(a)10g2000yqq.googlegroups.com...
>
> > Hi.
>
> > I'm using ADO connection and recordset to insert records from Table1
> > to table2.
> > The database is SQL Server 2005 and the connection is established
> > before the following the code snippet with
> > "csvSQL". The code works without any error but the result is not what
> > i want.
> > The issue is that when aTIME1 is "" and is inserted to table2, TIME1
> > of Table2 becomes "1901-1-1 00:00:00".
> > FYI, TIME1 is of type datetime and allow null. What i expect is when
> > aTIME1 is "" and inserted to Table2, TIME1 should be null.
> > Please help. Thanks.
>
> Why on earth do you expect TIME1 to be null?
> How is aTIME1 declared? (data type?).
>
> If you would read in Online Help about VBs Date data type and its
> internal repesentation and about the corresponding data type of
> SQL Server then you wouldn't ask this question.
>
> Helmut.

Hi. In this project, TIME1 can be null depending on whether there's a
matching time on the equipment to that event.
aTIME1 is declared as a String.
 | 
Pages: 1
Prev: Mapi email problem (and Apology)
Next: MAPI Issue