Re: Populating date into SQL
- From: "AMDRIT" <amdrit@xxxxxxxxxxx>
- Date: Mon, 14 Aug 2006 10:32:48 -0500
SQL doesn't understand #date#, it does understand 'date'. Perhaps that is
all there is to it.
"mabond" <mabond@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F3BB63A3-043D-4181-A612-1E1B1892236A@xxxxxxxxxxxxxxxx
erm ..... not sure how that helps. I already know the field is not being
populated because I get an error
The content of MyArray(i,2) (i being 0 for the first record) reads exactly
as follows:
"13/08/2006 00:00:15" ..... so my question is how do I get my code to
write
that in such a way as it will be recognised as a valid datetime for a
datetime field in SQL
To ensure I have a date value I have tried the following
dim mydate as date
mydate = myarray(i,2)
This gives an exact value to the variable which reads
"#8/13/2006 00:00:15 AM#"
But I can't find the correct syntax to put that variable into my insert
command
insertCMD = "Insert into CallLog (Date) values calldate;"
insertCMD = "Insert into CallLog (Date) values (calldate);"
insertCMD = "Insert into CallLog (Date) values ("calldate");"
do not work
Michael
"AMDRIT" wrote:
MyArray appears to be just strings, perhaps MyArray(x,2) is not a valid
date?
You can test that the field is properly populated.
myConnection = New Data.SqlClient.SqlConnection _
("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|"
_
+ "\Beta_MyInfo.mdf;Integrated Security=True;User
Instance=True")
Dim trans As SqlClient.SqlTransaction 'Transaction object
Dim myCommand As SqlClient.SqlCommand '
Dim insertCMD As String 'SQL String,
' consider using paramaters as
they
are more
' porable over versions of RDBMS
Dim iRet As Integer 'Test that every insert
inserted
one record
Dim AllClear As Boolean 'Tests for completeness
'Set flag
AllClear = True
'Open connection
myConnection.Open()
'Create a transaction
trans = myConnection.BeginTransaction
'Loop over fields to be inserted
For i As Integer = 0 To UBound(MyArray)
Try
'Test our fields
Debug.Assert(IsDate(MyArray(i, 2)) = True, "Invalid value
specified"))
Trace.WriteLineIf(IsDate(MyArray(i, 2)) = True, "Invalid value
specified"))
'Populate sql
insertCMD = String.Format("Insert into MyLog (Date) values
('{0}');", MyArray(i, 2))
'Initialize our command object
myCommand = New SqlClient.SqlCommand(insertCMD, myConnection,
trans)
'Execute our SQL
iRet = myCommand.ExecuteNonQuery()
myCommand.Dispose()
'Test results
Debug.Assert(iRet = 1, String.Format("Expected one record to be
inserted, instead {0} were inserted.", iRet))
Trace.WriteLineIf(iRet = 1, String.Format("Expected one record to
be
inserted, instead {0} were inserted.", iRet))
Catch ex As SqlClient.SqlException
AllClear = False
'Clean up
myCommand.Dispose()
Dim cmd As SqlClient.SqlCommand
cmd = New SqlClient.SqlCommand()
cmd.Connection = myConnection : cmd.Transaction = trans :
cmd.Transaction.Rollback()
cmd.Dispose()
Exit For
Catch ex As Exception
'Something more bad happened here.
AllClear = False
End Try
Next
If AllClear Then
Dim cmd As SqlClient.SqlCommand
cmd = New SqlClient.SqlCommand()
cmd.Connection = myConnection : cmd.Transaction = trans :
cmd.Transaction.Commit()
cmd.Dispose()
End If
myConnection.Close()
myConnection.Dispose()
"mabond" <mabond@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3AF6A94D-8E41-4810-9E6F-FF136F4E3F43@xxxxxxxxxxxxxxxx
Hi
Having trouble filling a datetime filed in SQL table with a value from
an
array.
The original source is a comma-delimited text file where the date and
time
values are in two columns. I require to combine the two colums into one
datetime field in the SQL table
Here is what I am doing
FileOpen(1, my_file_to_validate, OpenMode.Binary)
MyArray = New String(My_RecordCount - 1, 38) {}
For row = 0 To My_RecordCount - 1
myrecord = LineInput(1)
Dim x = Split(myrecord, ",")
Dim MyItemDate as Date
MyItemDate = New Date(Microsoft.VisualBasic.Left(x(3),
4),
_
Microsoft.VisualBasic.Mid(x(3), 5, 2), _
Microsoft.VisualBasic.Right(x(3), 2), _
Microsoft.VisualBasic.Left(x(2), 2), _
Microsoft.VisualBasic.Mid(x(2), 3, 2), _
Microsoft.VisualBasic.Mid(x(2), 5, 2))
MyArray_Array(row, 2) = CallDate
The above opens the text file and populates the array
...and when that is completed the following is supposed to take the
date
value from array and insert it into the SQL table .... BUT ..... I
receive
errors at the "myCommand.ExecuteNonQuery()" line indiacting that the
value
cannot be accepted because it is not the right format and cannot be
converted.
For i = 0 To UBound(MyArray)
myConnection = New SqlConnection _
("Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|" _
+ "\Beta_MyInfo.mdf;Integrated Security=True;User
Instance=True")
myConnection.Open()
insertCMD = "Insert into MyLog (Date) values ('" &
MyArray(i, 2) & "');"
myCommand = New SqlCommand(insertCMD, myConnection)
myCommand.ExecuteNonQuery()
myConnection.Close()
Next
Any ideas where I might be going wrong
Thanks
Michael Bond
.
- Follow-Ups:
- Re: Populating date into SQL
- From: mabond
- Re: Populating date into SQL
- References:
- Re: Populating date into SQL
- From: AMDRIT
- Re: Populating date into SQL
- From: mabond
- Re: Populating date into SQL
- Prev by Date: Re: Serial Port
- Next by Date: Re: Launching Word 2003 in prog creates exception
- Previous by thread: Re: Populating date into SQL
- Next by thread: Re: Populating date into SQL
- Index(es):
Relevant Pages
|
