I am just getting the hang of asp but am having a little problem with displaying the information I want.
I have an access database with a field called ProductList, populated with a comma separated list of numbers.
When the user clicks a link a variable is set (vSubProductID) and would like it to display a record, if that number is in the ProductList field in the database.
I used the InStr, but for example, if vSubProductID = 9 and in the ProductList field in the database, the number 99 exists then the records returns that as well.
I would only like it to return records that has a 9 in it.
have an access database with a field called ProductList, populated with a comma separated list of numbers.
You need to change your database design. This sounds an aweful lot like a one-to-many relationship and so should be modelled like one. ProductList should be a table - not a field - with a FK relationship to the table it currently resides in.
I have a table with products and id's.
I have another table called advert, this contains the field ProductList.
The field in the table Advert is populated by a multi select dropdown, inserting the comma separated list.
What would I need to change?
In thinking about it the Instr statement be better in the SQL Select statement so as it only pulls the record that has the productID in it.
At the moment I am not doing a where in the SQL statement as below,
rsProducts.Open "Select IDAdvert, AdvertType, CompanyName, ProductList, Telephone, Email from tblAdvert Order By AdvertType, CompanyName, IDAdvert ", conn
Then the records are filtered using the Instr.
Does this make sense.
Can you have an Instr in the SQL using variables?
Do you have an example?
I am not sure I fully understand what you mean.
How would these tables be populated?
Does that mean the the table/field ProductList, that I have at the moment would be redundant?
How would I search these "junction tables".
As long as your Products table has a row for each product, with a unique ID identifying that product, then that table is fine.
But yes, you need to get rid of your ProductList field in the Adverts table. That list of products-for-each-advert needs to become your junction table. It's not massively hard; someone with better SQL than me could probably do it directly in the database but you can equally write a quick ASP page to do the heavy lifting for you.
First, you need to make your junction table. Something like this should do:
CREATE TABLE ProductsAdverts (
idProducts INTEGER UNSIGNED NOT NULL ,
idAdverts INTEGER UNSIGNED NOT NULL ,
PRIMARY KEY(idProducts, idAdverts));
So, you've got your table to populate. Now, in ASP, create a recordset that gets a list of advert ID's and the troublesome ProductList field:
Select IDAdvert, ProductList FROM tblAdvert
Now loop through that recordset, using Split() to turn your comma-separated list into an array. For each product ID in your array, add a record to the new database table.
do while not oRS.EOF
iAdvertID = oRS.Fields("IDAdvert")
sProductList = oRS.Fields("ProductList")
aryProductList = split(sProductList, ",")
for (i = 0 to uBound(aryProductList))
iThisProductID = aryProductList(i)
sSQL = "INSERT INTO ProductsAdverts (idProducts, idAdverts) VALUES (" & iThisProductID & ", " & iAdvertID & ")"
Now you can delete the ProductList field from your tblAdverts, and get some use out of your relational database.
Here's how to get a list of products for a given Advert ID:
SELECT Products.IDProduct, Products.ProductName
INNER JOIN ProductsAdverts ON ProductsAdverts.idProducts = Products.IDProduct
INNER JOIN Adverts ON ProductsAdverts.idAdverts = Adverts.IDAdvert
WHERE Adverts.IDAdvert = 4