ASP Advanced Search

cancer10 · October 17th, 2006, 05:21 AM

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

dparsons · October 17th, 2006, 06:43 AM

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

cancer10 · October 17th, 2006, 07:04 AM

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

dparsons · October 17th, 2006, 07:16 AM

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

cancer10 · October 17th, 2006, 07:34 AM

response.write sSQL shows me the following result

'9819339972',' 9870291116',' 9820322543'

-----------------------------------------------
www.chargertek.in - Cheapest WebHosting

dparsons · October 17th, 2006, 07:43 AM

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

cancer10 · October 17th, 2006, 07:46 AM

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

dparsons · October 17th, 2006, 07:58 AM

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

cancer10 · October 17th, 2006, 08:02 AM

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

dparsons · October 17th, 2006, 08:06 AM

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