This article is the second in a series that discusses a number of issues which will
be faced by either an Access programmer when moving to .net or by a .net programmer
when converting an Access interface to .net.
Here we discuss data binding, which is the ability to bind a .net control field to a
field in the database, often referred to as two-way binding,
where data can be updated in addition to being viewed.
NOTE : Binding problems and updateability discussed here are within the context of
using a SQLDataSource, use of either an LINQDataSource or EntityDataSource removes this problem.
However, there are other issues to consider when using alternative data sources; described
in other articles on this site.
Field names
Data binding is unforgiving in terms of whether a field name has spaces or special
characters in it. So if attempts to save data fail then this is the first culprit to consider.
The following examples will result in data binding errors.
The first example has a space in the field name, and the second example has a
field which has been included twice in the SQL. The third example contains special characters.
An example is shown below of binding errors when displayed before executing a page.
It is possible to start generating a work around by aliasing the offending field name,
but this will make your application code more complicated; an example is shown below.
Top of Page
Memo fields
In SQL Server 2000 a memo field must be bound to a TEXT data type.
But in SQL Server 2005 memos can be bound to VARCHAR(MAX) data types.
Visual studio controls do NOT support write-back to TEXT fields, so the
best advice here is to use SQL Server 2005 and VARCHAR(MAX) fields and you
will get write-backs which succeed.
Below we see a simple example of a control bound to a TEXT data type in SQL Server.
Although the data is correctly displayed, when an update
is attempted this results in the following error message.
This can be very simply corrected when using SQL Server 2005 or later versions by changing the data
type from a TEXT to VARCHAR(MAX) data type (in fact the error message points to
this as a possible solution).
Top of Page
Image data fields
Visual Studio controls do not support bound image data in a database (which is a feature in Access
which developers may have taken advantage of, although there are very good reasons for not doing
this is Access, as it can make the database grow to an unreasonable size),
so image fields if held in a database must be exported to physical files and controls added
to display the image data. In order to make this work you will need to first save your images
as files on the server, then replace the images in the database with a url path which can be
used with an image control to display the image. When it comes to uploading new images
you will need to add special processing code to do this.
Below you can see an example of the problem with binding to image data in the database,
here an image is shown as a System.Byte[]; this is connected with limitations on information
that can be transferred in the viewstate, thinking about how this could be done in the viewstate
for large volumes of data indicates that Visual Studio's lack of support here is not unreasonable!
Top of Page
Data binding and drop down lists and NONE
This is probably one of the simplest modifications that can be made to a dropdownlist,
it enables the selection of NONE, which results in a value of NULL being written
into the database.
Shown below as part of an edit item template.
Below we see the resulting drop down.
And finally we can see the NULL value being written into the underlying table when NONE is selected.
Top of Page
Optimistic concurrency and NULL values
Visual Studio allows records to be written back to the database supporting
two locking mechanism as shown below; either you write back with lock detection
or you don't!
(In Visual Studio 2008 we can use LINQ to utilise TIMESTAMPS which removes all these problems).
Assuming that we do not just want to overwrite the record, but
detect potential write-conflicts where two users are editing the same data (recommended practise).
Visual Studio generates parameters to represent the field values in a form’s SQL,
and the write-back or update command then substitutes these parameters back into the SQL.
So let us look at a very simple form.
And try editing this record, noting that the category is NULL.
You may be suprised when the update fails to save our changes or give any warnings!!!
To understand this we must look at the SQL which Visual Studio has generated.
Because the @original_category = NULL, and the existing field value is NULL,
the where clause now reads
AND NULL = NULL
It turns out that this match in SQL NEVER gives a match ( NULL = NULL is never TRUE in SQL),
so the record is never found and thus never update!!
There are a couple ways to tackle this problem.
Probably the best know solution is to re-write the SQL as follows :-
([Category] = @original_ Category) OR ([Category] IS NULL and @original_ Category IS NULL)
This is a bit clumsy, and we have pioneered our own improved version of this which is generated
by MUST+WEB.
COALESCE([Category],'') =
COALESCE(@original_ Category,'')
For a numeric this becomes
COALESCE([Sales],0) =
COALESCE(@original_ Sales,0)
And for a date field
COALESCE([Startdate],'1 january 1900') =
COALESCE(@original_ Startdate,'1 January 1900')
This works because the where clauses become
'' = '' (where this indicates an empty string)
0 = 0
'1 january 1900' = '1 january 1900'
Which are all always true!
Below is an example of the code generated by MUST+WEB to resolve these problems.
Of course the better solution to all of this is to use timestamps supported in LINQ.
Top of Page
MUST+WEB solves all of these problems, our code generators
mark any fields with spaces or special characters as read-only, so you can see that
they can not be edited. All drop downs are generated with the NONE option.
All optimistic write-back code automatically uses COALESCE functions to avoid
write-back failures. One more reason that MUST is essential for successful .net conversions.
Top of Page