How to solve DataSet Designer SQL errors?

[David Carr]

Hi there,

I have writing easy to moderate SQL in the DataSet Designer and Query
Builder, using a Jet 4.0 connection to an Access 2003 DB. I am running
VB.NET 2005 sp1.

As soon as I try to do anything more than the most trivial SQL (e.g. simple
JOINs, more than 1 parameter other than in the WHERE clause, etc.), the
Designer/Query Builder balks giving SQL parsing error messages. This means
that the Fill, GetData() is not set up properly and compiler errors prevent
me from running my app. This is despite the fact that the SQL is perfectly
fine (it runs in Access) and in some situations it is possible to 'trick'
the Designer (ref. link below).

Aside from the critical issue of my app not running, I am not able to
leverage strongly typed DataSets, and all the useful commensurate objects
(e.g. DataTable, data row). I have been promoting this approach within our
development teams, but all these problems make significantly reduce
confidence in using these objects and the Designer. Nor it is sensible to
use a blend of approaches in my app (e.g. trivial SQL through strongly-typed
DataSets and the rest explicitly written in code) from a software
design/maintenance perspective. If this can't be solved, I'll look at
dropping use of the Designer and going back to hand-coded SQL.

Any and all feedback that provides solutions to this problem (e.g. is there
an update/patch to the Designer?) would be appreciated.

Thanks in advance,
David

Newsgroup posting "Dataset designer and complex Stored procedure" on
microsoft.public.dotnet.framework.adonet
http://groups.google.ca/group/micro..."dataset+designer"+sql+error#68917fe53b75058c

 

[Jim Rand]

My opinion backed by success:



Except for trivial cases, the query builder is wholly inadequate for
generating SQL to configure data adapters.



Here is what I did.



1) Gave up on the whole bundled table adapter / dataset approach. Went back
to the 2003 style of dragging data adapters onto a component designer
surface. This eliminated some of the really nasty problems of the bundled
table adapter / dataset approach. However, it still did not address the
issue of correctly and EASILY generating the sql to configure the data
adapter.



2) Analyzed the implied data structures within the data adapter sql
designer. Built my own data structure in Microsoft Access that modeled this
structure.



3) Analyzed the data adapter code generated by Microsoft's designer.



4) Built a bunch of forms that a) gets the meta-schema from the underlying
database (SQL Server,...) b) creates a data adapter set (multiple adapters
needed for a single dataset with multiple tables) and c) added a button
labeled "Generate Code". Pushing that button executes a VBA function that
generates a perfect C# class and adds the code to the VS 2005 project.
Instantiating that class creates all my data adapters available for use in
filling and updating my dataset tables. This includes not only the SQL but
all the table mappings and parameters as well.



What used to take hours / days takes minutes.



Well worth the investment. I'm in the process of building a new version of
this that is even better that uses XML, XSLT and an adapter factory.



Jim