Important Notice from AspDotNetStorefront
It is with dismay that we report that we have been forced, through the action of hackers, to shut off write-access to this forum. We are keen to leave the wealth of material available to you for research. We have opened a new forum from which our community of users can seek help, support and advice from us and from each other. To post a new question to our community, please visit: http://forums.vortx.com
Results 1 to 9 of 9

Thread: SQL Query to Update Extended Prices

  1. #1
    joecalardo is offline Member
    Join Date
    May 2011
    Posts
    61

    Default SQL Query to Update Extended Prices

    SQL Query to Update Extended Prices?? Does anyone know what this is? I tried the insert function for dbo.extendedpricing, didn't take affect tho... Thanks !. I have my spreadsheet with my variant id, and extended price, and product id etc, product id starts at a2, variant id is b2, and extended price is c2. thanksagain

  2. #2
    esedirect is offline Senior Member
    Join Date
    Feb 2010
    Location
    Norfolk, UK
    Posts
    343

    Default

    The table ExtendedPrice expects the following:

    Code:
    INSERT INTO [AspDotNetStorefront].[dbo].[ExtendedPrice]
               ([ExtendedPriceGUID]
               ,[VariantID]
               ,[CustomerLevelID]
               ,[Price]
               ,[ExtensionData]
               ,[CreatedOn])
         VALUES
               (<ExtendedPriceGUID, uniqueidentifier,>
               ,<VariantID, int,>
               ,<CustomerLevelID, int,>
               ,<Price, money,>
               ,<ExtensionData, ntext,>
               ,<CreatedOn, datetime,>)
    All columns have to have something except ExtensionData which can be NULL or blank. CreatedOn defaults to GETDATE() ie now, CustomerLevelID defaults to 0 and ExtendedPriceGUID defaults to NEWID(). It's not interested in ProductID.

    So you'll need this in your spreadsheet:

    Code:
    ="INSERT INTO ExtendedPrice VALUES (NEWID()," & B2 & ",0," & C2 & ",NULL,GETDATE())"
    This is for v8.0.1.2 db. Not tried or tested. I urge you to try this in a test db first.
    http://www.esedirect.co.uk
    --------------------------------------------------------------------------
    Using MS 9.2.0.0 with the following customisations:

    Lightbox/Fancybox enlarged images;
    Auto-suggest searchbox;
    Extra product information shown only to our IP Address (such as supplier info, costs, etc.);
    Failed transactions emailed via trigger;
    Custom app to show basket contents when customer online;
    Orders pushed through to accounting systems.

    All the above without source!

  3. #3
    joecalardo is offline Member
    Join Date
    May 2011
    Posts
    61

    Default

    I'm on a 7 version ... :-\

  4. #4
    esedirect is offline Senior Member
    Join Date
    Feb 2010
    Location
    Norfolk, UK
    Posts
    343

    Default

    That only means I don't know what the structure of the table is. So maybe someone else can chime in, unless you know what the structure is?
    http://www.esedirect.co.uk
    --------------------------------------------------------------------------
    Using MS 9.2.0.0 with the following customisations:

    Lightbox/Fancybox enlarged images;
    Auto-suggest searchbox;
    Extra product information shown only to our IP Address (such as supplier info, costs, etc.);
    Failed transactions emailed via trigger;
    Custom app to show basket contents when customer online;
    Orders pushed through to accounting systems.

    All the above without source!

  5. #5
    BFG 9000 is offline Senior Member
    Join Date
    Oct 2006
    Location
    South UK
    Posts
    882

    Default

    You can use this :-

    C#/VB.NET Code:
    ="INSERT INTO dbo.ExtendedPrice (VariantID, CustomerLevelID, Price) values ("&B2&","&D2&","&C2&");" 

    This assumes that you have your CustomerLevelID in D2.

    The query you need to run is as follows :-

    INSERT INTO dbo.ExtendedPrice (VariantID, CustomerLevelID, Price) values (333,6,1.3254);

    This inserts an extended price of 1.3254 for Customer Level 6 & VariantID 333



    The other fields in the table (GUID,Date) will be auto populated.


    TTFN

    BFG

  6. #6
    joecalardo is offline Member
    Join Date
    May 2011
    Posts
    61

    Default

    Whew thank you bfg, and yes i do have customer level id im going to run that in a little bit with one of my records, thanks ill let you know

  7. #7
    joecalardo is offline Member
    Join Date
    May 2011
    Posts
    61

    Default

    Ahhh BFG I just realized that didn't help the prices, it just made another variant within the variant, for example


    you can see there's multiple drop downs, because of the query i used to update the prices, in this case i updated twice. which means, that what you are seeing is not 3 seperate variants, but 1 variant with 3 extended prices.

    How can I fix this with SQL ? It's only a few dozen products I've used this query on nothing major but I want a simple fix and a better update to the extended pricing due to the fact that I will be needing to update my account prices.

  8. #8
    esedirect is offline Senior Member
    Join Date
    Feb 2010
    Location
    Norfolk, UK
    Posts
    343

    Default

    A simple way to do it would be to delete the rows for the variant first, and then re-insert the one you want, like this;

    Code:
    ="DELETE FROM dbo.ExtendedPrice WHERE VariantID="&B2&"; INSERT INTO dbo.ExtendedPrice (VariantID, CustomerLevelID, Price) values ("&B2&","&D2&","&C2&");"
    http://www.esedirect.co.uk
    --------------------------------------------------------------------------
    Using MS 9.2.0.0 with the following customisations:

    Lightbox/Fancybox enlarged images;
    Auto-suggest searchbox;
    Extra product information shown only to our IP Address (such as supplier info, costs, etc.);
    Failed transactions emailed via trigger;
    Custom app to show basket contents when customer online;
    Orders pushed through to accounting systems.

    All the above without source!

  9. #9
    joecalardo is offline Member
    Join Date
    May 2011
    Posts
    61

    Default

    Thank you so much ESE, I swear you've helped me with almost my entire sql life with my site as well as you BFG- much appreciate from you both