Well its still nice to learn something new every day :) Thank you for your feedback Richard. Some investigating indicates you're correct; the provider does a pretty good job at converting the native datetime object. I still have some difficulty with IBM's DB2, but hey that's always the case when I work with the OS/390.
Just in case anyone is looking for an example, I put together 2 subroutines. Create a form and add 2 buttons two it. Cut and paste the code below and point the connection strings to a database that has a table named "TableDate" with a datetime field named "created" and a varchar(50) field named "Info".
Johnny
Public
Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'setup a native sql connection to MS SQL 2000
Dim sqlConn As Data.SqlClient.SqlConnection
Dim sqlCmd As New Data.SqlClient.SqlCommand
Dim sqlReader As Data.SqlClient.SqlDataReader
Dim connectionString As String = _
"Data Source=fhnt103;Initial Catalog=forumPlayground;User ID=johnny;pwd=fhnt103"
Dim createdDate As DateTime
Dim lastRecord As String = ""
Try
'set our date to an invalid sql date
createdDate =
New DateTime(1, 1, 31)
sqlConn =
New Data.SqlClient.SqlConnection(connectionString)
sqlConn.Open()
sqlCmd.Connection = sqlConn
sqlCmd.CommandText =
"Insert into TableDate (created, info) values (@created, @info)"
sqlCmd.Parameters.Add(
"@created", SqlDbType.DateTime)
sqlCmd.Parameters.Add(
"@info", SqlDbType.VarChar, 50)
sqlCmd.Parameters.Item(
"@created").Value = createdDate.ToUniversalTime
sqlCmd.Parameters.Item(
"@info").Value = "A test record"
'insert the data
If sqlCmd.ExecuteScalar() Then
Err.Raise(vbObjectError + 1024,
Me, "Insert failed to update any records.")
End If
sqlCmd.CommandText =
"select created, info from TableDate"
sqlReader = sqlCmd.ExecuteReader
'read all records
While sqlReader.Read
'store the last records value
lastRecord = sqlReader.Item(
"created").ToString + " -- " + sqlReader.Item("info").ToString
End While
'display the value
MsgBox(lastRecord)
'close the reader
sqlReader.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'setup an ODBC sql connection to MS SQL 2000
Dim sqlConn As Data.Odbc.OdbcConnection
Dim sqlCmd As New Data.Odbc.OdbcCommand
Dim sqlReader As Data.Odbc.OdbcDataReader
Dim connectionString As String = _
"dsn=odbcDB;UID=johnny;pwd=fhnt103"
Dim createdDate As DateTime
Dim lastRecord As String = ""
Try
'set our date to an invalid sql date
createdDate =
New DateTime(1, 1, 31)
sqlConn =
New Data.Odbc.OdbcConnection(connectionString)
sqlConn.Open()
sqlCmd.Connection = sqlConn
'NOTE! .NET 2.0 no longer supports this syntax; named parameter fields.
'sqlCmd.CommandText = "Insert into TableDate (created, info) values (@created, @info)"
sqlCmd.CommandText =
"Insert into TableDate (created, info) values (?, ?)"
sqlCmd.Parameters.Add(
"?", Odbc.OdbcType.DateTime)
sqlCmd.Parameters.Add(
"?", Odbc.OdbcType.VarChar, 50)
sqlCmd.Parameters.Item(0).Value = createdDate.ToUniversalTime
sqlCmd.Parameters.Item(1).Value =
"A test record"
'insert the data
If sqlCmd.ExecuteScalar() Then
Err.Raise(vbObjectError + 1024,
Me, "Insert failed to update any records.")
End If
sqlCmd.CommandText =
"select created, info from TableDate"
sqlReader = sqlCmd.ExecuteReader
'read all records
While sqlReader.Read
'store the last records value
lastRecord = sqlReader.Item(
"created").ToString + " -- " + sqlReader.Item("info").ToString
End While
'display the value
MsgBox(lastRecord)
'close the reader
sqlReader.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End
Class
