AlexBB


With my new approach to VFP DBs trying to retrieve data into .NET via OleDb I am facing a few issues I previously ignored. I need to set validation rules for some character fields. The only reqirement is that there should be no blank fields.

I used the VFP Management studio Table Designer. I tried a few clauses I thought would make sense but they all failed. MSDN does not give any examples suitable to my situation.

The names of the fields in one table are "NUMEIRC_ID" & "NAMED" Both of them are strings. I want to stress that it is not that I do not want them to be NULL, It has been taken care of. I want them not to assume "" value - empty string.

Thanks for help.





Re: Field validation rule.


Re: Field validation rule.

AlexBB

dni wrote:

You may check:

http://msdn2.microsoft.com/en-us/library/1x2k7ys3(VS.80).aspx

It is a great way to get three stars: "You can check...." I've checked it already before I posted. Do you imagine I don't have an access to MSDN or I am too dumb to find my way to a topic I need a sample or a solution. I clearly stated in my post: "MSDN does not give any examples suitable to my situation." How else can I get my point accross.

I marked your post as not helpful.Your fourth star may be delayed indefinitelySmile







Re: Field validation rule.

dni

...Thanks...I am not worry about stars.......and I said "may" not "can"...and your question were incomplete because you did not say what method use to append data to give you exactly field validation rule you need, so, to a general question you had a general answer....






Re: Field validation rule.

AlexBB

dni wrote:
...Thanks...I am not worry about stars.......and I said "may" not "can"...and your question were incomplete because you did not say what method use to append data to give you exactly field validation rule you need, so, to a general question you had a general answer....

It eluded me that the append rule is important.

I use INSERT command from .NET code. Something like this:

DateTime dated = DateTime.Now.Date;

string cDated = dated.ToString ( "MM/dd/yyyy" );

comndText = "INSERT INTO " + dataTableName +

" (numeric_id, named, dated, memo1) VALUES ('" + numeric_id +

"', '" + this.txtBoxFindWords.Text.Trim ( ) + "', CTOD('" + cDated + "'), '' ) ";

cmdm.CommandText = comndText;

cmdm.ExecuteNonQuery ( );

comndText = "INSERT INTO crossRefTable (pointer_one, pointer_two) VALUES ('" +

parentNumeric_id + "', '" + numeric_id + "') ";

cmdm.CommandText = comndText;

cmdm.ExecuteNonQuery ( );

I do not know how I can be more specific. It is a standard approach. I do not want to use any append from .NET. If I use APPEND I cannot use any validation I want because some fields will be set to EMPTY from the start, won't they Aside I want to minimize the number of commands I use.

The above code works and programmatically I can ensure that fields will not be empty but I want an additional protection because weird things do happen.






Re: Field validation rule.

dni

You may use :

ALTER TABLE with clause DROP CHECK, that will remove validation rule...

Use INSERT, OR APPEND...

ALTER TABLE with clause SET CHECK, to put back validation rule or add a new one...






Re: Field validation rule.

AlexBB

dni wrote:

You may use :

ALTER TABLE with clause DROP CHECK, that will remove validation rule...

Use INSERT, OR APPEND...

ALTER TABLE with clause SET CHECK, to put back validation rule or add a new one...

I do not think you really understand what I am asking. It will have to wait for Cetin to crack it.

I DO NOT HAVE ANY VALIDATION RULE IN PLACE. I want to set it up.

Also I know how to do it. It is not my problem. I am planning to use Table Designer. I need an example as to how such a validation rule can be built in syntactically correct way. I need something like this:

.NOT. .EQUALS. "" or what ever. I have never done it.

I am not going to use the APPEND command. It will defeat the purpose of my app.

I am sorry, it appears you are trying (sic!) to help now but you are not listening. You are not reading the posts.

Being a man of principleSmile, I have to give you another "negative review.Smile" This answer of yours "is not helpful."

