Cannot select data by range.

  • Thread starter Thread starter Han Lim
  • Start date Start date
H

Han Lim

Dear All,
I have an application written by VB.Net with connect to a
Microsoft Access database.
One of the forms is to select data using oleAdapter and fill it
into a dataset. In the oleAdapter, i have two parameter : FromNation
and ToNation.
The select statement as follow :

SELECT SID, EmpyCode, EmpyName, LastDayWorked, NationCode,
LocCode, DeptCode, Status, RepStatus, ReqRemarks
FROM PRCR_EmpyPerMas
WHERE (NationCode >= FromNation) AND (NationCode <= ToNation)
ORDER BY EmpyName, EmpyCode

Then before i fill data into dataset, i assign the value to
parameters, if user not enter any data then system will default
FromNation = ' ' and ToNation = 'ZZZZZZ'. This works fine, it returns
all records.

However, when i enter 'IND' for FromNation and 'IND' for ToNation,
it doesn't return any record. I know my table has record with 'IND'.

Any solution?

Thanks a lot.

Regards,

Lee Han Lim
 
* (e-mail address removed) (Han Lim) scripsit:
One of the forms is to select data using oleAdapter and fill it
into a dataset. In the oleAdapter, i have two parameter : FromNation
and ToNation.
The select statement as follow :

SELECT SID, EmpyCode, EmpyName, LastDayWorked, NationCode,
LocCode, DeptCode, Status, RepStatus, ReqRemarks
FROM PRCR_EmpyPerMas
WHERE (NationCode >= FromNation) AND (NationCode <= ToNation)
ORDER BY EmpyName, EmpyCode
Click to expand...

For data(base) related questions, give this group a try:

<URL:
Web interface:

<URL:http://msdn.microsoft.com/newsgroups/?dg=microsoft.public.dotnet.framework.adonet>
 
Han Lim,
However, when i enter 'IND' for FromNation and 'IND' for ToNation,
it doesn't return any record. I know my table has record with 'IND'.
Click to expand...
Does you table contain IND specifically, or does it contain "INDIA"?

As INDIA is not less or equal to IND. If it contains INDIA, you may want to
consider using Like instead of a range.

Something like:

Where NationCode Like ToNation

