Problem trapping not null constraint

method · June 29th, 2005, 05:26 AM

Hi guys i got a form and sub form. When i leave the field hours empty in the sub form and try to click on close or some where in the form i get this error:

MsgBox "Primary key violation", , "Explanation"

instead of

MsgBox "Violation of Not Null constraint", , "Explanation"

i want to trap not null error and prompt the user that he has to enter value for hours field but aparently it does not. i be happy if some one help me fix this error .Thanks

picture of form in action:

http://i5.photobucket.com/albums/y18...nullerror1.jpg
http://i5.photobucket.com/albums/y18...nullerror2.jpg

code for on error :

Code:

Private Sub Form_Error(DataErr As Integer, Response As Integer)


Select Case DataErr
    Case 3162
        MsgBox "Violation of Not Null constraint", , "Explanation"
    Case 3146
        MsgBox "Primary key violation", , "Explanation"
    Case 3155
        MsgBox "Trigger violation: 'Wages of the employee in this project is not existed. You must enter data of hourly wage first'", , "Explanation"
    Case Else
        MsgBox "Other errors"
    End Select


End Sub

leehambly · June 29th, 2005, 06:00 AM

Validate the data before running your SQL insert code.

If there is a problem with inserting nulled fields into your SQL table, either default the field to 0 using nz(field, 0), OR set your field to nullable OR pop up a msgbox informing the user of data entry issue with running the sql insert code. It is your decision, though, based on your requirements.

Why is the field not nullable, when you are trying to set it to be nullable?

method · June 29th, 2005, 06:17 AM

Quote:

quote:Originally posted by leehambly
Validate the data before running your SQL insert code.

If there is a problem with inserting nulled fields into your SQL table, either default the field to 0 using nz(field, 0), OR set your field to nullable OR pop up a msgbox informing the user of data entry issue with running the sql insert code. It is your decision, though, based on your requirements.

Why is the field not nullable, when you are trying to set it to be nullable?

Thank u for u reply. Well i am using bounded form and i wonder what u mean by Validate the data before running your SQL insert code . I did not put any insert code in my code behind for the form. The field hours in the sql server 2000 db has option no null . Which mean it does not accep null value. What i am trying to do is prompt the user that they fill that data not to leave it empty. I be happy if u tell me what is wrong with my Form_Error function. i put the code for null error and it should detect it but it does not !!Thanks

leehambly · June 29th, 2005, 07:14 AM

no need to repost the whole reply. I assumed you were runnning an insert rather than a direct write to a sql table, the principles stand the same though...

"validate" means to check that the data is correct before you accept it as correct. (Personally, I would worry greatly that you do not know what validate means, especially when you are designing a database.)

In this instance, it would mean checking to see if there is a suitable value in your subform's textbox, ie: a number and not null. Since you are using bounded and the field is non-nullable, you NEED to put some validation into your form somewhere prior to the user moving away from the reocrd, else the user will not be able to move away from the record.

Having said that though, mny original suggestion of defaulting the value to 0 still stands. Your users will remember to do it properly if you set everything they dont do properly to zero! You can set the default either on the table in SQL or on the form, again entirely up to you.