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

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

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