Store Procedure for all join in multi table

Create PROCEDURE  MultijoinTable
(
@Barcode varchar(30)
)
AS
BEGIN
   
     SELECT   IBD.Barcode,IG.Item_Name, IBD.Gross_Weight, IBD.Stone_Weight,
             IBD.Net_Weight, IBD.Pure_Weight, IBD.MRP,
IBD.Size,IM.Item_Quantity, CATG.Category_Name, ITEMTYPE.Item_Type_Name, IBD.Item_Image,
IBD.Barcode_ID, IBD.Item_ID,IBD.Created_On, IBD.Created_By,
IBD.Modified_On, IBD.Modified_By, IBD.Is_Active, IBD.Is_Deleted,
     IG.Category_ID
FROM  Inventory.ItemBarcodeDetails AS IBD
INNER JOIN Inventory.D_ItemGeneralInfo AS IG
ON IBD.Item_ID = IG.Item_ID
LEFT JOIN  INVENTORY.STOCKQCAPPROVE AS IM
ON IBD.Barcode = IM.Matrix_Detail_ID
INNER JOIN Inventory.D_ItemCategory AS CATG
ON IG.Category_ID = CATG.Category_ID INNER JOIN
Inventory.D_ItemType AS ITEMTYPE
ON IG.Item_Type_ID = ITEMTYPE.Item_Type_ID
WHERE IBD.Barcode=@Barcode
                       COLLATE  SQL_Latin1_General_CP1_CS_AS  //E line ra meaning hela ki je sql small a and capital a sabu buji pare means not keysenstive setihi pain e line lekhila pare ame database ku manekara pathei thiba aa001 jadi  ame front end ru e value napatheiba jadi AA001 patheba taha hele se null value return kariba

 

END

Data Show in TreeView

 I want to show the data in a tree view.but thjat treeview is on the text box.




This is the treeview.

 void filltrv()
        {
            try
            {
                trvCategory.Nodes.Clear();
                var n = (from y in ERPManagement.GetInstance.GetAllMenus()
                         where y.Parent_Menu_ID == 0
                         select y).ToList();
                for (i = 0; i < n.Count; i++)
                {
                    TreeNode tn = new TreeNode();
                    tn.Tag = n[i].Menu_ID.ToString();
                    tn.Text = n[i].Menu_Name.ToString();
                    trvCategory.Nodes.Add(tn);
                }
                for (i = 0; i < trvCategory.Nodes.Count; i++)
                {
                    var nn = (from y in ERPManagement.GetInstance.GetAllMenus()
                              where y.Parent_Menu_ID == Convert.ToInt32(trvCategory.Nodes[i].Tag.ToString())
                              select y).ToList();
                    if (nn.Count > 0)
                    {
                        for (int j = 0; j < nn.Count; j++)
                        {
                            TreeNode tn1 = new TreeNode();
                            tn1.Tag = nn[j].Menu_ID.ToString();
                            tn1.Text = nn[j].Menu_Name.ToString();
                            trvCategory.Nodes[i].Nodes.Add(tn1);
                        }

                    }
                }
            }
            catch (Exception ex)
            {
            }
        }

  private void TxtParentMenu_Enter(object sender, EventArgs e)
        {
            try
            {
                filltrv();
                trvCategory.Visible = true;
            }
            catch (Exception ex)
            {
            }
        }

private void trvCategory_AfterSelect(object sender, TreeViewEventArgs e)
        {
            try
            {
                int x=trvCategory.SelectedNode.Level;
                if (x == 0)
                {
                    TxtParentMenu.Text = trvCategory.SelectedNode.Text;
                    ParentMenuID = Convert.ToInt32(trvCategory.SelectedNode.Tag);
                }
                else
                {
                    TxtParentMenu.Text = trvCategory.SelectedNode.Parent.Text;
                    ParentMenuID = Convert.ToInt32(trvCategory.SelectedNode.Parent.Tag);
                }
               
               
                trvCategory.Visible = false;
                CmbSequence.Enabled = true;
                BindSequence();
            }
            catch (Exception ex)
            {
            }
        }
