joe joe - 1 year ago 50 Question

Invalid character value for cast specification MS Access to SQL Server import

I have the following INSERT query in my program (the variables are being assigned a value from an Access database, and are being inserted into a new SQL Server database). The code runs through fine, the value in the

section are all what they should be, but when it gets to the line
I get given the error

Invalid character value for cast specification.
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

For Each dr As DataRow In getDataTable(sqlct, importsCon, Nothing).Rows

Dim conID As Integer
Dim custacc As String
Dim datestart As Object
Dim daterenew As Object
Dim fee As Double
Dim agree As String
Dim details As String
Dim datesent As Object
Dim timesent As Object
Dim remname As String
Dim remmail As String

conID = dr("ContractID")
custacc = dbToString(dr("CustAcc"))
datestart = dbToDate(dr("DateStart"))
daterenew = dbToDate(dr("DateRenew"))
fee = dr("Fee")
agree = dbToString(dr("Agreement"))
details = dbToString(dr("OtherDetails"))
datesent = dbToDate(dr("DateSent"))
timesent = dbToTime(dr("TimeSent"))
remname = dbToString(dr("ReminderName"))
remmail = dbToString(dr("ReminderEmail"))

sql.CommandText = "INSERT INTO tblContracts(ContractID, CustAcc, DateStart, DateRenew, Fee, Agreement, OtherDetails, DateSent, TimeSent, ReminderName, ReminderEmail) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
sql.Transaction = tr
sql.Parameters.Add("@ContractID", OleDbType.Integer).Value = conID
sql.Parameters.Add("@CustAcc", OleDbType.VarChar).Value = custacc
sql.Parameters.Add("@DateStart", OleDbType.DBDate).Value = datestart
sql.Parameters.Add("@DateRenew", OleDbType.DBDate).Value = daterenew
sql.Parameters.Add("@Fee", OleDbType.Double).Value = fee
sql.Parameters.Add("@Agreement", OleDbType.VarChar).Value = agree
sql.Parameters.Add("@OtherDetails", OleDbType.VarChar).Value = details
sql.Parameters.Add("@DateSent", OleDbType.DBDate).Value = datesent
sql.Parameters.Add("@TimeSent", OleDbType.DBDate).Value = timesent
sql.Parameters.Add("@ReminderName", OleDbType.VarChar).Value = remname
sql.Parameters.Add("@ReminderEmail", OleDbType.VarChar).Value = remmail



What is the problem with my code? All of the datatypes match that of the database (Objects are datetime in the database, but they're initially objects as they go through another function first)

Answer Source

As discovered in the comments, the problem is on line sql.Parameters.Add("@DateSent", OleDbType.DBDate).Value = datesent

Because the database you are importing is storing a null value in this field on one or more records, in the function that checks whether or not it is null, it's being converted from null to 12:00:00 AM (this code is not given in the question, so I'm not sure why or how.

Now, you cannot insert 12:00:00 AM as a DBDate value because, well, as it says on the tin, it wants a date format value (yyyyMMdd, or similar).

The solution to this problem is to simply change the aforementioned line to

sql.Parameters.Add("@DateSent", OleDbType.Date).Value = datesent

Using OleDbType.Date will not insert the value, even with no date being specified, as it able to work with this, and insert a default date for this method, which I believe is 1899-12-30. But either way, this will solve your problem