Results 1 to 6 of 6
  1. #1
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    1,663
    I've got the Superstore Electronics datafeed up and running with one minor problem to work around. In the category field there are lots of single quotes, most of which make no sense such as in " radio's " but correctness aside, I need to work around single quotes wherever they occur.

    Trying to avoid having to correct the data each time I import the text file, I think I need to do this at the database level with a query.

    I import it into Access and call the data with ASP. (This is where the crash occurs, whenever it finds a category with a '.) Instead of calling the table, is it possible to call a query that parses out or replaces the unnecessary single quotes?

    I can parse them out at the ASP level, but I'm not sure what the expression would be in the query.

    Thanks for any help. I'm drawing a blank on this after a late night of coding.

    Wayne

  2. #2
    Full Member
    Join Date
    January 18th, 2005
    Posts
    202
    I'm having the same problem but also be careful of the &, it's got to come out as well as the '.

    It seems that all the feeds have one or both of them in the categories.

    I find it easier to just do a quick Search and Replace on the fields until I have time to program a better solution.

    I don't know that a query can do the trick, it's probably going to be a function. I'm not sure if there's a built in one for Access. If you find it, let me know.



    SJohnson@NO_Spam_ClickMagazine.Net
    www.ClickMagazine.Net

  3. #3
    Full Member
    Join Date
    January 18th, 2005
    Posts
    379
    How are you using ASP to get the data, ADO ?

    I don't think I ever had problems getting single quotes from a DB. Writing data with single quotes can cause problems.

    Are you getting the error just by reading the data or when you go to do something like write to a file ?

    AT any rate I always do lot of cleanup in the ASP. For example if there is a category name called "radio's" that is the name of a file and also used for a page header I do something like:

    strPageHeader = RS.Fields("category").Value
    strPageName = replace(strPageHeader,"'","")

    Of course when you clean up one character you always find another that bones things up so Iusually write a function to cleanup data.

    <hr>
    Leave the gun, take the cannolis.

  4. #4
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    1,663
    Yes, using ADO to retrieve data. Error occurs when reading data in ASP "Select * FROM Products ..." (Products is the table.):

    Syntax error (missing operator) in query expression 'category LIKE '%Radio's%' ORDER BY category'.

    I was able to make a query in the DB which uses replace to strip the quote. Radio's becomes the correct plural, Radios. Running the query in the DBdisplays the correct results.

    Now ASP returns this message when trying to access the DB using "Select * from ProductQuery ...:

    * Error Type:
    Microsoft JET Database Engine (0x80040E14)
    Undefined function 'replace' in expression.

    I think the easiest solution is to just ask the merchant to get rid of the single quotes. Everyone is used incorrectly anyway.

    Wayne

  5. #5
    Full Member
    Join Date
    January 18th, 2005
    Posts
    202
    I've found that every Merchant has either the & or the ' in the categories. I kinda doubt we can get them all to remove them. Even when it is used properly.

    There is a Replace function in Access 2000. I took the example on Microsoft's web site and it works fine except when you try to call it from a query. If you do, it runs without protest and rather nicely deletes the entire contents of the field.

    So I guess the answer is to use the Module to setup a query that calls the function. When I find the time, I'll do that. Because it will need to be run pretty much on every datafeed.

    SJohnson@NO_Spam_ClickMagazine.Net
    www.ClickMagazine.Net

  6. #6
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    1,663
    Solved! Problem was not in data being returned but in my select statement. It was caused by the menu item calling a category variable with a single quote to be sent to the ASP page as is. I changed the code that created the menu string to change a single quote to two single quotes and Success!

    Works:

    "Select * FROM Products WHERE category LIKE '%Radio''s%' ORDER BY category"

    '%Radio's%' didn't work.

    Wayne

  7. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Category Problem
    By dreamerscove in forum SellFire
    Replies: 3
    Last Post: February 2nd, 2013, 02:35 PM
  2. Value of the Single Merchant Datafeed Site?
    By Cheesehead in forum Midnight Cafe'
    Replies: 4
    Last Post: June 28th, 2007, 09:12 AM
  3. A mall site or a single category site to start ?
    By b3v0r3 in forum Newbie Affiliate FAQs & Helpful Articles
    Replies: 7
    Last Post: July 5th, 2005, 12:27 PM
  4. A mall site or a single category site to start ?
    By b3v0r3 in forum Starting an Affiliate Program & Merchant Q&A
    Replies: 4
    Last Post: July 5th, 2005, 08:25 AM

Posting Permissions

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