ado - updating vfp database

[nbob]

I'm trying to update a fvp dbc from vb 6.0 using ado 2.6.
code like...

    ConnectionString = "Provider=vfpoledb.1;Data " _ 
        & "Source=..\star.dbc"

    Set objConnection = New adodb.Connection
    objConnection.Open (ConnectionString)

    cmd.ActiveConnection = m_objConnection
    cmd.CommandType = adCmdText

    cmd.CommandText = "UPDATE session SET " _
                    & "session.cl_date = ? " _
                    & "WHERE session.user = ?"

    cmd.Parameters.Append cmd.CreateParameter("@CloseDate", adDate, adParamInput, 8, Date)
    cmd.Parameters.Append cmd.CreateParameter("@UserID", adChar, adParamInput, 10, xUserID)

    cmd.Execute

.........
I get error ...
"Field CL_DATE does not accept null values."
.........

in session.dbf cl_date field - type DATE, width 8

I've tried adDate, adDBDate, adDBTimestamp also tried ado in vfp7.0. and get the same error.

can anyone help?
thanks

 

[BeeBaw]

Found this in MSDN:

Field "name" does not accept null values (Error 1581)
You attempted to store a null value in a field that does not accept null values.

You can change this setting by checking the NULL checkbox when modifying a field in the Table Designer, or specifying the NULL keyword in a field definition.

Hope it helps.

 

[nbob]

hmmm
I'm trying to update the field with todays date but ado seems to pass NULL not the correct date.

If I set the field to allow NULL I don't get the error but I don't get the date either.

Is it somthing to do with the format of the date?

thanks

 

[BeeBaw]

Apparently Fox does not like any of the formatting options you can use to represent a date, it does however support CTOD() in the SQL statement itself. Pop this orund your date fields and with a bit of luck you should be rocking and rolling.

 

[nbob]

thanks for the help...
I've just tried this

    Dim MyDate As String
    MyDate = "01/01/2000"

    cmd.CommandText = "UPDATE session SET " _
           & "session.cl_date = {" & MyDate & "}, " _
           & "session.cl_time = ? " _
           & "WHERE session.user = ?"

and it works.
It would be nice to be able to pass the date using
cmd.parameters but beggars can't be choosers.


thanks everyone.

 

[nbob]

again...

I've tried ..

    cmd.CommandText = "UPDATE session SET " _
         & "session.cl_date = CTOD('" & Date & "') " _
         & "WHERE session.user = ?"

this also works

thanks beebaw