How to test for dbNull field on OLEDB update of VFP table
- From: "JohnR" <JohnR104@xxxxxxxxxxx>
- Date: Tue, 19 Apr 2005 01:46:33 GMT
I seem to be running in circles here. I decided to use a datetime field in
a VFP DBF table (8 bytes, type DATE) to handle concurrency conflicts. So I
altered the existing table to add a LASTUPDT field of type DATE. Initially
all values in this column are NULL (as displayed in the browser window of
VFP).
In VB.NET I'm using OLEDB with the vfpoledb.1 provider. I'm also using a
dataset to perform all my updates. The problem comes in how to specify the
UPDATE statement so that it accounts for the possibility of the "old" value
of LASTUPDT being NULL. Remember that oledb with the vfp provider requires
all replacement values to be passed as parameters. So my update stmt looks
like this:
cmd = New OleDbCommand("update rolodex set rphone = ?, lastupdt = ? where
name = ? and lastupdt = ?", dconnTele)
cmd.Parameters.Add("@rphone", OleDbType.VarChar, 13, "rphone")
cmd.Parameters.Add("@lastupdt", OleDbType.Date).Value = Today
cmd.Parameters.Add("@name", OleDbType.VarChar, 30, "name")
cmd.Parameters.Add("@lastupdt", OleDbType.Date, 8, "lastupdt").SourceVersion
= DataRowVersion.Original
daTele.UpdateCommand = cmd
The problem is that it doesn't work. It updates 0 rows. I've narrowed it
down to the WHERE stmt comparing the lastupdt fields. So, to help isolate
the problem, I tried to do an update manually using the EXECUTENONQUERY
method like this:
cmd = New OleDbCommand("update rolodex set rphone = ?, lastupdt = ? where
name = ? and (lastupdt = ? or lastupdt = ?)", dconnTele)
cmd.Parameters.Add("@rphone", OleDbType.VarChar).Value =
dsChanged.Tables("rolodex").Rows(0).Item("rphone", DataRowVersion.Current)
cmd.Parameters.Add("@lastupdt", OleDbType.Date).Value = Today
cmd.Parameters.Add("@name", OleDbType.VarChar).Value =
dsChanged.Tables("rolodex").Rows(0).Item("name", DataRowVersion.Current)
cmd.Parameters.Add("@lastupdt", OleDbType.Date).Value =
dsChanged.Tables("rolodex").Rows(0).Item("lastupdt",
DataRowVersion.Original)
cmd.Parameters.Add("@lastupdt", OleDbType.date).Value = System.DBNull.Value
As you can see, I'm trying to test for lastupdt being an actual date or
trying to test if it's dbnull. I manually pre-loaded a record with a date
value, and if I update that record, everything works fine. If I try to
update a record where LASTUPDT is dbnull then the WHERE clause does not find
a match and 0 records are updated.
So the whole thing seems to be failing because I can't seem to pass a
parameter with a "null" value for the where clause to compare to the current
field (which is null also). I can't use the method ISNULL as was suggested
in some similar questions I found because the WHERE clause can't do a ISNULL
method, it has to do a straight comparison. (or so I believe).
If anybody understands what I'm asking for and has a solution I would be
very grateful for some help on this. I really don't want to be forced to
"pre-load" all of the fields to a date value (although I will if I must).
Seems like there should be a way to test for nulls in the where clause...
Thanks,
John
.
- Follow-Ups:
- Re: How to test for dbNull field on OLEDB update of VFP table
- From: Stephany Young
- Re: How to test for dbNull field on OLEDB update of VFP table
- Prev by Date: Re: how to do exclusive open of DBF file with Visual foxpro odbc driver
- Next by Date: Re: How to test for dbNull field on OLEDB update of VFP table
- Previous by thread: Re: how to do exclusive open of DBF file with Visual foxpro odbc driver
- Next by thread: Re: How to test for dbNull field on OLEDB update of VFP table
- Index(es):
Relevant Pages
|
|
