Hello, can anyone show me how to get array for many item by using function "LIKE" in visual basic application

Example:

Dim count integer
Dim part() as string

For i = o to i =count step 1
Sql = "SELECT DISTINCT TOP 65535 T1.A,T1.B " & _
      "FROM Table1 " & _
      "WHERE T1.A LIKE '" & part[1]& "%' " & _


But the array [1] got error. Please advise me for the above problem.
Thanks for help

apple

__________________
apple

Not sure your FOR statement is correct... Did you mean to use an "o" (lowercase "oh") instead of an "0" (zero) and do you mean to overwrite the value of "i"? I'm thinking you need something more like this...

FOR x = 0 to i STEP 1

Sql = "SELECT DISTINCT TOP 65535 T1.A,T1.B " & _
     "FROM Table1 " & _
     "WHERE T1.A LIKE '" & part[x] & "%' " & _



--Jeff Moden

Hi, Jeff

Thanks for your reply. Let'me answer your question first. The 0 is represented zero. The value will not overwrite because i want to view all the the user key item. Do you have any idea to solve this problem?

apple

Ok, if you say so... the "o" you posted is a lower case "oh", not a zero.

Would be helpful if you posted what the error was... other than that, I'm out of tricks on this one.

--Jeff Moden

Jeff,

This is the error

Run-time error '1004':

SQL Syntax Error.



apple

Not very helpful error listing, is it?

I do notice that you have a final "& _" in the posted code. Anything follow that? I think the best way to troubleshoot this is going to be to print the value of the SQL variable to see what it's actually doing.

--Jeff Moden

Hi Jeff,

At the back symbol _( underscore) is means concanate (&) of the Sql. I am thinking is it i should declare variable to solve my error?
For example declare :
Dim part()

The sql cant print out. Syntax error

apple

I know the "_" symbol means continue on the next line... what is the next line after the WHERE clause (it also has the "_")?

Also, you might be better off posting your question on a VB forum...

--Jeff Moden

This is not going to execute one giant sql statement; it is going to execute MANY SELECT statements.

You need something like

Dim sSqlMaster, sSqlLike
sSqlMaster = ""SELECT DISTINCT TOP 65535 T1.A,T1.B " & _
      "FROM Table1 " & _
      "WHERE T1.A "

For i = 0 to i =count step 1
sSqlLike = sSqlLike & "LIKE '" & part[i]& "%' AND T1.A "
Next

sSqlMaster = sSqlMaster & Left(sSqlLike, (Len(sSqlLike) - 10))
Your string will also have a trailing AND T1.A which you will need to remove which is what the LEFT() function is doing. (I am assuming that you are using classic ASP based upon your other posts in the forum)
hth

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

dparsons,

Thanks for your explanation. Can u elaborate more for this syntax
sSqlMaster = sSqlMaster & Left(sSqlLike, (Len(sSqlLike) - 10))?
I not understand : -10. Hope to hear from you soon. Thanks


apple