SQL Statement with several array items

[paulafernandes]

Hi!

I have a new problem here...
I have multiple list box where the user can choose several items:
intservicos = request("servico")
arrservicos = split(intservicos, ",", -1, 1)

Imagine that I have on my array 1, 3, 5.

Now I want to built an SQL String where it goes something like this:
strsql = "SELECT * FROM tab_1 where servico = 1 or servico =3 or servico =5"

Now I have:
for i = LBOUND(arrservicos) to UBOUND(arrservicos)
strsql = "SELECT tab_atendimentos.cod_indicador FROM tab_atendimentos INNER JOIN tab_servicos_locais ON "
strsql = strsql + "tab_atendimentos.cod_servico_local = tab_servicos_locais.cod_servico_local "
strsql = strsql + "WHERE tab_atendimentos.cod_servico_local = " &arrservicos(i)
if (UBOUND(arrservicos) > LBOUND(arrservicos)) then
strsql = strsql + " OR tab_atendimentos.cod_servico_local = " &arrservicos(i)
end if

objRecordset.Open strSQL, objConnection, adOpenKeyset
Response.Write strsql &"<br>"
do while not objrecordset.EOF
Response.Write objrecordset("cod_indicador") &"<br>"
objrecordset.MoveNext
loop
next

But the "OR" part doesn't work...
What's the better way to do this?

Thank's
Paula

[allida77]

You could just use:

strsql = "SELECT * FROM tab_1 where servico IN (" & request("servico") & ")"

[raf]

can't you use
select * from table where servicos In (values from area)

the problem with your code is:
- you should build the sql string using the loop and then execute in once
- " if (UBOUND(arrservicos) > LBOUND(arrservicos)) then " ; can this ever occur in this loop ?

this is some similar code to show what i mean : the main part of the sql is written before the loop, then only the condition is build using the loop, and after the loop, the sql-statement is executed.

sql = "delete from table where "

For each box in request.form
if request.form(box) = "1" then
if row = 1 then
sql = sql + "(Id=" + box + ")"
else
sql = sql + " OR (Id=" + box + ")"
end if
end if
next

dim numberdel
condb.Execute sql,numberdel

[whammy]

I agree with the above two posts - since you're using a multiple select, your data should already be formatted like:

1, 3, 5

etc.

That's perfect to use with the IN() statement in SQL, no need for a bunch of other stuff. I would just use something like:

strsql = "SELECT * FROM tab_1 where servico IN('" & servico & "')"

;)

[paulafernandes]

Thank's, I didn't knew this operator!

But if I use the string that I get from the request (Ex.: 1, 3, 5) I get:

select * from tab1 where servico = ('1, 3, 5') instead of

select * from tab1 where servico = ('1', '3', '5').

I know I can built the string to put the " ' ", but by the way you told me I guess I shouldn't need to built it. I think I'm missing something...

Thank's
Paula

[glenngv]

is servico numeric or varchar? if numeric, then you should use:

strsql = "SELECT * FROM tab_1 where servico IN(" & servico & ")"

if varchar, then you have to loop to each item.
you can use

for each item in request("servico")
'code
next

instead of splitting it

i dont have time now to give you the code, maybe other users can give you the code

[paulafernandes]

Thank's!!!

The field is a int so I get it working now! Really, thank's a lot!
I'm learning more from you that from books...

Sometimes I can do really complicated things, and I stuck on the easy ones...

I have another question for you and I know I should put another post, but here it goes:

What is wrong with this piece of code:
strsql = "select cod_indicador, data from tab_atendimentos where convert(double, data) like '20030319'"

It gives me the error:
Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 1: Incorrect syntax near ','.

The field "data" is of type char.
I don't know very well how to use CONVERT, and I can't find anywere how to use this.

Thank's a lot again
Paula

[raf]

Minor detail: you best use “In” as an operator (like for your code) an ‘IN’ as a claus to refer to an external db.

