Help with GridView empty fields and non-nullable columns

[Harlan Messinger]

I've got a legacy Access database that I want to use as the source for a
GridView. The character columns in the database are usually configured
not to allow null values, so the legacy application records zero-length
strings instead.

In my GridView I have

UpdateCommand="UPDATE [Ppt] SET [pptCode] = ?, [name] = ?, [title] = ? ..."

It is acceptable for the *title* column to be empty--that is, an empty
string, not a null.

When I load my test page and click the Edit link for a row in which the
title is empty, and then click the Update link, I get

"The field 'Ppt.title' cannot contain a Null value because the Required
property for this field is set to True. Enter a value in this field."

Fair enough. So I change UpdateCommand:

UpdateCommand="UPDATE [Ppt] SET [pptCode] = ?, [name] = ?, [title] =
coalesce(?, '') ..."

I get

"Undefined function 'coalesce' in expression."

Fair enough again, since coalesce isn't an Access function. So then I
try Nz, which I remember to be the equivalent Access function:

UpdateCommand="UPDATE [Ppt] SET [pptCode] = ?, [name] = ?, [title] =
nz(?, '') ..."

That gives me:

"Undefined function 'Nz' in expression."

Then I tried IsNull and got

"Wrong number of arguments used with function in query expression
'IsNull(?, '')'."

Is there a solution?

 

[Harlan Messinger]

I've got a legacy Access database that I want to use as the source for a
GridView. The character columns in the database are usually configured
not to allow null values, so the legacy application records zero-length
strings instead.

In my GridView I have

Correct: In my AccessDataSource control.

 

[Harlan Messinger]

Correct: In my AccessDataSource control.

I tried

<asparameter ConvertEmptyStringToNull="false" Name="title"
Type="String" />

and it didn't make any difference.

 

[Harlan Messinger]

I tried

<asparameter ConvertEmptyStringToNull="false" Name="title"
Type="String" />

and it didn't make any difference.

Neither did this in the GridView:

<asp:BoundField ConvertEmptyStringToNull="false" DataField="title"
HeaderText="title" SortExpression="title" />

 

[Harlan Messinger]

Neither did this in the GridView:

<asp:BoundField ConvertEmptyStringToNull="false" DataField="title"
HeaderText="title" SortExpression="title" />

Never mind. This did work. The error I got was from the next field down.

Sorry for the shower of messages. I just kept getting a new lead each
time I'd posted one.