Be careful. I've heard MS may start rolling the stars back. Fractionally. You may notice a chip missing from one of your rays, after your standing has been reduced to 2.95Smile






Re: Field validation rule.

dni

As I said I don't care about stars...and you "can" use ALTER TABLE ....SET CHECK (from MS help):

"SET CHECK lExpression3

Specifies the table validation rule. The lExpression3 parameter must evaluate to a logical expression and can be a user-defined function or a stored procedure."

I don't have time now to write a example....Feel free to add a "negative review"...





Re: Field validation rule.

CetinBasoz

Alex,

On the database level not null is the way to enforce it. Then you'd say that it accepts empty values as valid values. Yes that is true but excluding datetime datatype, you can have "empty" values on other backends too as valid values. You could specify !empty() as a validation rule per field on the database level but it'd be more than cumbersome to do it. Instead many do control it at application level rather than db level.

I'm sorry to say that your commandText construction is not the standard way of doing that. Instead a standard approach uses parameters collection. ie:

Code Block

DateTime dated = DateTime.Now.Date;

cmdm.CommandText =

String.Format("insert into {0} (numeric_id, named, dated, memo1) values ( , , , )",

dataTableName);

cmdm.Parameters.AddWithValue("p1", OleDbType.Integer).Value = numeric_id;

cmdm.Parameters.AddWithValue("p2", OleDbType.Char).Value = this.txtBoxFindWords.Trim();

cmdm.Parameters.AddWithValue("p1", OleDbType.Date).Value = dated;

cmdm.Parameters.AddWithValue("p1", OleDbType.Char).Value = String.Empty;

cmdm.ExecuteNonQuery();

With the default settings on VFPOLEDB driver you need to explicitly specify values for columns (default is "set null on" and could be set on the connection) and that provides "ensurance" for you.





Re: Field validation rule.

AlexBB

CetinBasoz wrote:

Alex,

On the database level not null is the way to enforce it. Then you'd say that it accepts empty values as valid values. Yes that is true but excluding datetime datatype, you can have "empty" values on other backends too as valid values. You could specify !empty() as a validation rule per field on the database level but it'd be more than cumbersome to do it. Instead many do control it at application level rather than db level.

I'm sorry to say that your commandText construction is not the standard way of doing that. Instead a standard approach uses parameters collection. ie:

Code Block

DateTime dated = DateTime.Now.Date;

cmdm.CommandText =

String.Format("insert into {0} (numeric_id, named, dated, memo1) values ( , , , )",

dataTableName);

cmdm.Parameters.AddWithValue("p1", OleDbType.Integer).Value = numeric_id;

cmdm.Parameters.AddWithValue("p2", OleDbType.Char).Value = this.txtBoxFindWords.Trim();

cmdm.Parameters.AddWithValue("p1", OleDbType.Date).Value = dated;

cmdm.Parameters.AddWithValue("p1", OleDbType.Char).Value = String.Empty;

cmdm.ExecuteNonQuery();

With the default settings on VFPOLEDB driver you need to explicitly specify values for columns (default is "set null on" and could be set on the connection) and that provides "ensurance" for you.

Cetin hi,

Thank you. I do what you are suggesting on some other ocsasions especially if I use Stored Procedures in Sql Server. Over there it is the only way.

With the CommandText this method is inconvenient, albeit it may be "substandard" in you judgment. The reason is: frequently I have to use numtiple parameter sets that are determined at runtime only. There might be as many as 50 numeric_id's or so. I use StringBuilder Class to build CommandText for such eventualities. It pays. In the example I gave I removed the StringBuilder for simplicity's sake.

OK, this is the good news: !EMPTY (numeric_id) did work. Why is it so cumbersome to do it on the DB level I do not understand it. To do it in the application is quite unsafe. The code may be changed inadvertently and the validation rule is out of window.

OK, I just set it up on DB level for ALL my tables in that DB. One more problem down, 10,000 more to goSmile






Re: Field validation rule.

AlexBB

dni wrote:

