Updating ASP pages ignoring blank fields 2

[knoid]

Hi, I have created a web page that accesses an Access databse and everything works fine except one thing. If a form field is blank when someone has pulled up a record to edit, there is an ODBC error upon hitting hte update button. Now, there is a SQL statement written for it and it goes something like this:

UPDATE Results SET
LastName='::LastName::',
FirstName='::FirstName::',
MI='::MI::'

WHERE Email='::Email::'

Now, say the person doesn't have a middle initial, when updating the other information, if it is blank, the error occurs and the rest of the data is not updated. This isn't the only field left blank, and I'm currently working aorund it by forcing a dash to be put in each field by default. What sort of SQL statement will handle a blank field? I was thinking of trying to write one that checked to see if the field was empty and if so, then don't attmpt to update that field. Something like this:

UPDATE Results SET
{IF ('::LastName::' IS NOT NULL)
{LastName='::LastName::'}
ELSE {LastName='-'}},
FirstName='::FirstName::',
MI='::MI::'

WHERE Email='::Email::'


Thanks in advance for any help you can give.

 

[link9]

I would probably just check for all that with vbscript before you attempt the update... the default value for any form element is "" -- so if you check to see if

request.form("theElementName") = ""

, then you will know if they didn't fill it in. If it is blank, then assign the resulting vbscript variable some other value, like " " or whatever you want to be the default value --

if request.form("theElementName") = "" then
    theVariable = " "
else
    theVariable = request.form("theElementName")
end if

Then you would run the insert on the already checked vbscript variable(s), rather than reading the values directly from what they input and everything should be ok...

also, you might want to make sure that your database has proper data rules set up in it... As you said, not everyone has a middle name, or maybe they don't want to give it if they do, so it would stand to reason that you should allow nulls in that field of the database. If you do, then you should be able to insert whatever you want into the field without raising any errors. It's still best to read the variables and check them, though, before the insert... just to be sure that you know what you are putting in your database.

good luck!
Paul Prewett

 

[vasah20]

I'm not sure about this, but try inserting NULL for the values that are left blank...

let me know how this turns out.

 

[ram123]

turn the property 'allow zero length string' to "yes" in table design mode

 

[elguiri]

Do what ram123 (Programmer) says.

It is an Access DB table structure problem which is giving you the error. If you 'allow zero length string' in every field of the Access table structure the error will no longer occur

 

[scroce]

concur with RAM 123. It's most likely the easiest way. I was having exactly the same error. It worked for me. How much water would there be in the ocean if it weren't for sponges?