Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL
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 Smile | :) I'll post my codes here This is the Stored Procedures i am using
    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
 
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 7-Apr-13 19:49pm
Edited 7-Apr-13 19:56pm
(no name)16.6K
v2
Comments
Vani Kulkarni at 8-Apr-13 0:56am
   
[EDIT]Formatted code. [/EDIT]
John Mark Olesco at 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)

  Print Answers RSS
0 OriginalGriff 277
1 Maciej Los 230
2 BillWoodruff 205
3 Sergey Alexandrovich Kryukov 195
4 DamithSL 165
0 OriginalGriff 5,130
1 DamithSL 4,157
2 Maciej Los 3,650
3 Kornfeld Eliyahu Peter 3,460
4 Sergey Alexandrovich Kryukov 2,811


Advertise | Privacy | Mobile
Web04 | 2.8.141216.1 | Last Updated 8 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100