...

View Full Version : Problem when not filling in required field



holty
10-13-2003, 11:30 AM
I having a wierd problem that I have never experienced before...

I'm updating data in an Access database and when I don't fill in a field that is not required I get:

'tblCustomer.MobileNumber' cannot be a zero-length string.

If I fill in this field all is ok... (I realise this is because its blank)

In the database the field is set to 'Required: No'...

Will I have to put default values in?

Usually if the field is left blank it doesn't insert anything into that field in the database - dunno why this is coming up now.

raf
10-13-2003, 01:26 PM
Can you post the insertquery that you use (else it's dificult to help you).

My geuss is that you have included the columnname in the columns-list, but didn't include a value in the values list. Like
sql="INSERT INTO table (var1, var2, var3) VALUES ('bla','bla')"
where var2 is the non-required column.

If you don't want to insert a value in that column, then you can just do
sql="INSERT INTO table (var1, var3) VALUES ('bla','bla')"

holty
10-13-2003, 02:07 PM
raf - its an edit page, with some fields optional (its up to the user)

heres my script

strSQL = "SELECT FIRST_NAME, FAMILY_NAME, DATE_OF_BIRTH, TITLE, SEX, FIRST_NATIONALITY, SECOND_NATIONALITY, ETHNIC_ORIGIN, ADDRESS, TOWN, COUNTRY, POST_CODE, TELEPHONE, MOBILE_TELEPHONE, EMAIL FROM tblCustomer WHERE ID = " & request.form("ID") & ";"

rsUpdateEntry.CursorType = 2
rsUpdateEntry.LockType = 3
rsUpdateEntry.Open strSQL, adoCon

rsUpdateEntry.Fields("FIRST_NAME") = Request.Form("txtFirstName")
rsUpdateEntry.Fields("FAMILY_NAME") = Request.Form("txtFamilyName")
rsUpdateEntry.Fields("DATE_OF_BIRTH") = request.form("cboDOBDay") & "/" & request.form("cboDOBMonth") & "/" & request.form("cboDOBYear")
rsUpdateEntry.Fields("TITLE") = Request.Form("cboTitle")
rsUpdateEntry.Fields("SEX") = Request.Form("cboSex")
rsUpdateEntry.Fields("FIRST_NATIONALITY") = Request.Form("cboFirstNationality")
rsUpdateEntry.Fields("SECOND_NATIONALITY") = Request.Form("cboSecondNationality")
rsUpdateEntry.Fields("ETHNIC_ORIGIN") = Request.Form("cboEthnicOrigin")
rsUpdateEntry.Fields("ADDRESS") = Request.Form("txtAddress")
rsUpdateEntry.Fields("TOWN") = Request.Form("txtTown")
rsUpdateEntry.Fields("COUNTRY") = Request.Form("cboCountry")
rsUpdateEntry.Fields("POST_CODE") = Request.Form("txtPostCode")
rsUpdateEntry.Fields("TELEPHONE") = Request.Form("txtTelephone")
rsUpdateEntry.Fields("MOBILE_TELEPHONE") = Request.Form("txtMobileTelephone")
rsUpdateEntry.Fields("EMAIL") = Request.Form("txtEmail")

rsUpdateEntry.Update

raf
10-13-2003, 03:41 PM
Euh...

Any reason why you don't use an updatequery ?
I never used this sort of recordset updating, but i think the problem is that only formfields that are 'set' (= that hold a value) are included in the formcollection. So if you didn't fill in a value for
"txtTelephone" in the form, then Request.Form("txtTelephone") will return nothing at all. So i think you need to check them all to see if they were set, and then dynamically build an updatestatement and execute that.

Or for your code, something like



dim, firstname, famname,birtdate, ..., email
if len Request.Form("txtFirstName") > 0 then
firstname=Request.Form("txtFirstName")
else
firstname = ""
end if
if len Request.Form("txtFamilyName") > 0 then
famname = Request.Form("txtFamilyName")
else
famname=""
end if
...

rsUpdateEntry.Fields("FIRST_NAME") = firstname
rsUpdateEntry.Fields("FAMILY_NAME") = famname
...

holty
10-14-2003, 08:38 AM
Raf,

I've done the changes that you suggested as it made sense but I'm still getting problems... heres a quick example

For one field

Dim FamilyName
if len(Request.Form("'txtFamilyName'") > 0) then
FamilyName = Request.Form("txtFamilyName")
else
FamilyName = ""
end if
...

rsUpdateEntry.Fields("FAMILY_NAME") = FamilyName

...

Here is the error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Field 'tblCustomer.FAMILY_NAME' cannot be a zero-length string.

Any clues?

raf
10-14-2003, 11:39 AM
this
if len(Request.Form("'txtFamilyName'") > 0) then
should be
if len(Request.Form("'txtFamilyName'")) > 0 then
in any case.

Try it like this. If you still get the error, then print the executed sql statement . Like

sql="update table set ..." ' your existing querystring
response.write sql
response.end

Then open that page in your browser and look at the printed sql-query + maybe copy it and post it here.

holty
10-14-2003, 12:14 PM
Cheers Raf - all is sorted now :)

You've been a great help!:thumbsup:

raf
10-14-2003, 12:17 PM
You're welcome.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum