|
 |
asp_databases thread: Search by keyword select statement
Message #1 by Kat Howlett <kat.howlett@c...> on Wed, 18 Apr 2001 12:49:24 +1000
|
|
Hello
I have been trying to do a search by keyword function in my asp database. I
have used a select statement to companre a keyword to an item in the
Description field in my database. Here is my SQL statement: (where keyword
is a previously entered variable)
SELECT * FROM Activity WHERE Description LIKE '*"&keyword&"*';
Have I done this right? is there a better way to search througha field in an
access database to find a keyword??
Thanks heaps
Kat
Message #2 by Jason Greenfeld-Unitek <jason.greenfeld@u...> on Wed, 18 Apr 2001 08:37:18 -0400
|
|
You are pretty close. Use % instead of *
ex:
SELECT * FROM Activity WHERE Description LIKE % 'string ' %
----------------------------------------------------
Jason A. Greenfeld
Application Developer
Unitek Technical Services
-----Original Message-----
From: Kat Howlett [mailto:kat.howlett@c...]
Sent: Tuesday, April 17, 2001 10:49 PM
To: ASP Databases
Subject: [asp_databases] Search by keyword select statement
Hello
I have been trying to do a search by keyword function in my asp database. I
have used a select statement to companre a keyword to an item in the
Description field in my database. Here is my SQL statement: (where keyword
is a previously entered variable)
SELECT * FROM Activity WHERE Description LIKE '*"&keyword&"*';
Have I done this right? is there a better way to search througha field in an
access database to find a keyword??
Thanks heaps
Kat
Message #3 by "Peter Foti (PeterF)" <PeterF@S...> on Wed, 18 Apr 2001 10:39:13 -0400
|
|
Hi Kat,
When using ASP to generate SQL expressions, the wildcard character for
Access is %, not *. I would suggest creating a variable to hold your
wildcard character (in case you ever move to a different database that
uses a different wildcard character, it would be easier to change it in
one place). Also, you should check to make sure your keyword isn't
empty, because if you search for the wildcard only, then you will return
ALL results, which is probably not what you are trying to do. For
example:
<%
' This is in some file that is included on all pages that do a search
dbWildcardChar = "%"
%>
<%
' This is in a page that does a search
if Len(keyword) > 0 then
strSearch = dbWildcardChar & keyword & dbWildcardChar
Else
strSearch = ""
End if
SQLstr = "SELECT * FROM Activity WHERE Description LIKE '" & strSearch &
"'"
%>
Also, you might want to consider doing a search for single quotes in
your keyword and replacing them. To do that, add this to your file that
is included:
<%
Function sql_quote(str)
str = replace(str,"'","''")
sql_quote = "'"& str & "'"
End Function
%>
and modify your SQL string to look like this:
SQLstr = "SELECT * FROM Activity WHERE Description LIKE " &
sql_quote(strSearch)
This will prevent runtime errors when the keyword contains one or more
single quotes.
Also note the the ; is not needed at the end of the SQL string.
Good luck,
Pete
> -----Original Message-----
> From: Kat Howlett [mailto:kat.howlett@c...]
> Sent: Tuesday, April 17, 2001 10:49 PM
> To: ASP Databases
> Subject: [asp_databases] Search by keyword select statement
>
>
> Hello
> I have been trying to do a search by keyword function in my
> asp database. I
> have used a select statement to companre a keyword to an item in the
> Description field in my database. Here is my SQL statement:
> (where keyword
> is a previously entered variable)
>
> SELECT * FROM Activity WHERE Description LIKE '*"&keyword&"*';
>
> Have I done this right? is there a better way to search
> througha field in an
> access database to find a keyword??
>
> Thanks heaps
> Kat
>
Message #4 by "Daniel O'Dorisio" <dodorisio@h...> on Wed, 18 Apr 2001 10:42:07 -0400
|
|
isnt this an "expensive" search? or will it work ok on a shared SQL server
with about oh say 100 hits a day
Daniel
-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...]
Sent: Wednesday, April 18, 2001 10:39 AM
To: ASP Databases
Subject: [asp_databases] RE: Search by keyword select statement
Hi Kat,
When using ASP to generate SQL expressions, the wildcard character for
Access is %, not *. I would suggest creating a variable to hold your
wildcard character (in case you ever move to a different database that
uses a different wildcard character, it would be easier to change it in
one place). Also, you should check to make sure your keyword isn't
empty, because if you search for the wildcard only, then you will return
ALL results, which is probably not what you are trying to do. For
example:
<%
' This is in some file that is included on all pages that do a search
dbWildcardChar = "%"
%>
<%
' This is in a page that does a search
if Len(keyword) > 0 then
strSearch = dbWildcardChar & keyword & dbWildcardChar
Else
strSearch = ""
End if
SQLstr = "SELECT * FROM Activity WHERE Description LIKE '" & strSearch &
"'"
%>
Also, you might want to consider doing a search for single quotes in
your keyword and replacing them. To do that, add this to your file that
is included:
<%
Function sql_quote(str)
str = replace(str,"'","''")
sql_quote = "'"& str & "'"
End Function
%>
and modify your SQL string to look like this:
SQLstr = "SELECT * FROM Activity WHERE Description LIKE " &
sql_quote(strSearch)
This will prevent runtime errors when the keyword contains one or more
single quotes.
Also note the the ; is not needed at the end of the SQL string.
Good luck,
Pete
> -----Original Message-----
> From: Kat Howlett [mailto:kat.howlett@c...]
> Sent: Tuesday, April 17, 2001 10:49 PM
> To: ASP Databases
> Subject: [asp_databases] Search by keyword select statement
>
>
> Hello
> I have been trying to do a search by keyword function in my
> asp database. I
> have used a select statement to companre a keyword to an item in the
> Description field in my database. Here is my SQL statement:
> (where keyword
> is a previously entered variable)
>
> SELECT * FROM Activity WHERE Description LIKE '*"&keyword&"*';
>
> Have I done this right? is there a better way to search
> througha field in an
> access database to find a keyword??
>
> Thanks heaps
> Kat
>
Message #5 by "Peter Foti (PeterF)" <PeterF@S...> on Wed, 18 Apr 2001 11:35:00 -0400
|
|
Expensive? How so?
I don't see why it wouldn't work ok on a shared SQL server with 100 hits
per day. Obviously there are some other things to take into
consideration (like exactly how much info is stored in the database),
but to my knowledge there is nothing problematic with this search
method.
-Pete
> -----Original Message-----
> From: Daniel O'Dorisio [mailto:dodorisio@h...]
> Sent: Wednesday, April 18, 2001 10:42 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Search by keyword select statement
>
>
> isnt this an "expensive" search? or will it work ok on a
> shared SQL server
> with about oh say 100 hits a day
>
> Daniel
>
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Wednesday, April 18, 2001 10:39 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Search by keyword select statement
>
>
> Hi Kat,
>
> When using ASP to generate SQL expressions, the wildcard character for
> Access is %, not *. I would suggest creating a variable to hold your
> wildcard character (in case you ever move to a different database that
> uses a different wildcard character, it would be easier to
> change it in
> one place). Also, you should check to make sure your keyword isn't
> empty, because if you search for the wildcard only, then you
> will return
> ALL results, which is probably not what you are trying to do. For
> example:
>
> <%
> ' This is in some file that is included on all pages that do a search
> dbWildcardChar = "%"
> %>
>
> <%
> ' This is in a page that does a search
> if Len(keyword) > 0 then
> strSearch = dbWildcardChar & keyword & dbWildcardChar
> Else
> strSearch = ""
> End if
>
> SQLstr = "SELECT * FROM Activity WHERE Description LIKE '" &
> strSearch &
> "'"
> %>
>
>
> Also, you might want to consider doing a search for single quotes in
> your keyword and replacing them. To do that, add this to
> your file that
> is included:
> <%
> Function sql_quote(str)
> str = replace(str,"'","''")
> sql_quote = "'"& str & "'"
> End Function
> %>
>
> and modify your SQL string to look like this:
> SQLstr = "SELECT * FROM Activity WHERE Description LIKE " &
> sql_quote(strSearch)
>
> This will prevent runtime errors when the keyword contains one or more
> single quotes.
> Also note the the ; is not needed at the end of the SQL string.
>
> Good luck,
> Pete
>
>
> > -----Original Message-----
> > From: Kat Howlett [mailto:kat.howlett@c...]
> > Sent: Tuesday, April 17, 2001 10:49 PM
> > To: ASP Databases
> > Subject: [asp_databases] Search by keyword select statement
> >
> >
> > Hello
> > I have been trying to do a search by keyword function in my
> > asp database. I
> > have used a select statement to companre a keyword to an item in the
> > Description field in my database. Here is my SQL statement:
> > (where keyword
> > is a previously entered variable)
> >
> > SELECT * FROM Activity WHERE Description LIKE '*"&keyword&"*';
> >
> > Have I done this right? is there a better way to search
> > througha field in an
> > access database to find a keyword??
> >
> > Thanks heaps
> > Kat
> >
>
Message #6 by "Daniel O'Dorisio" <dodorisio@h...> on Wed, 18 Apr 2001 11:38:51 -0400
|
|
i had read that doing just a table scan took alot of resources.
100 hits is only for me.. there are many many other databases on this
server. like several hundred, and heavens knows how many hits they get a
day.
i guess ill just try it. if i got the table indexed that would help..
correct? but how would i utilize the index?
as you can see... i have had no formal training on asp, ado, sql, html
even.... i am mostly using the trial and error method.. and a few books.
Daniel
-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...]
Sent: Wednesday, April 18, 2001 11:35 AM
To: ASP Databases
Subject: [asp_databases] RE: Search by keyword select statement
Expensive? How so?
I don't see why it wouldn't work ok on a shared SQL server with 100 hits
per day. Obviously there are some other things to take into
consideration (like exactly how much info is stored in the database),
but to my knowledge there is nothing problematic with this search
method.
-Pete
> -----Original Message-----
> From: Daniel O'Dorisio [mailto:dodorisio@h...]
> Sent: Wednesday, April 18, 2001 10:42 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Search by keyword select statement
>
>
> isnt this an "expensive" search? or will it work ok on a
> shared SQL server
> with about oh say 100 hits a day
>
> Daniel
>
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Wednesday, April 18, 2001 10:39 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Search by keyword select statement
>
>
> Hi Kat,
>
> When using ASP to generate SQL expressions, the wildcard character for
> Access is %, not *. I would suggest creating a variable to hold your
> wildcard character (in case you ever move to a different database that
> uses a different wildcard character, it would be easier to
> change it in
> one place). Also, you should check to make sure your keyword isn't
> empty, because if you search for the wildcard only, then you
> will return
> ALL results, which is probably not what you are trying to do. For
> example:
>
> <%
> ' This is in some file that is included on all pages that do a search
> dbWildcardChar = "%"
> %>
>
> <%
> ' This is in a page that does a search
> if Len(keyword) > 0 then
> strSearch = dbWildcardChar & keyword & dbWildcardChar
> Else
> strSearch = ""
> End if
>
> SQLstr = "SELECT * FROM Activity WHERE Description LIKE '" &
> strSearch &
> "'"
> %>
>
>
> Also, you might want to consider doing a search for single quotes in
> your keyword and replacing them. To do that, add this to
> your file that
> is included:
> <%
> Function sql_quote(str)
> str = replace(str,"'","''")
> sql_quote = "'"& str & "'"
> End Function
> %>
>
> and modify your SQL string to look like this:
> SQLstr = "SELECT * FROM Activity WHERE Description LIKE " &
> sql_quote(strSearch)
>
> This will prevent runtime errors when the keyword contains one or more
> single quotes.
> Also note the the ; is not needed at the end of the SQL string.
>
> Good luck,
> Pete
>
>
> > -----Original Message-----
> > From: Kat Howlett [mailto:kat.howlett@c...]
> > Sent: Tuesday, April 17, 2001 10:49 PM
> > To: ASP Databases
> > Subject: [asp_databases] Search by keyword select statement
> >
> >
> > Hello
> > I have been trying to do a search by keyword function in my
> > asp database. I
> > have used a select statement to companre a keyword to an item in the
> > Description field in my database. Here is my SQL statement:
> > (where keyword
> > is a previously entered variable)
> >
> > SELECT * FROM Activity WHERE Description LIKE '*"&keyword&"*';
> >
> > Have I done this right? is there a better way to search
> > througha field in an
> > access database to find a keyword??
> >
> > Thanks heaps
> > Kat
> >
>
Message #7 by "David E" <registerukh@h...> on Wed, 18 Apr 2001 12:36:01 -0400
|
|
Also, when the SQL query (which is used in this thread) is run, the database
server returns all the matching records (no matter how many) to the web
server.
Now, if you are not displaying all the hits in just one page then this act
will be (very) expensive one.
>From: "Daniel O'Dorisio" <dodorisio@h...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] RE: Search by keyword select statement
>Date: Wed, 18 Apr 2001 11:38:51 -0400
>
>i had read that doing just a table scan took alot of resources.
>100 hits is only for me.. there are many many other databases on this
>server. like several hundred, and heavens knows how many hits they get a
>day.
>
>i guess ill just try it. if i got the table indexed that would help..
>correct? but how would i utilize the index?
>
>as you can see... i have had no formal training on asp, ado, sql, html
>even.... i am mostly using the trial and error method.. and a few books.
>
>
>Daniel
>
>-----Original Message-----
>From: Peter Foti (PeterF) [mailto:PeterF@S...]
>Sent: Wednesday, April 18, 2001 11:35 AM
>To: ASP Databases
>Subject: [asp_databases] RE: Search by keyword select statement
>
>
>Expensive? How so?
>
>I don't see why it wouldn't work ok on a shared SQL server with 100 hits
>per day. Obviously there are some other things to take into
>consideration (like exactly how much info is stored in the database),
>but to my knowledge there is nothing problematic with this search
>method.
>
>-Pete
Message #8 by "Peter Lanoie" <planoie@e...> on Wed, 18 Apr 2001 13:04:46 -0400
|
|
"You" don't utilize an index. The SQL engine does. The index is an
internal thing. When you make a select call to a table that has an index,
the index makes the select happen faster. Particularly useful when you have
an index on a primary key field such as a userid. Typically, that's the
field in which the data you are "looking" for resides (where userid=xx in
your select where clause). The index on that field makes it faster for SQL
to find it.
Look at the index in a book. You could certainly find an entry in an
unorganized list if you looked long and hard enough, but it's a bit easier
when it's ordered, or 'indexed'.
In SQL enterprise manager you can create these indexes.
Right click on a table -> All Tasks -> Manage Indexes...
Peter
-----Original Message-----
From: Daniel O'Dorisio [mailto:dodorisio@h...]
Sent: Wednesday, April 18, 2001 11:39 AM
To: ASP Databases
Subject: [asp_databases] RE: Search by keyword select statement
i had read that doing just a table scan took alot of resources.
100 hits is only for me.. there are many many other databases on this
server. like several hundred, and heavens knows how many hits they get a
day.
i guess ill just try it. if i got the table indexed that would help..
correct? but how would i utilize the index?
as you can see... i have had no formal training on asp, ado, sql, html
even.... i am mostly using the trial and error method.. and a few books.
Daniel
-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...]
Sent: Wednesday, April 18, 2001 11:35 AM
To: ASP Databases
Subject: [asp_databases] RE: Search by keyword select statement
Expensive? How so?
I don't see why it wouldn't work ok on a shared SQL server with 100 hits
per day. Obviously there are some other things to take into
consideration (like exactly how much info is stored in the database),
but to my knowledge there is nothing problematic with this search
method.
-Pete
> -----Original Message-----
> From: Daniel O'Dorisio [mailto:dodorisio@h...]
> Sent: Wednesday, April 18, 2001 10:42 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Search by keyword select statement
>
>
> isnt this an "expensive" search? or will it work ok on a
> shared SQL server
> with about oh say 100 hits a day
>
> Daniel
>
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Wednesday, April 18, 2001 10:39 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Search by keyword select statement
>
>
> Hi Kat,
>
> When using ASP to generate SQL expressions, the wildcard character for
> Access is %, not *. I would suggest creating a variable to hold your
> wildcard character (in case you ever move to a different database that
> uses a different wildcard character, it would be easier to
> change it in
> one place). Also, you should check to make sure your keyword isn't
> empty, because if you search for the wildcard only, then you
> will return
> ALL results, which is probably not what you are trying to do. For
> example:
>
> <%
> ' This is in some file that is included on all pages that do a search
> dbWildcardChar = "%"
> %>
>
> <%
> ' This is in a page that does a search
> if Len(keyword) > 0 then
> strSearch = dbWildcardChar & keyword & dbWildcardChar
> Else
> strSearch = ""
> End if
>
> SQLstr = "SELECT * FROM Activity WHERE Description LIKE '" &
> strSearch &
> "'"
> %>
>
>
> Also, you might want to consider doing a search for single quotes in
> your keyword and replacing them. To do that, add this to
> your file that
> is included:
> <%
> Function sql_quote(str)
> str = replace(str,"'","''")
> sql_quote = "'"& str & "'"
> End Function
> %>
>
> and modify your SQL string to look like this:
> SQLstr = "SELECT * FROM Activity WHERE Description LIKE " &
> sql_quote(strSearch)
>
> This will prevent runtime errors when the keyword contains one or more
> single quotes.
> Also note the the ; is not needed at the end of the SQL string.
>
> Good luck,
> Pete
>
>
> > -----Original Message-----
> > From: Kat Howlett [mailto:kat.howlett@c...]
> > Sent: Tuesday, April 17, 2001 10:49 PM
> > To: ASP Databases
> > Subject: [asp_databases] Search by keyword select statement
> >
> >
> > Hello
> > I have been trying to do a search by keyword function in my
> > asp database. I
> > have used a select statement to companre a keyword to an item in the
> > Description field in my database. Here is my SQL statement:
> > (where keyword
> > is a previously entered variable)
> >
> > SELECT * FROM Activity WHERE Description LIKE '*"&keyword&"*';
> >
> > Have I done this right? is there a better way to search
> > througha field in an
> > access database to find a keyword??
> >
> > Thanks heaps
> > Kat
> >
>
Message #9 by "Peter Foti (PeterF)" <PeterF@S...> on Wed, 18 Apr 2001 13:25:07 -0400
|
|
Hi Daniel,
Actually, this is a case where indexes would be of no help. However,
you should have your tables indexed anyway. For example, you could do
your search, and then use the unique identifier to pass to other pages.
That way, you dont need to pass around every piece of information from
your search, just the index that identifies the record, and each page
could get the fields it needed from the database based on the ID.
But in answer to your question, this method could (and should) be
improved upon by paging your search results (for example, displaying 10
results per page or something to that effect). But for applications
where there are not many records to search from, this may not be needed.
Pete
> -----Original Message-----
> From: Daniel O'Dorisio [mailto:dodorisio@h...]
> Sent: Wednesday, April 18, 2001 11:39 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Search by keyword select statement
>
>
> i had read that doing just a table scan took alot of resources.
> 100 hits is only for me.. there are many many other databases on this
> server. like several hundred, and heavens knows how many hits
> they get a
> day.
>
> i guess ill just try it. if i got the table indexed that would help..
> correct? but how would i utilize the index?
>
> as you can see... i have had no formal training on asp, ado,
> sql, html
> even.... i am mostly using the trial and error method.. and a
> few books.
>
>
> Daniel
>
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Wednesday, April 18, 2001 11:35 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Search by keyword select statement
>
>
> Expensive? How so?
>
> I don't see why it wouldn't work ok on a shared SQL server
> with 100 hits
> per day. Obviously there are some other things to take into
> consideration (like exactly how much info is stored in the database),
> but to my knowledge there is nothing problematic with this search
> method.
>
> -Pete
>
> > -----Original Message-----
> > From: Daniel O'Dorisio [mailto:dodorisio@h...]
> > Sent: Wednesday, April 18, 2001 10:42 AM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Search by keyword select statement
> >
> >
> > isnt this an "expensive" search? or will it work ok on a
> > shared SQL server
> > with about oh say 100 hits a day
> >
> > Daniel
> >
> > -----Original Message-----
> > From: Peter Foti (PeterF) [mailto:PeterF@S...]
> > Sent: Wednesday, April 18, 2001 10:39 AM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Search by keyword select statement
> >
> >
> > Hi Kat,
> >
> > When using ASP to generate SQL expressions, the wildcard
> character for
> > Access is %, not *. I would suggest creating a variable to
> hold your
> > wildcard character (in case you ever move to a different
> database that
> > uses a different wildcard character, it would be easier to
> > change it in
> > one place). Also, you should check to make sure your keyword isn't
> > empty, because if you search for the wildcard only, then you
> > will return
> > ALL results, which is probably not what you are trying to do. For
> > example:
> >
> > <%
> > ' This is in some file that is included on all pages that
> do a search
> > dbWildcardChar = "%"
> > %>
> >
> > <%
> > ' This is in a page that does a search
> > if Len(keyword) > 0 then
> > strSearch = dbWildcardChar & keyword & dbWildcardChar
> > Else
> > strSearch = ""
> > End if
> >
> > SQLstr = "SELECT * FROM Activity WHERE Description LIKE '" &
> > strSearch &
> > "'"
> > %>
> >
> >
> > Also, you might want to consider doing a search for single quotes in
> > your keyword and replacing them. To do that, add this to
> > your file that
> > is included:
> > <%
> > Function sql_quote(str)
> > str = replace(str,"'","''")
> > sql_quote = "'"& str & "'"
> > End Function
> > %>
> >
> > and modify your SQL string to look like this:
> > SQLstr = "SELECT * FROM Activity WHERE Description LIKE " &
> > sql_quote(strSearch)
> >
> > This will prevent runtime errors when the keyword contains
> one or more
> > single quotes.
> > Also note the the ; is not needed at the end of the SQL string.
> >
> > Good luck,
> > Pete
> >
> >
> > > -----Original Message-----
> > > From: Kat Howlett [mailto:kat.howlett@c...]
> > > Sent: Tuesday, April 17, 2001 10:49 PM
> > > To: ASP Databases
> > > Subject: [asp_databases] Search by keyword select statement
> > >
> > >
> > > Hello
> > > I have been trying to do a search by keyword function in my
> > > asp database. I
> > > have used a select statement to companre a keyword to an
> item in the
> > > Description field in my database. Here is my SQL statement:
> > > (where keyword
> > > is a previously entered variable)
> > >
> > > SELECT * FROM Activity WHERE Description LIKE '*"&keyword&"*';
> > >
> > > Have I done this right? is there a better way to search
> > > througha field in an
> > > access database to find a keyword??
> > >
> > > Thanks heaps
> > > Kat
> > >
>
Message #10 by "Daniel O'Dorisio" <dodorisio@h...> on Wed, 18 Apr 2001 17:49:10 -0400
|
|
ahhh.. ok.. i got ya..
i was under the impression that i needed to code a different select
statement to use the Full Text Index.
i guess thats what i get for just reading articles and email lists:-) i
really need to go out and purchase a SQL book and Wrox's Pro ASP 3.0 and
maybe the Database book.... the only thing that is stopping me is... im not
sure whether i want to develop for a career (im 15, sophmore in hs). it is a
toss up between development (ASP Web apps, with COM+ or com.net whatever ms
is calling these days) and networking.. and i love them both... so.. hmmm
:-)
ya.. i have created them before.. but this is a shared environment so they
have to do it. i do know that they have it installed though. i jsut havnt
gotten them to create it yet.
so i can just do that LIKE clause on the three fields and it will utilize
the full text index. sounds good... this list might be getting an email from
me when i am banging my head against the wall at 2 am trying to get it to
work:-)
Daniel
-----Original Message-----
From: Peter Lanoie [mailto:planoie@e...]
Sent: Wednesday, April 18, 2001 1:05 PM
To: ASP Databases
Subject: [asp_databases] RE: Search by keyword select statement
"You" don't utilize an index. The SQL engine does. The index is an
internal thing. When you make a select call to a table that has an index,
the index makes the select happen faster. Particularly useful when you have
an index on a primary key field such as a userid. Typically, that's the
field in which the data you are "looking" for resides (where userid=xx in
your select where clause). The index on that field makes it faster for SQL
to find it.
Look at the index in a book. You could certainly find an entry in an
unorganized list if you looked long and hard enough, but it's a bit easier
when it's ordered, or 'indexed'.
In SQL enterprise manager you can create these indexes.
Right click on a table -> All Tasks -> Manage Indexes...
Peter
-----Original Message-----
From: Daniel O'Dorisio [mailto:dodorisio@h...]
Sent: Wednesday, April 18, 2001 11:39 AM
To: ASP Databases
Subject: [asp_databases] RE: Search by keyword select statement
i had read that doing just a table scan took alot of resources.
100 hits is only for me.. there are many many other databases on this
server. like several hundred, and heavens knows how many hits they get a
day.
i guess ill just try it. if i got the table indexed that would help..
correct? but how would i utilize the index?
as you can see... i have had no formal training on asp, ado, sql, html
even.... i am mostly using the trial and error method.. and a few books.
Daniel
-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...]
Sent: Wednesday, April 18, 2001 11:35 AM
To: ASP Databases
Subject: [asp_databases] RE: Search by keyword select statement
Expensive? How so?
I don't see why it wouldn't work ok on a shared SQL server with 100 hits
per day. Obviously there are some other things to take into
consideration (like exactly how much info is stored in the database),
but to my knowledge there is nothing problematic with this search
method.
-Pete
> -----Original Message-----
> From: Daniel O'Dorisio [mailto:dodorisio@h...]
> Sent: Wednesday, April 18, 2001 10:42 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Search by keyword select statement
>
>
> isnt this an "expensive" search? or will it work ok on a
> shared SQL server
> with about oh say 100 hits a day
>
> Daniel
>
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Wednesday, April 18, 2001 10:39 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Search by keyword select statement
>
>
> Hi Kat,
>
> When using ASP to generate SQL expressions, the wildcard character for
> Access is %, not *. I would suggest creating a variable to hold your
> wildcard character (in case you ever move to a different database that
> uses a different wildcard character, it would be easier to
> change it in
> one place). Also, you should check to make sure your keyword isn't
> empty, because if you search for the wildcard only, then you
> will return
> ALL results, which is probably not what you are trying to do. For
> example:
>
> <%
> ' This is in some file that is included on all pages that do a search
> dbWildcardChar = "%"
> %>
>
> <%
> ' This is in a page that does a search
> if Len(keyword) > 0 then
> strSearch = dbWildcardChar & keyword & dbWildcardChar
> Else
> strSearch = ""
> End if
>
> SQLstr = "SELECT * FROM Activity WHERE Description LIKE '" &
> strSearch &
> "'"
> %>
>
>
> Also, you might want to consider doing a search for single quotes in
> your keyword and replacing them. To do that, add this to
> your file that
> is included:
> <%
> Function sql_quote(str)
> str = replace(str,"'","''")
> sql_quote = "'"& str & "'"
> End Function
> %>
>
> and modify your SQL string to look like this:
> SQLstr = "SELECT * FROM Activity WHERE Description LIKE " &
> sql_quote(strSearch)
>
> This will prevent runtime errors when the keyword contains one or more
> single quotes.
> Also note the the ; is not needed at the end of the SQL string.
>
> Good luck,
> Pete
>
>
> > -----Original Message-----
> > From: Kat Howlett [mailto:kat.howlett@c...]
> > Sent: Tuesday, April 17, 2001 10:49 PM
> > To: ASP Databases
> > Subject: [asp_databases] Search by keyword select statement
> >
> >
> > Hello
> > I have been trying to do a search by keyword function in my
> > asp database. I
> > have used a select statement to companre a keyword to an item in the
> > Description field in my database. Here is my SQL statement:
> > (where keyword
> > is a previously entered variable)
> >
> > SELECT * FROM Activity WHERE Description LIKE '*"&keyword&"*';
> >
> > Have I done this right? is there a better way to search
> > througha field in an
> > access database to find a keyword??
> >
> > Thanks heaps
> > Kat
> >
>
Message #11 by "Charles Feduke" <webmaster@r...> on Wed, 18 Apr 2001 19:13:07 -0400
|
|
> maybe the Database book.... the only thing that is stopping me is... im
not
> sure whether i want to develop for a career (im 15, sophmore in hs). it is
a
> toss up between development (ASP Web apps, with COM+ or com.net whatever
ms
> is calling these days) and networking.. and i love them both... so.. hmmm
I started when I was 15. Its really good for people who are *really*
into computers. You make good money, you really don't need to go to college
(only major colleges are worth anything since the other ones can't keep
current), and its something that you can enjoy both as a career and a hobby.
You don't choose to develop software or choose to be a networker: you choose
to work with computers. If you choose to work with computers then you'll
find that you'll have a better time of it all and you'll get to do
everything you enjoy.
I have yet to run into anyone that I was co-developing software with (or
against >=] ) that was in the 14-17 range when I started who is still in
computers making less than $70,000 a year.
- Chuck
Message #12 by "Peter Lanoie" <planoie@e...> on Wed, 18 Apr 2001 20:34:18 -0400
|
|
Charles,
"(only major colleges are worth anything since the other ones can't keep
current)"
I beg to differ. I attended a very small school and was working with new
technology at the time. My major had approximately 25 people. We had a lot
of freedom to play with new things. I found things to be a bit converse to
the way you describe. The larger schools I looked at seemed to be lagging
behind.
I realize this is off-topic, but I just wanted to throw it in.
Peter
(An ungraduated early starter who's a far cry from 70k/yr, nice to meet you.
:)
-----Original Message-----
From: Charles Feduke [mailto:webmaster@r...]
Sent: Wednesday, April 18, 2001 7:13 PM
To: ASP Databases
Subject: [asp_databases] RE: Search by keyword select statement
> maybe the Database book.... the only thing that is stopping me is... im
not
> sure whether i want to develop for a career (im 15, sophmore in hs). it is
a
> toss up between development (ASP Web apps, with COM+ or com.net whatever
ms
> is calling these days) and networking.. and i love them both... so.. hmmm
I started when I was 15. Its really good for people who are *really*
into computers. You make good money, you really don't need to go to college
(only major colleges are worth anything since the other ones can't keep
current), and its something that you can enjoy both as a career and a hobby.
You don't choose to develop software or choose to be a networker: you choose
to work with computers. If you choose to work with computers then you'll
find that you'll have a better time of it all and you'll get to do
everything you enjoy.
I have yet to run into anyone that I was co-developing software with (or
against >=] ) that was in the 14-17 range when I started who is still in
computers making less than $70,000 a year.
- Chuck
Message #13 by Kat Howlett <kat.howlett@c...> on Thu, 19 Apr 2001 10:12:16 +1000
|
|
Thankyou so much for that, I missunderstood something I read and I got my
wildcard wrong :)
-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...]
Sent: Thursday, 19 April 2001 12:39 AM
To: ASP Databases
Subject: [asp_databases] RE: Search by keyword select statement
Hi Kat,
When using ASP to generate SQL expressions, the wildcard character for
Access is %, not *. I would suggest creating a variable to hold your
wildcard character (in case you ever move to a different database that
uses a different wildcard character, it would be easier to change it in
one place). Also, you should check to make sure your keyword isn't
empty, because if you search for the wildcard only, then you will return
ALL results, which is probably not what you are trying to do. For
example:
<%
' This is in some file that is included on all pages that do a search
dbWildcardChar = "%"
%>
<%
' This is in a page that does a search
if Len(keyword) > 0 then
strSearch = dbWildcardChar & keyword & dbWildcardChar
Else
strSearch = ""
End if
SQLstr = "SELECT * FROM Activity WHERE Description LIKE '" & strSearch &
"'"
%>
Also, you might want to consider doing a search for single quotes in
your keyword and replacing them. To do that, add this to your file that
is included:
<%
Function sql_quote(str)
str = replace(str,"'","''")
sql_quote = "'"& str & "'"
End Function
%>
and modify your SQL string to look like this:
SQLstr = "SELECT * FROM Activity WHERE Description LIKE " &
sql_quote(strSearch)
This will prevent runtime errors when the keyword contains one or more
single quotes.
Also note the the ; is not needed at the end of the SQL string.
Good luck,
Pete
> -----Original Message-----
> From: Kat Howlett [mailto:kat.howlett@c...]
> Sent: Tuesday, April 17, 2001 10:49 PM
> To: ASP Databases
> Subject: [asp_databases] Search by keyword select statement
>
>
> Hello
> I have been trying to do a search by keyword function in my
> asp database. I
> have used a select statement to companre a keyword to an item in the
> Description field in my database. Here is my SQL statement:
> (where keyword
> is a previously entered variable)
>
> SELECT * FROM Activity WHERE Description LIKE '*"&keyword&"*';
>
> Have I done this right? is there a better way to search
> througha field in an
> access database to find a keyword??
>
> Thanks heaps
> Kat
Message #14 by "Charles Feduke" <webmaster@r...> on Thu, 19 Apr 2001 00:03:17 -0400
|
|
> I beg to differ. I attended a very small school and was working with new
> technology at the time. My major had approximately 25 people. We had a
lot
> of freedom to play with new things. I found things to be a bit converse
to
> the way you describe. The larger schools I looked at seemed to be lagging
> behind.
<offtopic>
Hrm. To get my BS in CS from the colleges around here, I would simply
need to explain how I configured my Linux server as a NAT firewall using
iptables. That and the most advanced computer course I would have to take
would be either Assembly or Introduction to Java. If you've been
programming since you were 15 and decided to start college at 18, then I
would assume that routers, Assembly, and Java are already known to you so
you'd be just wasting your time and money.
However, some people may find this of use: if you do work for an
employer, some colleges will accept a description of the work, an example of
the project, signed by the employer to give you credits towards your CS
degree. They typically don't offer this to younger folks because they don't
assume that you've had professional experience. Simply put, if you already
have professional experience before you go to college, then you probably
don't need college.
The only school that comes to mind that I would get my BS and MA from
would be MIT. I'm sure there's others, but the larger schools have more
funding and a much more diverse and challenging environment.
http://www.satirewire.com/news/0006/satire-ellison.shtml >=]
</offtopic>
I won't post anymore off topic posts for a while. 0=]
- Chuck
----- Original Message -----
From: "Peter Lanoie" <planoie@e...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, April 18, 2001 8:34 PM
Subject: [asp_databases] RE: Search by keyword select statement
> Charles,
>
> "(only major colleges are worth anything since the other ones can't keep
> current)"
>
> I beg to differ. I attended a very small school and was working with new
> technology at the time. My major had approximately 25 people. We had a
lot
> of freedom to play with new things. I found things to be a bit converse
to
> the way you describe. The larger schools I looked at seemed to be lagging
> behind.
>
> I realize this is off-topic, but I just wanted to throw it in.
>
> Peter
> (An ungraduated early starter who's a far cry from 70k/yr, nice to meet
you.
> :)
>
> -----Original Message-----
> From: Charles Feduke [mailto:webmaster@r...]
> Sent: Wednesday, April 18, 2001 7:13 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Search by keyword select statement
>
>
> > maybe the Database book.... the only thing that is stopping me is... im
> not
> > sure whether i want to develop for a career (im 15, sophmore in hs). it
is
> a
> > toss up between development (ASP Web apps, with COM+ or com.net whatever
> ms
> > is calling these days) and networking.. and i love them both... so..
hmmm
>
> I started when I was 15. Its really good for people who are *really*
> into computers. You make good money, you really don't need to go to
college
> (only major colleges are worth anything since the other ones can't keep
> current), and its something that you can enjoy both as a career and a
hobby.
> You don't choose to develop software or choose to be a networker: you
choose
> to work with computers. If you choose to work with computers then you'll
> find that you'll have a better time of it all and you'll get to do
> everything you enjoy.
>
> I have yet to run into anyone that I was co-developing software with
(or
> against >=] ) that was in the 14-17 range when I started who is still in
> computers making less than $70,000 a year.
>
> - Chuck
>
>
Message #15 by "Daniel O'Dorisio" <dodorisio@h...> on Wed, 18 Apr 2001 21:38:22 -0400
|
|
thanks for both of yalls thougts... and yes charles.. i am really into
computers.. have been ever since i watched my dad (when i was 8) write a
simple BASIC program to calculate how many days old i was:-) i remember when
all we had was dos.... on a 286.. i know to some of yall this is nothing
compaired to your atari:-) but still it was pretty neat.. i would read
through the gorialla and nibbles games and try to figure out the
language..:-) hehe... anyway.. i know i will probally end up in computers.
ther is something about the frustration of a "illegal operation"
anyway... thanks for the thoughts..
Daniel
-----Original Message-----
From: Peter Lanoie [mailto:planoie@e...]
Sent: Wednesday, April 18, 2001 8:34 PM
To: ASP Databases
Subject: [asp_databases] RE: Search by keyword select statement
Charles,
"(only major colleges are worth anything since the other ones can't keep
current)"
I beg to differ. I attended a very small school and was working with new
technology at the time. My major had approximately 25 people. We had a lot
of freedom to play with new things. I found things to be a bit converse to
the way you describe. The larger schools I looked at seemed to be lagging
behind.
I realize this is off-topic, but I just wanted to throw it in.
Peter
(An ungraduated early starter who's a far cry from 70k/yr, nice to meet you.
:)
-----Original Message-----
From: Charles Feduke [mailto:webmaster@r...]
Sent: Wednesday, April 18, 2001 7:13 PM
To: ASP Databases
Subject: [asp_databases] RE: Search by keyword select statement
> maybe the Database book.... the only thing that is stopping me is... im
not
> sure whether i want to develop for a career (im 15, sophmore in hs). it is
a
> toss up between development (ASP Web apps, with COM+ or com.net whatever
ms
> is calling these days) and networking.. and i love them both... so.. hmmm
I started when I was 15. Its really good for people who are *really*
into computers. You make good money, you really don't need to go to college
(only major colleges are worth anything since the other ones can't keep
current), and its something that you can enjoy both as a career and a hobby.
You don't choose to develop software or choose to be a networker: you choose
to work with computers. If you choose to work with computers then you'll
find that you'll have a better time of it all and you'll get to do
everything you enjoy.
I have yet to run into anyone that I was co-developing software with (or
against >=] ) that was in the 14-17 range when I started who is still in
computers making less than $70,000 a year.
- Chuck
Message #16 by "Charles Feduke" <webmaster@r...> on Thu, 19 Apr 2001 08:59:31 -0400
|
|
> all we had was dos.... on a 286.. i know to some of yall this is nothing
> compaired to your atari:-) but still it was pretty neat.. i would read
My ALTAIR. Hehe, yeah right.
I started less than 7 years ago, with an IBM Aptiva, 486, 2400 baud modem,
4 MBs RAM. I played Doom like an addict. Doom 2 with the Winchester 1898
still owns me though.
? Chuck
|
|
|
View Cart
