time value overflows

[jdy0803]

Following code works fine with MS Access DB

        dtExamDate = DateValue(Now) 
        Sql = "SELECT * FROM surgical_exam_history"
        adapter = New OleDbDataAdapter(Sql, cn)
        commandBuilder = New OleDbCommandBuilder(adapter)
        ds = New DataSet()
        adapter.Fill(ds, "surgical_exam_history")
        datarow = ds.Tables("surgical_exam_history").NewRow()
        datarow("patient_id") = CLng(gszPATIENT_ID)
        datarow("exam_date") = strExamDate
        datarow("exam_seq") = nSeqNo
        datarow("save_datetime") = dtExamDate
        datarow("edit_datetime") = dtExamDate
        ' Add to the Rows collection
        ds.Tables("surgical_exam_history").Rows.Add(datarow)
[B]        adapter.Update(ds, "surgical_exam_history")  =====> Error![/B]
        ds.Dispose()
        commandBuilder.Dispose()
        adapter.Dispose()

But error occurs with SQL Server at bold text marked as "======> Error!"
Please refer the screen capture of the error.
FYI, each field's type is like following.

patient_id : bigint
exam_date : datetime
exam_seq : smallint
save_datetime : datetime
edit_datetime : datetime

 

[jmcilhinney]

Did you actually read the error message? It tells you exactly what the issue is and exactly what to do about it. The data you're passing in doesn't fit in the data type that you've defined your column as. You need to either change the data or change the data type of the column.

 

[jdy0803]

I've already read error message but couldn't fix error.

If I remove(comment) following 2 lines, then no error occurs.
' datarow("save_datetime") = dtExamDate
' datarow("edit_datetime") = dtExamDate
However I can't any problem this code.
dtExamDate is DateTime type.
Isn't this VB.NET type match with SQL Server's datetime type?

 

[jmcilhinney]

Have you read the documentation for the available SQL Server data types for dates and times? I'll wager not. Don't just assume. The information is a click away. Read it.

 

[jdy0803]

I changed my code to fit the byte size like following but still error occurs.

datarow("patient_id") = Convert.ToInt64(gszPATIENT_ID)
datarow("exam_date") = Convert.ToDateTime(strExamDate)
datarow("exam_seq") = nSeqNo
datarow("save_datetime") = dtExamDate
datarow("edit_datetime") = dtExamDate

 

[jmcilhinney]

I changed my code to fit the byte size like following but still error occurs.

datarow("patient_id") = Convert.ToInt64(gszPATIENT_ID)
datarow("exam_date") = Convert.ToDateTime(strExamDate)
datarow("exam_seq") = nSeqNo
datarow("save_datetime") = dtExamDate
datarow("edit_datetime") = dtExamDate

That suggests that you were using a String where a DateTime was expected. You should never rely on implicit conversions like that. You've seen now what the consequences can be. You can generally allow an implicit conversion if it's widening but you should never allow a narrowing implicit conversion. If you don't know the difference between widening and narrowing conversions then just don't use any implicit conversions to be safe. If you always provide data in the form that it's expected then you won't wind up with odd issues like this one.

 

[jdy0803]

Can you change my code directly?
Still I don't get.