Add Controls Dynamically WinForms WindowsFroms C# VB.NET

Add controls dynamically In winforms Or windows froms Using C# And VB.NET

In this post i'm going to explain how to add controls dynamically in winforms and windows forms using C# and VB.NET.

For this i have used northwind database and Employees table to populate combobox and dataGridView.

First of all place two buttons on the form to add combobox and datagridview on button click and write below mentioned code in click event on each button respectively.

C# Code

01private void btnDropDown_Click(object sender, EventArgs e)

02        {

03            int x = 13, y = 70;

04            ComboBox cmbDynamic = new ComboBox();

05            cmbDynamic.Location = new System.Drawing.Point(x, y);

06            cmbDynamic.Name = "cmbDyn";

07            cmbDynamic.DisplayMember = "FirstName";

08            cmbDynamic.ValueMember = "EmployeeID";

09            cmbDynamic.DataSource = employeesBindingSource;

10            Controls.Add(cmbDynamic);

11 

12 

13        }

Here X and Y co-ordinates are used to define at which location the control needs to be placed.

01private void btnDataGrid_Click(object sender, EventArgs e)

02        {

03            int x = 13, y = 100;

04            DataGridView gvDynamic = new DataGridView();

05            gvDynamic.Location = new System.Drawing.Point(x, y);

06            gvDynamic.Name = "gvDyn";

07            gvDynamic.Width = 250;

08            gvDynamic.Height = 260;

09            gvDynamic.DataSource = employeesBindingSource;

10            Controls.Add(gvDynamic);

11        }

VB.NET Code

01Private Sub btnDropDown_Click(sender As Object, e As EventArgs)

02 Dim x As Integer = 13, y As Integer = 70

03 Dim cmbDynamic As New ComboBox()

04 cmbDynamic.Location = New System.Drawing.Point(x, y)

05 cmbDynamic.Name = "cmbDyn"

06 cmbDynamic.DisplayMember = "FirstName"

07 cmbDynamic.ValueMember = "EmployeeID"

08 cmbDynamic.DataSource = employeesBindingSource

09 Controls.Add(cmbDynamic)

10 

11 

12End Sub

01Private Sub btnDataGrid_Click(sender As Object, e As EventArgs)

02 Dim x As Integer = 13, y As Integer = 100

03 Dim gvDynamic As New DataGridView()

04 gvDynamic.Location = New System.Drawing.Point(x, y)

05 gvDynamic.Name = "gvDyn"

06 gvDynamic.Width = 250

07 gvDynamic.Height = 260

08 gvDynamic.DataSource = employeesBindingSource

09 Controls.Add(gvDynamic)

10End Sub

Read More

GridView XMLDataSource Example

GridView XMLDataSource Example

In this post i'm going to explain how to use XML file or XML data as XMLDataSource to populate GridView in Asp.Net 2.0,3.5,4.0.

For this example i have created a simple xml file and added it in App_Data Folder of Asp.Net application.


The data in XML file look like shown below.

01<!--?xml version="1.0" encoding="utf-8" ?-->

02<employees>

03  <details>

04  <firstname>Amit</firstname>

05  <lastname>Jain</lastname>

06  <location>Mumbai</location>

07  </details>

08  <details>

09    <firstname>user</firstname>

10    <lastname>1</lastname>

11    <location>Delhi</location>

12  </details>

13  <details>

14    <firstname>User</firstname>

15    <lastname>2</lastname>

16    <location>Noida</location>

17  </details>

18  <details>

19    <firstname>User</firstname>

20    <lastname>3</lastname>

21    <location>Bangalore</location>

22  </details>

23</employees>

First of All Add a GridView on aspx page and click on smart tag in design view of page and select new data source.
Browse to xml file path and click on ok.

When we click on ok we get error as displayed in the image.

The data source for GridView with id 'GridView1' did not have any properties or attributes from which to generate columns. Ensure that your data source has content.

This error is caused because the XML data is not in the format gridview can read.

GridView needs XML data in below mentioned format.

1<employees>

2  <employee firstname="Amit" lastname="Jain" location="Mumbai">

3  <employee firstname="User" lastname="2" location="Delhi">

4</employee></employee></employees>

To fix this error we need to provide XSLT Schema. Right click on solution explorer and select add new item, from new dialog box that opens, select XSLT file.

Now we need to provide XML template in this XSLT file which should look like mentioned below.

01<!--?xml version="1.0" encoding="utf-8"?-->

02<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">

03    <xsl:output method="xml" indent="yes">

04 

05    <xsl:template match="/">

06      <details>

07 

08            <xsl:apply-templates select="//Details">

09      </xsl:apply-templates></details>

10 

11    </xsl:template>

12  <xsl:template match="//Details">

