Catalin Cernat Catalin Cernat - 1 month ago 5
Vb.net 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
Try
Dim a As String
con.ConnectionString = "Data Source=SVNAV;Initial Catalog=NAV_Vermorel_Live;User ID=sa;Password=1234"
con.Open()
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"
Else
laser = "In Progress"
dlaser = Nothing
End If
If NewCheckBox2.Checked = True Then
debit = "Finished"
Else
debit = "In Progress"
ddebit = Nothing
End If
If NewCheckBox3.Checked = True Then
indoire = "Finished"
Else
indoire = "In Progress"
dindoire = Nothing
End If
If NewCheckBox4.Checked = True Then
uzinaj = "Finished"
Else
uzinaj = "In Progress"
duzinaj = Nothing
End If
a = "INSERT INTO [dbo].[SC Vermorel SRL$PregatirePROD]
(
[FPO]
,[Articol]
,[Descriere]
,[Cantitate]
,[LASER]
,[DEBITARE]
,[INDOIRE]
,[UZINAJ]
,[EndDateLASER]
,[EndDateDEBIT]
,[EndDateINDOIRE]
,[EndDateUZINAJ])
VALUES
(@FPO,
@Articol
,@Descriere
,@Cantitate
,@LASER
,@DEBITARE
,@INDOIRE
,@UZINAJ
,@EndDateLASER
,@EndDateDEBIT
,@EndDateINDOIRE
,@EndDateUZINAJ)"
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)
myCommand.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show("Eroare ..." & ex.Message, "Inserare campuri")
Finally
con.Close()
End Try
Me.SC_Vermorel_SRL_PregatirePRODTableAdapter.Fill(Me.NAV_Vermorel_LiveDataSet._SC_Vermorel_SRL_PregatirePROD)
End Sub


The table design from, prtscreen from SSM:

tabledesign

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

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.

Comments