Saving Nulls to SQL Server.

[knopmike]

I've upsized a vfp 6.x dbc to sql server 2000 and am having a problem with dates. Null date fields in vfp migrate to sql server with no problem.

Our forms use the ms common calender control wrapped around a textbox and button so that when a user clicks the button, the calendar comes up, a user can select a date, and display it in the text box.

If a user leaves a non-required date field empty, instead of saving nulls in the sql server table, it saves the default sql server date. Then when I read back the record, instead of seeing empty dates, I see the sql server default date.

Is there any way to ensure that non-required, empty date fields will be saved to sql server as nulls?

I have a deadline for migrating several apps built around the same common classes, controls, etc and would appreciate any help as soon as possible. Thanks. Mike

 

[DEDMOD]

There are lots of ways to approach this. You can put code in the click event of your button perhaps, though that depends on how it's set up. I'd personally think the best thing would be a rule on the SQL Server table(s) such that if the default date is entered, it'd be replaced with .Null.

Dave Dardinger

 

[TomasDill]

It is good also to make your own textbox class or other control for date editing which will check - if date is empty, replace field value by NULL.
Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[knopmike]

Thanks for a quick response guys i appreciate it.

Dave,

Thanks for the idea of the rule on the sqls table(s) - i'm sure that would ensure that date fields either have a date or are null. Ideally it would be great to have a solution on the vfp side, but if its not possible, then I may have to do this.

Vlad,

Thanks for the idea; however i don't know where I would add that check. If its a non-manditory field, the control may never receive focus, potentially.

An idea i had on the way to work this am was to override the assign method of the control to assign null if empty; however, i'm not sure if the assign method is even triggered when and update is made to the view that the control is based on.

I'm going to do some more testing today and see what I can come up with.

Thanks again guys.

 

[DEDMOD]

I'm sure we could do a lot more on the VFP side of things if we knew what code was in the click event of your button. Or if the thing is that the button is never clicked, then whatever event you use to tell the form to send the data to SQL Server. I was doing a whole bunch of sending some old data to a SQL Server 7 database not long ago using 'INSERT INTO'. If your code does something similar it's easy enough to do whatever data checking you need before running the actual INSERT.

Dave Dardinger

 

[knopmike]

Dedmod,

When the app was file based, the form was based on a local view. When the dbc was upsized using the sqls wizard, i instructed the wizard to automatically created remote views based on the local tables and views. So now the form is based on a remote view, such that the custom date control uses a remote view field as its control source. When a user clicks create, append blank is used; after a user enters info into the manditory fields and clicks a save button, a tableupdate is used.

The control is a container custom class containing:
- a textbox (txtDateFieldNeb) and a
- command button (cmdShowCal)

The command button click code looks like this:

If !This.Parent.txtDateFieldNEB.ReadOnly
ofrm = newobject("frmMonthView","olemonthview", "",;
This.Parent.txtDateFieldNEB)
ofrm.show(1)
This.Parent.txtDateFieldNEB.SetFocus
Endif

frmMonthView is a form containing olemonthview which is an olecontrol based on the oleclass mscomctl2.monthview.2 (Microsoft common control).

The frmMonthView.olemonthview.dateclicked method below returns the date picked back to oControlName which is txtdatefieldneb

LPARAMETERS dateclicked

this.parent.oControlName.value = ttod(dateclicked)
this.destroy()
this.parent.deactivate()

The end result is that when a tableupdate is performed, if a date field contains a date it is written back to sqls and if a date field was never touched it results in the sqls default date getting written.

 

[DEDMOD]

Well, it'd seem to me that all you need to do is have the click event of the Save button contain code like:

 realDate = IIF(myDate > 0, myDate, .Null.)
REPLACE myDate WITH realDate

Do this for each date field you need nulls entered in. If you can enter more than one new record before hitting the 'save' button, you need to check each such record. I'll leave it to you to figure that out.

Note: I'm not sure what comparison should be made so I said '>0'. If that doesn't work, try something else like a string comparison '= ""' or VAL(myDate)>0 or whatever.

Also, I'm not sure if you need to or want to use the REPLACE. It may be sufficient to use

 MyDate = IIF(myDate > 0, myDate, .Null.)

Finally I don't remember if you need to use ".Null." or just .Null. I've never gotten it entirely clear in my mind when you have to quote some things and when you don't.

Dave Dardinger