471,325 Members | 1,453 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,325 software developers and data experts.

Can I do this in C#?

I'm writing a C# program to read from an Access database table called
LocationDetail. This table is made up of two columns: Location,
DiscDate. It looks like this:

0000001234 122590
0000001234 102207
0000001234 000000
1237847623 102207
1237847623 071395
4545454545 031206
4545454545 000000
and so on...
The first column denotes a location, the second a disconnect date.
If
the date is '000000' that means the location is active. So, that means
that location 0000001234 above is a currently active location with two
previous disconnects.

What I'd like to do is go through this table and only 'fetch' the
locations that do not have a DiscDate of '000000'. In this instance,
looking at the data above, I'd only want location 1237847623 because
it is the only location that doesn't have a DiscDate that is equal to
'000000'. It has two entries and both have a valid disconnect date.
That means that this location is still "inactive" and that's what I
want to find: All inactive locations.

My C# program currently loads the entire LocationDetail table into a
dataset in the program. I'm assuming that I have to use SQL-like
commands to act on this dataset and pull only the locations I require
from it. But, I have no idea how to do that. Can someone show me an
example?

Thanks,
Kevin
Jun 27 '08 #1
10 1008
ke************@gmail.com wrote:
I'm writing a C# program to read from an Access database table called
LocationDetail. This table is made up of two columns: Location,
DiscDate. It looks like this:

0000001234 122590
0000001234 102207
0000001234 000000
1237847623 102207
1237847623 071395
4545454545 031206
4545454545 000000
and so on...
The first column denotes a location, the second a disconnect date.
If
the date is '000000' that means the location is active. So, that means
that location 0000001234 above is a currently active location with two
previous disconnects.

What I'd like to do is go through this table and only 'fetch' the
locations that do not have a DiscDate of '000000'. In this instance,
looking at the data above, I'd only want location 1237847623 because
it is the only location that doesn't have a DiscDate that is equal to
'000000'. It has two entries and both have a valid disconnect date.
That means that this location is still "inactive" and that's what I
want to find: All inactive locations.

My C# program currently loads the entire LocationDetail table into a
dataset in the program. I'm assuming that I have to use SQL-like
commands to act on this dataset and pull only the locations I require
from it. But, I have no idea how to do that. Can someone show me an
example?
Only load the relevant data into the DataSet.

Something like:

SELECT location,discdate
FROM yourtable
WHERE location NOT IN (SELECT location FROM yourtable WHERE
discdate='000000')

Arne
Jun 27 '08 #2
On May 25, 9:37 pm, Arne Vajhøj <a...@vajhoej.dkwrote:
kevin.jenni...@gmail.com wrote:
I'm writing a C# program to read from an Access database table called
LocationDetail. This table is made up of two columns: Location,
DiscDate. It looks like this:
0000001234 122590
0000001234 102207
0000001234 000000
1237847623 102207
1237847623 071395
4545454545 031206
4545454545 000000
and so on...
The first column denotes a location, the second a disconnect date.
If
the date is '000000' that means the location is active. So, that means
that location 0000001234 above is a currently active location with two
previous disconnects.
What I'd like to do is go through this table and only 'fetch' the
locations that do not have a DiscDate of '000000'. In this instance,
looking at the data above, I'd only want location 1237847623 because
it is the only location that doesn't have a DiscDate that is equal to
'000000'. It has two entries and both have a valid disconnect date.
That means that this location is still "inactive" and that's what I
want to find: All inactive locations.
My C# program currently loads the entire LocationDetail table into a
dataset in the program. I'm assuming that I have to use SQL-like
commands to act on this dataset and pull only the locations I require
from it. But, I have no idea how to do that. Can someone show me an
example?

Only load the relevant data into the DataSet.

Something like:

SELECT location,discdate
FROM yourtable
WHERE location NOT IN (SELECT location FROM yourtable WHERE
discdate='000000')

Arne
Thanks for the response! I'll try out your example. I didn't realize
I could do nested selects in SQL. I've never used it before for
anything other than a straight 'select'.

I appreciate it!

Kevin
Jun 27 '08 #3
ke************@gmail.com wrote:
On May 25, 9:37 pm, Arne Vajhøj <a...@vajhoej.dkwrote:
>kevin.jenni...@gmail.com wrote:
>>I'm writing a C# program to read from an Access database table called
LocationDetail. This table is made up of two columns: Location,
DiscDate. It looks like this:
0000001234 122590
0000001234 102207
0000001234 000000
1237847623 102207
1237847623 071395
4545454545 031206
4545454545 000000
and so on...
The first column denotes a location, the second a disconnect date.
If
the date is '000000' that means the location is active. So, that means
that location 0000001234 above is a currently active location with two
previous disconnects.
What I'd like to do is go through this table and only 'fetch' the
locations that do not have a DiscDate of '000000'. In this instance,
looking at the data above, I'd only want location 1237847623 because
it is the only location that doesn't have a DiscDate that is equal to
'000000'. It has two entries and both have a valid disconnect date.
That means that this location is still "inactive" and that's what I
want to find: All inactive locations.
My C# program currently loads the entire LocationDetail table into a
dataset in the program. I'm assuming that I have to use SQL-like
commands to act on this dataset and pull only the locations I require
from it. But, I have no idea how to do that. Can someone show me an
example?
Only load the relevant data into the DataSet.

Something like:

SELECT location,discdate
FROM yourtable
WHERE location NOT IN (SELECT location FROM yourtable WHERE
discdate='000000')

Thanks for the response! I'll try out your example. I didn't realize
I could do nested selects in SQL. I've never used it before for
anything other than a straight 'select'.
Practically all databases except 10 year old MySQL databases
support that.

Arne
Jun 27 '08 #4
On May 26, 3:37 am, Arne Vajhøj <a...@vajhoej.dkwrote:
kevin.jenni...@gmail.com wrote:
I'm writing a C# program to read from an Access database table called
LocationDetail. This table is made up of two columns: Location,
DiscDate. It looks like this:
0000001234 122590
0000001234 102207
0000001234 000000
1237847623 102207
1237847623 071395
4545454545 031206
4545454545 000000
and so on...
The first column denotes a location, the second a disconnect date.
If
the date is '000000' that means the location is active. So, that means
that location 0000001234 above is a currently active location with two
previous disconnects.
What I'd like to do is go through this table and only 'fetch' the
locations that do not have a DiscDate of '000000'. In this instance,
looking at the data above, I'd only want location 1237847623 because
it is the only location that doesn't have a DiscDate that is equal to
'000000'. It has two entries and both have a valid disconnect date.
That means that this location is still "inactive" and that's what I
want to find: All inactive locations.
My C# program currently loads the entire LocationDetail table into a
dataset in the program. I'm assuming that I have to use SQL-like
commands to act on this dataset and pull only the locations I require
from it. But, I have no idea how to do that. Can someone show me an
example?

Only load the relevant data into the DataSet.

Something like:

SELECT location,discdate
FROM yourtable
WHERE location NOT IN (SELECT location FROM yourtable WHERE
discdate='000000')

Arne
Couldn't you also do:

SELECT location,discdate
FROM yourtable
WHERE discdate != '000000'

(or doesn't SQL support a not-equal operator?), in which case
synthesize from
WHERE (discdate < '0000' || '0000' < discdate)
Jun 27 '08 #5
Martin Bonner <ma**********@yahoo.co.ukwrote:
Couldn't you also do:

SELECT location,discdate
FROM yourtable
WHERE discdate != '000000'

(or doesn't SQL support a not-equal operator?), in which case
synthesize from
WHERE (discdate < '0000' || '0000' < discdate)
Some dialects of SQL use <instead of != but yes, I far prefer using
that to using the nested select, which may (depending on
implementation) be significantly slower.

--
Jon Skeet - <sk***@pobox.com>
Web site: http://www.pobox.com/~skeet
Blog: http://www.msmvps.com/jon.skeet
C# in Depth: http://csharpindepth.com
Jun 27 '08 #6
On May 26, 8:28*am, Martin Bonner <martinfro...@yahoo.co.ukwrote:
On May 26, 3:37 am, Arne Vajhøj <a...@vajhoej.dkwrote:
kevin.jenni...@gmail.com wrote:
I'm writing a C# program to read from an Access database table called
LocationDetail. *This table is made up of two columns: Location,
DiscDate. *It looks like this:
0000001234 * 122590
0000001234 * 102207
0000001234 * 000000
1237847623 * 102207
1237847623 * 071395
4545454545 * 031206
4545454545 * 000000
and so on...
The first column denotes a location, the second a disconnect date.
If
the date is '000000' that means the location is active. So, that means
that location 0000001234 above is a currently active location with two
previous disconnects.
What I'd like to do is go through this table and only 'fetch' the
locations that do not have a DiscDate of '000000'. *In this instance,
looking at the data above, I'd only want location 1237847623 because
it is the only location that doesn't have a DiscDate that is equal to
'000000'. *It has two entries and both have a valid disconnect date.
That means that this location is still "inactive" and that's what I
want to find: All inactive locations.
My C# program currently loads the entire LocationDetail table into a
dataset in the program. *I'm assuming that I have to use SQL-like
commands to act on this dataset and pull only the locations I require
from it. *But, I have no idea how to do that. *Can someone show mean
example?
Only load the relevant data into the DataSet.
Something like:
SELECT location,discdate
FROM yourtable
WHERE location NOT IN (SELECT location FROM yourtable WHERE
discdate='000000')
Arne

Couldn't you also do:

SELECT location,discdate
FROM yourtable
WHERE discdate != '000000'

(or doesn't SQL support a not-equal operator?), in which case
synthesize from
WHERE (discdate < '0000' || '0000' < discdate)
Are you this Sql Query suffice?
I think It'll give you

0000001234 122590
0000001234 102207
1237847623 102207
1237847623 071395
4545454545 031206

If I\m not Mistaken,,,
Jun 27 '08 #7
Martin Bonner wrote:
0000001234 122590
0000001234 102207
0000001234 000000
1237847623 102207
1237847623 071395
4545454545 031206
4545454545 000000
and so on...
have a valid disconnect date. That means that this location is
still "inactive" and that's what I want to find: All inactive
locations.
Couldn't you also do:

No that wouldn't be correct in this case it wouldn't return what Kevin
wanted, this query would return....

0000001234 122590
0000001234 102207
1237847623 102207
1237847623 071395
4545454545 031206

When what he wanted was those locations that are not current i.e. just

1237847623

I think that Arne's response was the correct one, or in fact maybe even
refined as...

SELECT distinct location from mytable where location not in (select
location from mytable where discdate = "000000")

to simply return a distinct list of locations that are not current.

Regards Tim.

--

Jun 27 '08 #8
Tim Jarvis <ti*@jarvis.com.auwrote:
No that wouldn't be correct in this case it wouldn't return what Kevin
wanted, this query would return....
<snip>

Ah, yes... Oops. That'll teach me to read the question more closely
(and/or not try to read news before my first coffee...)

--
Jon Skeet - <sk***@pobox.com>
Web site: http://www.pobox.com/~skeet
Blog: http://www.msmvps.com/jon.skeet
C# in Depth: http://csharpindepth.com
Jun 27 '08 #9
Martin Bonner wrote:
On May 26, 3:37 am, Arne Vajhøj <a...@vajhoej.dkwrote:
>kevin.jenni...@gmail.com wrote:
>>I'm writing a C# program to read from an Access database table called
LocationDetail. This table is made up of two columns: Location,
DiscDate. It looks like this:
0000001234 122590
0000001234 102207
0000001234 000000
1237847623 102207
1237847623 071395
4545454545 031206
4545454545 000000
and so on...
The first column denotes a location, the second a disconnect date.
If
the date is '000000' that means the location is active. So, that means
that location 0000001234 above is a currently active location with two
previous disconnects.
What I'd like to do is go through this table and only 'fetch' the
locations that do not have a DiscDate of '000000'. In this instance,
looking at the data above, I'd only want location 1237847623 because
it is the only location that doesn't have a DiscDate that is equal to
'000000'. It has two entries and both have a valid disconnect date.
That means that this location is still "inactive" and that's what I
want to find: All inactive locations.
My C# program currently loads the entire LocationDetail table into a
dataset in the program. I'm assuming that I have to use SQL-like
commands to act on this dataset and pull only the locations I require
from it. But, I have no idea how to do that. Can someone show me an
example?
Only load the relevant data into the DataSet.

Something like:

SELECT location,discdate
FROM yourtable
WHERE location NOT IN (SELECT location FROM yourtable WHERE
discdate='000000')

Couldn't you also do:

SELECT location,discdate
FROM yourtable
WHERE discdate != '000000'

(or doesn't SQL support a not-equal operator?), in which case
synthesize from
WHERE (discdate < '0000' || '0000' < discdate)
The SQL standard is <>.

But your solution gives a comletely different result.

Arne
Jun 27 '08 #10
On May 26, 3:49 pm, Arne Vajhøj <a...@vajhoej.dkwrote:
Martin Bonner wrote:
On May 26, 3:37 am, Arne Vajhøj <a...@vajhoej.dkwrote:
kevin.jenni...@gmail.com wrote:
I'm writing a C# program to read from an Access database table called
LocationDetail. This table is made up of two columns: Location,
DiscDate. It looks like this:
0000001234 122590
0000001234 102207
0000001234 000000
1237847623 102207
1237847623 071395
4545454545 031206
4545454545 000000
and so on...
The first column denotes a location, the second a disconnect date.
If
the date is '000000' that means the location is active. So, that means
that location 0000001234 above is a currently active location with two
previous disconnects.
What I'd like to do is go through this table and only 'fetch' the
locations that do not have a DiscDate of '000000'. In this instance,
looking at the data above, I'd only want location 1237847623 because
it is the only location that doesn't have a DiscDate that is equal to
'000000'. It has two entries and both have a valid disconnect date.
That means that this location is still "inactive" and that's what I
want to find: All inactive locations.
My C# program currently loads the entire LocationDetail table into a
dataset in the program. I'm assuming that I have to use SQL-like
commands to act on this dataset and pull only the locations I require
from it. But, I have no idea how to do that. Can someone show me an
example?
Only load the relevant data into the DataSet.
Something like:
SELECT location,discdate
FROM yourtable
WHERE location NOT IN (SELECT location FROM yourtable WHERE
discdate='000000')
Couldn't you also do:
SELECT location,discdate
FROM yourtable
WHERE discdate != '000000'
(or doesn't SQL support a not-equal operator?), in which case
synthesize from
WHERE (discdate < '0000' || '0000' < discdate)

The SQL standard is <>.

But your solution gives a comletely different result.
D'oh!

That would be because I didn't read the question carefully enough.
Sorry for the noise :-(
Jun 27 '08 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by William C. White | last post: by
2 posts views Thread by Albert Ahtenberg | last post: by
3 posts views Thread by James | last post: by
reply views Thread by Ollivier Robert | last post: by
1 post views Thread by Richard Galli | last post: by
4 posts views Thread by Albert Ahtenberg | last post: by
1 post views Thread by inderjit S Gabrie | last post: by
2 posts views Thread by Jack | last post: by
3 posts views Thread by Sandwick | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.