Solved

Two table Select Statement ADO issue

Posted on 2006-07-12
12
258 Views
Last Modified: 2011-10-03
I am trying to extract data from two tables in an access database.  I have checked the database and indexed everything correctly, but unable to select the criteria data based on the statement below.

sql = "SELECT data.Firewall, data.Date, data.CMD, data.ScrIP, data.Sburb, data.Dburb, data.BWTC, data.ScrDomain, data.Sender, data.Recipient, data.Mail_ID, data.FW_Session_ID, FROM data; SELECT Country.countryname, FROM Country WHERE data.ipl1 BETWEEN countryname.ipl1 and countryname.ipl2"
 
Thank you for your time and expertise,
Gr8life  
0
Comment
Question by:gr8life
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
Are you trying to get two sets of results or do you want CountryName combined with the other fields in a single set of results ?
0
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
If you want both tables kept separate, use dataadapter.fill(dataset)... the data from "data" will be in table 0, the data from Country will be in table 1.

You're also got a typo -- you've got an extra comma right before FROM Country.

If you want the tables joined, you need to use the JOIN operator... if you're unfamiliar with this, let us know and we'll help.
0
 
LVL 14

Expert Comment

by:ptakja
Comment Utility
I don't think Access supports compound SQL statements. I know SQL Server does... You may need to do this in 2 operations.
0
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
I don't think you can use two select statements, separated by ";", as the commandtext for an OleDbCommand, at least with with Access.  I say "I don't think" because I've never really persisted in trying to do so.  Having hit a problem in the past I've always regarded it as simpler just to run the two commands separately.  But I've just tried it again now and got the error message "Characters found after end of SQL statement".

If you want to persist with that approach, good luck.  But I'm not sure what the downside is of using two separate commands - unless, as the other answers have asked about, you really want a Join of the two tables.

And the same typo - an extra comma before the FROM - is there in the FROM data statement, too.

Roger

PS  I see ptakja's been here before me with some of that - whilst I was testing/typing.  So let's just say I second ptakja.
0
 

Author Comment

by:gr8life
Comment Utility
Thank you very much for all the great and valuable advice.

carl_tawn
I am tried to want CountryName combined with the other fields in a single set of results.

Chaosian
If it is mot too much trouble if you could help with a join example.

ptakja
I believe you and Roger are correct the two selects approach does not appear to work in access.  Also I did find several documented ways to do that with SQL server.

Roger
I originally tried to use two separate selects but posted the semicolon approach because I couldn’t get it to work correctly.  If you would mind posting some examples I would greatly appreciate it.

Thank you very much for your time and expertise,
Gr8life
0
 

Author Comment

by:gr8life
Comment Utility
I just got home from an 18 hour shift. I apologize for my critic responses.

Posting take two:

carl_tawn
I am trying to have one set of data which combines CountryName with the other fields in a single set of results.

Chaosian
If it is not too much trouble if you could help me by posting a join operator example.

ptakja
I believe you and Roger are correct the two selects approach does not appear to work in access.  Also I did find several documented ways to do that with SQL server, used the semicolon.

Roger
I originally tried to use two separate selects but when I couldn’t get it to work I figured it wasn’t possible.  I then posted the semicolon approach because I thought it was the only way this approach would work using ADO.  If it is not too much trouble can you post some examples using the two separate selects approach I would greatly appreciate it.

Thank you very much for your time and expertise,
Gr8life
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Expert Comment

by:Sancler
Comment Utility
In suggesting two separate select commands, I hadn't appreciated the dependency of the second on the first.  You do need to combine them.

This is the query you want as designed in, and copied and pasted from, Access

>>
SELECT data.Firewall, data.Date, data.CMD, data.ScrIP, data.Sburb, data.Dburb, data.BWTC, data.ScrDomain, data.Sender, data.Recipient, data.Mail_ID, data.FW_Session_ID, Country.countryname
FROM data, Country
WHERE (((data.ipl1) Between Country!ipl1 And Country!ipl2));
<<

What I suggest you do is actually put that query in Access, calling it something like DataWithCountry and then, from your VB.NET app, simply use

    "SELECT * FROM DataWithCountry"

as the SQL statement for your CommandText.  I've just tested that approach and it works for me.

If you do not have access or permissions to put such a query in Access, then it will probably be possible to replicate the query in SQL.  But it doesn't work as it stands - raising "Syntax error in JOIN statement" - and I haven't given any thought as to how to revise it.

Roger
0
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
Looks like Roger beat me to it.

Although I will point out that you should avoid using *.

The following query will work in both Access and SQL (assuming that the PK for Country is "CountryId")

SELECT data.Firewall, data.Date, data.CMD, data.ScrIP, data.Sburb, data.Dburb, data.BWTC, data.ScrDomain, data.Sender, data.Recipient, data.Mail_ID, data.FW_Session_ID, c.countryname
FROM data
LEFT JOIN  Country c ON c.CountryId = data.CountryId
WHERE (((data.ipl1) Between c.ipl1 And c.ipl2));
0
 
LVL 34

Accepted Solution

by:
Sancler earned 250 total points
Comment Utility
Jeff

We'll have to see what gr8life says but I think - from previous questions, by the way, not by instinct - that the problem with this scenario is that there is no CountryID.  The Country table is three-field: one being the country name, and the other two being the start and end of a RANGE of IP Addresses.  Any given IP Address from data may fall WITHIN just one of those ranges and, if it does, the query wants to show the country name as well as (or instead of) the IP Address.  If I'm right in that, then a normal join, of specific value to specific value - foreign key to primary key - won't work.  It's more like an IN(a, b, c ... x, y, z), but with the contents of the IN(?)  having to be implied from the start and end of the range rather than being specified.  As my earlier post showed, this is possible in Access itself: I assume it is in SQL, too.  But I've never attempted coding this sort of thing to pass to Access as an SQL statement via Jet and my - somewhat desultory - attempts at it this morning got nowhere.

And why - in the specific situation outlined in my earlier post, of the specific fields being named in an AccessQuery, which is then got by "SELECT * FROM " that query - do you say we should avoid using *?

Roger
0
 
LVL 24

Assisted Solution

by:Jeff Certain
Jeff Certain earned 250 total points
Comment Utility
Roger,

If you're right about Country, the solution may be as simple as using . in place of !. The ! is not valid SQL server syntax; we use a . to qualify the field name.

Even if you're coming off another query, you still have the same issues as with a table:
1. * causes the database engine to perform a lookup to resolve the field list
2. You may not actually need all the data in the underlying table/query
3. Changes to the underlying table/query may result in maintenance issues. For example, there are still some third-party controls that use positional column identifiers; using * allows the order of the columns to potentially be changed. This also rears it's head with autogenerated columns in datagrid (although I haven't checked to see if it is still an issue in datagridview).

Jeff
0
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
>>
If you're right about Country, the solution may be as simple as using . in place of !.
<<

No, that's the first thing I tried.  And I put [] round Date.  And a few etceteras ;-)

Roger
0
 

Author Comment

by:gr8life
Comment Utility
Thank you for all the great inputs...
Gr8life
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now