Problem Inserting/datetime field

[David Fúnez]

Hi;

I'm working with SQ CE, i can insert data in a datetime field, this is the
code:

' This code works fine
'----------------------
cn = New SqlCeConnection("Data Source=\program files\sistema\zafra.sdf")
cn.Open()

Dim cmd As SqlCeCommand = cn.CreateCommand

Dim truck As String
Dim fec As Date
Dim lot, car As Integer

truck = Me.txtCamion.Text
fec = Me.txtFecha.Text
lot = Me.txtLote.Text
car = Me.txtCargas.Text

' Agregamos registro
cmd.CommandText = "INSERT INTO colecta" & _
"(lote, fecha, camion, cargas)" & _
" VALUES (?, ?, ?, ?)"

cmd.Parameters.Add("@lote", lot)
cmd.Parameters.Add("@fecha", fec)
cmd.Parameters.Add("@camion", truck)
cmd.Parameters.Add("@cargas", car)

cmd.Prepare()
cmd.ExecuteNonQuery()

*** The UPDATE Code, it launches this error "Data Conversion Failed [OLE DB
status value,,,,,]"
*** the problem is with the DATE value, so i don't know how to fix it.

Dim cn As SqlCeConnection

cn = New SqlCeConnection("Data Source=\Program Files\sistema\zafra.sdf")
cn.Open()

Dim cmd As SqlCeCommand = cn.CreateCommand

Dim truck As String
Dim fec As Date
Dim lot, car As Integer

truck = Me.txtCamion.Text
fec = Me.txtFecha.Text
lot = Me.txtLote.Text
car = Me.txtCargas.Text

' Actualizamos registro
cmd.CommandText = _
"UPDATE colecta SET fecha=?, camion=?, cargas=? WHERE lote=?"

cmd.Parameters.Add(New SqlCeParameter("@lote", truck))
cmd.Parameters.Add(New SqlCeParameter("@fecha", fec))
cmd.Parameters.Add(New SqlCeParameter("@camion", lot))
cmd.Parameters.Add(New SqlCeParameter("@cargas", car))

cmd.Prepare()
cmd.ExecuteNonQuery()

***************************************

Thanks on advance.

 

[Darren Shaffer]

Instead of trusting that the date entered by the user will conform
to a format that is acceptable as a .NET DateTime or a SQL CE
Date field, change:

fec = Me.txtFecha.Text

to use DateTime.Parse() and then convert this value to a string
which will insert into SQL CE nicely.
--
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com

 

[Alex Feinman [MVP]]

This code cannot "work fine"
You are assigning Control.Text value to a datetime variable "fec". Besides
it is declared using a non-existent data type Date

Should be:
Try
fec = DateTime.Parse(Me.txtFecha.Text)
catch ...
....
end try
....

cmd.Parameters.Add("@fecha", typeof(DateTime)).Value = fec

 

[Daniel Moth]

Not detracting from the reply but just for the record...

You are assigning Control.Text value to a datetime variable "fec".

.... which shows that the OP has Option Strict Off. I have given up even
helping code that hasn't turned option strict on, although it is largely
Microsoft's fault (or more specifically the VB's team mentality) for not
changing the default option.

it is declared using a non-existent data type Date

Date in VB is an alias to DateTime (yet another unnecessary backwards
compatibility piece of baggage)

Cheers
Daniel
--
http://www.danielmoth.com/Blog/

This code cannot "work fine"
You are assigning Control.Text value to a datetime variable "fec". Besides
it is declared using a non-existent data type Date

Should be:
Try
fec = DateTime.Parse(Me.txtFecha.Text)
catch ...
...
end try
...

cmd.Parameters.Add("@fecha", typeof(DateTime)).Value = fec

--
Alex Feinman
---
Visit http://www.opennetcf.org

Hi;

I'm working with SQ CE, i can insert data in a datetime field, this is
the code:

' This code works fine
'----------------------
cn = New SqlCeConnection("Data Source=\program files\sistema\zafra.sdf")
cn.Open()

Dim cmd As SqlCeCommand = cn.CreateCommand

Dim truck As String
Dim fec As Date
Dim lot, car As Integer

truck = Me.txtCamion.Text
fec = Me.txtFecha.Text
lot = Me.txtLote.Text
car = Me.txtCargas.Text

' Agregamos registro
cmd.CommandText = "INSERT INTO colecta" & _
"(lote, fecha, camion, cargas)" & _
" VALUES (?, ?, ?, ?)"

cmd.Parameters.Add("@lote", lot)
cmd.Parameters.Add("@fecha", fec)
cmd.Parameters.Add("@camion", truck)
cmd.Parameters.Add("@cargas", car)

cmd.Prepare()
cmd.ExecuteNonQuery()

*** The UPDATE Code, it launches this error "Data Conversion Failed [OLE
DB status value,,,,,]"
*** the problem is with the DATE value, so i don't know how to fix it.

Dim cn As SqlCeConnection

cn = New SqlCeConnection("Data Source=\Program Files\sistema\zafra.sdf")
cn.Open()

Dim cmd As SqlCeCommand = cn.CreateCommand

Dim truck As String
Dim fec As Date
Dim lot, car As Integer

truck = Me.txtCamion.Text
fec = Me.txtFecha.Text
lot = Me.txtLote.Text
car = Me.txtCargas.Text

' Actualizamos registro
cmd.CommandText = _
"UPDATE colecta SET fecha=?, camion=?, cargas=? WHERE lote=?"

cmd.Parameters.Add(New SqlCeParameter("@lote", truck))
cmd.Parameters.Add(New SqlCeParameter("@fecha", fec))
cmd.Parameters.Add(New SqlCeParameter("@camion", lot))
cmd.Parameters.Add(New SqlCeParameter("@cargas", car))

cmd.Prepare()
cmd.ExecuteNonQuery()

***************************************

Thanks on advance.

 

[Guest]

I'm wondering if the order of the parameters is correct in the UPDATE portion.

awni