SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Zealot Goldilocks's Avatar
    Join Date
    Apr 2003
    Location
    Three Bear's Cottage
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    HELP! This is driving me nuts!

    I have an SQL statement to retrieve data from an access database:

    SQL = "SELECT FaultNo, [User], Fault, Resolution, DateRecd, PriorityID from tblHelpDesk WHERE FaultNo =" & strSearch & " AND StatusID=1 ORDER BY [User]"

    strSearch is a variable where the value is coming from an html form on an ASP page.

    When I search by a person's first name OR their surname everything works fine, but when I enter both names, it either finds nothing or just displays one record when there are actually more. (The field 'User' stores the person's entire name)

    I have already wasted hours going round in circles and cannot work this out. I'm sure it must be something really simple.

    Please help!
    Last edited by Goldilocks; Sep 16, 2003 at 04:00.

  2. #2
    SitePoint Addict Avido's Avatar
    Join Date
    Jul 2003
    Location
    Kortrijk, Belgium, Europe, the world
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can call me stupid, but what has that sql statement to do with your search(both names, etc...)? :-)

  3. #3
    SitePoint Zealot Goldilocks's Avatar
    Join Date
    Apr 2003
    Location
    Three Bear's Cottage
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry i copied the wrong one!

    Here it is:

    SQL = "SELECT FaultNo, [User], Fault, Resolution, DateRecd, PriorityID from tblHelpDesk WHERE [User] LIKE '%" & strSearch & "%' AND StatusID=1 ORDER BY [User], FaultNo"

  4. #4
    SitePoint Addict Avido's Avatar
    Join Date
    Jul 2003
    Location
    Kortrijk, Belgium, Europe, the world
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I quickly made a table where i simulated your probleme. You should use * instead of % as wildcard.

  5. #5
    SitePoint Zealot Goldilocks's Avatar
    Join Date
    Apr 2003
    Location
    Three Bear's Cottage
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried that but now I don't get any results at all whatever I type in.

    If it helps, here is my code in full:

    Code:
    <%@ Language=VBScript %>
    <HTML>
    
    
    
    <%
    Session("nav")="Departments >> IT HelpDesk"
    %>
    
    <%
    Session("title")="IT HelpDesk"
    %>
    
    <!-- #include file="../include/header.asp" -->
    
    <%
    strSearch=request("txtSearch")
    flag=request("flag")
    
    	'Escape is just a function to replace special characters that databases cant handle and format them into HTML code
    Function Escape(sString)
    
        'Replace any Cr and Lf to <br>
        strReturn = Replace(sString , vbCrLf, "<br>")     : 'visual basic carriage return line feed     ***********\
        strReturn = Replace(strReturn , vbCr , "<br>")     : 'visual basic carriage return               *********These 3 are line breaks hence <BR>
        strReturn = Replace(strReturn , vbLf , "<br>")     : 'visual basic line feed          ***********/
        strReturn = Replace(strReturn, "'", "''")          : 'Single quote changed to 2 single quotes ASP knows what to do
        Escape = strReturn
    End Function
    
    Session.LCID=2057
    
    accessDB = "D:\WebData\whoswho.mdb"
    DSNStr = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & accessdb & ";"
    set conn2 = Server.CreateObject("ADODB.Connection")
    conn2.open DSNStr
    
    accessDB = "D:\WebData\HelpDesk v2.mdb"
    DSNStr = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & accessdb & ";"
    set conn = Server.CreateObject("ADODB.Connection")
    conn.open DSNStr
    
    %>
    
    <table width="95%" border="0" cellspacing="0" cellpadding="0" align="center" valign="top">
                  <tr> 
                    <td height="247" valign="top"> 
                      <table width="95%" border="0" cellspacing="0" cellpadding="0" align="center">
                        <tr> 
                          <td height="215" valign="top"> 
                            <div align="center"> 
                              <p class="navysmall"><img src="helpdeskbar.jpg" width="500" height="35"><span class="large"><a href="ithelpdesk.asp"><font color="#9900FF"></font></a></span></p>
                              <table width="100%" border="1" cellspacing="0" cellpadding="0" bordercolor="#3EB3B3">
                                <tr> 
                                  <td> 
                                    <table width="100%" border="0" cellspacing="0" cellpadding="3" bgcolor="#F7FDFD" class="navysmall">
                                      <tr> 
                                        <td colspan="3" height="31" bgcolor="#DBF2F2"> 
                                          <div align="center"><b>Search Open IT HelpDesk 
                                            Calls</b></div>
                                        </td>
                                      </tr>
                                      <tr valign="bottom"> 
                                        <td width="50%" height="28"> 
                                          <div align="center"><b>Enter a name:</b></div>
                                        </td>
                                        <td height="28"> 
                                          <div align="center"><b>OR</b></div>
                                        </td>
                                        <td height="28"> 
                                          <div align="center"><b>Select a Request 
                                            No:</b></div>
                                        </td>
                                      </tr>
                                      <tr> 
                                        <td height="33"> 
    							  <% if flag=1 then
    							  strValue=Trim(strSearch)
    							  else strValue=""
    							  end if					  
    							 
    							  
    							  
    							  %>
                                          <form name="form1" method="post" action="ithelpdesksearch.asp?flag=1">
                                            <div align="center">
                                              <table width="100%" border="0" cellspacing="0" cellpadding="0">
                                                <tr> 
                                                  <td> 
                                                    <div align="right"> 
    							<%
    							 if flag="" then  
    							UserName=Request.ServerVariables("auth_user")
    							UserName=Replace(UserName,"SEOS\","")								  
    							  
    							SQL = "SELECT PeNetId, PeForeName, PeSurname FROM Person WHERE PeNetId='" & UserName & "'"
    
    							Set rs = Server.CreateObject("ADODB.Recordset")
    							rs.open SQL, Conn2							  
    							strValue=rs("PeForeName") & "&nbsp;" & rs("PeSurname")
    							rs.close
    							set rs=nothing
    							end if
    
    							  %>
                                                      <input type="text" name="txtSearch" value="<%=strValue%>">
                                                    </div>
                                                  </td>
                                                  <td> &nbsp; 
                                                    <input type="image" border="0" width="60" height="25" name="Submit3" src="helpdesksearch.jpg">
                                                  </td>
                                                </tr>
                                                <tr> 
                                                  <td colspan="2" class="navysmall">
                                                    <div align="center"><font size="2">(Leave 
                                                      blank to view all open HelpDesk 
                                                      calls)</font></div>
                                                  </td>
                                                </tr>
                                              </table>
                                            </div>
                                          </form>
                                        </td>
                                        <td height="33">&nbsp;</td>
                                        <td height="33"> 
                                    <%
    								SQL = "SELECT FaultNo from tblHelpDesk WHERE StatusID=1 ORDER BY FaultNo"
    
    								Set rs = Server.CreateObject("ADODB.Recordset")
    								rs.open SQL, Conn
    								
    								strRefNo = rs.GetRows()
    rs.Close
    Set rs = Nothing
    								
    								%>
                                          <form name="form1" method="post" action="ithelpdesksearch.asp?flag=2">
                                            <div align="center">
                                              <table width="100%" border="0" cellspacing="0" cellpadding="0">
                                                <tr> 
                                                  <td> 
                                                    <div align="right"> 
                                                      <select name="txtSearch">
                                                <option value=''></option>
                                                <%For intRow = 0 to UBound (strRefNo, 2)
    									  			if trim(strSearch) = trim(strRefNo(0, intRow)) Then 
    												SelStatus = "selected"
    												else
    												SelStatus = ""
    										end if%>
                                                <option value='<%=(strRefNo(0, intRow))%>' <%=SelStatus%>><%=(strRefNo(0, intRow))%></option>
                                                <%Next%>
                                                      </select>
                                                    </div>
                                                  </td>
                                                  <td> &nbsp;
                                                    <input type="image" border="0" width="60" height="25" name="Submit2" src="helpdesksearch.jpg">
                                                  </td>
                                                </tr>
                                                <tr>
                                                  <td>&nbsp;</td>
                                                  <td>&nbsp;</td>
                                                </tr>
                                              </table>
                                            </div>
                                          </form>
                                        </td>
                                      </tr>
                                    </table>
                                  </td>
                                </tr>
                              </table>
                              <%
    						  
    						  if flag<>"" then
    						  if flag="1" then
    						SQLcount = "SELECT COUNT(*) as rec_count from tblHelpDesk WHERE [User] LIKE '%" & strSearch & "%' AND StatusID=1"
    						  
    						  SQL = "SELECT FaultNo, [User], Fault, Resolution, DateRecd, PriorityID from tblHelpDesk WHERE [User] LIKE '%" & Trim(strSearch) & "%' AND StatusID=1 ORDER BY [User], FaultNo"
    						  end if
    						  
    						  if flag="2" then
    						  SQLcount = "SELECT COUNT(*) as rec_count from tblHelpDesk WHERE FaultNo =" & strSearch & " AND StatusID=1"
    						  
    						  SQL = "SELECT FaultNo, [User], Fault, Resolution, DateRecd, PriorityID from tblHelpDesk WHERE FaultNo =" & strSearch & " AND StatusID=1 ORDER BY [User]"
    						  end if
    						  
    						Set rsCount = Server.CreateObject("ADODB.Recordset")
    						rsCount.open SQLcount, Conn	
    						counter=rsCount("rec_count")
    						rsCount.close
    						set rsCount = nothing
    						
    						Set rs = Server.CreateObject("ADODB.Recordset")
    						rs.open SQL, Conn	
    						%>
    												  <br><div align="left"><span class="navysmall"><b><%=counter%> records found</b></span></div>
    						<%
    						  ' Start loop
    						  Do while not rs.EOF
    						  rFaultNo=rs("FaultNo")
    						  rUser=Trim(rs("User"))
    						  rDate=rs("DateRecd")
    						  if rs("PriorityID") <> "" then
    						  rPriorityID=rs("PriorityID")
    						  else rPriorityID = 0
    						  end if						  
    						  rFault=rs("Fault")
    						  if rFault <> "" then
    						  	rFault=Escape(rFault)
    						  end if
    						  rResolution=rs("Resolution")		
    						  if rResolution <> "" then
    							rResolution=Escape(rResolution)
    						  end if
    						  
    						  SQL = "SELECT Priority from tblPriority WHERE PriorityID =" & rPriorityID
    Set rsPriority = Server.CreateObject("ADODB.Recordset")
    						rsPriority.open SQL, Conn	
    						if rsPriority.EOF then
    						Priority="None"
    						else
    						Priority=rsPriority("Priority")
    						end if
    						rsPriority.close
    						set rsPriority = nothing
    %>
                              <br>
                          
    
                              <table width="100%" border="1" cellpadding="0" cellspacing="0" bordercolor="#3EB3B3">
                                <tr><td>
                                    <table width="100%" border="0" cellspacing="0" cellpadding="3" class="navysmall">
                                      <tr bgcolor="#DBF2F2"> 
                                        <td width="17%"><b>Request No:</b></td>
                                        <td width="10%"><font color="red"><%=rFaultNo%></font></td>
                                        <td width="8%"><b>Name:</b></td>
                                        <td width="23%"><%=rUser%></td>
                                        <td width="18%"><b>Date Logged:</b></td>
                                        <td width="24%"><%=rDate%></td>
                                      </tr>
                                      <tr> 
                                        <td colspan="2"><b>Assigned Priority:</b></td>
                                        <td colspan="2"><%=Priority%></td>
                                        <td>&nbsp;</td>
                                        <td>&nbsp;</td>
                                      </tr>
                                      <tr> 
                                        <td colspan="6"><b>Problem/Request:</b></td>
                                      </tr>
                                      <tr> 
                                        <td colspan="6"><%=rFault%></td>
                                      </tr>
                                      <tr> 
                                        <td colspan="6">&nbsp;</td>
                                      </tr>
                                      <tr> 
                                        <td colspan="6"><b>Resolution:</b></td>
                                      </tr>
                                      <tr> 
                                        <td colspan="6"><%=rResolution%></td>
                                      </tr>
                                      <tr> 
                                        <td colspan="6" bgcolor="#FCFEFE">&nbsp;</td>
                                      </tr>
                                    </table>
    								</td></tr></table>
    
    								  <%
    						  rs.movenext
    						  loop
    						  rs.close
    						  set rs = nothing
    						  end if
    						  %>	
                              
                            </div>
                          </td>
                        </tr>
                        <tr>
                          <td height="2" valign="top">&nbsp;</td>
                        </tr>
                      </table>
    	
    			
    			
              </td>
                  </tr>
                </table>
    
    
    <!-- #include file="../include/footer.asp" -->
    
    </BODY>
    </HTML>

  6. #6
    SitePoint Addict Avido's Avatar
    Join Date
    Jul 2003
    Location
    Kortrijk, Belgium, Europe, the world
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've used your sql-statement on my table and it gave me the right results. So it must be something else.

    But, i'm wandering... can you give me some records from that table, and some inputs you use to get the results.

  7. #7
    SitePoint Zealot Goldilocks's Avatar
    Join Date
    Apr 2003
    Location
    Three Bear's Cottage
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well having played around with it some more, it now seems that it is something to do with my WHERE clause.
    If I put the following it works fine:

    SQL = "SELECT FaultNo, [User], Fault, Resolution, DateRecd, PriorityID from tblHelpDesk WHERE [User] LIKE '%" & strSearch & "%'"

    As soon as I add the bit at the end, it doesn't:

    SQL = "SELECT FaultNo, [User], Fault, Resolution, DateRecd, PriorityID from tblHelpDesk WHERE [User] LIKE '%" & strSearch & "%' AND StatusID=1 ORDER BY [User], FaultNo"

  8. #8
    SitePoint Addict Avido's Avatar
    Join Date
    Jul 2003
    Location
    Kortrijk, Belgium, Europe, the world
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you're sure statusID is 1, each time?

  9. #9
    SitePoint Addict Avido's Avatar
    Join Date
    Jul 2003
    Location
    Kortrijk, Belgium, Europe, the world
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oopse,

    i must correct something i said in this thread.
    Wildcard * is used inside access, wildcard % is used when querying from outside.

  10. #10
    SitePoint Zealot Goldilocks's Avatar
    Join Date
    Apr 2003
    Location
    Three Bear's Cottage
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes. What is weird is that if I type in 'Keith' as my search term I get two matching records, both statusID=1 which is correct, both for Keith Smith.

    If I type in 'Keith Smith' I only get one record returned even though the name in the User field for both is Keith Smith.

    This is really frustrating!

    Thanks for you helps so far, it's appreciated. If you have any more ideas let me know.

  11. #11
    SitePoint Addict Avido's Avatar
    Join Date
    Jul 2003
    Location
    Kortrijk, Belgium, Europe, the world
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    before you send the sql statement to the db, print the statement on the screen. you never know...

  12. #12
    SitePoint Addict Avido's Avatar
    Join Date
    Jul 2003
    Location
    Kortrijk, Belgium, Europe, the world
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh,

    and do you still use 'trim(strSearch)' in your code? That would explain why you can find a surname or a name, but not both together

  13. #13
    SitePoint Zealot Goldilocks's Avatar
    Join Date
    Apr 2003
    Location
    Three Bear's Cottage
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Avido
    oh,

    and do you still use 'trim(strSearch)' in your code? That would explain why you can find a surname or a name, but not both together
    Does that get rid of the space in the middle of the words then?

  14. #14
    SitePoint Addict Avido's Avatar
    Join Date
    Jul 2003
    Location
    Kortrijk, Belgium, Europe, the world
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it clear all the spaces. You can use LTrim and RTrim to clear spaces before and after the string

  15. #15
    Phil fillup07's Avatar
    Join Date
    May 2002
    Location
    Jacksonville, FL
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Avido
    it clear all the spaces. You can use LTrim and RTrim to clear spaces before and after the string
    That is not true. Trim() only clears the spaces on the outside of a string. For example:

    trim("hi there") = "hi there"
    trim(" hi ") = "hi"

  16. #16
    SitePoint Addict Avido's Avatar
    Join Date
    Jul 2003
    Location
    Kortrijk, Belgium, Europe, the world
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    *slik*,

    yes, you're right. Damn, what a mistake.

  17. #17
    SitePoint Addict Avido's Avatar
    Join Date
    Jul 2003
    Location
    Kortrijk, Belgium, Europe, the world
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Goldilocks,

    have you already tried to use the query in the database itself, without using ASP?

  18. #18
    SitePoint Zealot Goldilocks's Avatar
    Join Date
    Apr 2003
    Location
    Three Bear's Cottage
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I have tried running the same queries in the database itself and I get the same results as on my ASP page.

    It appears to be the space in between the words that is causing the problem. Does anyone know how I get around this?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •