Query help

Here is what I am trying to do... I have a phone list of all our stores. It
contains managers home and cell numbers for emergencies. So the list is
about 44 stores and about 7 to 8 managers to each store...so that's about
350 or so numbers on this list... Now our store each have there own unique
IP address, ex. Store 2 would be 192.168.2.? So within the phone list I
have, for each store I have stored the Ip like 192.168.2 or 192.168.6 and
so on.. Well what I want to do is try to build a query that will only return
records that are similar to the users IP... so I have a hidden request for
the IP... Request.ServerVariables("REMOTE_ADDR") which I want to use as a
variable in my query to compare their ip with what's in the table..
So the results would be like this...
My IP 192.168.6.15
I should see ONLY records with 192.168.6 as part of the IP.
Any suggestions? Ideas? not sure if this would be the place to post this,
but would appreciate any help.
You might want to try the LIKE operator, read more in Books Online.
Regards,
Eric Garza
AMIGE
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uRdrbBByEHA.1404@.TK2MSFTNGP11.phx.gbl...
> Here is what I am trying to do... I have a phone list of all our stores.
It
> contains managers home and cell numbers for emergencies. So the list is
> about 44 stores and about 7 to 8 managers to each store...so that's about
> 350 or so numbers on this list... Now our store each have there own unique
> IP address, ex. Store 2 would be 192.168.2.? So within the phone list I
> have, for each store I have stored the Ip like 192.168.2 or 192.168.6 and
> so on.. Well what I want to do is try to build a query that will only
return
> records that are similar to the users IP... so I have a hidden request for
> the IP... Request.ServerVariables("REMOTE_ADDR") which I want to use as a
> variable in my query to compare their ip with what's in the table..
> So the results would be like this...
> My IP 192.168.6.15
> I should see ONLY records with 192.168.6 as part of the IP.
> Any suggestions? Ideas? not sure if this would be the place to post this,
> but would appreciate any help.
>
|||Daniel_Cha wrote:
> Here is what I am trying to do... I have a phone list of all our
> stores. It contains managers home and cell numbers for emergencies.
> So the list is about 44 stores and about 7 to 8 managers to each
> store...so that's about 350 or so numbers on this list... Now our
> store each have there own unique IP address, ex. Store 2 would be
> 192.168.2.? So within the phone list I have, for each store I have
> stored the Ip like 192.168.2 or 192.168.6 and so on.. Well what I
> want to do is try to build a query that will only return records that
> are similar to the users IP... so I have a hidden request for the
> IP... Request.ServerVariables("REMOTE_ADDR") which I want to use as a
> variable in my query to compare their ip with what's in the table..
> So the results would be like this...
> My IP 192.168.6.15
> I should see ONLY records with 192.168.6 as part of the IP.
> Any suggestions? Ideas? not sure if this would be the place to post
> this, but would appreciate any help.
It sounds like your IP address is stored in character format, and that's
good. To select only those stores that match your specs, use:
Select store_information
From Stores
Where IP LIKE '192.168.6.%'
Or if you are using a parameter:
@.IP as varchar(12)
Set @.IP = @.IP + '%'
Select store_information
From Stores
Where IP LIKE @.IP
David Gugick
Imceda Software
www.imceda.com
|||ok, here is my sql statement...
SELECT *
FROM tbEmerStore, tbIpRange
WHERE tbEmerStore.Store = tbIpRange.StoreNum AND tbIpRange.IpRange LIKE
'varIP'
ORDER BY Store
Variable is Name= varIP Run-Time Value =
Request.ServerVariable("REMOTE_ADDR")
what am i doing wrong?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23fMvpGByEHA.2540@.TK2MSFTNGP09.phx.gbl...
> Daniel_Cha wrote:
> It sounds like your IP address is stored in character format, and that's
> good. To select only those stores that match your specs, use:
> Select store_information
> From Stores
> Where IP LIKE '192.168.6.%'
> Or if you are using a parameter:
> @.IP as varchar(12)
> Set @.IP = @.IP + '%'
> Select store_information
> From Stores
> Where IP LIKE @.IP
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Daniel_Cha wrote:
> ok, here is my sql statement...
> SELECT *
> FROM tbEmerStore, tbIpRange
> WHERE tbEmerStore.Store = tbIpRange.StoreNum AND tbIpRange.IpRange
> LIKE 'varIP'
> ORDER BY Store
> Variable is Name= varIP Run-Time Value =
> Request.ServerVariable("REMOTE_ADDR")
> what am i doing wrong?
>
You are not using a variable. You are using the literal 'varIP'. 'varIP'
is not a variable.
Also, you should never use SELECT * in a production environment. It's
considered bad practice because you almost always end up returning more
columns than you really need. Even if you need them all, specify each
one in the query.
Review my example again.
David Gugick
Imceda Software
www.imceda.com
|||I'm assuming that the varIP is storing the full octets, what does the
tbIpRane.IpRange field data look like?
I'm also assuming you are sending ad hoc T-SQL to the server instead of
using a stored procedure. I'll also assume that you are only storing the
truncated octet in the database. If this is so, modify your query, thusly:
varSQL = "
SELECT *
FROM tbEmerStore AS es
INNER JOIN
tbIpRange AS ir
ON es.Store = ir.StoreNum
WHERE ir.IpRange = " & LEFT(varIP, 12) & "
ORDER BY es.Store
"
conn.Execute(varSQL)
This is probably not exact given the line breaks, but you should be able to
get the point.
To make life easier, I would strongly recommend that you use fixed-length,
zero-padded IP addresses and subnets; otherwise, you are going to have to do
some INSTR pattern matching to find the last "." octet seperator to determine
if the characters you need to truncate is 1, 2, or 3--that would suck.
Sincerely,
Anthony Thomas
"Daniel_Cha" wrote:

> ok, here is my sql statement...
> SELECT *
> FROM tbEmerStore, tbIpRange
> WHERE tbEmerStore.Store = tbIpRange.StoreNum AND tbIpRange.IpRange LIKE
> 'varIP'
> ORDER BY Store
> Variable is Name= varIP Run-Time Value =
> Request.ServerVariable("REMOTE_ADDR")
> what am i doing wrong?
>
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23fMvpGByEHA.2540@.TK2MSFTNGP09.phx.gbl...
>
>
|||Ok, I what is stored in the database is the following:
Table = tbIpRange
Column Name Value
StoreNum 02 San Antonio
IPRange 192.168.2
Table = tbEmerStore
Column Name Value
Store 02 San Antonio
So I have one table that contains this information for each store, since
this will not change unless we add a new location to the company.
In the table that contains all the information there is a column that
contains the same StoreNum value and its called Store
So my select statement looks like this..
SELECT Store, StoreNum
FROM tbEmerStore, tbIpRange
WHERE tbEmerStore.Store = tbIpRange.StoreNum AND 'varIP' LIKE
tbIpRange.IpRange
my variable varIP has a run-time value of
Request.ServerVariable("REMOTE_ADDR") so that I can capture the users IP
and compare it to the table.
Im not all that great with queries and variables... so im not sure what im
doing wrong...and our SQL guru is out on vacation since he just had a little
boy this week.. so I dont have anyone to help me with this. So I want to
compare the REMOTE_ADDR to the IPRange column.. and if they are alike then
ONLY return the stores that have that IP Range.
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:89C657FA-CA4F-4C8C-BF08-3C9447F6C1B5@.microsoft.com...
> I'm assuming that the varIP is storing the full octets, what does the
> tbIpRane.IpRange field data look like?
> I'm also assuming you are sending ad hoc T-SQL to the server instead of
> using a stored procedure. I'll also assume that you are only storing the
> truncated octet in the database. If this is so, modify your query,
thusly:
> varSQL = "
> SELECT *
> FROM tbEmerStore AS es
> INNER JOIN
> tbIpRange AS ir
> ON es.Store = ir.StoreNum
> WHERE ir.IpRange = " & LEFT(varIP, 12) & "
> ORDER BY es.Store
> "
> conn.Execute(varSQL)
> This is probably not exact given the line breaks, but you should be able
to
> get the point.
> To make life easier, I would strongly recommend that you use fixed-length,
> zero-padded IP addresses and subnets; otherwise, you are going to have to
do
> some INSTR pattern matching to find the last "." octet seperator to
determine[vbcol=seagreen]
> if the characters you need to truncate is 1, 2, or 3--that would suck.
> Sincerely,
>
> Anthony Thomas
> "Daniel_Cha" wrote:
that[vbcol=seagreen]
a[vbcol=seagreen]
that's[vbcol=seagreen]
|||I don't mean to give you too much trouble but what are you, dense?
Three of us have given you examples now. Your problem is two-fold.
1. You can't quote your variable: 'varIP'
2. Your source is longer than your search. You'll have to truncate part of
your source.
Look at the examples again.
Sincerely,
Anthony Thomas

"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:%23zj3AaCyEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Ok, I what is stored in the database is the following:
> Table = tbIpRange
> Column Name Value
> StoreNum 02 San Antonio
> IPRange 192.168.2
> Table = tbEmerStore
> Column Name Value
> Store 02 San Antonio
>
> So I have one table that contains this information for each store, since
> this will not change unless we add a new location to the company.
> In the table that contains all the information there is a column that
> contains the same StoreNum value and its called Store
> So my select statement looks like this..
> SELECT Store, StoreNum
> FROM tbEmerStore, tbIpRange
> WHERE tbEmerStore.Store = tbIpRange.StoreNum AND 'varIP' LIKE
> tbIpRange.IpRange
> my variable varIP has a run-time value of
> Request.ServerVariable("REMOTE_ADDR") so that I can capture the users IP
> and compare it to the table.
> Im not all that great with queries and variables... so im not sure what im
> doing wrong...and our SQL guru is out on vacation since he just had a
little
> boy this week.. so I dont have anyone to help me with this. So I want to
> compare the REMOTE_ADDR to the IPRange column.. and if they are alike
then[vbcol=seagreen]
> ONLY return the stores that have that IP Range.
>
>
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
> news:89C657FA-CA4F-4C8C-BF08-3C9447F6C1B5@.microsoft.com...
the[vbcol=seagreen]
> thusly:
> to
fixed-length,[vbcol=seagreen]
to[vbcol=seagreen]
> do
> determine
LIKE[vbcol=seagreen]
emergencies.[vbcol=seagreen]
have[vbcol=seagreen]
> that
as[vbcol=seagreen]
> a
table..[vbcol=seagreen]
post
> that's
>