Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem when not filling in required field

    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.

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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')"

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

    Code:
    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
    ...

  • #5
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #6
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #7
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Cheers Raf - all is sorted now

    You've been a great help!

  • #8
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You're welcome.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •