Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ASP > Using split to get results from db, but only manage to get results for 2nd entry

Reply
Page 1 of 2 1 2
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 01-17-06, 23:37
Cuttie0506 Cuttie0506 is offline
Registered User
  
Join Date: Jan 2006
Posts: 44
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
Reply With Quote
  #2 (permalink)  
Old 01-18-06, 16:58
rokslide rokslide is offline
Registered User
  
Join Date: Nov 2003
Location: Christchurch, New Zealand
Posts: 1,617
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.
Reply With Quote
  #3 (permalink)  
Old 01-18-06, 20:39
Cuttie0506 Cuttie0506 is offline
Registered User
  
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 ^^
Reply With Quote
  #4 (permalink)  
Old 01-18-06, 20:52
rokslide rokslide is offline
Registered User
  
Join Date: Nov 2003
Location: Christchurch, New Zealand
Posts: 1,617
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.
Reply With Quote
  #5 (permalink)  
Old 01-18-06, 20:54
Cuttie0506 Cuttie0506 is offline
Registered User
  
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!! ^^
Reply With Quote
  #6 (permalink)  
Old 01-18-06, 20:59
rokslide rokslide is offline
Registered User
  
Join Date: Nov 2003
Location: Christchurch, New Zealand
Posts: 1,617
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.
Reply With Quote
  #7 (permalink)  
Old 01-18-06, 21:03
Cuttie0506 Cuttie0506 is offline
Registered User
  
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 21:10.
Reply With Quote
  #8 (permalink)  
Old 01-18-06, 21:15
rokslide rokslide is offline
Registered User
  
Join Date: Nov 2003
Location: Christchurch, New Zealand
Posts: 1,617
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
Reply With Quote
  #9 (permalink)  
Old 01-18-06, 21:42
Cuttie0506 Cuttie0506 is offline
Registered User
  
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 ^^
Reply With Quote
  #10 (permalink)  
Old 01-18-06, 22:17
rokslide rokslide is offline
Registered User
  
Join Date: Nov 2003
Location: Christchurch, New Zealand
Posts: 1,617
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...
Reply With Quote
  #11 (permalink)  
Old 01-18-06, 22:33
rokslide rokslide is offline
Registered User
  
Join Date: Nov 2003
Location: Christchurch, New Zealand
Posts: 1,617
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 22:35. Reason: Corrected sqlstring problem in loop
Reply With Quote
  #12 (permalink)  
Old 01-18-06, 23:21
Cuttie0506 Cuttie0506 is offline
Registered User
  
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! ^^
Reply With Quote
  #13 (permalink)  
Old 01-18-06, 23:41
rokslide rokslide is offline
Registered User
  
Join Date: Nov 2003
Location: Christchurch, New Zealand
Posts: 1,617
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....
Reply With Quote
  #14 (permalink)  
Old 01-19-06, 02:03
Cuttie0506 Cuttie0506 is offline
Registered User
  
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?
Reply With Quote
  #15 (permalink)  
Old 01-19-06, 17:01
rokslide rokslide is offline
Registered User
  
Join Date: Nov 2003
Location: Christchurch, New Zealand
Posts: 1,617
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??
Reply With Quote
Reply
Page 1 of 2 1 2

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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 On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.