How would you perform an SQL query on a number range in a recordset,(i.e. 255.255.255.0 -
255.255.255.255) and what would the actual expression look like ?

Chris Atherton
Harrison County
Computer Operations
xxx-xxx-xxxx
xxx-xxx-xxxx

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Chris Atherton" <webmaster@c...>
Subject: [asp_web_howto] SQL Number Query


: How would you perform an SQL query on a number
: range in a recordset,(i.e. 255.255.255.0 - 255.255.255.255)
: and what would the actual expression look like ?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Hi Chris,

Recordset's are an ADO concept. SQL is a DBMS data manipulation language.
You can't perform an "SQL query on a Recordset". You can run an SQL query in
a DBMS, and it will return a resultset that can be placed into an ADO
recordset.

255.255.255.0 - 255.255.255.255 isn't a "number range" as far as I can see.
It looks like literal text, or what programmers would call a "string". 255
is a number, as is 255.255.

As for your last question: "what would the expression look like?" - what
expression? Are you asking about what the WHERE clause in the SQL statement
would look like? If so, you need to explain what your criteria are: ie what
is going to selected, and what is going to be excluded.

Perhaps you could explain, in more detail, what you're trying to achieve.

Cheers
Ken

Well Ken,
Basically, I'm an idiot.    :o)     Actually, I'm only about a month into
ASP, SQL, and Databases so I still have a lot to learn. I have a fair amount
of stuff running, and running quite well.

Here's what I'm trying to accomplish.

I have a database that contains info such as department, user, description,
etc. This database also contains the I.P. and subnet of every machine on our
network. (hence the 255.255.255.0 segment. To me, a network administrator
trying to learn a language, this is an actual number range, although I'm not
sure how SQL or ASP actually see it) Our network contains 17 remote sites
all running different segments. In this database, I have 17 tables setup
(one for each of these sites). Then I created another table with all the
same info contained in one table. (4,000 + rows). I created my database in
Excel and imported into Access. I use no editor other than straight coding
in my html editor (Dreamweaver 4)

I would like to be able to query these ranges for editing via a form without
having to display all of them. I can do it using the individual tables, or
by displaying the entire contents of the one table.

I guess my first question would be:
Would it be better to run a single table with all the info there, or in
multiple tables ? ( Note: each table contains no more than 7 columns and 255
rows) If it is better to use this table, how would I be able to query
information and what would the WHERE clause look like ?
(okay, 2 questions :o)    so I guess I have 2 more)

2nd question:
If multiple tables is the way to go, how would I join that many tables in
SQL, and what would that clause look  like?
Most of my knowledge has come from Beginning ASP 3.0 and a whole lot of
experimentation.
Thanks for the response, sorry for the book size post and my lack of proper
language.

Most of all, thanks to everyone who takes the time to help a beginner like
me !


----- Original Message -----
From: "Ken Schaefer" <ken@a...>
To: "ASP Web HowTo" <asp_web_howto@p...>
Sent: Tuesday, January 07, 2003 7:25 PM
Subject: [asp_web_howto] Re: SQL Number Query


> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Chris Atherton" <webmaster@c...>
> Subject: [asp_web_howto] SQL Number Query
>
>
> : How would you perform an SQL query on a number
> : range in a recordset,(i.e. 255.255.255.0 - 255.255.255.255)
> : and what would the actual expression look like ?
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> Hi Chris,
>
> Recordset's are an ADO concept. SQL is a DBMS data manipulation language.
> You can't perform an "SQL query on a Recordset". You can run an SQL query
in
> a DBMS, and it will return a resultset that can be placed into an ADO
> recordset.
>
> 255.255.255.0 - 255.255.255.255 isn't a "number range" as far as I can
see.
> It looks like literal text, or what programmers would call a "string". 255
> is a number, as is 255.255.
>
> As for your last question: "what would the expression look like?" - what
> expression? Are you asking about what the WHERE clause in the SQL
statement
> would look like? If so, you need to explain what your criteria are: ie
what
> is going to selected, and what is going to be excluded.
>
> Perhaps you could explain, in more detail, what you're trying to achieve.
>
> Cheers
> Ken
>
>

Hi,

Seems like you have a few questions :-) I'll give you the simple answer
first, then the other answers.

If you have all the data in one table, like so:

IPAddress    Netmask            Field3        Fieldn
208.0.0.1      255.255.255.0      1                1
208.0.0.2      255.255.255.0      1                1
...
208.0.0.254  255.255.255.0       1                1
...
208.0.1.1      255.255.255.0       1               1
208.0.1.2      255.255.255.0       1               1

and you wanted to get all the records for the 208.0.1.0/255.255.255.0
subnet, then you could do:

SELECT
    IPAddress,
    Netmask,
    Field3,
    Field3
FROM
    Table1
WHERE
    LEFT(IPAddress, 7) = '208.0.1'

obviously, this would create problems if you had another subnet which was
'208.10.1' since that requires you to take the 8 leftmost characters. You
could use LIKE (which however defeats indexes...):

SELECT
    IPAddress,
    Netmask,
    Field3,
    Field3
FROM
    Table1
WHERE
    IPAddress LIKE '208.0.1.%'

-vs-

SELECT
    IPAddress,
    Netmask,
    Field3,
    Field3
FROM
    Table1
WHERE
    IPAddress LIKE '208.0.10.%'

***BUT***

The big question is "is this the right way to structure your database?" And
this is where you have to learn some kind of theory. There are two things
you should probably learn straight away *before* you get too heavily into
designing your database. The first is entity-relationship modelling, and the
second is normalisation. Both will help you design a proper data structure
that can be queried using SQL (SQL is a set-based language, and it didn't
develop in isolation. It developed alongside the principles of data
normalisation. Properly normalised data can be easily queried using SQL.
Everything else is real hassle).

In your case, a good ER diagram would help you sort these queries out. For
example, part of your problem is that an IP address is the concatenation of
4 octets. They're just written as 127.108.96.3 to make it easier for humans
to comprehend. So you're storing 4 sets of *numerical* data in one big text
field. Depending on your requirements, this may cause problems getting the
right data back out again. Then again, your requirements might be relatively
simple...

If you want the most flexibility, I'd store each octet in it's own byte
number field (values 0-255). That way, to get an octet out, you can do:

SELECT
    ...
WHERE
    Octet1 = 208
AND
    Octet2 = 0
AND
    Octet3 = 10

It'll also become easier to group records (suppose you want a count of the
number of IP Addresses allocated in each subnet etc, especially if you have
both ClassB and ClassC addresses:)

SELECT
    Octet1,
    Octet2,
    0,
    COUNT(*)
FROM
    Table1
WHERE
    NetMaskOctet3 = 0
GROUP BY
    Octet1,
    Octet2,
    0

UNION

SELECT
    Octet1,
    Octet2,
    Octet3
    COUNT(*)
FROM
    Table1
WHERE
    NetMaskOctet3 = 255
GROUP BY
    Octet1,
    Octet2,
    Octet3

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Harco Webmaster" <webmaster@c...>
Subject: [asp_web_howto] Re: SQL Number Query


: Well Ken,
: Basically, I'm an idiot.    :o)     Actually, I'm only about a month into
: ASP, SQL, and Databases so I still have a lot to learn. I have a fair
amount
: of stuff running, and running quite well.
:
: Here's what I'm trying to accomplish.
:
: I have a database that contains info such as department, user,
description,
: etc. This database also contains the I.P. and subnet of every machine on
our
: network. (hence the 255.255.255.0 segment. To me, a network administrator
: trying to learn a language, this is an actual number range, although I'm
not
: sure how SQL or ASP actually see it) Our network contains 17 remote sites
: all running different segments. In this database, I have 17 tables setup
: (one for each of these sites). Then I created another table with all the
: same info contained in one table. (4,000 + rows). I created my database in
: Excel and imported into Access. I use no editor other than straight coding
: in my html editor (Dreamweaver 4)
:
: I would like to be able to query these ranges for editing via a form
without
: having to display all of them. I can do it using the individual tables, or
: by displaying the entire contents of the one table.
:
: I guess my first question would be:
: Would it be better to run a single table with all the info there, or in
: multiple tables ? ( Note: each table contains no more than 7 columns and
255
: rows) If it is better to use this table, how would I be able to query
: information and what would the WHERE clause look like ?
: (okay, 2 questions :o)    so I guess I have 2 more)
:
: 2nd question:
: If multiple tables is the way to go, how would I join that many tables in
: SQL, and what would that clause look  like?
: Most of my knowledge has come from Beginning ASP 3.0 and a whole lot of
: experimentation.
: Thanks for the response, sorry for the book size post and my lack of
proper
: language.
:
: Most of all, thanks to everyone who takes the time to help a beginner like
: me !
:
:
: ----- Original Message -----
: From: "Ken Schaefer" <ken@a...>
: To: "ASP Web HowTo" <asp_web_howto@p...>
: Sent: Tuesday, January 07, 2003 7:25 PM
: Subject: [asp_web_howto] Re: SQL Number Query
:
:
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > From: "Chris Atherton" <webmaster@c...>
: > Subject: [asp_web_howto] SQL Number Query
: >
: >
: > : How would you perform an SQL query on a number
: > : range in a recordset,(i.e. 255.255.255.0 - 255.255.255.255)
: > : and what would the actual expression look like ?
: >
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: >
: > Hi Chris,
: >
: > Recordset's are an ADO concept. SQL is a DBMS data manipulation
language.
: > You can't perform an "SQL query on a Recordset". You can run an SQL
query
: in
: > a DBMS, and it will return a resultset that can be placed into an ADO
: > recordset.
: >
: > 255.255.255.0 - 255.255.255.255 isn't a "number range" as far as I can
: see.
: > It looks like literal text, or what programmers would call a "string".
255
: > is a number, as is 255.255.
: >
: > As for your last question: "what would the expression look like?" - what
: > expression? Are you asking about what the WHERE clause in the SQL
: statement
: > would look like? If so, you need to explain what your criteria are: ie
: what
: > is going to selected, and what is going to be excluded.
: >
: > Perhaps you could explain, in more detail, what you're trying to
achieve.
: >
: > Cheers
: > Ken
: >
: >
:
:

Ken,
YOU ARE THA' MAN !
I'm, for the most part, using objRS.Filter to get alot of my data. Seems to
work well and is very quick with returning data. I am also using a few basic
SQL queries and I like the idea of SQL better. Seems to be more efficient.

What recommendations do you have for learning SQL WROX pub is my only
example so far?

Thanks again. That helped probably more than you could know !

Chris


----- Original Message -----
From: "Ken Schaefer" <ken@a...>
To: "ASP Web HowTo" <asp_web_howto@p...>
Sent: Wednesday, January 08, 2003 6:45 PM
Subject: [asp_web_howto] Re: SQL Number Query


> Hi,
>
> Seems like you have a few questions :-) I'll give you the simple answer
> first, then the other answers.
>
> If you have all the data in one table, like so:
>
> IPAddress    Netmask            Field3        Fieldn
> 208.0.0.1      255.255.255.0      1                1
> 208.0.0.2      255.255.255.0      1                1
> ...
> 208.0.0.254  255.255.255.0       1                1
> ...
> 208.0.1.1      255.255.255.0       1               1
> 208.0.1.2      255.255.255.0       1               1
>
> and you wanted to get all the records for the 208.0.1.0/255.255.255.0
> subnet, then you could do:
>
> SELECT
>     IPAddress,
>     Netmask,
>     Field3,
>     Field3
> FROM
>     Table1
> WHERE
>     LEFT(IPAddress, 7) = '208.0.1'
>
> obviously, this would create problems if you had another subnet which was
> '208.10.1' since that requires you to take the 8 leftmost characters. You
> could use LIKE (which however defeats indexes...):
>
> SELECT
>     IPAddress,
>     Netmask,
>     Field3,
>     Field3
> FROM
>     Table1
> WHERE
>     IPAddress LIKE '208.0.1.%'
>
> -vs-
>
> SELECT
>     IPAddress,
>     Netmask,
>     Field3,
>     Field3
> FROM
>     Table1
> WHERE
>     IPAddress LIKE '208.0.10.%'
>
> ***BUT***
>
> The big question is "is this the right way to structure your database?"
And
> this is where you have to learn some kind of theory. There are two things
> you should probably learn straight away *before* you get too heavily into
> designing your database. The first is entity-relationship modelling, and
the
> second is normalisation. Both will help you design a proper data structure
> that can be queried using SQL (SQL is a set-based language, and it didn't
> develop in isolation. It developed alongside the principles of data
> normalisation. Properly normalised data can be easily queried using SQL.
> Everything else is real hassle).
>
> In your case, a good ER diagram would help you sort these queries out. For
> example, part of your problem is that an IP address is the concatenation
of
> 4 octets. They're just written as 127.108.96.3 to make it easier for
humans
> to comprehend. So you're storing 4 sets of *numerical* data in one big
text
> field. Depending on your requirements, this may cause problems getting the
> right data back out again. Then again, your requirements might be
relatively
> simple...
>
> If you want the most flexibility, I'd store each octet in it's own byte
> number field (values 0-255). That way, to get an octet out, you can do:
>
> SELECT
>     ...
> WHERE
>     Octet1 = 208
> AND
>     Octet2 = 0
> AND
>     Octet3 = 10
>
> It'll also become easier to group records (suppose you want a count of the
> number of IP Addresses allocated in each subnet etc, especially if you
have
> both ClassB and ClassC addresses:)
>
> SELECT
>     Octet1,
>     Octet2,
>     0,
>     COUNT(*)
> FROM
>     Table1
> WHERE
>     NetMaskOctet3 = 0
> GROUP BY
>     Octet1,
>     Octet2,
>     0
>
> UNION
>
> SELECT
>     Octet1,
>     Octet2,
>     Octet3
>     COUNT(*)
> FROM
>     Table1
> WHERE
>     NetMaskOctet3 = 255
> GROUP BY
>     Octet1,
>     Octet2,
>     Octet3
>
> Cheers
> Ken
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Harco Webmaster" <webmaster@c...>
> Subject: [asp_web_howto] Re: SQL Number Query
>
>
> : Well Ken,
> : Basically, I'm an idiot.    :o)     Actually, I'm only about a month
into
> : ASP, SQL, and Databases so I still have a lot to learn. I have a fair
> amount
> : of stuff running, and running quite well.
> :
> : Here's what I'm trying to accomplish.
> :
> : I have a database that contains info such as department, user,
> description,
> : etc. This database also contains the I.P. and subnet of every machine on
> our
> : network. (hence the 255.255.255.0 segment. To me, a network
administrator
> : trying to learn a language, this is an actual number range, although I'm
> not
> : sure how SQL or ASP actually see it) Our network contains 17 remote
sites
> : all running different segments. In this database, I have 17 tables setup
> : (one for each of these sites). Then I created another table with all the
> : same info contained in one table. (4,000 + rows). I created my database
in
> : Excel and imported into Access. I use no editor other than straight
coding
> : in my html editor (Dreamweaver 4)
> :
> : I would like to be able to query these ranges for editing via a form
> without
> : having to display all of them. I can do it using the individual tables,
or
> : by displaying the entire contents of the one table.
> :
> : I guess my first question would be:
> : Would it be better to run a single table with all the info there, or in
> : multiple tables ? ( Note: each table contains no more than 7 columns and
> 255
> : rows) If it is better to use this table, how would I be able to query
> : information and what would the WHERE clause look like ?
> : (okay, 2 questions :o)    so I guess I have 2 more)
> :
> : 2nd question:
> : If multiple tables is the way to go, how would I join that many tables
in
> : SQL, and what would that clause look  like?
> : Most of my knowledge has come from Beginning ASP 3.0 and a whole lot of
> : experimentation.
> : Thanks for the response, sorry for the book size post and my lack of
> proper
> : language.
> :
> : Most of all, thanks to everyone who takes the time to help a beginner
like
> : me !
> :
> :
> : ----- Original Message -----
> : From: "Ken Schaefer" <ken@a...>
> : To: "ASP Web HowTo" <asp_web_howto@p...>
> : Sent: Tuesday, January 07, 2003 7:25 PM
> : Subject: [asp_web_howto] Re: SQL Number Query
> :
> :
> : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : > From: "Chris Atherton" <webmaster@c...>
> : > Subject: [asp_web_howto] SQL Number Query
> : >
> : >
> : > : How would you perform an SQL query on a number
> : > : range in a recordset,(i.e. 255.255.255.0 - 255.255.255.255)
> : > : and what would the actual expression look like ?
> : >
> : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : >
> : > Hi Chris,
> : >
> : > Recordset's are an ADO concept. SQL is a DBMS data manipulation
> language.
> : > You can't perform an "SQL query on a Recordset". You can run an SQL
> query
> : in
> : > a DBMS, and it will return a resultset that can be placed into an ADO
> : > recordset.
> : >
> : > 255.255.255.0 - 255.255.255.255 isn't a "number range" as far as I can
> : see.
> : > It looks like literal text, or what programmers would call a "string".
> 255
> : > is a number, as is 255.255.
> : >
> : > As for your last question: "what would the expression look like?" -
what
> : > expression? Are you asking about what the WHERE clause in the SQL
> : statement
> : > would look like? If so, you need to explain what your criteria are: ie
> : what
> : > is going to selected, and what is going to be excluded.
> : >
> : > Perhaps you could explain, in more detail, what you're trying to
> achieve.
> : >
> : > Cheers
> : > Ken
> : >
> : >
> :
> :
>
>

Hi,

Sorry for the long delay in replying - I've been away on holidays for the
most part.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Harco Webmaster" <webmaster@c...>
Subject: [asp_web_howto] Re: SQL Number Query


: I'm, for the most part, using objRS.Filter to get alot of my data. Seems
to
: work well and is very quick with returning data.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

...maybe if you are the only person using the website. However, if you load
test your application you will see about a 40-50% drop in performance.
20-30% will come just from having to use an adOpenStatic cursor. Another 20%
will come from using .Filter.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: I am also using a few basic
: SQL queries and I like the idea of SQL better. Seems to be more efficient.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL is *much* more efficient. Additionally, you can put your SQL into a
stored procedure in SQL Server. This then runs faster (since the sproc's
execution plan is compiled), and it's more secure, and it gives you access
to features of T-SQL (SQL Server's version of SQL) that isn't available
though straight SQL (and certainly not through the Recordset object).

If you can use SQL to return just a limited subset of data to your
application layer, you'll get much better performance. This is probably a
bad analogy, but it's the best I can think of quickly.

You're a consumer. You go to a restaurant. They bring out everything that
the kitchen can produce. You select the meal you want. Everything else is
thrown away.

 -vs-

 You go to a restaurant. You tell the kitchen what you want, and they work
out what needs to be done, and bring you just the thing you ordered, and
nothing else.

 Which do you think is faster? :-)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: What recommendations do you have for learning SQL WROX pub is my only
: example so far?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If you are using SQL Server, pick up Professional SQL Server 2000 (or v7 if
have the older version) by Rob Vieira (Wrox Press). This is a good
beginner-intermediate book on developing for SQL Server. For a straight SQL
tutorial, you could take a look at: http://www.w3schools.com/sql/default.asp

Cheers
Ken