Can I do this in C#?

  • Thread starter Thread starter kevin.jennings
  • Start date Start date
K

kevin.jennings

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
 
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?
Click to expand...

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
 
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
Click to expand...

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
 
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'.
Click to expand...

Practically all databases except 10 year old MySQL databases
support that.

Arne
 
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
Click to expand...

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)
 
Martin Bonner said:
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)
Click to expand...

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.
 
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)
Click to expand...

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,,,
 
Martin said:
Couldn't you also do:
Click to expand...


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.

--
 
Tim Jarvis said:
No that wouldn't be correct in this case it wouldn't return what Kevin
wanted, this query would return....
Click to expand...

<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...)
 
Martin said:
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)
Click to expand...

The SQL standard is <>.

But your solution gives a comletely different result.

Arne
 
The SQL standard is <>.

But your solution gives a comletely different result.
Click to expand...

D'oh!

That would be because I didn't read the question carefully enough.
Sorry for the noise :-(
 
Back
Top