variables and IN statement in SQL

[trc]

What am I doing wrong with this?

searchArray = split(Recordset1__MMColParam, " ")

Build the sql string
Do while x <= UBound(searchArray)
If x < Ubound(searchArray) Then
strSQL = strSQL & "num = '" & trim(searchArray(x)) & "', "
Else
strSQL = strSQL & "num = '" & trim(searchArray(x)) & "'"
End If
x = x + 1
Loop

myVar1 = searchArray(0)
myVar2 = searchArray(1)
myVar3 = searchArray(2)
myVar4 = searchArray(3)
strSql="SELECT KB_ID, KB_Title, KB_Error_Msg, KB_Keywords, KB_Date, KB_Sum " & "FROM Tbl_KB WHERE KB_Keywords IN " _
'& ('" & myVar1 & "','" & myVar2 & "','" & "','" & myVar3 _
'& "','" & myVar4 & "')"


************
My purpose in life is to show others what not to do.
<!--Caution, dates on calendar are closer then they appear.-->

 

[ehsant]

Hi...
I don't get it ...
what's that Do Loop up there ?
do you use it ?
if so, where ?
and also, are you getting any error or what ?
you code, looks well...

----
Harsh words break no bones but they do break hearts.
E.T.

 

[SnaveBelac]

What exactly is the problem? An error message and an idea of what you are trying to do would be v. useful.

Cheers


----------------------------
SnaveBelac - Adventurer
----------------------------

 

[Tarwn]

Here is your error:

strSql="SELECT KB_ID, KB_Title, KB_Error_Msg, KB_Keywords, KB_Date, KB_Sum " & "FROM Tbl_KB  WHERE KB_Keywords IN " _
'&  ('" & myVar1 & "','" & myVar2 & "','" & "','" & myVar3 _
'& "','" & myVar4 & "')"

Ignore the do loop. It is building the portion of the list you plan on putting into the IN() statement in your SQL string, BUT when the abnove code is executed it throws out anything that was previously in the strSql variable and replaces it with ="SELECT KB_ID, KB_Title, KB_Error_Msg, KB_Keywords, KB_Date, KB_Sum " & "FROM Tbl_KB WHERE KB_Keywords IN "

The underscore is not an error because it basically just tells the sxcript that the next line will be part of the current line (in this case a comment).

So my guess is your getting an SQL error, because you haven't finished the IN portion of the sql string, it doesn't even have parantheses.

The next part of your problem is that your building the WHERE portion of the string in your loop incorrectly. At no point can you SELECT * FROM Something WHERE num = '1', num='2', etc... You need to either use AND or OR between those arguments, not commas. In this case I would expect to use an OR because there is no way one field could be equal to n differant values unless n = 1 or all n values were the same.

The easiest way here would be to continue using the IN statement. It looks like you originally had the number oif inputs hardcoded and are looking to change that to be dynamic.

Here is the easiest way to convert you space-delimited value into a list that will work in the IN statement:

searchArray = split(Recordset1__MMColParam, " ")

Dim in_values
in_values = "'" & Join(searchArray,"','") & "'"

this will output a comma-delimited list of all the values in single quotes so you can just pop it between two parantheses for an IN SQL call (ie, at the end of the existing SQl string to replace the commented out lines).

I would also suggest doing a Trim() inside your split, otherwise beginning or trailing spaces will giv you some null values in the array which will then get placed into your searh string:

searchArray = split(Trim(Recordset1__MMColParam), " ")

I would also try not to depend on Dreamweaver to write to much of your code for you. It works alright for generic type stuff, but is a real pain to edit, doesn't give you as much practice as ASP, and can be much less efficient than hand coding. I've head it's good for doing page layout and stuff like that, but I can't comment on that because I do t all by hand.

Hope that helped,
-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/

 

[trc]

I just wanted to thank everyone that helped me with this challenge. Tarwn your code was great, thanks. Unfortunately, I did not get the results that I wanted from the search. I was trying to build a knowledge base and the In statement did not give the desired results.

The following code is what I wound up using:

Dim searchArray
Dim strSQL
searchArray = split(Trim(Recordset1__MMColParam), " ")

strSQL = "SELECT ID, Title, SYMPTOM, Keywords, Sum, Resolution, Cause, Component, FAQDate, PUBLICFAQ FROM FAQ WHERE "

'Build the sql string
Do while x <= UBound(searchArray)
If x < Ubound(searchArray) Then
strSQL = strSQL & " ID Like '%" & trim(searchArray(x)) & "%' OR " & "Title Like '%" & trim(searchArray(x)) & "%' OR " & "Symptom Like '%" & trim(searchArray(x)) & "%' OR "& "Keywords Like '%" & trim(searchArray(x)) & "%' Or " & "Cause Like '%" & trim(searchArray(x)) & "%' Or "& "Sum Like '%" & trim(searchArray(x)) & "%' Or " & "Resolution Like '%" & trim(searchArray(x)) & "%' Or " & "Component Like '%" & trim(searchArray(x)) & "%' Or "
Else
strSQL = strSQL & " ID Like '%" & trim(searchArray(x)) & "%' OR " & "Title Like '%" & trim(searchArray(x)) & "%' OR " & "Symptom Like '%" & trim(searchArray(x)) & "%' OR "& "Keywords Like '%" & trim(searchArray(x)) & "%' Or " & "Cause Like '%" & trim(searchArray(x)) & "%' Or "& "Sum Like '%" & trim(searchArray(x)) & "%' Or " & "Resolution Like '%" & trim(searchArray(x)) & "%' Or " & "Component Like '%" & trim(searchArray(x)) & "%'"
End If
x = x + 1
Loop

The result was the following SQL statement when a search for one word was entered. It gets very entertaining with several words.

SELECT ID, Title, SYMPTOM, Keywords, Sum, Resolution, Cause, Component, FAQDate, PUBLICFAQ FROM FAQ WHERE ID Like '%Mobile%' OR Title Like '%Mobile%' OR Symptom Like '%Mobile%' OR Keywords Like '%Mobile%' Or Cause Like '%Mobile%' Or Sum Like '%Mobile%' Or Resolution Like '%Mobile%' Or Component Like '%Mobile%'

************
My purpose in life is to show others what not to do.
<!--Caution, dates on calendar are closer then they appear.-->

 

[ehsant]

Hi...
you can use this code :

strSQL = "SELECT ID, Title, SYMPTOM, Keywords, Sum, Resolution, Cause, Component, FAQDate, PUBLICFAQ FROM FAQ WHERE "

'Build the sql string
For x = LBound(searchArray) To UBound(searchArray)
	If x > LBound(searchArray) Then
		strSQL = strSQL & " AND "
	End If
	strSQL = strSQL &  "(ID Like '%" & trim(searchArray(x)) & "%' OR " &  "Title Like '%" & trim(searchArray(x)) & "%' OR " &  "Symptom Like '%" & trim(searchArray(x)) & "%' OR "& "Keywords Like '%" & trim(searchArray(x)) & "%' Or " & "Cause Like '%" & trim(searchArray(x)) & "%' Or "& "Sum Like '%" & trim(searchArray(x)) & "%' Or " & "Resolution Like '%" & trim(searchArray(x)) & "%' Or " & "Component Like '%" & trim(searchArray(x)) & "%')"
Next

----
Harsh words break no bones but they do break hearts.
E.T.