About that sql-string:
Convert ? Never seen that. Do you mean CDbl() as below?

Also, you can’t use a function straight into a string like that. And “like” should not be used like that. Only use like if you include wildcards.

For stringvariables
strsql = "select cod_indicador, data from tab_atendimentos where thevariale='20030319'"
strsql = replace(strsql, “thevariable”, the result of that convert)

Buth why use a function on the variablename ?

What exactly are you trying to do?

More info on CDbl()
CDbl Function Language Reference Version 1
See Also

Description
Returns an expression that has been converted to a Variant of subtype Double.
Syntax
CDbl(expression)
The expression argument is any valid expression.
Remarks
Minor detail: you best use “In” as an operator (like for your code) an ‘IN’ as a claus to refer to an external db.

About that sql-string:
Convert ? Never seen that. Do you mean CDbl() as below?

Also, you can’t use a function straight into a string like that. And “like” should not be used like that. Only use like if you include wildcards.

For stringvariables

strsql = "select cod_indicador, data from tab_atendimentos where thevariale='20030319'"
strsql = replace(strsql, “thevariable”, the result of that convert)

Buth why use a function on the variablename ?:confused:

What exactly are you trying to do?

More info on CDbl()
---------------------------------------------------------------------------
CDbl Function Language Reference Version 1
See Also

Description
Returns an expression that has been converted to a Variant of subtype Double.
Syntax
CDbl(expression)
The expression argument is any valid expression.
Remarks
In general, you can document your code using the subtype conversion functions to show that the result of some operation should be expressed as a particular data type rather than the default data type. For example, use CDbl or CSng to force double-precision or single-precision arithmetic in cases where currency or integer arithmetic normally would occur.
Use the CDbl function to provide internationally aware conversions from any other data type to a Double subtype. For example, different decimal separators and thousands separators are properly recognized depending on the locale setting of your system.
This example uses the CDbl function to convert an expression to a Double.
Dim MyCurr, MyDouble
MyCurr = CCur(234.456784) ' MyCurr is a Currency (234.4567).
MyDouble = CDbl(MyCurr * 8.2 * 0.01) ' Convert result to a Double (19.2254576).

[paulafernandes]

What I'm trying to do is this:

I have a field on my database that stores a date on a field of type char formatted like (ex.: today´s date) 20030326.
Now I have a date that the user chooses and I put it on that format too.

So when I do the select statement I want to manipulate them as int (not double, my mistake...) so I can see witch one's bigger, etc...

I use the CONVERT because of a tip that was given to me in this forum. You can see it in:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odappepr_5.asp

But I can use it in this case...

Thank's
Paula

[raf]

Hmm. Think i understand.
Wouldn’t it be easier to change the variable in your db into an integer? Then you could do

Sql = “Select * from table where data > adate”
Sql=replace(sql,”adate”,CInt(request.form(“date”)))

(if you need all records with a higher date then the present date.
If you cant change the data-type of that variable, then you woun’t be able to use convert in an sql statement either …

Why not have it as a date-variable ?

I’ve look at your reference. Very interesting.
If that’s correct, i suppose it would be something like this then that you need

Sql = “SELECT * FROM table WHERE {fn CONVERT(data,SQL_INTEGER)} > 'adate'”
Sql=replace(sql,”adate”,CInt(request.form(“date”)))

but that doesn’t work. Not for access it did.


But then again, i run this statement on an access db. Field 1 is a textvariable, and still the query ran perfectly and returned only records with a “date” lower then 2003-03-10
SQL =”SELECT TEST.Field1 FROM TEST WHERE (((TEST.Field1)<"20030310"))”

[paulafernandes]

I have to keep the field as an char, because I was told to do so, and I'm just a trainee... :(

I had already tried to compare the strings as chars and it worked just fine, but (I confess my ignorance...) I don't know if that's a right way to do it. I think that is better to do it as int, but...

Thank's
Paula