Populating date into SQL

[mabond]

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_vali date, OpenMode.Binary )

MyArray = New String(My_Recor dCount - 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.Lef t(x(3), 4), _
Microsoft.Visua lBasic.Mid(x(3) , 5, 2), _
Microsoft.Visua lBasic.Right(x( 3), 2), _
Microsoft.Visua lBasic.Left(x(2 ), 2), _
Microsoft.Visua lBasic.Mid(x(2) , 3, 2), _
Microsoft.Visua lBasic.Mid(x(2) , 5, 2))
MyArray_Array(r ow, 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.Exec uteNonQuery()" 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=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|" _
+ "\Beta_MyInfo.m df;Integrated Security=True;U ser
Instance=True")
myConnection.Op en()
insertCMD = "Insert into MyLog (Date) values ('" &
MyArray(i, 2) & "');"
myCommand = New SqlCommand(inse rtCMD, myConnection)
myCommand.Execu teNonQuery()
myConnection.Cl ose()

Next

Any ideas where I might be going wrong

Thanks

Michael Bond

[AMDRIT]

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=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|" _
+ "\Beta_MyInfo.m df;Integrated Security=True;U ser
Instance=True")

Dim trans As SqlClient.SqlTr ansaction 'Transaction object
Dim myCommand As SqlClient.SqlCo mmand '
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.Op en()

'Create a transaction
trans = myConnection.Be ginTransaction

'Loop over fields to be inserted
For i As Integer = 0 To UBound(MyArray)

Try

'Test our fields
Debug.Assert(Is Date(MyArray(i, 2)) = True, "Invalid value
specified"))
Trace.WriteLine If(IsDate(MyArr ay(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.SqlCo mmand(insertCMD , myConnection, trans)

'Execute our SQL
iRet = myCommand.Execu teNonQuery()

myCommand.Dispo se()

'Test results
Debug.Assert(iR et = 1, String.Format(" Expected one record to be
inserted, instead {0} were inserted.", iRet))
Trace.WriteLine If(iRet = 1, String.Format(" Expected one record to be
inserted, instead {0} were inserted.", iRet))

Catch ex As SqlClient.SqlEx ception

AllClear = False

'Clean up
myCommand.Dispo se()

Dim cmd As SqlClient.SqlCo mmand

cmd = New SqlClient.SqlCo mmand()
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.SqlCo mmand

cmd = New SqlClient.SqlCo mmand()
cmd.Connection = myConnection : cmd.Transaction = trans :
cmd.Transaction .Commit()
cmd.Dispose()

End If

myConnection.Cl ose()
myConnection.Di spose()

"mabond" <ma****@discuss ions.microsoft. comwrote in message
news:3A******** *************** ***********@mic rosoft.com...

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_vali date, OpenMode.Binary )

MyArray = New String(My_Recor dCount - 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.Lef t(x(3), 4),
_
Microsoft.Visua lBasic.Mid(x(3) , 5, 2), _
Microsoft.Visua lBasic.Right(x( 3), 2), _
Microsoft.Visua lBasic.Left(x(2 ), 2), _
Microsoft.Visua lBasic.Mid(x(2) , 3, 2), _
Microsoft.Visua lBasic.Mid(x(2) , 5, 2))
MyArray_Array(r ow, 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.Exec uteNonQuery()" 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=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|" _
+ "\Beta_MyInfo.m df;Integrated Security=True;U ser
Instance=True")
myConnection.Op en()
insertCMD = "Insert into MyLog (Date) values ('" &
MyArray(i, 2) & "');"
myCommand = New SqlCommand(inse rtCMD, myConnection)
myCommand.Execu teNonQuery()
myConnection.Cl ose()

Next

Any ideas where I might be going wrong

Thanks

Michael Bond

[mabond]

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=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|" _
+ "\Beta_MyInfo.m df;Integrated Security=True;U ser
Instance=True")

Dim trans As SqlClient.SqlTr ansaction 'Transaction object
Dim myCommand As SqlClient.SqlCo mmand '
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.Op en()

'Create a transaction
trans = myConnection.Be ginTransaction

'Loop over fields to be inserted
For i As Integer = 0 To UBound(MyArray)

Try

'Test our fields
Debug.Assert(Is Date(MyArray(i, 2)) = True, "Invalid value
specified"))
Trace.WriteLine If(IsDate(MyArr ay(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.SqlCo mmand(insertCMD , myConnection, trans)

'Execute our SQL
iRet = myCommand.Execu teNonQuery()

myCommand.Dispo se()

'Test results
Debug.Assert(iR et = 1, String.Format(" Expected one record to be
inserted, instead {0} were inserted.", iRet))
Trace.WriteLine If(iRet = 1, String.Format(" Expected one record to be
inserted, instead {0} were inserted.", iRet))

Catch ex As SqlClient.SqlEx ception

AllClear = False

'Clean up
myCommand.Dispo se()

Dim cmd As SqlClient.SqlCo mmand

cmd = New SqlClient.SqlCo mmand()
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.SqlCo mmand

cmd = New SqlClient.SqlCo mmand()
cmd.Connection = myConnection : cmd.Transaction = trans :
cmd.Transaction .Commit()
cmd.Dispose()

End If

myConnection.Cl ose()
myConnection.Di spose()

"mabond" <ma****@discuss ions.microsoft. comwrote in message
news:3A******** *************** ***********@mic rosoft.com...

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_vali date, OpenMode.Binary )

MyArray = New String(My_Recor dCount - 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.Lef t(x(3), 4),
_
Microsoft.Visua lBasic.Mid(x(3) , 5, 2), _
Microsoft.Visua lBasic.Right(x( 3), 2), _
Microsoft.Visua lBasic.Left(x(2 ), 2), _
Microsoft.Visua lBasic.Mid(x(2) , 3, 2), _
Microsoft.Visua lBasic.Mid(x(2) , 5, 2))
MyArray_Array(r ow, 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.Exec uteNonQuery()" 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=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|" _
+ "\Beta_MyInfo.m df;Integrated Security=True;U ser
Instance=True")
myConnection.Op en()
insertCMD = "Insert into MyLog (Date) values ('" &
MyArray(i, 2) & "');"
myCommand = New SqlCommand(inse rtCMD, myConnection)
myCommand.Execu teNonQuery()
myConnection.Cl ose()

Next

Any ideas where I might be going wrong

Thanks

Michael Bond

[AMDRIT]

SQL doesn't understand #date#, it does understand 'date'. Perhaps that is
all there is to it.


"mabond" <ma****@discuss ions.microsoft. comwrote in message
news:F3******** *************** ***********@mic rosoft.com...

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=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|"
_
+ "\Beta_MyInfo.m df;Integrated Security=True;U ser
Instance=True" )

Dim trans As SqlClient.SqlTr ansaction 'Transaction object
Dim myCommand As SqlClient.SqlCo mmand '
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.Op en()

'Create a transaction
trans = myConnection.Be ginTransaction

'Loop over fields to be inserted
For i As Integer = 0 To UBound(MyArray)

Try

'Test our fields
Debug.Assert(Is Date(MyArray(i, 2)) = True, "Invalid value
specified"))
Trace.WriteLine If(IsDate(MyArr ay(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.SqlCo mmand(insertCMD , myConnection,
trans)

'Execute our SQL
iRet = myCommand.Execu teNonQuery()

myCommand.Dispo se()

'Test results
Debug.Assert(iR et = 1, String.Format(" Expected one record to be
inserted, instead {0} were inserted.", iRet))
Trace.WriteLine If(iRet = 1, String.Format(" Expected one record to
be
inserted, instead {0} were inserted.", iRet))

Catch ex As SqlClient.SqlEx ception

AllClear = False

'Clean up
myCommand.Dispo se()

Dim cmd As SqlClient.SqlCo mmand

cmd = New SqlClient.SqlCo mmand()
cmd.Connection = myConnection : cmd.Transaction = trans :
cmd.Transactio n.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.SqlCo mmand

cmd = New SqlClient.SqlCo mmand()
cmd.Connection = myConnection : cmd.Transaction = trans :
cmd.Transactio n.Commit()
cmd.Dispose()

End If

myConnection.Cl ose()
myConnection.Di spose()

"mabond" <ma****@discuss ions.microsoft. comwrote in message
news:3A******* *************** ************@mi crosoft.com...

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_vali date, OpenMode.Binary )

MyArray = New String(My_Recor dCount - 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.Lef t(x(3),
4),
_
Microsoft.Visua lBasic.Mid(x(3) , 5, 2), _
Microsoft.Visua lBasic.Right(x( 3), 2), _
Microsoft.Visua lBasic.Left(x(2 ), 2), _
Microsoft.Visua lBasic.Mid(x(2) , 3, 2), _
Microsoft.Visua lBasic.Mid(x(2) , 5, 2))
MyArray_Array(r ow, 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.Exec uteNonQuery()" 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=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|" _
+ "\Beta_MyInfo.m df;Integrated Security=True;U ser
Instance=True")
myConnection.Op en()
insertCMD = "Insert into MyLog (Date) values ('" &
MyArray(i, 2) & "');"
myCommand = New SqlCommand(inse rtCMD, myConnection)
myCommand.Execu teNonQuery()
myConnection.Cl ose()

Next

Any ideas where I might be going wrong

Thanks

Michael Bond

[mabond]

Hi

I've resolved the problem by the use of parameters

the following meets my needs

insertCMD = "Insert into MyLog (Date) values (@MYDATE);"
myCommand = New SqlCommand(inse rtCMD, myConnection)
mycommand.param eters.add(new sqlparamter("@m ydate",sqldbtyp e.datetime))
mycommand.param eters(0).value = cdate(myarray(i ,0))
myCommand.Execu teNonQuery()

Thanks for the pointers

Michael Bond
"AMDRIT" wrote:

SQL doesn't understand #date#, it does understand 'date'. Perhaps that is
all there is to it.


"mabond" <ma****@discuss ions.microsoft. comwrote in message
news:F3******** *************** ***********@mic rosoft.com...

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=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|"
_
+ "\Beta_MyInfo.m df;Integrated Security=True;U ser
Instance=True")

Dim trans As SqlClient.SqlTr ansaction 'Transaction object
Dim myCommand As SqlClient.SqlCo mmand '
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.Op en()

'Create a transaction
trans = myConnection.Be ginTransaction

'Loop over fields to be inserted
For i As Integer = 0 To UBound(MyArray)

Try

'Test our fields
Debug.Assert(Is Date(MyArray(i, 2)) = True, "Invalid value
specified"))
Trace.WriteLine If(IsDate(MyArr ay(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.SqlCo mmand(insertCMD , myConnection,
trans)

'Execute our SQL
iRet = myCommand.Execu teNonQuery()

myCommand.Dispo se()

'Test results
Debug.Assert(iR et = 1, String.Format(" Expected one record to be
inserted, instead {0} were inserted.", iRet))
Trace.WriteLine If(iRet = 1, String.Format(" Expected one record to
be
inserted, instead {0} were inserted.", iRet))

Catch ex As SqlClient.SqlEx ception

AllClear = False

'Clean up
myCommand.Dispo se()

Dim cmd As SqlClient.SqlCo mmand

cmd = New SqlClient.SqlCo mmand()
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.SqlCo mmand

cmd = New SqlClient.SqlCo mmand()
cmd.Connection = myConnection : cmd.Transaction = trans :
cmd.Transaction .Commit()
cmd.Dispose()

End If

myConnection.Cl ose()
myConnection.Di spose()

"mabond" <ma****@discuss ions.microsoft. comwrote in message
news:3A******** *************** ***********@mic rosoft.com...
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_vali date, OpenMode.Binary )

MyArray = New String(My_Recor dCount - 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.Lef t(x(3),
4),
_
Microsoft.Visua lBasic.Mid(x(3) , 5, 2), _
Microsoft.Visua lBasic.Right(x( 3), 2), _
Microsoft.Visua lBasic.Left(x(2 ), 2), _
Microsoft.Visua lBasic.Mid(x(2) , 3, 2), _
Microsoft.Visua lBasic.Mid(x(2) , 5, 2))
MyArray_Array(r ow, 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.Exec uteNonQuery()" 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=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|" _
+ "\Beta_MyInfo.m df;Integrated Security=True;U ser
Instance=True")
myConnection.Op en()
insertCMD = "Insert into MyLog (Date) values ('" &
MyArray(i, 2) & "');"
myCommand = New SqlCommand(inse rtCMD, myConnection)
myCommand.Execu teNonQuery()
myConnection.Cl ose()

Next

Any ideas where I might be going wrong

Thanks

Michael Bond

[raibeart]

Using a parameter was the right way of fixing it. You should never
send a SQL string directly. You should create a stored procedure and
pass in the values to the UPDATE and let the server handle it. Or, in
your case, the INSERT.

mabond wrote:

Hi

I've resolved the problem by the use of parameters

the following meets my needs

insertCMD = "Insert into MyLog (Date) values (@MYDATE);"
myCommand = New SqlCommand(inse rtCMD, myConnection)
mycommand.param eters.add(new sqlparamter("@m ydate",sqldbtyp e.datetime))
mycommand.param eters(0).value = cdate(myarray(i ,0))
myCommand.Execu teNonQuery()

Thanks for the pointers

Michael Bond
"AMDRIT" wrote: