1. Forum
  2. Usenet
  3. COMP.LANG.CLIPPER.VISUAL-

  • Vo2Ado Empty dates

    From Carlos Vazquez@21:1/5 to All on Wed May 19 05:40:09 2021
    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)
  • From Phil McGuinness@21:1/5 to Carlos Vazquez on Fri May 21 07:15:59 2021
    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)
  • From Carlos Vazquez@21:1/5 to All on Sat May 22 04:37:15 2021
    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)
  • From Jamal@21:1/5 to Carlos Vazquez on Sat May 22 12:42:11 2021
    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)
  • From Carlos Vazquez@21:1/5 to All on Tue Oct 31 11:42:41 2023
    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)