Easy Form Design at Run Time C# Windows Forms

 
My previous article (Windows Form Design at Run Time) explained how to design a form at runtime. I have extended the program to the next advanced version with more functionality. This is one of my favorite articles that I have written. I hope you all like this. Now let's see what's new in my Easy Form Design @ Run Time.

Limitations of my previous article

In my previous article it had the limitation that the user must design and add code for the events like binding the grid and so on. The user can load only one form at a time. Adding a parameter to a SQL query is not possible.

New features in Easy Form Design at Run Time

This has all my previous article features but with more functionality like:

  1. A new program has 2 parts, the 1st is the Form View part where the user can view all the dynamically created forms by selecting Menu from the tree view.
  2. The 2nd Form Design part is to Add New/Edit/Save and Open a form dynamically.
  3. The user does not need to write any code.
  4. Easy form design.
  5. The New Feature has options to select a Stored Procedure for their form.
  6. The user can select a parameter and select a dynamic TextBox control for a parameter that needs to be used in a SQL Server Stored Procedure.
  7. Save and open the form.

The Easy Form Design at Run Time software application was developed to design your own form using a panel control to add Labels, Buttons, TextBoxes, DataGridViews and so on. The user can select the Stored Procedure to be used for the form for example. Now the user can design there and simply search a form with a TextBox, Button and a DataGridView. For this the user must pass the TextBox as a search parameter to the Stored Procedure and the result must be finally bound to a DataGridView by clicking on the Button. In the form design screen the user can add a TextBox, Label, Button and DataGridView and from the Tools the user can click on the SQL Setting Tool Menu. The user can select his Search Stored Procedure and assign the controls to the parameter matching and finally save the form.

In the main form now the user can view his saved Form Name in the Tree view when he clicks on the menu. His saved form will be displayed. When the user clicks on the button he can view all the details to be loaded from the database using the Stored Procedure that he or she has assigned to the form. This simple C# application allows the user to add:

  1. Create a new form
  2. Save a form as a XML file.
  3. Open a form from a XML file.
  4. Cut, copy and paste all controls.
  5. Delete all controls.
  6. Delete a selected control.
  7. Add/Change the background color for the form (here a Panel is used as a form).
  8. Controls can be brought to the front and sent to the back.
  9. Add a Label Control and using the property window design the Label.
  10. Add a Button Control and using the property window design the Button.
  11. Add a CheckBox Control and using the property window design the CheckBox.
  12. Add a Button Control and using the property window design the Button.
  13. Add a ComboBox Control and using the property window design the ComboBox.
  14. Add a DataGridView Control and using the property window design the DataGridView.
  15. Add a DataTimePicker Control and using the property window design the DataTimePicker.
  16. Add a Panel Control and using the property window design the ListBox.
  17. Add a PictureBox Control and using the property window design the PictureBox.
  18. Add a RadioButton Control and using the property window design the RadioButton.
  19. Add a TextBox Control and using the property window design the TextBox.
  20. SQL Setting This is a new feature in this application. Now the user need not write any code here. You can write your Stored Procedure for your database and using the SQL Setting tools menu, you can select your Stored Procedure for your form. Assign the controls property as a parameter to the Stored Procedure.
  21. Bind/Insert/Edit and Delete Data from database using Stored Procedure.

New Easy Form Design has the following 5 parts to design your from at runtime.

  1. Form View part
  2. Form Design part
  3. SQL Stored Procedure Setting for your Form
  4. Save Form
  5. open Existing Form to edit.
Now let's see one by one in detail here.
Section 1st Form View part: In the following image I have pointed to each part by numbers. 
 
 

  1. When the user clicks on the “New Form Design” Button, the form will be opened where the user can add, edit, open and save forms. We can see more details in the Form Design part.
  2. In a Tree View I will list all the saved form names. Whenever users create a new form and save, the saved filename will be listed in the Treeview. When the user clicks on the Treeview Node the related form will be loaded into the right side.
  3. Here for example we can see that, now the user has clicked on “NewItem” from the Treeview and the related form has been loaded. For this form I have selected the ItemInsert Stored Procedure and passed all the TextBox controls as parameters to the SP. 

The following  is an animated image that explains it in more detail.

 

Section 2nd Form Design part

In this application we can see the Toolbar at the left to add controls to the form (here our Panel). The center has a Form (Panel) where the user can add and design their controls. In the right is the Property window to add all the design to the selected controls.

Toolbar: Here we can see all the list of controls that can be added to the form at runtime. There are other features, like Create New Form, Save Form, Open Form, Cut, Copy and Paste Controls. Finally the control you can see is the SQL Setting that is a very important tool to set our form controls with a Stored Procedure to do our CRUD Operations.

 

When the user clicks on the “New Form Design” button the form design will be opened. You can refer to the preceding image to see how the form design looks. This form plays the main role of creating a dynamic form at runtime. Here users can add New, Edit, Open and Save forms. In the preceding image I have pointed at each part by a number.

  1. In the left side we have the Tool Menu where the user can add a TextBox, Label, Button, Panel, DataGridView and so on at runtime to design the form.
  2. The user can design their form here at runtime. The user can drag and drop controls and can resize controls.
  3. Using the property grid users can change the property of each selected control such as BackColor, FontColor, Text and so on.
  4. This is to select a Stored Procedure for a form to do CRUD operations and set the parameter of the SP with controls. We will see the details of this part in Section 3rdpart.

Easy Form Design at Run Time will allow users to design a form at runtime, open and save a form and reuse an existing form. The user can add controls at runtime from the Toolbar, design there form, select the Stored Procedure, match and add parameters with controls to perform some action like Select, Insert, Update and Delete.

Now for example the user can add a DataGridView and a Button Control at runtime with a TextBox for searching the user by UserCode and UserName.


Section 3rd SQL Stored Procedure Setting for your Form

After we have designed our form, now it's time to assign our Stored Procedure to our form and pass the control parameters to the SP to do CRUD Operations.

 
To set the Stored Procedure and assign parameters to the SP, click the SQL Settings menu from the Tool Bar.
 
When the user clicks on the SQL Setting a new form will be opened as in the following. In the following image I have marked with numbers and let's see each use one by one.
 

  1. In the Select Procedure Combo Box I will display all the Stored Procedure names from our database. Note that in our application in the 1Bin folder we can find the “DBConnection.txt” text file that has the default Database Connection String. The user can change the default Connection String depending on the database server name, database name, SQL User id and PWD.
  2. Once the Stored Procedure for our form is selected, click the Parameter Button. Here we can see I have selected the “USP_USER_SELECT” that will be used in our form to search and bind the user details.
  3. When we click on the parameter I will display all the parameters for the selected Stored Procedure. Note that everything will be dynamic so the user does not need to add any parameter here. I will list all the parameters that are declared in the Stored Procedure. If the user needs to add or delete any parameter then they can update their Stored Procedure and redesign the form here.
  4. Select a SQL parameter one by one and click the down arrow button. When the user clicks on the down arrow, the SQL parameter will be displayed in the parameter TextBox.
  5. In the Control Name List, I will display all the Text Box control names that needs to be assigned to each SQL parameter.
  6. The same here, select the appropriate control for the SQL parameter and click the 6th button down. The Control Name will be added to the Control TextBox.
  7. Once the SQL parameter and control has been selected, click the Add Button one by one to add the final SQL parameter list that needs to be saved for our form.
  8. All the SQL Parameters for the selected Stored Procedure will be added here.
  9. Confirm the list and click OK to save the form.

Here is the sample GIF image that has an example for the user search bind.

 

Section 4th Save Form

 

In the Toolbar the user has the option to save the modified form to be viewed from the main screen. You can refer to the following image. When the users click on the Save Toolbar button a list of Main Menu names will be displayed, here all the previously saved file names (I will be using the file name as the Menu name to be displayed in the main screen Treeview). If users want to save the form as a submenu to an existing menu then the user can select the main menu name and enter the new form name to save. If the user wants the form to be a main menu then he can select “Select” from the ComboBox and enter the new form name. Once the form is saved, the menu will be added with the new form name on the main screen.

Note: I will save the form as a XML file. In the application root bin folder you can see the 2 folders “XMLFILE” and “XMLForms”. In the “XMLFILE” folder you can see “NewFormNameList.XML” in this file; I will store all the file names that will be used to list in the main menu TreeView.

 
In the “XMLForms” folder, for each form you can see 2 XML files. One XML file will be used to store the form control details with all the properties and the other XML file will store each form's Stored Procedure names with parameter details.
Here you can see a sample ItemAdd XML file that has all the control's information with properties like Location, Font Size, FontColor, BackColor, Image and so on.

 

ItemAdd_query.XML

The ItemAdd_query.XML file has all the details of a form Stored Procedure name with parameters and Control Names.

When a user enters the existing form name to save, then I will overwrite the existing form with the new saved version. I will be saving the forms as XML files.
 
 

5th open Existing Form to edit

In the Toolbar the user has an option to open an existing form for modification. You can refer to the following image. When the users click on the Open Toolbar button a list of menu names (which is our form name) will be displayed. The user can select their form name from the list that needs to be opened for modification. If the user knows the form name then they can directly enter the form name in the TextBox and click Open to modify the existing form.

 
Code Part

The article is an extension of my previous article Windows Form Design at Run Time. Usually I reuse the same code part with a few more new modifications. The form design part is the same as in the previous article. Refer to my previous article about adding controls at runtime and performing drag, drop, resize and delete of controls and so on from my previous article.

In this code part let's see how to do it.

Assign Stored Procedure to form and Add Parameter

In the form design Tool Bar Button Click event I will open the new form to perform the SP Settings.

Form Design SQL Setting Tool Bar Click Event

In the SQL Setting Button Click I will get all the TextBox Control names from the design form and add all the control names to the list and pass the control names list.

  1. private void toolStripButton1_Click_1(object sender, EventArgs e)   
  2. {  
  3.     ControlNames.Clear();  
  4.     if (pnControls.Controls.Count > 0)   
  5.     {  
  6.         foreach(TextBox tb in pnControls.Controls.OfType < TextBox > ())   
  7.         {  
  8.             ControlNames.Add(tb.Name.ToString());  
  9.         }  
  10.   
  11.         FrmParameterSetting obj = new FrmParameterSetting(ControlNames);  
  12.         //  obj.ShowDialog();    
  13.         if (obj.ShowDialog() == DialogResult.OK)   
  14.         {  
  15.   
  16.         }  
  17.     }  
  18. }  

FormParameterSetting Form Load event

In FrmParameterSetting Form Load I will get the entire Stored Procedure name and bind to the ComboBox. Bind all the Control Names list to the List view to assign the parameter.
  1. private void FrmParameterSetting_Load(object sender, EventArgs e)   
  2. {  
  3.     LoadSPCopmbo();  
  4.     listView2.Items.Clear();  
  5.     foreach(string prime in ControlNames) // Loop through List with foreach.      
  6.     {  
  7.         ListViewItem lvi = new ListViewItem(prime);  
  8.         this.listView2.Items.Add(lvi);  
  9.     }  
  10. }  
In the LoadSPCopmbo function I will get all the SP Names using the following query. Here I pass the query to the returnDataDatable function.
  1. public void LoadSPCopmbo()   
  2. {  
  3.     comboBox1.AutoCompleteMode = AutoCompleteMode.SuggestAppend;  
  4.     comboBox1.AutoCompleteSource = AutoCompleteSource.ListItems;  
  5.     String Query = "SELECT name  FROM dbo.sysobjects WHERE (type = 'P') ORDER BY  name";  
  6.     DataTable dt = returnDataDatable(Query);  
  7.     comboBox1.ValueMember = "name";  
  8.     comboBox1.DisplayMember = "name";  
  9.     comboBox1.DataSource = dt;  
  10.   
  11. }  

InreturnDataDatable” I will read the Connection String from the text file “DBConnection.ext”. As I explained before in this article, the Connection String will be stored as a text file in the application's root folder.

 

  1. public DataTable returnDataDatable(String Query)   
  2. {  
  3.     String ConnectionString = ReadConnectionString();  
  4.     DataTable dt = new DataTable();  
  5.     SqlConnection con = new SqlConnection(ConnectionString);  
  6.   
  7.     SqlCommand cmd = new SqlCommand(Query, con);  
  8.     cmd.CommandType = CommandType.Text;  
  9.     SqlDataAdapter sda = new SqlDataAdapter(cmd);  
  10.   
  11.     sda.Fill(dt);  
  12.     return dt;  
  13. }  

I will check for the text file. If the file exists in the folder then I will read the connection string from the file. If it does not exist then I will write the default Connection String. So kindly check for the “DBConnection.txt” in the root folder and change the Connection String depending on your system database setting.

  1. <>private String ReadConnectionString()   
  2. <>{  
  3. <>    string path = Application.StartupPath + @  
  4. <>    "\DBConnection.txt";  
  5. <>    String connectionString = "";  
  6. <>    if (!File.Exists(path))   
  7. <>    {  
  8. <>        using(StreamWriter tw = File.CreateText(path))   
  9. <>        {  
  10. <>            tw.WriteLine("Data Source=YOURDBServerName;Initial Catalog=YOURDBNAME;User id = YOURUSERNAME;password=YOURPASSWORD");  
  11. <>            tw.Close();  
  12. <>        }  
  13. <>  
  14. <>    }   
  15. <>    else   
  16. <>    {  
  17. <>        TextReader tr = new StreamReader(path);  
  18. <>        connectionString = tr.ReadLine();  
  19. <>        tr.Close();  
  20. <>    }  
  21. <>    return connectionString;  
  22. <>  
  23. <>}  

When the user clicks on the Parameter Buttons, I bind the list of parameters related to the Stored Procedure that has been selected. In the code part you can see I have used the query to get all the parameters for the selected Stored Procedure and bind the final result to the List.

FormParameterSetting Parameter Button Click event 

  1. private void button2_Click(object sender, EventArgs e)   
  2. {  
  3.     listView1.Items.Clear();  
  4.     String Query = "SELECT p.name AS Name, t.name AS Type, p.max_length AS Length FROM sys.parameters AS p JOIN sys.types AS t ON t.user_type_id = p.user_type_id WHERE object_id = OBJECT_ID('" + txtSPNAME.Text.Trim().ToString() + "')";  
  5.     DataTable dt = returnDataDatable(Query);  
  6.     foreach(DataRow dr in dt.Rows)   
  7.     {  
  8.         ListViewItem lvi = new ListViewItem(dr["Name"].ToString());  
  9.   
  10.         lvi.SubItems.Add(dr["Type"].ToString());  
  11.         lvi.SubItems.Add(dr["Length"].ToString());  
  12.   
  13.         this.listView1.Items.Add(lvi);  
  14.     }  
  15. }  

FormParameterSetting Ok Button Click

Here I will get all the final Stored Procedure names, parameter list and controls for the parameter list and bound the result to the public list class to use in our form design to save the file. 

  1. private void button5_Click(object sender, EventArgs e)   
  2. {  
  3.     for (int i = 0; i < listView3.Items.Count; i++)   
  4.     {  
  5.         ShanuEasyFormDesign.Class.ControlList obj1 = new ShanuEasyFormDesign.Class.ControlList(txtSPNAME.Text.Trim(), listView3.Items[i].SubItems[0].Text.ToString(), listView3.Items[i].SubItems[1].Text.ToString());  
  6.   
  7.         ShanuEasyFormDesign.Class.ControlList.objDGVBind.Add(obj1);  
  8.     }  
  9. }  
Form Design Save
 
In the save button click I will get all the control details with Property and save the form as XML file.
  1. // To Save as XML FIle    
  2. private void toolSaves_Click(object sender, EventArgs e)    
  3. {    
  4.     if (pnControls.Controls.Count > 0)    
  5.     {                          
  6.          frmSave obj = new frmSave();    
  7.        //  obj.ShowDialog();    
  8.          if (obj.ShowDialog() == DialogResult.OK)    
  9.         {    
  10.             if (obj.SaveFileName != "")    
  11.             {    
  12.                 Name_Query = Application.StartupPath + @"\XMLForms\" + obj.SaveFileName + "_Query.XML";    
  13.                 if(!File.Exists(NewFileName_Query))    
  14.                 {    
  15.                 if (ShanuEasyFormDesign.Class.ControlList.objDGVBind.Count <= 0)    
  16.                 {    
  17.                     MessageBox.Show("procedure and parameter need to be set before save");    
  18.                 }    
  19.                 }    
  20.                 SavetoXML(obj.SaveFileName);    
  21.             }    
  22.             
  23.          }    
  24.     }    
  25. }    

Form Open

To open the existing form I will load all the form controls from the selected XML file. 

  1. // to Open XML file as Form    
  2. private void toolOpens_Click(object sender, EventArgs e)    
  3. {    
  4.    frmOpen obj = new frmOpen();    
  5.    //  obj.ShowDialog();    
  6.    if (obj.ShowDialog() == DialogResult.OK)    
  7.    {    
  8.        if (obj.OpenFileName != "")    
  9.        {    
  10.           xmlFileName = Application.StartupPath + @"\XMLForms\" + obj.OpenFileName + ".XML";    
  11.           xmlFileName_Query = Application.StartupPath + @"\XMLForms\" + obj.OpenFileName + "_Query.XML";     
  12.           pnControls.Controls.Clear();    
  13.           loadXMLFILE();    
  14.        }    
  15.    }    
  16.                        
  17. }   

Main Form CRUD operations

In the main form we can see the dynamic CRUD operations will be done for each form. Here is the code that I used to do it dynamically.

TreeView Node Click

When the user clicks on the File Name from the TreeView I will read the appropriate saved XML file and add all the controls to the panel as a form. 

  1. private void treeMenu_AfterSelect(object sender, TreeViewEventArgs e)    
  2. {    
  3.     // MessageBox.Show(treeMenu.SelectedNode.Text);    
  4.   
  5.     try    
  6.     {    
  7.         fileName = Application.StartupPath + @"\XMLForms\" + treeMenu.SelectedNode.Text + ".XML";    
  8.         xmlFileName_Query = Application.StartupPath + @"\XMLForms\" + treeMenu.SelectedNode.Text + "_Query.XML";    
  9.         pnlMain.Controls.Clear();    
  10.         loadXMLFILE();    
  11.         // MessageBox.Show();    
  12.     }    
  13.     catch (Exception ex)    
  14.     {    
  15.     }    
  16. }  
FromXml File
 
I will check for the Button Control and create a dynamic Click event for the Button Control as in the following.
  1. case "System.Windows.Forms.Button":    
  2. {    
  3.    System.Drawing.Color myBackColor = new System.Drawing.Color();    
  4.    myBackColor = System.Drawing.ColorTranslator.FromHtml(gParam[8]);    
  5.    Button ctrl = new Button();    
  6.    //ctrl.Image = global::DragObject.Properties.Resources.Sunset;    
  7.    ctrl.BackColor = myBackColor;    
  8.    ctrl.Name = gParam[10];    
  9.    ctrl.Location = new Point(System.Convert.ToInt32(gParam[1]), System.Convert.ToInt32(gParam[2]));    
  10.    ctrl.Text = gParam[5];    
  11.    ctrl.Size = new System.Drawing.Size(System.Convert.ToInt32(gParam[3]), System.Convert.ToInt32(gParam[4]));    
  12.    if (gParam[11] == "Front")    
  13.    {    
  14.       ctrl.BringToFront();    
  15.    }    
  16.    else    
  17.    {    
  18.       ctrl.SendToBack();    
  19.    }    
  20.    ctrl.Click += new EventHandler(control_Click);    
  21.    pnlMain.Controls.Add(ctrl);    
  22. }   
In the Button Click event I will read the Stored Procedure details from the XML file and bind the final result to the DataGridView. All CRUD operations will be managed in this Click Event.
  1. private void control_Click(object sender, EventArgs e)      
  2. {      
  3.     try      
  4.     {      
  5.         if (xmlFileName_Query != "")      
  6.         {      
  7.             XmlDocument xmlQuery = new XmlDocument();      
  8.             xmlQuery.Load(xmlFileName_Query);      
  9.             XmlNode xnQueryList = xmlQuery.SelectSingleNode("ShanuQuerySave");      
  10.             int i = 0;      
  11.            //XmlNode xnListQuery = xnList.SelectSingleNode("SQLQuery");      
  12.                     String txtBox1Name = "";      
  13.                     String ParameterName1 = "";      
  14.                     String ControltoBindName = "";      
  15.                     String Query = "";      
  16.       
  17.                     foreach (XmlNode xn in xnQueryList)      
  18.                     {      
  19.                         Query = xn["ProcedureName"].InnerText;      
  20.                     }      
  21.       
  22.                     DataTable dt = new DataTable();      
  23.                     String ConnectionString = ReadConnectionString();      
  24.                     SqlConnection con = new SqlConnection(ConnectionString);      
  25.                     SqlCommand cmd = new SqlCommand(Query, con);      
  26.                     cmd.CommandType = CommandType.StoredProcedure;      
  27.       
  28.                     foreach (XmlNode xn in xnQueryList)      
  29.                     {      
  30.                         txtBox1Name = xn["cntrlName"].InnerText;      
  31.                         ParameterName1 = xn["ParameterName"].InnerText;      
  32.                         ControltoBindName = xn["cntrltoBind"].InnerText;      
  33.                         Query = xn["ProcedureName"].InnerText;      
  34.                         if (txtBox1Name != "")      
  35.                         {      
  36.                             Control control = returnTextBox(txtBox1Name);      
  37.                             if (control is TextBox)      
  38.                             {      
  39.                                 txtBox1Name = control.Text.Trim().ToString();      
  40.                             }      
  41.                         }      
  42.                         cmd.Parameters.AddWithValue(ParameterName1, txtBox1Name);      
  43.                     }      
  44.       
  45.                     SqlDataAdapter sda = new SqlDataAdapter(cmd);      
  46.                     sda.Fill(dt);      
  47.                     if (dt.Rows.Count > 0)      
  48.                     {      
  49.                         if (dt.Columns[0].ColumnName == "Result")      
  50.                         {      
  51.                             MessageBox.Show("Record :" + dt.Rows[0].ItemArray[0].ToString());      
  52.                             return;      
  53.                         }      
  54.                     }      
  55.       
  56.                     if (ControltoBindName != "")      
  57.                     {      
  58.                        foreach (Control pnlCntl in pnlMain.Controls)      
  59.                        {      
  60.                           if (pnlCntl is DataGridView)      
  61.                           {      
  62.                               if (pnlCntl.Name == ControltoBindName)      
  63.                               {      
  64.                                  DataGridView grid = (DataGridView)pnlCntl;      
  65.                                  grid.DataSource = dt;      
  66.                               }      
  67.                           }      
  68.                        }      
  69.                   }      
  70.             }      
  71.       }      
  72.       catch (Exception ex)      
  73.       {      
  74.           MessageBox.Show(ex.Message.ToString());      
  75.       }      
  76. }   

Conclusion

 

Note: Things to do before running my application.

