inserting blank values into access database

[monkeymagic2222]

Hi,

I have created a number of ASP pages on our intranet that write to several Access 97 databases. When adding new records to a database through the ASP pages sometimes we don't have data to put in certain fields so need to leave them blank. The problem is unless there is a value in these fields, the ASP page fails trying to write the data. I think this only happens on numeric fields. I have checked the database and made sure I haven't forced any of the fields to "Only Accept Values" which I haven't.

On one of the pages I added some If statements to the ASP to say if the field was blank then automatically insert a "0" but I wondered if there was a better way around this?

Thanks

 

[Tarwn]

If your creating an SQL string on the fly the problem may be with ASP not your db. If you insert a null Request entry into a string it generally turns the whole string null. Try hardcoding an sqlstring, for example:
INSERT INTO MyTable(myNumber,myOtherNumber,myText) VALUES(1,,'text')
That should insert a null into the second numeric field.

If you are using the .Open and .AddNew methods than simply don't assign a value to the one you want to leave null:

rsMyTable.AddNew
rsMyTable("myNumber&quot = 1
rsMyTable("myText&quot = 'text'
rsMyTable.Update

That should leave the second number null also.

-Tarwn ------------ My Little Dictionary ---------
Extreme Programming - (1)Trying to code before my second cup of coffee. (2) While(1){ Ctrl+C; Ctrl+V; }
FAQ - Web-ese for "Forget Asking Questions, I am to busy"

 

[monkeymagic2222]

Hi,

Sort of following on from this. I am retrieving records using form.request("field_name&quot from another form and updating existing records in the database using a couple of lines of SQL. Is there a way on my page that updates the database to write some sort of routine that will only update the fields with data in them and ignore the empty ones?

 

[TonyU]

[tt] Or you could include a hidden field inserting a value in that field of "" or "." or "0"

<%=Tony%>