Populating date into SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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()
 
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
 
SQL doesn't understand #date#, it does understand 'date'. Perhaps that is
all there is to it.
 
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
 
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.
 
Back
Top