Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 17th, 2006, 05:21 AM
Authorized User
 
Join Date: Oct 2006
Location: New Delhi, Delhi, India.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default ASP Advanced Search

Well I am trying to search a database using ASP. I want to search all the names of persons in a database by their mobile number.


The mobile numbers are entered in a textarea and needs to seach them at once.

I could not figure out how can this be solved.


I am using the following code

Code:
str= request.Form("txtbulk")
splitarray = split(str,chr(13))
for i = 0 to ubound(splitarray)
'response.write splitarray(i) & "<BR>"
rs.open "select * from tblcontest where mobile='" & splitarray(i) & "'",conn,1,3
next

and display the result here

Code:
<table>
  <tr align="center">
    <td><% response.Write rs(0) %></td>
    <td><% response.Write rs(1) %></td>
</tr>
</table>

i am not sure if this is the correct way to code this, may be someone can help me?

-----------------------------------------------
www.chargertek.in - Cheapest WebHosting
__________________
-----------------------------------------------
www.chargertek.in - Cheapest WebHosting
Reply With Quote
  #2 (permalink)  
Old October 17th, 2006, 06:43 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Try this:

Dim sSQL
Dim i, iLength

For i = 0 to UBound(splitArray(0))
   sSQL = sSQL & "'" & splitArray(i) &"',"
Next

iLength = Len(sSQL)
sSQL = Left(sSQL, (iLength - 1))

"select * from tblcontest where mobile IN(" & sSQL &")",conn,1,3


-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature
Reply With Quote
  #3 (permalink)  
Old October 17th, 2006, 07:04 AM
Authorized User
 
Join Date: Oct 2006
Location: New Delhi, Delhi, India.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanx but it shows me only the first row result.

if I want to search for 10 mobile numbers. What loop syntex should I put?

-----------------------------------------------
www.chargertek.in - Cheapest WebHosting
Reply With Quote
  #4 (permalink)  
Old October 17th, 2006, 07:16 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

If your array, splitArray, contains all 10 mobile numbers, they should be appeneded to the variable sSQL in that for loop. Do a response.write sSQL and see what values are acutally being appended to the variable.

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature
Reply With Quote
  #5 (permalink)  
Old October 17th, 2006, 07:34 AM
Authorized User
 
Join Date: Oct 2006
Location: New Delhi, Delhi, India.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

response.write sSQL shows me the following result


'9819339972',' 9870291116',' 9820322543'

-----------------------------------------------
www.chargertek.in - Cheapest WebHosting
Reply With Quote
  #6 (permalink)  
Old October 17th, 2006, 07:43 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Are you 100% certain that splitarray contains more then those numbers? In any case, that is something you will need to troubleshoot.

That for loop will append the variable sSQL in this fashion:

'9819339972',' 9870291116',' 9820322543'

so if you have 10 entries in your array, sSQL will have 10 numbers in it.

The in statement, as long as the numbers you provide are in the column mobile, will return all of the rows where the mobile column equals one of those values.

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature
Reply With Quote
  #7 (permalink)  
Old October 17th, 2006, 07:46 AM
Authorized User
 
Join Date: Oct 2006
Location: New Delhi, Delhi, India.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

splitarray has just those 3 numbers but in the result I am only getting the result for the first number in the list, ie.9819339972


herez the full code


Code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Search Result</title>
<style type="text/css">
<!--
.style3 {color: #FFFFFF; font-weight: bold; }
.style6 {font-size: 12px; font-family: Verdana, Arial, Helvetica, sans-serif; }
-->
</style>
</head>

<body>

<%
keyword = request.Form("txtkeyword")
searchby = request.Form("txtsearch")
dim rs
set rs = server.CreateObject("adodb.recordset")
Dim sSQL
Dim i, iLength
str= request.Form("txtbulk")
splitarray = split(str,chr(13))


For i = 0 to uBound(splitArray)
   sSQL = sSQL & "'" & splitArray(i) &"',"
Next

iLength = Len(sSQL)
sSQL = Left(sSQL, (iLength - 1))

strSQL = "select * from tblcontest where mobile IN(" & sSQL &")"

rs.open strSQL, conn, 1, 3

response.write sSQL

%>
<table width="100%" border="1" cellspacing="0" cellpadding="0">
  <tr>
    <td bgcolor="#330066"><div align="center" class="style6"><span class="style3">City</span></div></td>
    <td bgcolor="#330066"><div align="center" class="style6"><span class="style3">Outlet</span></div></td>
    <td bgcolor="#330066"><div align="center" class="style6"><span class="style3">Name</span></div></td>
    <td bgcolor="#330066"><div align="center" class="style6"><span class="style3">Mobile</span></div></td>
    <td bgcolor="#330066"><div align="center" class="style6"><span class="style3">Phone </span></div></td>
    <td bgcolor="#330066"><div align="center" class="style6"><span class="style3">Email</span></div></td>
    <td bgcolor="#330066"><div align="center" class="style6"><span class="style3">Address</span></div></td>
    <td bgcolor="#330066"><div align="center" class="style6"><span class="style3">SMS Reply </span></div></td>
  </tr>
<%
while not(rs.EOF)
%>  
  <tr align="center">
    <td><% response.Write rs(0) %></td>
    <td><% response.Write rs(1) %></td>
    <td><% response.Write rs(2) %></td>
    <td><% response.Write rs(3) %></td>
    <td><% response.Write rs(4) %></td>
    <td><% response.Write rs(5) %></td>
    <td><% response.Write rs(6) %></td>
    <td><% response.Write rs(7) %></td>
  </tr>
<%
rs.movenext
wend
%>  
</table>
<%
if rs.recordcount < 1 then
response.Write "<center><B><BR><BR>No records found</b></center>"
end if

rs.close
set rs = nothing
%>
</body>
</html>
-----------------------------------------------
www.chargertek.in - Cheapest WebHosting
Reply With Quote
  #8 (permalink)  
Old October 17th, 2006, 07:58 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Its not a problem with your code (i dont think) I think it lies more in your database. Execute this query:

"select * from tblcontest where mobile IN('9819339972',' 9870291116',' 9820322543')",conn,1,3

Tell me if you have only one record returned. If you do, then the numbers you are trying to fetch don't exist in your data base.

I also suggest never using indicies to retrieve data from a recordset:
Bad practice: response.Write rs(0)
Good practice: response.Write rs("field")

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature
Reply With Quote
  #9 (permalink)  
Old October 17th, 2006, 08:02 AM
Authorized User
 
Join Date: Oct 2006
Location: New Delhi, Delhi, India.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Notice the spaces in the second and third numbers. I need to get rid of those spaces using the trim command.

But I m confused where to put the trim command....

-----------------------------------------------
www.chargertek.in - Cheapest WebHosting
Reply With Quote
  #10 (permalink)  
Old October 17th, 2006, 08:06 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

For i = 0 to UBound(splitArray(0))
   sSQL = sSQL & "'" & Trim(splitArray(i)) &"',"
Next

The Trim() will remove Leading and trailing spaces only.

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Where is Advanced Search ? MikeW2 BOOK: Beginning SharePoint 2007: Building Team Solutions with MOSS 2007 ISBN: 978-0-470-12449-9 2 August 13th, 2007 03:36 AM
advanced search Moharo Pro PHP 1 February 2nd, 2004 04:43 PM
Intermediate to advanced ASP 3.0 Book conundrum Classic ASP Professional 3 October 12th, 2003 12:35 PM



All times are GMT -4. The time now is 04:07 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.