Catalin Cernat Catalin Cernat - 10 months ago 31 Question

Datetime VB net to SQL table

I have this:

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
Dim con As New SqlConnection
Dim myCommand As New SqlCommand
Dim a As String
con.ConnectionString = "Data Source=SVNAV;Initial Catalog=NAV_Vermorel_Live;User ID=sa;Password=1234"
Dim laser As String
Dim debit As String
Dim indoire As String
Dim uzinaj As String
Dim dlaser As Nullable(Of DateTime) = DateTime.Now
Dim ddebit As Nullable(Of DateTime) = DateTime.Now
Dim dindoire As Nullable(Of DateTime) = DateTime.Now
Dim duzinaj As Nullable(Of DateTime) = DateTime.Now
If NewCheckBox1.Checked = True Then
laser = "Finished"
laser = "In Progress"
dlaser = Nothing
End If
If NewCheckBox2.Checked = True Then
debit = "Finished"
debit = "In Progress"
ddebit = Nothing
End If
If NewCheckBox3.Checked = True Then
indoire = "Finished"
indoire = "In Progress"
dindoire = Nothing
End If
If NewCheckBox4.Checked = True Then
uzinaj = "Finished"
uzinaj = "In Progress"
duzinaj = Nothing
End If
a = "INSERT INTO [dbo].[SC Vermorel SRL$PregatirePROD]
myCommand = New SqlCommand(a, con)
myCommand.Parameters.AddWithValue("@FPO", txtFpo.Text)
myCommand.Parameters.AddWithValue("@Articol", txtItem.Text)
myCommand.Parameters.AddWithValue("@Descriere", txtDesc.Text)
myCommand.Parameters.AddWithValue("@Cantitate", txtQty.Text)
myCommand.Parameters.AddWithValue("@LASER", laser)
myCommand.Parameters.AddWithValue("@DEBITARE", debit)
myCommand.Parameters.AddWithValue("@INDOIRE", indoire)
myCommand.Parameters.AddWithValue("@UZINAJ", uzinaj)
myCommand.Parameters.AddWithValue("@EndDateLaser", dlaser)
myCommand.Parameters.AddWithValue("@EndDateDEBIT", ddebit)
myCommand.Parameters.AddWithValue("@EndDateINDOIRE", dindoire)
myCommand.Parameters.AddWithValue("@EndDateUZINAJ", duzinaj)
Catch ex As Exception
MessageBox.Show("Eroare ..." & ex.Message, "Inserare campuri")
End Try
End Sub

The table design from, prtscreen from SSM:


Im trying to add the DateTime.Now value of dlaser into an SQL field. I get SQL type overflow, dates must be between etc etc.

The format of date witch SMS displays is: 2016-09-30 14:58:46.343. SQL Server 2005.

How can i be sure that vb net application outputs datetime in same format?

Answer Source

In the Else part you leave VB variable dlaser uninitialized, which means it has value 0001-01-01 00:00:00. But that variable is always used for parameter @EndDateLaser to set column [EndDateLASER].

Column [EndDateLASER] has SQL type datetime, but datetime does not allow 0001-01-01, the minimum value allowed is 1753-01-01.

Apart from that, I wonder why you sometimes add a @dlaser SqlParameter (with value DBNull). For the query as shown that parameter is irrelevant because it does not use @dlaser anywhere. And also, why add it only in one situation, while your query is fixed.