System.Data.SQLite

I've created a DataSet xsd in Visual Studio 2005. When trying to add a TableAdapter (rightclick on the dataset designer surface, select Add->TableAdapter....) using the trick with subqueries to have Insert/Update/Delete statements autogenerated instead of using joins (which makes us need to write them manually) described in step 5 here http://www.asp.net/learn/dataaccess/tutorial01cs.aspx?tabid=63 by Scott Mitchell, I get an SQL parsing error saying "Error in SELECT clause: Expression near SELECT...".

Altough the query in question runs perfectly fine from the commandline or DBManager Proffesional. Is this an error in the Designer?

regards,

wwfDev

Can you post the exact query giving you an error in the designer?  I'll try and duplicate it here.

Robert

Hi,

I have a simple test-database with two tables (NB! this is from the top of my head as I don't have my developing computer with me at this moment):

TABLE #1:
Event
   Id
   Title
   CreatedById
   Content

TABLE #2:
User
   Id
   Username
   Fullname
  
The query I'm trying to produce is as follows:

SELECT e.Title, e.Content, (SELECT u.Fullname FROM User u WHERE e.CreatedById = u.id) AS CreatedBy FROM Event e

This works when run from a SQLite manager tool such as DBTools Manager, but not from within the TableAdapter Wizard (and/or querybuilder) in the Designer.

Please tell me if there is some errors in the information I've provided, and I will post the exact information from my developing machine as soon as I get to it.

Regards,
wwfDev

The query works fine in SQLite, and SQLite can parse the command text and return rows.  If you try to execute the command in the querybuilder, it will give you a parsing error -- but if you hit "Continue" or "OK" or whatever, it will execute fine and give results.

It's strictly a limitation in VS's querybuilder/parser.  The underpinnings of VS's query builder and dataset designers are notoriously difficult to wedge into, but I'll see if there's anything I can do about it.

Robert

Hi again,

Yes, your observation is the same as mine - I must add that alltough the query executes fine if we hit "Continue" or "Ok", the INSERT, UPDATE and DELETE queries are not generated (hence there are no methods for these generated to the dataset). I highly appreciate youre investigation on this! For now a workaround which seems to do OK is to use clean "SELECT * FROM SomeTable" for the entity-tableadapters to get autogenerated INSERT, UPDATE and DELETE methods/queries. This gives us functionallity regarding the editing of any given entity, and then use simple views to generate the "joined entities" for presentation info entities.

regards,
wwfDev

Hi Robert,
I too am having a similar problem creating a TableAdapter in the designer. Mine is happening when I add a LIMIT clause. I've tried to use a hard coded value and a parameter. Both fail with the designer telling me it can't parse the querry.

Table:

CREATE TABLE foo (
 fooId INT PRIMARY KEY,
 dateOfBirth DATE,
 nameFirst VARCHAR (32) COLLATE NOCASE
)

Query:

SELECT fooId,
 dateOfBirth,
 nameFirst
FROM foo
WHERE ( @dateOfBirth IS NULL OR dateOfBirth = @dateOfBirth )
 AND ( @nameFirst IS NULL OR nameFirst LIKE @nameFirst )
 LIMIT ( 50 )

Thanks!

Kenneth.

Unfortunately there's nothing I can do about this.  This is a Visual Studio querybuilder limitation and I don't have any control over it.

Robert

rsimpson:

Unfortunately there's nothing I can do about this.  This is a Visual Studio querybuilder limitation and I don't have any control over it.

Robert

 

Okay, well there is a work arround:

Add the query without the LIMIT, but all your other paramerters. That will get you the method generated the first time. Then go to that query in the DataSet designer and select it. Right-mouse-click and select "Properties" to bring up the properties window for the query. You can select the "CommandText" property and add the "LIMIT @numRows" text to the query. Then select the "Parameters" collection property and add an Int32 paramater named @numRows. Build this and every thing works as expected.

Thanks.