13    <details>

14      <xsl:attribute name="FirstName">

15        <xsl:value-of select="FirstName">

16      </xsl:value-of></xsl:attribute>

17      <xsl:attribute name="LastName">

18        <xsl:value-of select="LastName">

19      </xsl:value-of></xsl:attribute>

20      <xsl:attribute name="Location">

21        <xsl:value-of select="Location">

22      </xsl:value-of></xsl:attribute>

23    </details>

24  </xsl:template>

25</xsl:output></xsl:stylesheet>

HTML Source of GridView

<asp:GridView ID="GridView1" runat="server" 
              AutoGenerateColumns="False" 
              DataSourceID="XmlDataSource1">
<Columns>
<asp:BoundField DataField="FirstName" HeaderText="FirstName" 
                SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" 
                SortExpression="LastName" />
<asp:BoundField DataField="Location" HeaderText="Location" 
                SortExpression="Location" />
</Columns>
</asp:GridView>

<asp:XmlDataSource ID="XmlDataSource1" runat="server" 
                 DataFile="~/App_Data/XMLFile.xml" 
                 TransformFile="~/App_Data/XSLTFile.xslt">
</asp:XmlDataSource>

Build and run the application.

Read More

0 Read CSV File And Save To SQL Server In ASP.NET C# VB.NET

Read CSV File or Data In ASP.NET using C# VB.NET And save to MS SQL Server.

In this post i'm going to explain how to read CSV file and save the data to sql server In ASP.NET using C# or VB.NET.

My sql table has 3 columns FirstName,LastName and Department. and i m using datatable to read Data from CSV file and temporarily storing in datatable.

First of all you need to add reference to Microsoft.VisualBasic dll by rightclicking in solution explorer,select add reference and select microsoft.VisualBasic from list.


Add these namespaces in code behind of page.

1using System.Data;

2using System.Data.SqlClient;

3using Microsoft.VisualBasic.FileIO;

Now write below mentioned code in click event of button.

C# CODE

01protected void Button1_Click(object sender, EventArgs e)

02    {

03        DataTable tblReadCSV = new DataTable();

04 

05        tblReadCSV.Columns.Add("FirstName");

06        tblReadCSV.Columns.Add("LastName");

07        tblReadCSV.Columns.Add("Department");

08 

09        TextFieldParser csvParser = new TextFieldParser("C:\\test.txt");

10 

11        csvParser.Delimiters = new string[] { "," };

12        csvParser.TrimWhiteSpace = true;

13        csvParser.ReadLine();

14 

15        while (!(csvParser.EndOfData == true))

16        {

17            tblReadCSV.Rows.Add(csvParser.ReadFields());

18        }

19 

20        //Create SQL Connection, Sql Command and Sql DataAdapter to save CSV data into SQL Server

21        string strCon = ConfigurationManager.ConnectionStrings["testdbConnectionString"].ConnectionString;

22        string strSql = "Insert into Employees(FirstName,LastName,Department) values(@Fname,@Lname,@Dept)";

23        SqlConnection con = new SqlConnection(strCon);

24        SqlCommand cmd = new SqlCommand();

25        cmd.CommandType = CommandType.Text;

26        cmd.CommandText = strSql;

27        cmd.Connection = con;

28        cmd.Parameters.Add("@Fname", SqlDbType.VarChar, 50, "FirstName");

29        cmd.Parameters.Add("@Lname", SqlDbType.VarChar, 50, "LastName");

30        cmd.Parameters.Add("@Dept", SqlDbType.VarChar, 50, "Department");

31 

32        SqlDataAdapter dAdapter = new SqlDataAdapter();

33        dAdapter.InsertCommand = cmd;

34        int result = dAdapter.Update(tblReadCSV);

35 

36    }

VB.NET CODE

01Protected Sub Button1_Click(sender As Object, e As EventArgs)

02 Dim tblReadCSV As New DataTable()

03 

04 tblReadCSV.Columns.Add("FirstName")

05 tblReadCSV.Columns.Add("LastName")

06 tblReadCSV.Columns.Add("Department")

07 

08 Dim csvParser As New TextFieldParser("C:\test.txt")

09 

10 csvParser.Delimiters = New String() {","}

11 csvParser.TrimWhiteSpace = True

12 csvParser.ReadLine()

13 

14 While Not (csvParser.EndOfData = True)

15  tblReadCSV.Rows.Add(csvParser.ReadFields())

16 End While

17 

18 'Create SQL Connection, Sql Command and Sql DataAdapter to save CSV data into SQL Server

19 Dim strCon As String = ConfigurationManager.ConnectionStrings("testdbConnectionString").ConnectionString

20 Dim strSql As String = "Insert into Employees(FirstName,LastName,Department) values(@Fname,@Lname,@Dept)"

21 Dim con As New SqlConnection(strCon)

22 Dim cmd As New SqlCommand()

23 cmd.CommandType = CommandType.Text

24 cmd.CommandText = strSql

25 cmd.Connection = con

26 cmd.Parameters.Add("@Fname", SqlDbType.VarChar, 50, "FirstName")

27 cmd.Parameters.Add("@Lname", SqlDbType.VarChar, 50, "LastName")

28 cmd.Parameters.Add("@Dept", SqlDbType.VarChar, 50, "Department")

29 

30 Dim dAdapter As New SqlDataAdapter()

31 dAdapter.InsertCommand = cmd

32 Dim result As Integer = dAdapter.Update(tblReadCSV)

33 

34End Sub

Read More

Upload And Read Excel File In Asp.Net

Now place a button on the page, in click even of this button we will be uploading the excel file in a folder on server and read it's content.

HTML SOURCE OF PAGE

<form id="form1" runat="server">

<div>
    
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" 
            Height="21px" Text="Upload" 
            Width="92px" onclick="btnUpload_Click"/>

</div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</form>

Add these namespaces in code behind of page

show source

1using System.IO;

2using System.Data.OleDb;

3using System.Data;

Write below mentioned code in Click Event of Upload Button

C# CODE

show source

01protected void btnUpload_Click(object sender, EventArgs e)

02 {

03 string connectionString ="";

04 if (FileUpload1.HasFile)

05 {

06 string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);

07 string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);

08 string fileLocation = Server.MapPath("~/App_Data/" + fileName);

09 FileUpload1.SaveAs(fileLocation);

10

11 //Check whether file extension is xls or xslx

12

13 if (fileExtension == ".xls")

14 {

15 connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

16 }

17 else if (fileExtension == ".xlsx")

18 {

19 connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

20 }

21

22 //Create OleDB Connection and OleDb Command

23

24 OleDbConnection con = new OleDbConnection(connectionString);

25 OleDbCommand cmd = new OleDbCommand();

26 cmd.CommandType = System.Data.CommandType.Text;

27 cmd.Connection = con;

28 OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);

29 DataTable dtExcelRecords = new DataTable();

30 con.Open();

31 DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

32 string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();

33 cmd.CommandText = "SELECT * FROM [" + getExcelSheetName +"]";

34 dAdapter.SelectCommand = cmd;

35 dAdapter.Fill(dtExcelRecords);

36 con.Close();

37 GridView1.DataSource = dtExcelRecords;

38 GridView1.DataBind();

39 }

40 }

VB.NET CODE

show source

01Protected Sub btnUpload_Click(sender As Object, e As EventArgs)

02 Dim connectionString As String = ""

03 If FileUpload1.HasFile Then

04 Dim fileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)

05 Dim fileExtension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)

06 Dim fileLocation As String = Server.MapPath("~/App_Data/" & fileName)

07 FileUpload1.SaveAs(fileLocation)

08

09 'Check whether file extension is xls or xslx

10

11 If fileExtension = ".xls" Then

12 connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileLocation & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""

13 ElseIf fileExtension = ".xlsx" Then

14 connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileLocation & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""

15 End If

16

17 'Create OleDB Connection and OleDb Command

18

19 Dim con As New OleDbConnection(connectionString)

20 Dim cmd As New OleDbCommand()

21 cmd.CommandType = System.Data.CommandType.Text

22 cmd.Connection = con

23 Dim dAdapter As New OleDbDataAdapter(cmd)

24 Dim dtExcelRecords As New DataTable()

25 con.Open()

26 Dim dtExcelSheetName As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

27 Dim getExcelSheetName As String = dtExcelSheetName.Rows(0)("Table_Name").ToString()

28 cmd.CommandText = "SELECT * FROM [" & getExcelSheetName & "]"

29 dAdapter.SelectCommand = cmd

30 dAdapter.Fill(dtExcelRecords)

31 con.Close()

32 GridView1.DataSource = dtExcelRecords

33 GridView1.DataBind()

34 End If

35End Sub

Build and run the application.

In this code if the excel sheet contains text characters or special characters in numeric field like EmpID, then it's not read by C# or VB.NET and display blank in gridview as shown in Image.

The reason for this is excel doesn't handle mixed data format very well, entry like 1A or 1-A etc doesn't get read by this code.



To fix this error we need to make some changes in connection string of excel, and need to add some extended properties, change the connection string as shown below.

show source

1connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + @";Extended Properties=" + Convert.ToChar(34).ToString() + @"Excel 8.0;Imex=1;HDR=Yes;" + Convert.ToChar(34).ToString();

Now it will read the excel sheet without any errors.

Read More