Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jan 2006
    Posts
    44

    Unanswered: Using split to get results from db, but only manage to get results for 2nd entry

    Hi, i gt 1 prob..

    I got a textfield and i name it "affectCom"
    and i gt a button "correlate".

    When i click "correlate", it will search the db using the data that i have input. If i input 1 entry "Windows", it will display what i want. When i input 2 entries, "Windows, AA", it will split the sentence out by ", ". But with de codes i have below, i can only get the results for "AA"

    Can ani1 help mi check what's wrong wif my codes?
    Thanx alot! ^^

    Code:
    If (CStr(Request("correlate")) = "form1") Then
    
    	If request.form("all") = "on" Then
    
    			strSentence = CStr(request.form("affectCom"))
    			strArray = Split(strSentence, ", ")
    			
    			Set rs_vServerName = Server.CreateObject("ADODB.Recordset")
    			rs_vServerName.ActiveConnection = MM_dsprms_STRING
    
    			For x=0 To UBound(strArray)
    
    			rs_vServerName.Source = "SELECT Server_Name FROM Sys_Config WHERE OS = ('"& strArray(x) &"') OR Application = ('"& strArray(x) &"') OR Database = ('"& strArray(x) &"') OR Filesets = ('"& strArray(x) &"') OR Security_Patches = ('"& strArray(x) &"') OR Services = ('"& strArray(x) &"') OR FTP = ('"& strArray(x) &"') OR JDK_JRE = ('"& strArray(x) &"')"		
    
    			Next
    			
    
    			rs_vServerName.CursorType = 0
    			rs_vServerName.CursorLocation = 2
    			rs_vServerName.LockType = 1
    			rs_vServerName.Open()
    
    				If rs_vServerName.eof Then 
    					
    					response.redirect ("Correlate_EOF.asp")
    				
    				End If
    			
    
    			ServerArray = rs_vServerName.GetRows
    			
    			
    
    			Response.write "<b>Server(s):</b><br>"
    			For x=0 To UBound(ServerArray,2)
    			Response.write "<td>"
    			Response.write ServerArray(0,x)
    			Response.write "</td>"
    			Response.write "<br>" 
    			Next
    	
    			
    	End If
    
    End If

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Well you are always replacing your entire sql string for one thing.

    The other is that if you have multiple criteria that you want to search on you might want to use the "in" clause instead of an "="

    Here's two possible solutions.
    1.
    Code:
    If (CStr(Request("correlate")) = "form1") Then
    
    	If request.form("all") = "on" Then
    
    			strSentence = CStr(request.form("affectCom"))
    			strArray = Split(strSentence, ", ")
    			
    			Set rs_vServerName = Server.CreateObject("ADODB.Recordset")
    			rs_vServerName.ActiveConnection = MM_dsprms_STRING
    			sqlString = ""
    			For x=0 To UBound(strArray)
    				If sqlString <> "" then sqlString = sqlString & " OR "
    				sqlString = strString & "OS = ('"& strArray(x) &"') OR Application = ('"& strArray(x) &"') OR Database = ('"& strArray(x) &"') OR Filesets = ('"& strArray(x) &"') OR Security_Patches = ('"& strArray(x) &"') OR Services = ('"& strArray(x) &"') OR FTP = ('"& strArray(x) &"') OR JDK_JRE = ('"& strArray(x) &"')"		
    
    			Next
    			sqlString = "SELECT Server_Name FROM Sys_Config WHERE " & sqlString
    			rs_vServerName.Source = sqlString
    
    			
    
    			rs_vServerName.CursorType = 0
    			rs_vServerName.CursorLocation = 2
    			rs_vServerName.LockType = 1
    			rs_vServerName.Open()
    
    				If rs_vServerName.eof Then 
    					
    					response.redirect ("Correlate_EOF.asp")
    				
    				End If
    			
    
    			ServerArray = rs_vServerName.GetRows
    			
    			
    
    			Response.write "<b>Server(s):</b><br>"
    			For x=0 To UBound(ServerArray,2)
    			Response.write "<td>"
    			Response.write ServerArray(0,x)
    			Response.write "</td>"
    			Response.write "<br>" 
    			Next
    	
    			
    	End If
    
    End If
    2.
    Code:
    If (CStr(Request("correlate")) = "form1") Then
    
    	If request.form("all") = "on" Then
    
    			strSentence = CStr(request.form("affectCom"))
    			strArray = Split(strSentence, ", ")
    			
    			Set rs_vServerName = Server.CreateObject("ADODB.Recordset")
    			rs_vServerName.ActiveConnection = MM_dsprms_STRING
    			inString = ""
    			For x=0 To UBound(strArray)
    				If inString <> "" then inString = inString + " , "
    				inString = inString & "'" & strArray(x) &"'"
    			Next
    			sqlString = strString + "OS in ("& inString &") OR Application in ("& inString &") OR Database in ("& inString &")  OR Filesets in ("& inString &")  OR Security_Patches in ("& inString &")  OR Services in ("& inString &")  OR FTP in ("& inString &")  OR JDK_JRE in ("& inString &")"		
    
    			rs_vServerName.Source = sqlString
    
    			
    
    			rs_vServerName.CursorType = 0
    			rs_vServerName.CursorLocation = 2
    			rs_vServerName.LockType = 1
    			rs_vServerName.Open()
    
    				If rs_vServerName.eof Then 
    					
    					response.redirect ("Correlate_EOF.asp")
    				
    				End If
    			
    
    			ServerArray = rs_vServerName.GetRows
    			
    			
    
    			Response.write "<b>Server(s):</b><br>"
    			For x=0 To UBound(ServerArray,2)
    			Response.write "<td>"
    			Response.write ServerArray(0,x)
    			Response.write "</td>"
    			Response.write "<br>" 
    			Next
    	
    			
    	End If
    
    End If
    Hope that helps.

  3. #3
    Join Date
    Jan 2006
    Posts
    44
    hi, thanx for ur help..
    i have tried both of ur methods..

    For de 1st code, i get the same results lk mine...

    As for the 2nd code, i gt tis error which i don understand y..

    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'OS in () OR Application in () OR Database in () OR Filesets in () OR Security_Patches in () OR Services in () OR FTP in () OR JDK_JRE in ()'.

    Hope u can help mi...
    Thanx ^^

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    The second error looks to be because you haven't got any search criteria specified.... like you have clicked the submit button without any sentence value.

    Not sure why the first one would return the exact same results... unless some of your search criteria does not actually have any matching values...

    What you might want to do is check the sql statements that are being generated.

    Before the rs_vServerName.Source = sqlString lines put a response.write sqlString and post the results here so I can see what the generated query looks like.

  5. #5
    Join Date
    Jan 2006
    Posts
    44
    Hey, i got in le..
    previously i add wrongly.. now i gt it rite.. using ur 2nd code...
    Thanx!! ^^

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    No problems. Glad to help. BTW, if you write a little clearer instead of dropping vowels all over the place it's make it easier for people to help you out.

  7. #7
    Join Date
    Jan 2006
    Posts
    44
    Okay, sorry about that.

    Now i have another problem.. Hope u can help me..

    Now i can search by using the data that the user input.

    Code:
    If (CStr(Request("correlate")) = "form1") Then
    
    			strSentence = CStr(request.form("affectCom"))
    			strArray = Split(strSentence, ", ")
    			
    			Set rs_vServerName = Server.CreateObject("ADODB.Recordset")
    			rs_vServerName.ActiveConnection = MM_dsprms_STRING
    
    
    			inString = ""
    
    			For x=0 To UBound(strArray)
    				If inString <> "" then inString = inString + " , "
    				inString = inString & "'" & strArray(x) &"'"
    			Next
    
    
    			sqlString = strString + "OS in ("& inString &") OR Application in ("& inString &") OR Database in ("& inString &")  OR Filesets in ("& inString &")  OR Security_Patches in ("& inString &")  OR Services in ("& inString &")  OR FTP in ("& inString &")  OR JDK_JRE in ("& inString &")"		
    			
    			sqlString = "SELECT Server_Name FROM Sys_Config WHERE " & sqlString
    
    			rs_vServerName.Source = sqlString
    
    			
    			rs_vServerName.CursorType = 0
    			rs_vServerName.CursorLocation = 2
    			rs_vServerName.LockType = 1
    			rs_vServerName.Open()
    
    				If rs_vServerName.eof Then 
    					
    					response.redirect ("Correlate_EOF.asp")
    				
    				End If
    
    			ServerArray = rs_vServerName.GetRows
    			
    			rs_vServerName.Close()
    
    
    			Response.write "<b>Server(s):</b><br>"
    			For x=0 To UBound(ServerArray,2)
    			Response.write "<td>"
    			Response.write ServerArray(0,x)
    			Response.write "</td>"
    			Response.write "<br>" 
    			Next
    
    End If
    If no results found, the user will be directed to another page.

    But for now, i want it to be like, if there's no results found, it will execute another sql statement

    Code:
    rs_vServerName.Source = "SELECT Server_Name FROM Sys_Config WHERE (OS LIKE ('%"& strArray(x) &"%')) OR (Application LIKE ('%"& strArray(x) &"%')) OR (Database LIKE ('%"& strArray(x) &"%')) OR (Filesets LIKE ('%"& strArray(x) &"%')) OR (Security_Patches LIKE ('%"& strArray(x) &"%')) OR (Services LIKE ('%"& strArray(x) &"%')) OR (FTP LIKE ('%"& strArray(x) &"%')) OR (JDK_JRE LIKE ('%"& strArray(x) &"%'))"
    So that if no exact solution is found, then it will search for something similar..

    For example..
    in my database, the data are 'microsoft, smtp1'

    The user key in 'microsoft, smtp'
    It will get the results for 'microsoft' and no results for 'smtp' But now what i want is, if no 'smtp1', search for data that is contains 'smtp'

    Is there ani ways?
    Thanx! ^^
    Last edited by Cuttie0506; 01-18-06 at 22:10.

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Something like this then? Unfortunately you can't use a an in clause and like clause in the same way....
    Code:
    If (CStr(Request("correlate")) = "form1") Then
    
    			strSentence = CStr(request.form("affectCom"))
    			strArray = Split(strSentence, ", ")
    			
    			Set rs_vServerName = Server.CreateObject("ADODB.Recordset")
    			rs_vServerName.ActiveConnection = MM_dsprms_STRING
    
    
    			inString = ""
    
    			For x=0 To UBound(strArray)
    				If inString <> "" then inString = inString + " , "
    				inString = inString & "'" & strArray(x) &"'"
    			Next
    
    
    			sqlString = strString + "OS in ("& inString &") OR Application in ("& inString &") OR Database in ("& inString &")  OR Filesets in ("& inString &")  OR Security_Patches in ("& inString &")  OR Services in ("& inString &")  OR FTP in ("& inString &")  OR JDK_JRE in ("& inString &")"		
    			
    			sqlString = "SELECT Server_Name FROM Sys_Config WHERE " & sqlString
    
    			rs_vServerName.Source = sqlString
    
    			
    			rs_vServerName.CursorType = 0
    			rs_vServerName.CursorLocation = 2
    			rs_vServerName.LockType = 1
    			rs_vServerName.Open()
    
    			If rs_vServerName.eof Then 
    					
    				rs_vServerName.Close()						sqlString = ""
    				For x=0 To UBound(strArray)
    					If sqlString <> "" then sqlString = sqlString & " OR "
    					sqlString = strString & "OS Like ('%"& strArray(x) &"%') OR Application Like  ('%"& strArray(x) &"%') OR Database Like  ('%"& strArray(x) &"%') OR Filesets Like  ('%"& strArray(x) &"%') OR Security_Patches Like  ('%"& strArray(x) &"%') OR Services Like  ('%"& strArray(x) &"%') OR FTP Like  ('%"& strArray(x) &"%') OR JDK_JRE Like  ('%"& strArray(x) &"%')"		
    
    				Next
    				sqlString = "SELECT Server_Name FROM Sys_Config WHERE " & sqlString
    				rs_vServerName.Source = sqlString
    
    				rs_vServerName.CursorType = 0
    				rs_vServerName.CursorLocation = 2
    				rs_vServerName.LockType = 1
    				rs_vServerName.Open()
    
    			End If
    
    			ServerArray = rs_vServerName.GetRows
    			
    			rs_vServerName.Close()
    
    
    			Response.write "<b>Server(s):</b><br>"
    			For x=0 To UBound(ServerArray,2)
    			Response.write "<td>"
    			Response.write ServerArray(0,x)
    			Response.write "</td>"
    			Response.write "<br>" 
    			Next
    
    End If

  9. #9
    Join Date
    Jan 2006
    Posts
    44
    Hi, this way works only if i input 1 information. It will only works on the last data that i have input. For example, 'microsoft, smtp', it will only get 'smtp'.

    Btw, this method will only occur when if there's no results found..

    But i'm doing something like, if input 2 data, 'microsoft, smtp', i can find results for my microsoft, and no results for smtp (because in db, there's smtp1 only), but i want it at that point, if no smtp, it will search results similar to 'smtp'. So the results will be from 'microsoft' and data similar to 'smtp'

    Thanx ^^

  10. #10
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    In that case you are in for a bit of fun... you are going to have to run seperate queries for every different search criteria you want to include....

    I'm just having some lunch, I will post a solution shortly...

  11. #11
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    I think this should be right....
    Code:
    If (CStr(Request("correlate")) = "form1") Then
    
    	strSentence = CStr(request.form("affectCom"))
    	strArray = Split(strSentence, ", ")
    			
    	Set rs_vServerName = Server.CreateObject("ADODB.Recordset")
    	rs_vServerName.ActiveConnection = MM_dsprms_STRING
    
    
    			
    	For x=0 To UBound(strArray)
    
    		sqlString = "OS =('"& strArray(x) &"') OR Application =('"& strArray(x) &"') OR Database =('"& strArray(x) &"') OR Filesets =('"& strArray(x) &"') OR Security_Patches =('"& strArray(x) &"') OR Services = ('"& strArray(x) &"') OR FTP = ('"& strArray(x) &"') OR JDK_JRE = ('"& strArray(x) &"')"		
    	
    			
    		sqlString = "SELECT Server_Name FROM Sys_Config WHERE " & sqlString
    
    		rs_vServerName.Source = sqlString
    			
    		rs_vServerName.CursorType = 0
    		rs_vServerName.CursorLocation = 2
    		rs_vServerName.LockType = 1
    		rs_vServerName.Open()
    
    		If rs_vServerName.eof Then 
    					
    			rs_vServerName.Close()						
    			sqlString = "OS Like ('%"& strArray(x) &"%') OR Application Like  ('%"& strArray(x) &"%') OR Database Like  ('%"& strArray(x) &"%') OR Filesets Like  ('%"& strArray(x) &"%') OR Security_Patches Like  ('%"& strArray(x) &"%') OR Services Like  ('%"& strArray(x) &"%') OR FTP Like  ('%"& strArray(x) &"%') OR JDK_JRE Like  ('%"& strArray(x) &"%')"		
    
    			sqlString = "SELECT Server_Name FROM Sys_Config WHERE " & sqlString
    			rs_vServerName.Source = sqlString
    
    			rs_vServerName.CursorType = 0
    			rs_vServerName.CursorLocation = 2
    			rs_vServerName.LockType = 1
    			rs_vServerName.Open()
    
    		End If
    
    		ServerArray = rs_vServerName.GetRows
    			
    		rs_vServerName.Close()
    
    
    		Response.write "<b>Server(s):</b><br>"
    		For x=0 To UBound(ServerArray,2)
    			Response.write "<td>"
    			Response.write ServerArray(0,x)
    			Response.write "</td>"
    			Response.write "<br>" 
    		Next
    	Next
    End If
    Last edited by rokslide; 01-18-06 at 23:35. Reason: Corrected sqlstring problem in loop

  12. #12
    Join Date
    Jan 2006
    Posts
    44
    Hi, it's working now, thanx alot..
    Really appreciate!

    And ya, everytime when i clicked the button to get the results, it will display, but the information that i have previously entered are lost. How can i do to make my data remains on that page. By using session or? If by using session, how can i apply?

    And can i assign my results to be display on a textarea thats actually on my webform? Cos currently my results will display on top of my page.

    Not veri sure of using session at ASP, cos i normally doing ASP.NET, logic should be the same, but normally i'm using visual studio.net to do asp.net, so when now i'm doing asp on notepad, i'm quite lost..

    Thanks! ^^

  13. #13
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Well you are requesting the values submitted for you search so there is not reason why you can't populate the textbox or whatever you are using with the orginal values.

    You can put the results into whatever you like... if you want it all to turn up in the same textarea open one before the first if clause and then close it at the end....

  14. #14
    Join Date
    Jan 2006
    Posts
    44
    Okay, thanx for your answer.

    And ya, now i'm getting results that have repeated data..
    So now 'microsoft, aix 5.0' are now under the same server name, but it will get the results 2 times out.. But i only want it to appear 1 time..
    I can use 'UNION' right?
    But what i know is put

    SQL Statement
    UNION
    SQL Statement

    But my 2 sql statements are being seperated. how should i solve it?

  15. #15
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Errr,... no, not really....

    If you use union you will not be able to do the "records were found" check on each individual search criteria (unless you want to loop through the returned recordset and check for it).

    Essential this is what you want to do (please correct me if I am wrong).

    Get all the search key words.
    Do an exactly match search on each keyword.
    If no match is found do a like match search on each keyword.
    Combine the results but strip any duplicates out to one result.

    Is that about right??

Posting Permissions

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