Array handling SQL 1

[santanudas]

Hi all,

I need some help to write a SQL query. I’ve an array say ‘Array(x, y,....)’ where the number of the elements and their values are not static. It changes both the number of the elements and their values time to time. Now the SELECT query should be like this:

SELECT * FROM [tblRoom] WHERE [XXX] = 'x' AND [XXX] = ‘y’;
when 'Array(x,y)'
Or
SELECT * FROM [tblRoom] WHERE [XXX] = 'a' AND [XXX] = ‘b’ AND [XXX] = ‘c’; when 'Array(a,b,c)' and so on...

That means it has to search row-by-row with array values I guess.

I’ll be really grateful to you if any body can give me a clue.
Thank you in advance.

Santanu

 

[swampBoogie]

Which langauge are you writing the application in?

 

[santanudas]

Access 2000 with VBA, Thanks

 

[swampBoogie]

q = "select * from tblRoom where xxx in ('" & _
join(arr,"','") & "')"
set rs = connectObject.execue(q)

 

[santanudas]

swampBoogie,
thanks for your help. Do I need to add any reference to use 'connectObject'? Actually I haven't use that before, selecting 'connectObject' and then pressing F1 telling "Keyword not found". Any idea why?

Regards,
Santanu

 

[cmmrfrds]

You will need to create the connection object.

dim connectObject as new adodb.connection
dim rs as new adodb.recordset

set connectObject = CurrentProject.Connection

'-This returns the results into rs
set rs = connectObject.execute(q)

 

[batmanforever]

Can anyone help me out with this ?

I have a C code on Win2k machine [ purely C code, not MFC ] that reads off an MS excel database (Office 2k version ).
All is well, expcet that I now need some kind of array handling in my SQL queries.

Here is what I am doing now is:
Pass a number ID value from my C code as a parameter to the SQL SELECT stmt.
So, the SELECT stmt looks like this...

wsprintf( szSql," SELECT Code, StreetID, StreetName FROM [Det$] WHERE Code = %lu OR Code =%lu ORDER BY Code, StreetID", scodeID0, socdeID1);

What I want to do is:
Since, I dont know at compile time, how many ID's I will be searching for, ie I dont know the number of scodeID's, How do I make it read from an array which has these values ?
my C code will first fill in an array with ID values at runtime. This will be fed to the SELECT stmt and it has do do the iteration for the values in the array.

I hope u understood the problem