Monday, January 26, 2015

CURSOR and Temporary Table on Microsoft SQL Server

Prepare the Ingredients
We have four tables named food_category, food, orders, and orders_detail. The Structure of the tables :

food table
No. Table Name Data Type Desc.
1 food_id INT PK, Autoincrement
2 food_name VARCHAR(255) -
3 food_description VARCHAR(255) -
4 food_status BIT -
5 category_id INT FK

food_category table
No. Table Name Data Type Desc.
1 category_id INT PK, Autoincrement
2 category_name VARCHAR(100) -
3 category_description VARCHAR(255) -
4 category_status BIT -

orders table
No. Table Name Data Type Desc.
1 order_id INT PK, Autoincrement
2 order_date DATE -
3 order_description VARCHAR(255) -
4 order_status BIT -

orders_detail table
No. Table Name Data Type Desc.
1 order_detail_id INT PK, Autoincrement
2 order_id INT FK
3 food_id INT FK
4 amount INT -

Here is the relationship between food, food_category, orders, and orders_detail table :


We want to get a list of foods with minimum sales of [X] item.

Let's Cook, Bro !
1. Run your Visual Studio. I’m using Visual Studio 2010 and Microsoft SQL Server 2008 as DataSource
2. Create a New Web Site > ASP.NET Empty Web Site > Give the name of Food : menu02_cursortemptable
3. Add New Item > Web Form > Default.aspx
4. Create a simple page like this :


5. Create a New Stored Procedure, write this query, and execute the query :

USE [menu02]
GO
/****** Object: StoredProcedure [dbo].[sp_view_food_order] Script Date: 01/26/2015 13:43:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Yudha Permana
-- Create date: 2015-01-22
-- Description: Cursor and Temporary Table
-- =============================================
ALTER PROCEDURE [dbo].[sp_view_food_order]
    -- Add the parameters for the stored procedure here
    @minimum_amount AS INT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Decalare Temporary Variable to Store food_id from CURSOR
    DECLARE @get_food_id INT

    -- Declare Temporary Table
    DECLARE @TempTable TABLE(col_food_id INT,
                col_food_name VARCHAR(255),
                col_food_description VARCHAR(255))

    -- Declare CURSOR to Get the List of food_id (Selected Category and All Orders)
    DECLARE cursor_food_id CURSOR
    STATIC FOR
        SELECT DISTINCT(food_id)
        FROM orders_detail
        WHERE amount > @minimum_amount

    -- Open the CURSOR
    OPEN cursor_food_id
    -- If Data is Available
    IF @@CURSOR_ROWS > 0
    BEGIN
      FETCH NEXT FROM cursor_food_id INTO @get_food_id
      WHILE @@FETCH_STATUS = 0
      BEGIN
          -- Store the Value on Temporary Table
          INSERT INTO @TempTable(col_food_id, col_food_name,
                    col_food_description)
          SELECT food_id, food_name, food_description
          FROM food
          WHERE food_id = @get_food_id

          FETCH NEXT FROM cursor_food_id INTO @get_food_id
      END
    END

    -- Close the CURSOR
    CLOSE cursor_food_id
    DEALLOCATE cursor_food_id

    SET NOCOUNT OFF

    -- View the Result of Temporary Table
    SELECT * FROM @TempTable;
END

6. Back to Visual Studio 2010, Click Gridview, Configure Data Source...


7. Choose the Data Connection from your local DataSource.


8. Choose "Specify a custom SQL statement or stored procedure" when configure the Select Statement.


9. Choose your Stored procedure.


10. Define Parameters :

No. Parameters Parameter Source ControlID DefaultValue
1 minimum_amount Control txtAmount 1



11. Test the Query with several parameters.


Okey… It’s time to EAT :-)




Download Source : menu02_cursortemptable.zip

Thursday, January 22, 2015

Warning : "Saving changes is not permitted." on Microsoft SQL Server 2008

When you hungry and suddenly you've got this error... (T_T)'



Yaps, that's right... If you're using SQL Server Management Studio 2008 and you want to save the changes of database diagrams or table structure, by default you'll get that error message.
Just go to the kitchen and take some cake or coffe --ups-- :-D hehe...

On SQL Server Management Studio 2008, go to Tools > Options...



On Option Windows, click the Designers and uncheck this option "Prevent saving changes that require table re-creation"



Okey… It’s time to EAT and don't forget to save your works :-D

Monday, January 5, 2015

How to Show All Category of Item to Gridview based on Dropdownlist ASP.NET

Prepare the Ingredients
We have two table named food_category and food. The Structure of the tables :

food table
No. Table Name Data Type Desc.
1 food_id AutoNumber PK, Autoincrement
2 food_name Short Text(255) -
3 description Short Text(255) -
4 food_status Yes/No -
5 category_id Number FK

food_category table
No. Table Name Data Type Desc.
1 category_id AutoNumber PK, Autoincrement
2 category_name Short Text(100) -
3 description Short Text(255) -
4 category_status Yes/No -

Here is the relationship between food and food_category table :


Now... Let's Cook !
1. Run your Visual Studio. I’m using Visual Studio 2010 and Microsoft Access as DataSource for this food
2. Create a New Web Site > ASP.NET Empty Web Site > Give the name of Food : menu01_allcategory
3. Add New Item > Web Form > Default.aspx
4. Add ASP.NET Folder > App_Data
5. Add Exisitng Access Database on App_Data folder
6. Create a simple page like this :


7. Assign food_category DataSource to a DropDownList and food DataSource to a GridView
8. Change the properties of DropDownList : “AppendDataBoudItems” to True, “AutoPostBack” to True
9. Edit Item on a DropDownList, Add a ListItem, Text = All Category, Value = 0
10. Edit the Query of food DataSource :

SELECT [food_id], [food_name], [description], [food_status], [category_id]
FROM [food] WHERE ([food_status] = ?)
AND (([category_id] = ?) OR (’0′ = ?))

11. Define Parameters :

No. Parameters Parameter Source ControlID DefaultValue
1 food_status None - True
2 category_id Control DropDownList1 0
3 category_id Control DropDownList1 0

Okey… It’s time to EAT :-)



Download Source : menu01_allcategory.zip