Newbie question regarding blank fields in web database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

paul - 04 Jun 2006 21:56 GMT

Unsure whether I should ask this question here or in the asp forums anyway
here goes...
Access is not my strong point but I have got it to do most of what I need
apart from the fact that when I create an update page in asap and then try
and update it in a browser I get an error if any of the fields in the update
page don't have any data in them.
As there are several fields and over 500 records in the database is there a
way in Access where it can automatically insert a space in all the empty
fields which would then stop the asp page from crashing. Or does someone have
a better suggestion of solving this problem.
All help appreciated
Regards
Paul

Reply to this Message

Steve Schapel - 04 Jun 2006 22:17 GMT

Paul,

It is not clear where the source of the problem is. The exact wording
of the error message might have given a clue.

But no, you can't just write a space by itself into a field in a Jet
database.

It could be that the fields in question have their Required property set
to Yes, or a Validation Rule applied, and this is preventing the record
from being written without these conditions being satisfied.

Or, it could be the ASP code that writes the data to the database, which
has not been written to allow for nulls.

Signature

Steve Schapel, Microsoft Access MVP

> Unsure whether I should ask this question here or in the asp forums anyway
> here goes...
[quoted text clipped - 9 lines]
> Regards
> Paul

Reply to this Message

paul - 04 Jun 2006 22:39 GMT

Thanks Steve for making me aware of this I think its an asp problem correct
me if I'm wrong but the browser error message says;
" Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Field 'PromoterInput.website'
cannot be a zero-length string.
/onthescene/UpdateForm.asp, line 133 "

And on line 133 on the actual web page it says: MM_editCmd.Execute

Please let me know your thoughts.

> Paul,
>
[quoted text clipped - 24 lines]
> > Regards
> > Paul

Reply to this Message

Steve Schapel - 04 Jun 2006 22:53 GMT

Paul,

Check the design of your table. You can set the Allow Zero Length
property of the field(s) to Yes. That might solve the problem... unless
of course for some reason having a zero length string in these fields in
the table causes a problem elsewhere in the application. But anyway,
that's probably the simplest solution, so I'd try that first.

Signature

Steve Schapel, Microsoft Access MVP

> Thanks Steve for making me aware of this I think its an asp problem correct
> me if I'm wrong but the browser error message says;
[quoted text clipped - 5 lines]
>
> And on line 133 on the actual web page it says: MM_editCmd.Execute

Reply to this Message

paul - 05 Jun 2006 00:35 GMT

Thanks Steve,
I'm pleased to say that your suggestion of changing the fields to 'allow
zero length' sorted out the problem.
Cheers
Paul

> Paul,
>
[quoted text clipped - 13 lines]
> >
> > And on line 133 on the actual web page it says: MM_editCmd.Execute

Reply to this Message

Steve Schapel - 05 Jun 2006 02:06 GMT

Thanks for letting us know, Paul. Best wishes for the rest of your project.

Signature

Steve Schapel, Microsoft Access MVP

> Thanks Steve,
> I'm pleased to say that your suggestion of changing the fields to 'allow
> zero length' sorted out the problem.
> Cheers
> Paul

Reply to this Message

paul - 05 Jun 2006 15:58 GMT

HI Steve,
It looks like I've got the same problem with fields other than text and memo
fields. as I also have another form connected to the database that has
several tick boxes in it which when i look in the design view it doesn't give
me an option to 'allow a zero string' in the properties as it does in the
text boxes which is understandable but how do I stop the same thing
reoccurring.
All help appreciated.
Paul

> Thanks for letting us know, Paul. Best wishes for the rest of your project.
>
[quoted text clipped - 3 lines]
> > Cheers
> > Paul

Reply to this Message

Steve Schapel - 05 Jun 2006 21:03 GMT

Paul,

It's probably not exactly the "same problem". I assume you are not
getting an error message complaining that the Yes/No field can't contain
a zero length string? If the ASP application is trying to write data to
the table which is invalid according to the table's design, I guess we
need to identify exactly where that is happening. Once again, the error
message might give a clue. If you think it is to do with the Yes/No
fields, you would need to identify the values that the ASP script is
trying to write to these fields. Sorry, not much of an answer!

Signature

Steve Schapel, Microsoft Access MVP

> HI Steve,
> It looks like I've got the same problem with fields other than text and memo
[quoted text clipped - 5 lines]
> All help appreciated.
> Paul

Reply to this Message

paul - 06 Jun 2006 00:00 GMT

Unfortunately Steve the error message doesn't give any clue which I might add
is very annoying and wondered whether the error pages are created by the web
host/server and whether they differ depending which server/host you are with
as they are not helpful at all.
Error Page text below:
'Internal Server Error
The server encountered an internal error or misconfiguration and was unable
to complete your request.
Please contact the server administrator to inform of the time the error
occurred and of anything you might have done that may have caused the error.
More information about this error may be available in the server error log.'

Regards
Paul

> Paul,
>
[quoted text clipped - 16 lines]
> > All help appreciated.
> > Paul

Reply to this Message

Steve Schapel - 06 Jun 2006 09:01 GMT

Paul,

In that case, I'm sorry, I don't have any further suggestions. I think
you will have better chance of good help in an ASP-related forum. You
will need to give details of the code that is running on the ASP page,
and what it is supposed to be doing.

Signature

Steve Schapel, Microsoft Access MVP

> Unfortunately Steve the error message doesn't give any clue which I might add
> is very annoying and wondered whether the error pages are created by the web
[quoted text clipped - 7 lines]
> occurred and of anything you might have done that may have caused the error.
> More information about this error may be available in the server error log.'

Reply to this Message

onedaywhen - 05 Jun 2006 11:46 GMT

> But no, you can't just write a space by itself into a field in a Jet
> database.

I've been pondering that one for a few minutes: what could you possibly
mean? I've drawn a blank. The statement is quite obviously false.

If what you say is true then the following would create an impossible
thing: a Jet database with a space by itself in a field:

Sub Curious()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb;"

With .ActiveConnection

' Dummy one-row temp table for bulk inserts
.Execute _
"CREATE TABLE Curious (" & _
"space_only CHAR(1) DEFAULT ' ' NOT NULL," & _
" CONSTRAINT space_only__must_be_a_space" & _
" CHECK (space_only = ' '));"

.Execute _
"INSERT INTO Curious VALUES (' ');"

End With
Set .ActiveConnection = Nothing
End With
End Sub

Actually, as it stands, the column cannot contain anyting but a space
by itself.

Jamie.

--

Reply to this Message