2. ASPRunnerPro General questions
  3. Force blank space to avoid NULLs
This topic is locked

Force blank space to avoid NULLs

 		5/18/2007 2:38:34 PM
ASPRunnerPro General questions
B
 berkeleyjw author

Hi. I haven't tried this in a while but I'll ask here to be sure...

Right now my database allows NULL values in strings. I do not want this to be the case. I would rather force a blank space into an empty field to avoid all the problems with NULL comparisons, etc. I had originally set up the database columns to avoid NULLs but then if I left a field blank the ASPRUnner pages would try to pass a NULL value in during the insert.

I first tried to work around this by using the event scripting. I would test for NULL (or length of string = 0). If the length was zero, I reassigned a " " to the dictionary item.

But, it seems that all of the entries get trimmed before being written to the database, so I ended up with the NULLs again.

Is there a way to work around this? My system is starting to get more use and I really want to get rid of all the NULLs.

THanks for any assistance.
Admin 5/18/2007

Jim,
I feel some confusion here.
ASPRunnerPro do not trim values before they written to the database. Moreover even if it's trimmed it should write an empty string to the database instead of NULL.
So the question is - are you talking about NULLs or empty strings?
B
 		berkeleyjw author 5/21/2007

Sergey,
Thanks for the info.
I am using SQL server as a back end. I had originally set up my columns to not allow nulls. But then, when I left fields blank and tried to save a record, SQL Server returned an error saying that the column did not allow nulls. I believe SQL Server tries to convert an empty string to a null when written to the database in an insert statement (I know SQL server has some funny issues with nulls vs empty strings).
I think what I have to do to solve the problem is to modify the add.asp and edit.asp source files as follows:
Change the line that says:

if isnull(strValue) then strValue=""
To this:

if isnull(strValue) then strValue=" "
-or-
in ASP Runner, maybe I have to force the default value to " " (single space) and then hope nobody backs over the space and leaves a null value.
-or-
In SQL Server, actually set a default value for the fields at the database level. Set to a single space string.
I think this is just an issue with SQL server.

  • Jim
B
 		berkeleyjw author 5/21/2007

This seems to work:
In BeforeRecordAdded event, create the following function:
Function NullToBlank(sInput)

if isnull(sInput) or len(sInput)=0 then

NullToBlank = " "

else

NullToBlank = sInput

end if

End Function
Then in the event code, create several lines like the following:
dict("middle_name") = NullToBlank(dict("middle_name"))
I had been having some issues with this a while ago but it seems to be working OK now. Sorry for the trouble.