Populating date into SQL

14 Aug 2006 12:41 PM

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_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

14 Aug 2006 2:35 PM

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=.\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()

Show quoteHide quote

"mabond" <mab***@discussions.microsoft.com> wrote in message
news:3AF6A94D-8E41-4810-9E6F-FF136F4E3F43@microsoft.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_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
>

14 Aug 2006 3:12 PM

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

Show quoteHide quote

"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" <mab***@discussions.microsoft.com> wrote in message
> news:3AF6A94D-8E41-4810-9E6F-FF136F4E3F43@microsoft.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_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
> >
>
>
>

14 Aug 2006 3:32 PM

AMDRIT

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

Show quoteHide quote

"mabond" <mab***@discussions.microsoft.com> wrote in message
news:F3BB63A3-043D-4181-A612-1E1B1892236A@microsoft.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=.\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" <mab***@discussions.microsoft.com> wrote in message
>> news:3AF6A94D-8E41-4810-9E6F-FF136F4E3F43@microsoft.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_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
>> >
>>
>>
>>

15 Aug 2006 9:59 AM

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(insertCMD, myConnection)
mycommand.parameters.add(new sqlparamter("@mydate",sqldbtype.datetime))
mycommand.parameters(0).value = cdate(myarray(i,0))
myCommand.ExecuteNonQuery()

Thanks for the pointers

Michael Bond
Show quoteHide quote

"AMDRIT" wrote:

> SQL doesn't understand #date#, it does understand 'date'. Perhaps that is
> all there is to it.
>
>
>
>
> "mabond" <mab***@discussions.microsoft.com> wrote in message
> news:F3BB63A3-043D-4181-A612-1E1B1892236A@microsoft.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=.\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" <mab***@discussions.microsoft.com> wrote in message
> >> news:3AF6A94D-8E41-4810-9E6F-FF136F4E3F43@microsoft.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_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
> >> >
> >>
> >>
> >>
>
>
>

15 Aug 2006 2:44 PM

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:
Show quoteHide quote

> 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(insertCMD, myConnection)
> mycommand.parameters.add(new sqlparamter("@mydate",sqldbtype.datetime))
> mycommand.parameters(0).value = cdate(myarray(i,0))
> myCommand.ExecuteNonQuery()
>
> Thanks for the pointers
>
> Michael Bond
> "AMDRIT" wrote:
>