Results 1 to 13 of 13

Thread: InStr Problems

  1. #1
    Join Date
    Feb 2009
    Posts
    10

    Unanswered: InStr Problems

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by ianj846
    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.

    Fix the design and issues like this dissapear
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2009
    Posts
    10
    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

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2009
    Posts
    10
    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

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2009
    Posts
    10
    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

  9. #9
    Join Date
    Jan 2002
    Posts
    189
    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:

    Code:
    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.

    Code:
    <%
    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 & ")"
    		
    	next
    	
    	oRS.MoveNext()
    	
    loop
    %>
    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:

    Code:
    SELECT Products.IDProduct, Products.ProductName
    FROM Products
    INNER JOIN ProductsAdverts ON ProductsAdverts.idProducts = Products.IDProduct
    INNER JOIN Adverts ON ProductsAdverts.idAdverts = Adverts.IDAdvert
    WHERE Adverts.IDAdvert = 4

  10. #10
    Join Date
    Feb 2009
    Posts
    10
    Thanks very much.

    I will give that a go.

    Thanks

    Ian

  11. #11
    Join Date
    Feb 2009
    Posts
    10
    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

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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) ?
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2009
    Posts
    10
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •