Last post Aug 11, 2009 08:23 PM by danielszabo1981
Jun 05, 2007 02:51 PM|nmadd|LINK
Beginning ASP.NET user. I'm following Scott Mitchell's great tutorial on this site to help me setup a web interfaces to my Access backend. It's suggested that I use a subquery instead of multiple JOINs to return data from multiple tables so that the TableAdapter
wizard can automatically write the UPDATE, INSERT, etc. statements. I'm having trouble with my query. Here is a slimmed down version
SELECT TitleID, Title, LicensorID,
(SELECT Licensor FROM tblLicensor WHERE tblTitles.LicensorID = tblLicensor.LicensorID) AS LicensorName
This works just fine when I run it in Access, but I get an error when running in the Query Builder in the TableAdapter wizard. Actually, when I execute the query it seems to pull the correct results, however, I receive
Error in Select clause: expression near Select
Error in Select clause: expression near From
Missing From Clause
Unable to parse query text
So, therefore, all of my statements do not get generated. Thanks for any help at all!
Jun 05, 2007 03:35 PM|Mikesdotnetting|LINK
Try the same thing with a join. Some of the integration with Access and VS/VWD is not so good. The have trouble recognising saved select queries for example.
Incidentally, with regard to sub-queries over joins, have a look at this:
Jun 05, 2007 03:39 PM|jasonjanofsky|LINK
Yeah, the typed dataset wizard tends to screw up things a bit. You end up finding workarounds. In the case of these subqueries.. I don't know that I would agree, joins are much more efficient if simply because you save all the typing from all those other
fields. I would suggest this to you. When you are creating a new table wizard, go ahead and do a simple query with all your normal fields (no joins or subqueries), this will allow your tableadapter to successfully auto generate the CRUD stuff (Create, Retrieve,
Once you have done that, you will see your datatable and tableadapter view with everything there except your join fields. Click on your table adapter and you will see that its default query is the getdata query, in the properties window you get insert,
update, delete, and select statements. Go in at this point and change the select statement to reflect the join statements and all the extra fields you want to have. The tricky part comes when it asks you if you want to refresh your queries. The wizard basically
is asking if you want to refresh your tableadapter and datatable with your newly minted select statement with the joins in there, say NO as this will, in effect, delete your auto generated updates/inserts/deletes.
MSN me if you have any issues with this stuff, I have actually taken Scott's class at the UCSD Extension and spent some time learning from him in a one on one scenario (he is expensive but absolulely worth it if you live in San Diego.
Jun 05, 2007 04:00 PM|nmadd|LINK
Thanks for the responses! For some time I thought I was doing something wrong.
Thanks for the suggestion too Jason. I think I'll give that a try.
Aug 11, 2009 08:23 PM|danielszabo1981|LINK
jasonjanofsky - I know its years after the fact, but I just wanted to say thank you. I've been working on this for a few days, and your post help me out BIGTIME.