Need help with the SELECT statement

[Huang_A]

Ok, here's the long story short... I wish to select from a table with the WHERE switch and use variables for it.

IE:
"SELECT * FROM Contents WHERE Name = " & VARIABLE

Is it possible to do that? I've made numerous attempts at this and always ended up killing my brain thinking it's impossible yet in theory it should work.

I've used the code
"SELECT * FROM Contents WHERE Name = '" & Variable & "'" but it says "Item cannot be found in the collection corresponding to the requested name or ordinal." and when I don't have the ' 's it says "[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1." Any ideas anyone?

Thanks,

[Dreamquick]

If in doubt always enclose/delimit your column names and remember to replace any single quotes...

"SELECT * FROM [Contents] WHERE [Name] = '" & VARIABLE & "';"

Quotes surrounding VARIABLE assume that [Name] is a text field rather than numeric.

- Tony

[Birdman]

Welcome to WebmasterWorld!

I'm not sure if I understand what you are looking for. Are you saying you will already know the "name" and you want to search for anything with "name" + variable or is "name" the variable itself?

[mattglet]

you are making sure that your "Name" column exists, and that the variable is actually a choice?

[Huang_A]

I'm trying to let it search the table of the database where certain column is equal to certain variabled data.

IE:
I want to find a column in the Table Content1 where the column Name is the same as the value of variable "strMyVar".

In my code, I use DSNLess connection, so basically, I am using primitive method of declaring a SQL String, and submit the SQL query alongside with the record set query.

Here's a snipplet of my code:
Set con = Server.CreateObject("ADODB.Connection")
con.Open "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath("database.mdb") & ";"
Set rs = Server.CreateObject("ADODB.Recordset")
SqlStr = "SELECT * FROM Contents1 WHERE Name = '" & SubDiv & "'"
rs.Open SqlStr,con

When I use the above code, it tells me that:
"Item cannot be found in the collection corresponding to the requested name or ordinal." Even though I have made sure to triple check that the database.mdb contains a table called Contents1 and there is a column called Name, and the default value of SubDiv (Main) is included in the column. I've also made sure that it was in the same case incase if it is cAsE SenStive. I don't know what else there is to do... so here I am... lol :) Thanks all.

[Dreamquick]

Ah! Assuming you just want to find if one of the columns in your table has a specific name then that's very easy - if you then want to know what value this column has then you could modify the example to do that v. easily.

If you were using a real database other than Access it would be far easier as the structure is often held as a table.

In access w/ ADO you could probably do something like;

SELECT TOP 0 *
FROM SomeTable

Assuming we open this query in objRecordset then we could do something like this - assuming sTheFieldName holds the name of the field you want to look for and that bFieldExists stores the result;

bFieldExists = False
For Each objField In objRecordset.Fields
--If StrComp( objField.Name, sTheFieldName, vbTextCompare ) = 0 Then
----bFieldExists = True
----Exit For
--End If
Next

- Tony