.Net Query Builder

[stingrae]

Has anybody else had any problems with the VB.Net query builder? I think it is a shocker.

I have two tables, join them together and it works sweet. I then "Add Table" in a 3rd, join them and so then it builds the SQL, right click the Results pane, Run and then it gives me an SQL error.

if i then take that SQL to Access, it gives me the same error. I use Access to fix it up, then copy and paste it back, then SQL Error.
Error message reads: "Syntax Error in SQL statement".

how do other people do these things?

[MXAlPhA]

i personally find it easier handcoding the sql queries (when i can) and use this is a reference: www.w3schools.com

[stingrae]

MXAlPhA, do you hand code all queries? like, even large ones with many tables, groups and sums etc?

i know if i sat down with a book for a couple of hours and tried, i could write them manually, but it sounds like too much work.

why microsoft can come so close to, and yet still leave us so far behind, the ideal is really annoying.

[MXAlPhA]

i've never tried doing large ones, never had a need for it. Yah... i do like doing the stuff myself, or at the very least, using a program but checking over it later. I don't really like doing something without knowing how it works and stuff like that, that's what i'm very interested in.

Edit. Where's the .net query builder located, i'm interested in looking at it?

[powdir]

Query designers/builders tend NOT to build the best SQL. Sure they have a go...but

I think it is a good idea to always hand code SQL for the best performance, if the query is going to involve more than just a couple of joins i would consider using stored procedures instead. The SQL that the builder tool gives you might give rough a 'rough' idea of how to go about it, but you should refine it.

...thats my tuppence worth anyway. Cheers

[MXAlPhA]

Originally posted by powdir
Query designers/builders tend NOT to build the best SQL. Sure they have a go...but

I think it is a good idea to always hand code SQL for the best performance, if the query is going to involve more than just a couple of joins i would consider using stored procedures instead. The SQL that the builder tool gives you might give rough a 'rough' idea of how to go about it, but you should refine it.

...thats my tuppence worth anyway. Cheers

i agree with you 100%, where is the sql builder in .net anywayz?

[stingrae]

To access the .Not query builder:

1. Open project and add form (err der...)
2. click and drag an OleDataAdapter control onto the form
3. Click next, next & next (filling out the details along the way)
4. It will come to a screen that says: fill in your SQL or use the Query Builder. Click the button on the bottom right.

....have fun.....

[MXAlPhA]

i'm new to the vb.net scenario, but in vb6 i didn't really use controls for databases and stuff like that.

In vb.net do you guys use controls more often or do you code in the connection string and open string (i'm more familiar with the latter because of my experiences with asp)?

[stingrae]

in vb6 i used to create all the reports and stuff using the controls supplied by VB but then in the code i would overwrite it.
in other words, i build the report by linking it to an ADO recordset, then in the code i would say datareport.datasource = "already connected ado datasource". this would allow my code to be flexible and not worry where the datasource was housed and made report design a real piece of piss.

in vb.net - one word - HELP! i am struggling.

[MXAlPhA]

Originally posted by stingrae
in vb6 i used to create all the reports and stuff using the controls supplied by VB but then in the code i would overwrite it.
in other words, i build the report by linking it to an ADO recordset, then in the code i would say datareport.datasource = "already connected ado datasource". this would allow my code to be flexible and not worry where the datasource was housed and made report design a real piece of piss.

in vb.net - one word - HELP! i am struggling.

lol... i'm beginning vb.net too.

[powdir]

i am a bit of a luddite when it comes to data controls - i prefer to write the code by hand (i feel it gives me a bit more control over the action!). Plus, in large scale apps you would tend to wrap dataconnections etc. up in other classes so i think it would be easy to lose track of where your datacontrols are and what they are doing at any given point.

[stingrae]

powdir, i fully agree with you. i think though that this is far easier with dao-->ado 2.x than with ADO.Net.

I hope that i'm wrong and that experience will show me otherwise.

[stingrae]

nice to see that Microsoft has caught up!

here's an official report on this problem in case anybody else finds it:

http://support.microsoft.com/default...en-us%3B318646

now if they would just provide us with a solution....