Hi All,

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.

vInstSubProduct = InStr(1,vProductList,vSubProductID,1)

I have tried making it a string in the database etc but still returns all values.

Can someone please help.

Many thanks

Ian

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.

Fix the design and issues like this dissapear

Just to re-emphasise George's point this is a classic design error, and is addressed in one of the first rules of database design. Google normalisation, and especially look at First Normal Form.

pootle flump ur codings are working excelent.

Hi George,

Thanks for getting back to me.

My database design is as follows,

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?

Any help would be greatly appreciated.

Thanks
Ian

which of the following statements is true

one product can be on only one advert
one product can be on many adverts
many products can be on one advert
many products can be on many adverts

many products can be on many adverts.

For each advert the client selects up to 10, 20 or 40 products from a multi select dropdown, populated by a recordset from the products table.

The advert would be placed by anyone wishing to sign up to place an advert.

The problem is in the search page when I need to pull the records for a specific product from the field in the main advert table. This is where the products list is contained.

Hope this helps.

Many Thanks

Ian

Apologies, appear to have missed this thread!

To model a M:M relationship, you have to implement a "junction table" which, for your example, would look a little like this:

products (product_id, attribute_1, attribute_2, etc)
adverts (advert_id, attribute_1, attribute_2, etc)
product_adverts (product_id, advert_id)

The junction table only contains the fields that make up the primary key in both tables, and the primary key of the junction table is a composite made up of all these fields.

Hope this helps, let us know how you get on and feel free to ask any questions

Many thanks for replying.

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".

Please bear with me as SQL is not my forte.

Thanks
Ian

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:

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.

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:

Thanks very much.

I will give that a go.

Thanks

Ian

hi,

The junction table, is that a temporary table?
I got an error when the page was loading.
I have then created it, in doing this I set the idproducts as the Primary field.
Is this okay?

I am now getting an error as follows,

Microsoft VBScript compilation error '800a03f2'

Expected identifier

/clients/ConNet864538/htdocs/search-product.asp, line 32

for (i = 0 to uBound(aryProductList))
----^

I have dimmed i but that did not work
Can you please advise.

Many thanks
Ian

The junction table is a permanent table used to relate products and adverts.

Have you dimmed the other variables (oRS, iAdvertID, sProductList, aryProductList, iThisProductID, sSQL) ?

Yes,

I have altered all my searches using the junction tables and now all works.
The insertion of the split products, I moved to the placing advert page. Exactly the same code now works.

This is the first time I have used the forums and am very impressed with the time and effort you guys put in, in helping others who are not as profficient as yourselves.

Many thanks for all your help.
Very much appreciated.

Ian