Click here to Skip to main content
15,861,125 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am creating a an application which has a Sql Query Notification but i'ved encountered a problem .. The System is kept on looping that goes onto infinite loop.The Query that i'ved used is in Stored Procedures and I'ved used 7 tables in my Query. What seems to be the problem of my query or my application ? Kindly Help me to resolved this thanks :) I'll post my codes here This is the Stored Procedures i am using
SQL
ALTER PROCEDURE [dbo].[INVENTORYLOG_VIEW]
AS
    BEGIN
    SELECT
     ACCOUNT.Emp_Fname + ' ' + ACCOUNT.Emp_Lname AS [USERS],
     INVENTORYLOG.ACTIVITYDATE AS [ACTIVITY DATE],
     INVENTORYLOGDETAILS.ACTIVITYTIME AS [ACTIVITY TIME],
     INVENTORYLOGDETAILS.LOG_ACTION AS [USER LOG],
     CATEGORY.CATEGORY_NAME,
     BRAND.Brand_Name AS BRAND,
     PRODUCTDESCRIPTION.DESCRIPTION_TYPE,
     COLOR.Color_Name AS [COLOR NAME],
     SIZES.SIZE,
     INVENTORY.QUANTITY_ON_HAND AS [CURRENT QTY],
     INVENTORYLOGDETAILS.QUANTITY_LOG_ACTION AS [USER LOG QTY]
FROM
dbo.ACCOUNT INNER JOIN
dbo.INVENTORYLOG ON dbo.ACCOUNT.ACCOUNT_ID = dbo.INVENTORYLOG.Account_Id INNER JOIN
dbo.INVENTORYLOGDETAILS ON dbo.INVENTORYLOG.INVENTORYLOGID = dbo.INVENTORYLOGDETAILS.INVENTORYLOGID INNER JOIN
dbo.INVENTORY ON dbo.INVENTORYLOGDETAILS.INVENTORY_ID = dbo.INVENTORY.INVENTORY_ID INNER JOIN
dbo.PRODUCT_DTL ON dbo.INVENTORY.PRODUCT_DTL_ID = dbo.PRODUCT_DTL.PRODUCT_DTL_ID INNER JOIN
dbo.COLOR ON dbo.PRODUCT_DTL.COLOR_ID = dbo.COLOR.Color_Id INNER JOIN
dbo.BRAND ON dbo.PRODUCT_DTL.BRAND_ID = dbo.BRAND.Brand_Id INNER JOIN
dbo.CATEGORY ON dbo.PRODUCT_DTL.CATEGORY_ID = dbo.CATEGORY.CATEGORY_ID INNER JOIN
dbo.PRODUCTDESCRIPTION ON dbo.PRODUCT_DTL.DESCRIPTION_ID = dbo.PRODUCTDESCRIPTION.DESCRIPTION_ID INNER JOIN
dbo.SIZES ON dbo.PRODUCT_DTL.SIZE_ID = dbo.SIZES.SIZE_ID
END


and this is my code

C#
string connect;
        String connection;
        private SqlConnection cons = null;
        private SqlCommand command = null;
        private DataSet myDataSet = null;
        public Inventory_InventoryLog()
        {
            InitializeComponent();
            this.gridView1.OptionsView.ColumnAutoWidth = false;
            this.gridView1.ScrollStyle = ScrollStyleFlags.LiveHorzScroll | ScrollStyleFlags.LiveVertScroll;
            ConnectionString access = new ConnectionString();
            connection = access.AccessConnection(connect).ToString();

            // Sql Notifications Automatic
            EnoughPermission();
        }

        public bool EnoughPermission()
        {
            SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
            try
            {
                perm.Demand();
                MessageBox.Show(""+perm);
                return true;
            }
            catch(System.Exception)
            {
                return false;
            }
        }
        private void GetActivateData()
        {
            try
            {
                //String query = "SELECT [USER], [ACTIVITY DATE], [ACTIVITY TIME], [USER LOG], CATEGORY_NAME, Brand_Name, DESCRIPTION_TYPE, Color_Name, SIZE, [ CURRENT QTY], [USER LOG QTY] FROM INVENTORY_INVENTORY_LOG_DETAILS";
                SqlDependency.Stop(connection);
                SqlDependency.Start(connection);
                if (cons == null)
                  cons = new SqlConnection(connection);
                if (command == null)
                {
                    command = new SqlCommand("dbo.INVENTORYLOG_VIEW", cons);
                    command.CommandType = CommandType.StoredProcedure;
                }
                if (myDataSet == null)
                    myDataSet = new DataSet();
                GetActualData();
            }
            catch (Exception p)
            {
                MessageBox.Show(""+p);
            }
            MessageBox.Show("Being called");
        }
        private void GetActualData()
        {
            myDataSet.Clear();
            command.Notification = null;
            SqlDependency dependecy = new SqlDependency(command);
            dependecy.OnChange +=new OnChangeEventHandler(dependecy_OnChange);
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
            {
                adapter.Fill(myDataSet, "dbo.ACCOUNT, dbo.INVENTORYLOG, dbo.INVENTORYLOGDETAILS, dbo.INVENTORY, dbo.PRODUCT_DTL,dbo.COLOR,dbo.BRAND,dbo.CATEGORY,dbo.PRODUCTDESCRIPTION, dbo.SIZES");
                gridControl1.DataSource = myDataSet.Tables["dbo.ACCOUNT, dbo.INVENTORYLOG, dbo.INVENTORYLOGDETAILS, dbo.INVENTORY, dbo.PRODUCT_DTL,dbo.COLOR,dbo.BRAND,dbo.CATEGORY,dbo.PRODUCTDESCRIPTION, dbo.SIZES"];
                gridView1.BestFitColumns();
                gridView1.BestFitMaxRowCount = 10;
            }

        }

        delegate void UIDelegate();
        public void dependecy_OnChange(object sender, SqlNotificationEventArgs e)
        {
            try
            {
                UIDelegate uidel = new UIDelegate(RefreshDatas);
                this.Invoke(uidel, null);
                SqlDependency dependency = (SqlDependency)sender;
                dependency.OnChange -= dependecy_OnChange;
            }
            catch (Exception es)
            {
             //   XtraMessageBox.Show(es.Message);
            }
        }
        public void RefreshDatas()
        { 
            ////////////////////////
            //---------------------------------------   NOTIFICATION INSERTS HERE ----------------------------------- //////
            label1.Text = "Changes Happened";
            GetActualData();
        }

        /// <summary>
        /// //////////////////////////////////////////////////////////////////////////////////////////////////
        /// //--------------------------------------------------------  SQL NOTIFICATION ------------------------------------
        /// </summary>
        /// <param name="args"></param>


        public SqlConnection ConnectionStringMethod()
        {
            SqlConnection con = new SqlConnection(connection);
            con.Open();
            return con;
        }
        private void Inventory_InventoryLog_Load(object sender, EventArgs e)
        {
            GetActivateData();           
        }
Posted
Updated 7-Apr-13 18:56pm
v2
Comments
Vani Kulkarni 8-Apr-13 0:56am    
[EDIT]Formatted code. [/EDIT]
John Mark Olesco 8-Apr-13 11:11am    
hello Vani Kulkarni please help me with my problem..

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900