Where ToNation contains "IND%" (verify the '%' on Access, it may be '*'.

In your original case I would consider using Between, something like:

Where NationCode Between FromNation And ToNation

The reason you should use Like instead of appending "ZZZZ" to the end of IND
is it will handle international characters correctly.

As Herfried suggested, you may want to ask this question "down the hall" in
either the adonet newsgroup he gave or one of the access specific
newsgroups.

Hope this helps
Jay
 
Jay B. Harlow said:
Han Lim,
Does you table contain IND specifically, or does it contain "INDIA"?

As INDIA is not less or equal to IND. If it contains INDIA, you may want to
consider using Like instead of a range.

Something like:

Where NationCode Like ToNation

Where ToNation contains "IND%" (verify the '%' on Access, it may be '*'.

In your original case I would consider using Between, something like:

Where NationCode Between FromNation And ToNation

The reason you should use Like instead of appending "ZZZZ" to the end of IND
is it will handle international characters correctly.

As Herfried suggested, you may want to ask this question "down the hall" in
either the adonet newsgroup he gave or one of the access specific
newsgroups.

Hope this helps
Jay
Click to expand...

First of all, thanks for your reply. Sorry for posting the question at
the wrong group.

Yes my table contains IND specifically, i did try to use between
statement, it did work.

I found that, if i format the lsFromNation = ' IND' and to
lsToNation = ' IND' then everything works. My column
datatype is text, length = 15. Is it a must to format my parameter to
15 chars? I know if I declare varchar(15) in MS SQL, i don't have to
format parameter to equel length, but i'm not sure about ACCESS.

Regards,

Lee Han Lim
 
Han,
I found that, if i format the lsFromNation = ' IND' and to
lsToNation = ' IND' then everything works. My column
datatype is text, length = 15. Is it a must to format my parameter to
15 chars? I know if I declare varchar(15) in MS SQL, i don't have to
format parameter to equel length, but i'm not sure about ACCESS.
Click to expand...

As you found, 'IND' is not ' IND' , if it works with the spaces,
then you need the spaces.

Personally I would trim the spaces from the values going into the Access
database, unless there is a specific need to have them there.

Hope this helps
Jay

Han Lim said:
"Jay B. Harlow [MVP - Outlook]" <[email protected]> wrote in message
Han Lim,
Does you table contain IND specifically, or does it contain "INDIA"?

As INDIA is not less or equal to IND. If it contains INDIA, you may want to
consider using Like instead of a range.

Something like:

Where NationCode Like ToNation

Where ToNation contains "IND%" (verify the '%' on Access, it may be '*'.

In your original case I would consider using Between, something like:

Where NationCode Between FromNation And ToNation

The reason you should use Like instead of appending "ZZZZ" to the end of IND
is it will handle international characters correctly.

As Herfried suggested, you may want to ask this question "down the hall" in
either the adonet newsgroup he gave or one of the access specific
newsgroups.

Hope this helps
Jay
Click to expand...

First of all, thanks for your reply. Sorry for posting the question at
the wrong group.

Yes my table contains IND specifically, i did try to use between
statement, it did work.

I found that, if i format the lsFromNation = ' IND' and to
lsToNation = ' IND' then everything works. My column
datatype is text, length = 15. Is it a must to format my parameter to
15 chars? I know if I declare varchar(15) in MS SQL, i don't have to
format parameter to equel length, but i'm not sure about ACCESS.

Regards,

Lee Han Lim
Click to expand...
 
Jay, thanks a lot for your suggestion. I use "Trim", and i works.

Regards,

Lee Han Lim.



Jay B. Harlow said:
Han,
I found that, if i format the lsFromNation = ' IND' and to
lsToNation = ' IND' then everything works. My column
datatype is text, length = 15. Is it a must to format my parameter to
15 chars? I know if I declare varchar(15) in MS SQL, i don't have to
format parameter to equel length, but i'm not sure about ACCESS.
Click to expand...

As you found, 'IND' is not ' IND' , if it works with the spaces,
then you need the spaces.

Personally I would trim the spaces from the values going into the Access
database, unless there is a specific need to have them there.

Hope this helps
Jay

Han Lim said:
"Jay B. Harlow [MVP - Outlook]" <[email protected]> wrote in message
Han Lim,
However, when i enter 'IND' for FromNation and 'IND' for ToNation,
it doesn't return any record. I know my table has record with 'IND'.
Does you table contain IND specifically, or does it contain "INDIA"?

As INDIA is not less or equal to IND. If it contains INDIA, you may want to
consider using Like instead of a range.

Something like:

Where NationCode Like ToNation

Where ToNation contains "IND%" (verify the '%' on Access, it may be '*'.

In your original case I would consider using Between, something like:

Where NationCode Between FromNation And ToNation

The reason you should use Like instead of appending "ZZZZ" to the end of IND
is it will handle international characters correctly.

As Herfried suggested, you may want to ask this question "down the hall" in
either the adonet newsgroup he gave or one of the access specific
newsgroups.

Hope this helps
Jay

Dear All,
I have an application written by VB.Net with connect to a
Microsoft Access database.
One of the forms is to select data using oleAdapter and fill it
into a dataset. In the oleAdapter, i have two parameter : FromNation
and ToNation.
The select statement as follow :

SELECT SID, EmpyCode, EmpyName, LastDayWorked, NationCode,
LocCode, DeptCode, Status, RepStatus, ReqRemarks
FROM PRCR_EmpyPerMas
WHERE (NationCode >= FromNation) AND (NationCode <= ToNation)
ORDER BY EmpyName, EmpyCode

Then before i fill data into dataset, i assign the value to
parameters, if user not enter any data then system will default
FromNation = ' ' and ToNation = 'ZZZZZZ'. This works fine, it returns
all records.

However, when i enter 'IND' for FromNation and 'IND' for ToNation,
it doesn't return any record. I know my table has record with 'IND'.

Any solution?

Thanks a lot.

Regards,

Lee Han Lim
Click to expand...

First of all, thanks for your reply. Sorry for posting the question at
the wrong group.

Yes my table contains IND specifically, i did try to use between
statement, it did work.

I found that, if i format the lsFromNation = ' IND' and to
lsToNation = ' IND' then everything works. My column
datatype is text, length = 15. Is it a must to format my parameter to
15 chars? I know if I declare varchar(15) in MS SQL, i don't have to
format parameter to equel length, but i'm not sure about ACCESS.

Regards,

Lee Han Lim
Click to expand...
Click to expand...
 
Back
Top