Can I do this in C#?

**=?ISO-8859-1?Q?Arne_Vajh=F8j?=** · Jun 27 '08, 07:53 PM

Re: Can I do this in C#?

kevin.jennings@ 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,discda te
FROM yourtable
WHERE location NOT IN (SELECT location FROM yourtable WHERE
discdate='00000 0')

Arne

**kevin.jennings@gmail.com** · Jun 27 '08, 07:53 PM

Re: Can I do this in C#?

On May 25, 9:37 pm, Arne Vajhøj <a...@vajhoej.d kwrote:

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,discda te
FROM yourtable
WHERE location NOT IN (SELECT location FROM yourtable WHERE
discdate='00000 0')
>
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

**=?ISO-8859-1?Q?Arne_Vajh=F8j?=** · Jun 27 '08, 07:53 PM

Re: Can I do this in C#?

kevin.jennings@ gmail.com wrote:

On May 25, 9:37 pm, Arne Vajhøj <a...@vajhoej.d kwrote:

>kevin.jenni... @gmail.com wrote:

>>I'm writing a C# program to read from an Access database table called
>>LocationDetai l. 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,discda te
>FROM yourtable
>WHERE location NOT IN (SELECT location FROM yourtable WHERE
>discdate='0000 00')

>
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

**Martin Bonner** · Jun 27 '08, 07:53 PM

Re: Can I do this in C#?

On May 26, 3:37 am, Arne Vajhøj <a...@vajhoej.d kwrote:

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,discda te
FROM yourtable
WHERE location NOT IN (SELECT location FROM yourtable WHERE
discdate='00000 0')
>
Arne

Couldn't you also do:

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

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

**Jon Skeet [C# MVP]** · Jun 27 '08, 07:53 PM

Re: Can I do this in C#?

Martin Bonner <martinfrompi@y ahoo.co.ukwrote :

Couldn't you also do:
>
SELECT location,discda te
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 - <skeet@pobox.co m>
Web site: http://www.pobox.com/~skeet
Blog: http://www.msmvps.com/jon.skeet
C# in Depth: http://csharpindepth.com

**ManicQin** · Jun 27 '08, 07:53 PM

Re: Can I do this in C#?

On May 26, 8:28 am, Martin Bonner <martinfro...@y ahoo.co.ukwrote :

On May 26, 3:37 am, Arne Vajhøj <a...@vajhoej.d kwrote:
>
>
>

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,discda te
FROM yourtable
WHERE location NOT IN (SELECT location FROM yourtable WHERE
discdate='00000 0')

>

Arne

>
Couldn't you also do:
>
SELECT location,discda te
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,,,

**Tim Jarvis** · Jun 27 '08, 07:53 PM

Re: Can I do this in C#?

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.

--

**Jon Skeet [C# MVP]** · Jun 27 '08, 07:53 PM

Re: Can I do this in C#?

Tim Jarvis <tim@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 - <skeet@pobox.co m>
Web site: http://www.pobox.com/~skeet
Blog: http://www.msmvps.com/jon.skeet
C# in Depth: http://csharpindepth.com

**=?ISO-8859-1?Q?Arne_Vajh=F8j?=** · Jun 27 '08, 07:53 PM

Re: Can I do this in C#?

Martin Bonner wrote:

On May 26, 3:37 am, Arne Vajhøj <a...@vajhoej.d kwrote:

>kevin.jenni... @gmail.com wrote:

>>I'm writing a C# program to read from an Access database table called
>>LocationDetai l. 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,discda te
>FROM yourtable
>WHERE location NOT IN (SELECT location FROM yourtable WHERE
>discdate='0000 00')

>
Couldn't you also do:
>
SELECT location,discda te
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

**Martin Bonner** · Jun 27 '08, 07:53 PM

Re: Can I do this in C#?

On May 26, 3:49 pm, Arne Vajhøj <a...@vajhoej.d kwrote:

Martin Bonner wrote:

On May 26, 3:37 am, Arne Vajhøj <a...@vajhoej.d kwrote:

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,discda te
FROM yourtable
WHERE location NOT IN (SELECT location FROM yourtable WHERE
discdate='00000 0')

>

Couldn't you also do:

>

SELECT location,discda te
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 :-(

Can I do this in C#?

Can I do this in C#?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment