Solved

Two table Select Statement ADO issue

Posted on 2006-07-12
12
263 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 17094385
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
ID: 17094508
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
ID: 17095271
I don't think Access supports compound SQL statements. I know SQL Server does... You may need to do this in 2 operations.
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 34

Expert Comment

by:Sancler
ID: 17095374
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
ID: 17105195
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
ID: 17105307
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
 
LVL 34

Expert Comment

by:Sancler
ID: 17106502
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
ID: 17107462
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
ID: 17107858
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
ID: 17107909
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
ID: 17108010
>>
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
ID: 17171424
Thank you for all the great inputs...
Gr8life
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

688 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