StoreProcedure
ALTER procedure [Inventory].[KC_SelectMenu1]
 as
 begin

  WITH Menu1(Menu_ID,Parent_Menu_Name)
     AS
     (SELECT Menu_ID,Menu_Name
      FROM Inventory.MST_Menu WHERE Is_Active=1 )
   
     SELECT a.Menu_ID,a.Menu_Name,a.Menu_Text,
a.Parent_Menu_ID,a.Sequence,a.Icon,a.Page_Name,a.Status,b.Parent_Menu_Name,
      Created_On,Created_By,
     Modified_On,Modified_By,Is_Active,Is_Deleted
     FROM Inventory.MST_Menu a
     LEFT OUTER JOIN Menu1 b
     ON a.Parent_Menu_ID=b.Menu_ID  WHERE Is_Active=1 AND Is_Deleted=0

 end
Database Design

Image insert Into Textbox and button Not Using File Upload Control

 Databasee re Field Datatype =Image
 string CurrentFileName;
private void BtnBrowse_Click(object sender, EventArgs e)
        {
            try
            {
                OpenFileDialog open = new OpenFileDialog();
                open.Filter = "Image Files(*.jpg; *.jpeg; *.gif; *.bmp)|*.jpg; *.jpeg; *.gif; *.bmp";

                if (open.ShowDialog() == DialogResult.OK)
                {
                    PictureBoxDesign.Image = new Bitmap(open.FileName);
                    TxtDesignImage.Text = open.FileName;
                    CurrentFileName = open.SafeFileName;
                }
            }
            catch (Exception)
            {
                throw new ApplicationException("Image loading error....");
            }
        }

   private void RemoveImage()
        {
            string GetImage = (System.Environment.CurrentDirectory);
            GetImage = GetImage.Substring(0, GetImage.Length - GetImage.StartsWith("bin").ToString().Length - 4);

            GetImage += "ItemImage\\" + DGVMenu.CurrentRow.Cells[4].Value.ToString();
            if (DGVMenu.CurrentRow.Cells[4].Value.ToString() != null)
            {
                System.IO.File.Delete(GetImage);
            }
        }

   private void btnSave_Click(object sender, EventArgs e)
        {
   if (checkData() == true)
                {
                    if (checkDuplicateSave() == true)
                    {
                        if (menuid == 0)
                        {
                            TAGGING_Menu objMenu = new TAGGING_Menu();
                            objMenu.Menu_Name = TxtMenu.Text.Trim();
                            objMenu.Menu_Text = TxtMenuText.Text.Trim();
                            objMenu.Parent_Menu_ID = ParentMenuID;
                            objMenu.Sequence = int.Parse(CmbSequence.SelectedItem.ToString());
                            objMenu.Page_Name = TxtPageName.Text.Trim();
                            //------------Code For Save The Image -------------------------
                            if (TxtDesignImage.Text != "")
                            {
                                if (CurrentFileName != "" || CurrentFileName != null)
                                {
                                    if (CurrentFileName != TxtDesignImage.Text)
                                    {
                                        String sBLOBFilePath = TxtDesignImage.Text;
                                        FileStream fsBLOBFile = new FileStream(sBLOBFilePath, FileMode.Open, FileAccess.Read);
                                        Byte[] bytBLOBData = new Byte[fsBLOBFile.Length];
                                        fsBLOBFile.Read(bytBLOBData, 0, bytBLOBData.Length);
                                        fsBLOBFile.Close();
                                        objMenu.Icon = bytBLOBData;
                                    }
                                }
                            }
                            //------------End of Code For Save The Image ------------------
                            if (ChkStatus.Checked)
                            {
                                objMenu.Status = true;
                            }
                            else
                            {
                                objMenu.Status = false;
                            }
                            objMenu.Created_By = 1;
                            ERPManagement.GetInstance.InsertMenu(objMenu);
                            MessageBox.Show("Data saved successfully.", "KenCloud", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            fillGrid();
                            Reset();
                        }
}

ALL POST

21 JANYARY  2012

1. All Sql Introduction

2. Multiple insert from gridview

3. File Upload Control In ASP.Net

4. Dispalying Pasword as Encrypted and Decrypted Format in GridView

5. Select Data From Database And Show in a Gridview using Generic

6.Two Texbox here one textbox tke only capital Letter another nothing Taken

7. Access Masterpage Control in Child Page And Create events

8. Convert Report In to Word in ASP.Net

9. Select Gridview and do edit or delete by the help of gridview_rowcommand eve

10. Simple Javascript Validation in Asp.net

11. SQL Syntax - SELECT,UPDATE and DELETE

12. SQL CAST and CONVERT

13. Show Data In a Gridview and Edit , Update & Delete using Modal Popup

14. (How to Set Tab Index)In Pressing tab key user go to next control

15. Email and Website Validation Example

16. Insert Data into Data Base Table using Class File(Properties) and Stored Procedure

17. Sql TRANSACTION (Two Table Here how we Insert Data With the help of Transction and commit and RollBack)

18. At a time insert Data in two table with the help of Transction and Class File Methods

19. insert Date Using spilt() Function and retrive Date in different Date,Month and Year Format

20. Date Validation in Asp.Net to Retrive Data from Database

 

7 FEBRUARY 2012

 

20. Transaction Example.[How user can pay money one account to another account]

21. All Validation Control in Asp.Net and message are show in Validation summery

22. What is WCF ? And Example.

23. Add Record to Database using Empty Template and Footer Template and in gridview serch data through Autocomplete Extender

24. Data show in a gridview and using scrollbar

25. To retrive image size from database and show in a gridview

 

1 March 2012

 

26. Dynamically Bind Tables Data from Database in Frontnd

 

4 May 2012

 

27. Subquery in Sql .Example highest Sallary.

28. Insert,Update,Delete and Add by the GridView Commnd Event

29. Insert,Update and Delete through the StoreProcedure

30. Example of Registration Page and validation and data show in a Griedview

 

9 June 2012

 

31. Inser,Update,Delete and view in same page or choose anothe page and the data will show in main page use Request query string

31. Insert,Update and Delete through the WCF.

32. Insert,Update and Delete through the WCF.

33. Auto Complete Extender using WCF in a Tetbox.

34. File upload in Database and File show in a GridView And Download the Word File

35. Some Interview Sql Query

36. How to retrive More than one Select Statement in a single Command.And Show in a GridView.Using Normal query and Storeprocedure.

37. in pressing the Enter key how the user go to Next Contol using JQuery.

38. Difference bettwen DBMS and RDBMS and about KEY

 

6 July 2012

 

40. How to Create a user control and how to use in aspx page.

41. Auto Generated Id in Sql

42. At a time multiple data inserted in a single field using Data Table

43. Transaction in ASPX page Example

44. How to find Parent page Dropdownlist in childpage and auto refresh

45. Textbox validation to accept only integer / Only integer with fixed length /only character or show in a validation summery

 

2 August 2012

 

46. Report Demo in Linq with Financial Year Checking

47. Storeprocedure with Table Example

48. How to set a Local Variable in a store procedure and hold that value in Aspx page

 

3 January 2013

 

49. Cristal Report In Dyanamically Method

50. Other City Add When user choose other in Combobox in WindoApplication

51. Treeview Data Retrive in Database and show in trieeview in window application

 

8 February 2013

 

52. Store Procedure for all join in multi table

53. Data Show in TreeView

54. Image insert Into Textbox and button Not Using File Upload Control

55. At a time check Multiple checkbox in a gridview in Window application

56. check one checkbox at a time in a gridview column in window application

57. How to Insert Multiple data at a time in to database through the datatable and using type in sql

58. TreeView Node Select and Disselect

 

 

 

 

 

 

 

 

 

 

 

At a time check Multiple checkbox in a gridview in Window application

 private void DGVFormula_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

            try
            {
                DataGridViewCheckBoxCell ch1 = new DataGridViewCheckBoxCell();
                ch1 = (DataGridViewCheckBoxCell)DGVFormula.Rows[DGVFormula.CurrentRow.Index].Cells[0];

                if (ch1.Value == null)
                    ch1.Value = false;
                switch (ch1.Value.ToString())
                {
                    case "True":
                        ch1.Value = false;
                        BtnDelete.Visible = false;
                        break;
                    case "False":
                            ch1.Value = true;
                            BtnDelete.Visible = true;
                            count++;
                            break;
                   
                }

            }
            catch (Exception ex)
            {
            }
}

check one checkbox at a time in a gridview column in window application

 private void DGVFormula_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (DGVFormula.Columns[e.ColumnIndex] is DataGridViewCheckBoxColumn)
            {
                bool state = Convert.ToBoolean(((DataGridViewCheckBoxCell)DGVFormula.Rows[e.RowIndex].Cells[e.ColumnIndex]).EditedFormattedValue);
                int intDemarcationID = Convert.ToInt32(((DataGridViewCheckBoxCell)DGVFormula.Rows[e.RowIndex].Cells[0]).EditedFormattedValue);

                if (state)
                {
                    BtnDelete.Visible = true;
                    foreach (DataGridViewRow dgvrow in DGVFormula.Rows)
                    {
                        if (Convert.ToInt32(dgvrow.Cells[0].Value) == intDemarcationID)
                        {
                            ((DataGridViewCheckBoxCell)dgvrow.Cells[e.ColumnIndex]).Value = false;

                        }

                    }
                }
                else
                {
                    BtnDelete.Visible = false;
                }
            }
}

How to Insert Multiple data at a time in to database through the datatable and using type in sql

Page Design


Store Procedure For Multipledata (At a time 2 ta table re data insert heba gote )
ALTER PROCEDURE   [linku].[Satyabrata]
(
@Formula_Name VARCHAR(50)=NULL,
@Parameter  Inventory.D_Formula READONLY
)
AS
 BEGIN

INSERT INTO  Inventory.D_ItemFormula
(Formula_Name ,Created_On
 )

VALUES(@Formula_Name,  GETDATE() )

DECLARE @Out INT
SET @Out=SCOPE_IDENTITY()

INSERT INTO Inventory.D_ItemFormulaDetails
(Formula_ID,Field_ID,Type)

     SELECT @Out,
Field_ID,
Type
FROM @Parameter
 END
====================
How to declare a Type IN Sql

 CREATE  TYPE Inventory.D_Formula AS
 TABLE
 (
 Field_ID INT NULL,
Type VarChar(50)=NULL
 )
 GO

=========================
In object Layer we can just Add This

   public DataTable DataFormula
        {
            get;
            set;
        }

===============================================
 DataTable dtformula;

  void CreateDatatable()
        {
            dtformula = new DataTable();
            dtformula.Columns.Add("FieldID", typeof(int));
            dtformula.Columns.Add("Type", typeof(string));
        }

        private void BtnSave_Click(object sender, EventArgs e)

        {

            if (Checkdata()==true)

            {

                SaveFormula();

            }

        }

        void SaveFormula()

        {

            Satya objformula = new Satya();

            objformula.FormulaName = TxtFormulaName.Text.Trim();

            objformula.DataFormula = dtformula;

            ERPManagement.GetInstance.InsertFormula(objformula);

            MessageBox.Show("Data Saved Successfully.", "Kencloud", MessageBoxButtons.OK, MessageBoxIcon.Information);

            Reset();

        }

TreeView Node Select and Disselect

  private void btnUncheckAll_Click(object sender, EventArgs e)
        {
            try
            {
            string status = btnUncheckAll.Text.Trim();
            if (status == "Uncheck All")
            {
                ResetPrevileges();
                trvPrevileges.CollapseAll();
                btnUncheckAll.Text = "Check All";
            }
            else
            {
                ResetPrevileges();
               
                foreach (TreeNode ptn in trvPrevileges.Nodes)
                {
                    ptn.Checked = true;
                  
                }
                trvPrevileges.ExpandAll();
                btnUncheckAll.Text = "Uncheck All";
            }
            }
            catch (Exception ex)
            {

            }
        }

  private void ResetPrevileges()
        {
            foreach (TreeNode ptn in trvPrevileges.Nodes)
            {
                ptn.Checked = false;
                foreach (TreeNode subnodes in ptn.Nodes)
                {
                    subnodes.Checked = false;
                }
            }
        }

Deesign