Need the highest value of the Sku field in Products table

[Mike718NY]

I need a query that will return the highest value that is in the Sku field (Products table).
I not sure how to do it with the Able Commerce data structures.

Can anyone help me with this? thanks

[mazhar]

You can use the following query and make sure that column does not have alphanumeric characters

Code: Select all

SELECT TOP 1 CAST(Sku AS int) AS SKUNUM
FROM ac_Products
Order BY CAST(Sku AS int) DESC;

[Mike718NY]

thanks mazhar, but how do use in the AC7 code?
Can I use something like what Scott showed me:

ProductCollection productList = ProductDataSource.LoadForCriteria("ManufacturerId=3 AND VisibilityId=0", "name ASC");

I could use that query but I would need to open a connection, command object,
stored procedure, etc . . which I don't mind doing, . . but if there is something
available in the data access layer then that would be better I guess.

[mazhar]

You can use this code to run this query in AbleCommerce, this is all you need to get it run, don't worry about connection etc every thing will be done automatically for you.

Code: Select all

Database database = Token.Instance.Database;
                DbCommand selectCommand = database.GetSqlStringCommand("SELECT TOP 1 CAST(Sku AS int) AS SKUNUM FROM ac_Products Order BY CAST(Sku AS int) DESC");
                int sku = AlwaysConvert.ToInt(database.ExecuteScalar(selectCommand));