Connection String: Before running the application kindly update the Connection String from the application root folder to your local SQL Server database.

I have used SQL Server 2008 R2 and Visual Studio 2010.

 
Stored Procedures: In the attached Zip file you can find the folder name “SQLScripts” that has all the tables, databases and Stored Procedure Creation script files. Run all the script files one by one to your SQL Server Database.
 
 
Run the program

Output

Item Add: I have used the following Sp to insert the Item details.

Note: after an insert I will return the message “Inserted” for confirmation.

  1. -- Author      : Shanu                                                                    
  2. -- Create date : 2015-02-05                                                                    
  3. -- Description : To Insert Item Master                                                  
  4. -- Tables used :  ItemMasters                                                                   
  5. -- Modifier    : Shanu                                                                    
  6. -- Modify date : 2015-02-05                                                                    
  7. -- =============================================                                                                    
  8. -- exec USP_Item_Insert '',''        
  9. -- =============================================                                                               
  10. ALTER PROCEDURE [dbo].[USP_Item_Insert]                                                  
  11.    (          
  12.      @Item_Code          VARCHAR(50)     = '',                                
  13.      @Item_Name     VARCHAR(50)     = '',    
  14.      @Price      INT=0 ,    
  15.      @TAX1      INT=0 ,    
  16.      @Discount     INT=0 ,    
  17.      @Description     VARCHAR(50)     = '',    
  18.      @USR_Name     VARCHAR(50)     = ''         
  19.       )                                                            
  20. AS                                                                    
  21. BEGIN                    
  22.   
  23.    IF NOT EXISTS (SELECT * FROM ItemMasters WHERE Item_Code=@Item_Code and Item_Name=@Item_Name)    
  24.   BEGIN    
  25.     
  26.    INSERT INTO [ItemMasters]       
  27.       ([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]    
  28.         ,[IN_USR_ID],[UP_DATE],[UP_USR_ID])    
  29.    VALUES    
  30.         (@Item_Code,@Item_Name,@Price,@TAX1,@Discount,@Description,GETDATE(),@USR_Name    
  31.         ,GETDATE(),@USR_Name)    
  32.       select 'Inserted' as 'Result'  
  33.    END  
  34.    
  35. END   

For a better understanding I have added the image as an animated GIF file.

 

Item Edit: I have used the following Sp to update the Item details.

Note after update I will return the message as “updated” for confirmation.

  1. -- Author      : Shanu                                                                    
  2. -- Create date : 2015-02-05                                                                    
  3. -- Description : To Update Item Master                                                  
  4. -- Tables used :  ItemMasters                                                                   
  5. -- Modifier    : Shanu                                                                    
  6. -- Modify date : 2015-02-05                                                                    
  7. -- =============================================                                                                    
  8. -- exec USP_Item_Update '',''        
  9. -- =============================================                                                               
  10. ALTER PROCEDURE [dbo].[USP_Item_Update]                                                  
  11.    (          
  12.      @Item_Code          VARCHAR(50)     = '',                                
  13.      @Item_Name     VARCHAR(50)     = '',    
  14.      @Price      INT=0 ,    
  15.      @TAX1      INT=0 ,    
  16.      @Discount     INT=0 ,    
  17.      @Description     VARCHAR(50)     = '',    
  18.      @USR_Name     VARCHAR(50)     = ''         
  19.       )                                                            
  20. AS                                                                    
  21. BEGIN                    
  22.   
  23.    UPDATE [ItemMasters]     
  24.      SET [Item_Name]=@Item_Name,    
  25.       [Price]=@Price,    
  26.       [TAX1]=@TAX1,    
  27.       [Discount]=@Discount,    
  28.       [Description]=@Description,    
  29.       [UP_DATE]=GETDATE(),    
  30.       [UP_USR_ID]=@USR_Name    
  31.      WHERE    
  32.       Item_Code=@Item_Code    
  33. select 'Updated' as 'Result'  
  34. END  

For a better understanding I have added the image as an animated GIF file.