As I said I don't care about stars...and you "can" use ALTER TABLE ....SET CHECK (from MS help):

"SET CHECK lExpression3

Specifies the table validation rule. The lExpression3 parameter must evaluate to a logical expression and can be a user-defined function or a stored procedure."

I don't have time now to write a example....Feel free to add a "negative review"...

While I've been away for a few days somebody marked this post as an answer! NO, NO, NO! It is a bogus answer! Just unmarked and marked it as unhelpful which it is.

Look how CetinBasoz does it! Learn a lesson!






Re: Field validation rule.

CetinBasoz

I personally find it cumbersome to do that at db level, your patience might be higherSmile

It is not unsafe to do that application level. We are working in an OOP world, but unfortunately both in VFP and .Net data operations are not so OOPish, more direct access flat structures. However in .Net there is also ObjectDataSource which you can decorate with attributes, properties (get and/or set accessors), verification methods which in a way takes the data into an OOP state.

!empty() is a simple rule at first glance (but hides some pitfalls in it - ieStick out tongueut in a nonprintable character like 'tab') but what if it was instead:

FirstName should be at least 2, at most 30 characters with only alpha letters. Still an easy enforcement in VFP or SQL server or any other backend. Sooner or later it gets more complex and one field's value depends on another field on the same table or another table. Triggers might come to the rescue, but do they really In VFP trigger implementation is harder, in SQL server much better, Still in SQL server it's not trivial to create a rule that depends on multiple tables, fields and whatsoever. If you can all write these at db level than you're already an expert where I want to beSmile I can't and I find it easier to control at my biz objects' level.

I also don't like the data is validated in multiple places. For example in !empty() case would you wait to get an exception from db layer Probably not and block it directly at biz layer and/or maybe even at UI layer. Sorry that's my nature to look at data storage layer mostly as a storage layer onlySmile




Re: Field validation rule.

AlexBB

CetinBasoz wrote:

I personally find it cumbersome to do that at db level, your patience might be higher

It is not unsafe to do that application level. We are working in an OOP world, but unfortunately both in VFP and .Net data operations are not so OOPish, more direct access flat structures. However in .Net there is also ObjectDataSource which you can decorate with attributes, properties (get and/or set accessors), verification methods which in a way takes the data into an OOP state.

!empty() is a simple rule at first glance (but hides some pitfalls in it - ieut in a nonprintable character like 'tab') but what if it was instead:

FirstName should be at least 2, at most 30 characters with only alpha letters. Still an easy enforcement in VFP or SQL server or any other backend. Sooner or later it gets more complex and one field's value depends on another field on the same table or another table. Triggers might come to the rescue, but do they really In VFP trigger implementation is harder, in SQL server much better, Still in SQL server it's not trivial to create a rule that depends on multiple tables, fields and whatsoever. If you can all write these at db level than you're already an expert where I want to be I can't and I find it easier to control at my biz objects' level.

I also don't like the data is validated in multiple places. For example in !empty() case would you wait to get an exception from db layer Probably not and block it directly at biz layer and/or maybe even at UI layer. Sorry that's my nature to look at data storage layer mostly as a storage layer only

Thank you, Cetin.

It makes a lot of sense however, I cannot trust myself in terms of vigilace. I frequently change codes for the sake of improvement and it may screw any rule to the point that some undesirable records will slip in.

I found it very easy to write a rule for one field, however, when I tried to do it for two fields it caused an error. The error was at runtime. It said (contrary to what the data was) that the validation rule was violated for the first field (numeric_id).

!EMPTY (numeric_id) .AND. !EMPTY (named) <== did not work.

I think it is a bug.






Re: Field validation rule.

CetinBasoz

Oh that is one of the 'harder' to express dependant validation. Just think of this:

On a new record either one is null. Assuming you prevented nulls and put 'empty' instead, numeric_id is dependant on named. Of somehow it gets focus first you're locked. Then instead, look at table valid rule. It's evaluated at row level.