Hi!
I'm converting some DBF table to SQLServer. When there is an empty date value, the following code throws an error "type conflict: text is not compatible with date".

How can I pass empty date values to SQLServer?

Thanks!!
****************

aParametros := ArrayCreate(2)

oCmd:CommandText := "INSERT INTO [Ark].[dbo].[Cliente] ( [Ark].[dbo].[Cliente].[Codigo], [Ark].[dbo].[Cliente].[Fecha] ) VALUES (?,?)"

sCliente:GoTop()
WHILE !sCliente:EoF
aParametros[ 1] := sCliente:FIELDGET( #CODIGO )
aParametros[ 2] := sCliente:FIELDGET( #FECHA )
//
oCmd:Execute(@uResult,aParametros,NIL)
IF uResult != 1
MostrarError( oCmd:Error:description )
BREAK
ENDIF
sCliente:Skip()
ENDDO

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Wednesday, May 19, 2021 at 10:40:10 PM UTC+10, Carlos Vazquez wrote:

Hi!
I'm converting some DBF table to SQLServer. When there is an empty date value, the following code throws an error "type conflict: text is not compatible with date".

How can I pass empty date values to SQLServer?

FUNCTION dtosSQL( dDate AS USUAL ) AS STRING

LOCAL cDate AS STRING

IF IsString(dDate) .OR. dDate = NULL_DATE
cDate := [null]
ELSE
cDate := ['] + Stuff(Stuff(DToS(dDate),5,0,[-]),8,0,[-]) + ['] // 20071201 return "2007-12-01"
ENDIF
//
RETURN cDate

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

El viernes, 21 de mayo de 2021 a las 16:16:00 UTC+2, Phil McGuinness escribió:

FUNCTION dtosSQL( dDate AS USUAL ) AS STRING

LOCAL cDate AS STRING

IF IsString(dDate) .OR. dDate = NULL_DATE
cDate := [null]
ELSE
cDate := ['] + Stuff(Stuff(DToS(dDate),5,0,[-]),8,0,[-]) + ['] // 20071201 return "2007-12-01"
ENDIF
//
RETURN cDate

Phil, that function returns a string. I've tried to use that concept but then i get "error converting string to date". I've tried also to return NIL, NULL, NULL_DATE... to no avail.
The only thing that works is returning a valid date, say CToD( '01/01/1900' )

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

In your app, try calling

AdoConvertNullStringToNull ( TRUE )


P.S. I don't use the Vo2Ado, but I looked at the documentation. Source:


FUNCTION AdoConvertNullStringToNull ( lSet ) AS

Argument(s)

lSet When TRUE, NULL_STRINGS will be converted to NULL, else an Empty
value is passed to COM. The default behaviour is NOT to convert them to NULL


Return


The process of converting NULL_STRINGS can be switched ON/OFF by calling
the AdoCheckNullString function


See Also

Support functions, AdoCheckNullString, Data Types


HTH,
Jamal

On 5/19/2021 8:40 AM, Carlos Vazquez wrote:

Hi!
I'm converting some DBF table to SQLServer. When there is an empty date value, the following code throws an error "type conflict: text is not compatible with date".

How can I pass empty date values to SQLServer?

Thanks!!
****************

aParametros := ArrayCreate(2)

oCmd:CommandText := "INSERT INTO [Ark].[dbo].[Cliente] ( [Ark].[dbo].[Cliente].[Codigo], [Ark].[dbo].[Cliente].[Fecha] ) VALUES (?,?)"

sCliente:GoTop()
WHILE !sCliente:EoF
aParametros[ 1] := sCliente:FIELDGET( #CODIGO )
aParametros[ 2] := sCliente:FIELDGET( #FECHA )
//
oCmd:Execute(@uResult,aParametros,NIL)
IF uResult != 1
MostrarError( oCmd:Error:description )
BREAK
ENDIF
sCliente:Skip()
ENDDO

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Jamal, sorry for ultra-late response... :)
I was (again) looking for an answer to "How to send NULL values to SQL servers" and see this message. Your anwser is the one I needed. Thx!!!

El sábado, 22 de mayo de 2021 a las 18:42:13 UTC+2, Jamal escribió:

In your app, try calling

AdoConvertNullStringToNull ( TRUE )

P.S. I don't use the Vo2Ado, but I looked at the documentation. Source:

FUNCTION AdoConvertNullStringToNull ( lSet ) AS

Argument(s)

lSet When TRUE, NULL_STRINGS will be converted to NULL, else an Empty
value is passed to COM. The default behaviour is NOT to convert them to NULL

Return

The process of converting NULL_STRINGS can be switched ON/OFF by calling
the AdoCheckNullString function

See Also

Support functions, AdoCheckNullString, Data Types

HTH,
Jamal
On 5/19/2021 8:40 AM, Carlos Vazquez wrote:

Hi!
I'm converting some DBF table to SQLServer. When there is an empty date value, the following code throws an error "type conflict: text is not compatible with date".

How can I pass empty date values to SQLServer?

Thanks!!
****************

aParametros := ArrayCreate(2)

oCmd:CommandText := "INSERT INTO [Ark].[dbo].[Cliente] ( [Ark].[dbo].[Cliente].[Codigo], [Ark].[dbo].[Cliente].[Fecha] ) VALUES (?,?)"

sCliente:GoTop()
WHILE !sCliente:EoF
aParametros[ 1] := sCliente:FIELDGET( #CODIGO )
aParametros[ 2] := sCliente:FIELDGET( #FECHA )
//
oCmd:Execute(@uResult,aParametros,NIL)
IF uResult != 1
MostrarError( oCmd:Error:description )
BREAK
ENDIF
sCliente:Skip()
ENDDO

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)