Print GridView only in ASP.NET

Hi,

Lets come back to ASP.NET tips and trick series. We are once again going to learn new trick related to GridView. Previously I have posted following articles related to GridView.

• Export GridView data to Excel
• Pagination in GridView
• Export GridView data to Word
• Custom Error Message for Empty GridView
• GridView with scroll bar
• GridView with Fixed Header and Scroll Bar

Requirement

Suppose, you have a page where you are displaying any kind of data to user. You want to add a functionality to print these records. But problem with Browsers Default Print options (Ctrl+P) is that, it will print whole page. All header images, footer, sidebar and colors will printed, which is not required. You have two options, either create a new page for print view and redirect user to that page or just print the required area, in our case it is GridView. 

Most of developer working in a web applications have MasterPage for their web-application. MasterPage are similar to templates in normal HTML having some advanced functionality and customized code for maintaining easier and common look of website or application.

Lets have a look at below screen to make our requirement more clear.

Code for Default.aspx

This is the code for GridView.

   <asp:GridView runat="server" ID="gvProducts" AutoGenerateColumns="false" AllowPaging="false" AlternatingRowStyle-BackColor="Linen" HeaderStyle-BackColor="SkyBlue" Width="100%" OnPageIndexChanging="gvProducts_PageIndexChanging" EmptyDataText="Sorry! No Products to List. First Add from Add Product Link.">
                    <Columns>
                        <asp:TemplateField HeaderText="Product ID">
                            <ItemTemplate>
                                <asp:Label ID="lblProductID" runat="server" Text='<%#Eval("ProductID")%>' ToolTip="ID of Product as stored in Database."></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <Columns>
                        <asp:TemplateField HeaderText="Product Name">
                            <ItemTemplate>
                                <asp:Label ID="lblProductName" runat="server" ToolTip="Name of Product" Text='<%#Eval("ProductName")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <Columns>
                        <asp:TemplateField HeaderText="Brand">
                            <ItemTemplate>
                                <asp:Label ID="lblBrandName" runat="server" ToolTip="Brand of Product" Text='<%#Eval("BrandName")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <Columns>
                        <asp:TemplateField HeaderText="Category">
                            <ItemTemplate>
                                <asp:Label ID="lblProductCat" runat="server" ToolTip="Category of Product" Text='<%#Eval("CategoryName")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <Columns>
                        <asp:TemplateField HeaderText="In Stock">
                            <ItemTemplate>
                                <asp:Label ID="lblProductinStock" runat="server" ToolTip="Quantity available in Stock"
                                    Text='<%#Eval("UnitsInStock")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>

Code for Default.aspx.cs

We do not have much more at backend, we are just going to bind data to GridView.

 protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConToStore"].ConnectionString);
            SqlDataAdapter adp = new SqlDataAdapter("Select * from Products,Brands,Category where Products.BrandID=Brands.BrandID and Products.CategoryID=Category.CategoryID", con);
            DataSet ds = new DataSet();
            adp.Fill(ds);
            gvProducts.DataSource = ds.Tables[0];
            gvProducts.DataBind();
        }
    }

Printing Page.

Lets press Ctrl+P key combination to print page, before printing see the Preview.

Printing GridView Only

We need to make following change to print GridView only. Lets start with HTML.

Wrap the GridView inside a Table or Panel. I am using Table. You can also go with Panel.

  <table width="70%" id="pnlGridView" runat="server" align="center" class="ContentTable">
        <tr>
            <td colspan="2" align="center">
                <h1>All Products in Store</h1>
            </td>
        </tr>
        <tr>
            <td> </td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:GridView runat="server" ID="gvProducts" AutoGenerateColumns="false" AllowPaging="false" 
    AlternatingRowStyle-BackColor="Linen" HeaderStyle-BackColor="SkyBlue" Width="100%" 
    OnPageIndexChanging="gvProducts_PageIndexChanging" 
    EmptyDataText="Sorry! No Products to List. First Add from Add Product Link.">
                    ...............
     .............
                </asp:GridView>
            </td>
        </tr>
        <tr>
            <td align="right">
                <asp:LinkButton ID="lnkPrint" runat="server" ToolTip="Click to Print All Records" Text="Print Data" OnClick="lnkPrint_Click"></asp:LinkButton>     
                <asp:LinkButton ID="lnkExportAll" runat="server" ToolTip="Export this List" Text="Export to Excel" OnClick="lnkExportAll_Click"></asp:LinkButton>
                     
                <asp:LinkButton ID="lnkAddNew" runat="server" ToolTip="Add New Product"
                    Text="Add New" OnClick="lnkAddNew_Click"></asp:LinkButton>
            </td>
        </tr>
    </table>

Now just add following JavaScript function in head section and assign that function to Print linkbutton.

    <script language="javascript" type="text/javascript">
        function PrintPage() {
            var printContent = document.getElementById('<%= pnlGridView.ClientID %>');
            var printWindow = window.open("All Records", "Print Panel", 'left=50000,top=50000,width=0,height=0');
            printWindow.document.write(printContent.innerHTML);
            printWindow.document.close();
            printWindow.focus();
            printWindow.print();
        }
    </script>

Now lets click on Print Link button which we have created. Following will be output for above code.

Bingo! It was the requirement.
Hope you enjoyed reading. If you have any feedback or suggestions, please send us as comment or using contact options. Keep sharing.
John Bhatt

Read More

Export to Word from GridView in ASP.NET

Hi,

Today, I am going to continue our GridView tricks series ahead and posting a nice and useful feature. For all list of GridView articles we have written here, you can search for GridView in search box. As I have previously written about Exporting data to Excel from GridView, this is about exporting GridView to Microsoft Word which is another most used document format and needed in most cases.
Lets create a design as below.

I have just dragged and dropped a gridview control and used autoformat to make GridView look some better. 

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        .auto-style1 {
            text-align: justify;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h1 class="auto-style1">
        Export to Word 
    </h1>
        <asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3" Width="590px">
            <FooterStyle BackColor="White" ForeColor="#000066" />
            <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
            <RowStyle ForeColor="#000066" />
            <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#007DBB" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#00547E" />
        </asp:GridView>
        <br />
        <asp:Button ID="Button1" runat="server" Font-Bold="True" OnClick="Button1_Click" Text="Export to Word" />
    </div>
    </form>
</body>
</html>

Now bind data to this gridview from backend.

 protected void Page_Load(object sender, EventArgs e)
    {
        string ConString = "Server=.\\sqldb;database=Store;integrated security=true;";
        SqlConnection con = new SqlConnection(ConString);
        string QueryText = "SELECT DISTINCT Products.ProductName, Brands.BrandName, Category.CategoryName, 
Products.UnitPrice, Products.UnitsInStock from products,brands,Suppliers,Category 
where Products.BrandID=Brands.BrandID and Products.CategoryID=Category.CategoryID;";

        SqlDataAdapter adp = new SqlDataAdapter(QueryText, con);
        DataTable dt = new DataTable();
        adp.Fill(dt);
        GridView1.DataSource = dt.DefaultView;
        GridView1.DataBind();
    }

Let's view page in Browser. You must see data from your database. Mine query has resulted below data in grid.

Perfect, now its time to do the important stuff. Lets write code for Export Button. Double click in button to create a method and automatically bind event with control. Now write below code in backend.

 public override void VerifyRenderingInServerForm(Control control)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment;filename=Products.doc"));        
        Response.ContentType = "application/vnd.ms-word ";
        StringWriter WriterForWord = new StringWriter();
        HtmlTextWriter HTMLWriter = new HtmlTextWriter(WriterForWord);
        GridView1.AllowPaging = false;
        GridView1.DataBind();
        GridView1.RenderControl(HTMLWriter);
        Response.Output.Write(WriterForWord.ToString());
        Response.Flush();
        Response.End();
    }

Its time to check our code, Click on Export to Word button. I have attached snapshot of Firefox because it prompts user.

You can save file or just click on Open with your word processor software. I am using Microsoft Word here.

Did you read our previous Export to Excel tutorial? What are the similarities between exporting to Word and Excel?
I am leaving with a question, if you know, leave a reply below.
Thanks for reading . Do not forget to say thanks or plus1 or provide feedback.
+John Bhatt 

Read More

List All SQL Server Instances in Combo (Windows Form) - Part-2

Hello Friends,
On the Previous Post on this series, I have told you the code and procedure upto Connect Button. Hope you have been through it and enjoyed that. If not, please read below post before moving ahead. http://www.prithviraj.com.np/2012/10/list-all-sql-server-instances-in-combo_9.html
Today, I am going to share rest of the Code for Listing Database, Tables Inside Database and Columns and apply them with Operator and simply perform Select Operation from database and list the Result in DataGrid. Let's look the Design you have previously saw when making connection.



In above design,
In Database Combo, we will list all database from Connected Server. Then in Table Combo, we will list all Tables of Selected Database and in Column Combo all columns of Selected Table.

Lets Begin the Code. On Button Click and Successful Connection, we will load all database names in Database Combo. Actually we have did it earlier.
Lets have look at this Code:

comboBox2.Items.Clear();
                   if (checkBox1.Checked == true)
                   {
                       ConStr = "Data Source = " + comboBox1.Text.ToString() + ";Integrated Security = true;";
                   }
                   else
                   {
                       ConStr = "Data Source = " + comboBox1.Text.ToString() + ";UID=" + textBox1.Text + ";pwd=" + textBox2.Text + ";";
                   }
                   SqlConnection Conexion = new SqlConnection(ConStr);
                   Conexion.Open();
                   label9.Visible = false;
                   panel2.Visible = false;
                   button2.Visible = true;
                   panel1.Visible = true;
                   groupBox1.Visible = true;
                   DataTable tblDatabases = Conexion.GetSchema("Databases");
 
                   for (int i = 0; i <= tblDatabases.Rows.Count - 1; i++)
                   {
                       comboBox2.Items.Add(tblDatabases.Rows[i][0].ToString());
                   }
                   Conexion.Close();

This is code from earlier post.
Now Lets see the Code on SelectedIndex Change event of Database Combo (Combobox2).

private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            comboBox3.Items.Clear();
            SqlConnection conx = new SqlConnection(ConStr + "Database ="+comboBox2.Text.ToString()+";");
            conx.Open();
            DataTable tblTables = conx.GetSchema("Tables");
            for (int i = 0; i <= tblTables.Rows.Count - 1; i++)
            {
                comboBox3.Items.Add(tblTables.Rows[i][2].ToString());
            }
            conx.Close();
        }

Here we are creating Connection String Everytime, because, It is changing every-time. Now this code will list all the Tables inside Database in Table Combo (combobox3): Code:

private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
        {
            comboBox4.Items.Clear();
            SqlConnection conx = new SqlConnection(ConStr + "Database =" + comboBox2.Text.ToString() + ";");
            conx.Open();
            string tableName = comboBox3.SelectedItem.ToString();
            SqlDataAdapter adp = new SqlDataAdapter("select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '"+comboBox3.SelectedItem.ToString()+"'",conx);
            DataTable tblColumns = new DataTable();
            adp.Fill(tblColumns);
            for (int i = 0; i <= tblColumns.Rows.Count - 1; i++)
            {
                comboBox4.Items.Add(tblColumns.Rows[i][3].ToString());
            }
            conx.Close();
        }

Now above code will make all Columns of Selected table from database.
Now Directly move to Keyword TextBox textchange event which will load data as you type.

private void textBox3_TextChanged(object sender, EventArgs e)
        {
            string QueryCon = ConStr;
            if (comboBox5.Text.ToString() == "like")
            {
                QueryStr = "SELECT * FROM " + comboBox3.Text.ToString() + " WHERE " + comboBox4.Text.ToString() + " " + comboBox5.Text.ToString() + " '%" + textBox3.Text + "%'";
            }
            else
            {
                QueryStr = "SELECT * FROM " + comboBox3.Text.ToString() + " WHERE " + comboBox4.Text.ToString() + " " + comboBox5.Text.ToString() + " '" + textBox3.Text + "'";
            }
            TxtQueryBox.Text = ConStr + "Database=" + comboBox2.Text.ToString() + ";"+"\n" + QueryStr;
            SqlDataAdapter adp = new SqlDataAdapter(QueryStr, ConStr+"Database="+comboBox2.Text.ToString()+";");
            DataSet ds = new DataSet();
            adp.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
        }

This will load data in GridView automatically. Lets see a snapshot once again with Result.
All the Best, Enjoy....

Read More

List All SQL Server Instances in Combo (Windows Form)

Hi,

I was checking my Old Projects and Like to share a simple example with. Why not to create a SQL Management Application.

How we would do this? What is our objective?

Lets be clear.
We will List All SQL Server Instances available in Network into Application.
Then We will connect to Server using Windows Authentication or SQL Server Authentication.
After successfull authentication ,
We will list all databases in Instance.
After Database, we will List All Tables in Database,
After Database , we will list All Columns in Selected table.
We will place a Create button next to Database, Table list box also.
After selecting a Column, we provided a Filter (operator) and parameters in box.
Which will fetch data and display in DataGridView.

First we Designed a Following Screen.

In Servers List we have to Load All List of Servers and Connect. We have Options for Username and Password and also a Checkbox for Integrated Security (Windows Authentication).
Lets See the Code in Form Load.
Namespace to Include:

using System.Data.Sql;
using System.Data.SqlClient;

Now Form_Load Event Code

private void Form1_Load(object sender, EventArgs e)
        {
            panel1.Visible = false;
            groupBox1.Visible = false;
            label9.Visible = true;
            label9.TextAlign = ContentAlignment.MiddleCenter;
            SqlDataSourceEnumerator SerInstances = SqlDataSourceEnumerator.Instance;
            DataTable SerNames = SerInstances.GetDataSources();
            for (int i = 0; i <= SerNames.Rows.Count - 1; i++)
            {
                comboBox1.Items.Add(SerNames.Rows[i][0].ToString()+"\\"+SerNames.Rows[i][1].ToString());
            }
        }

Description of Above Code: We have placed rest design in Panel and Groupbox so we set that invisible on Form Load. Please Make a Conncetion First.. is Label9 and we set is Visible and aligned. Now main task is to List Sql Servers on Network. For this we used SqlDataSourceEnumerator class. In Next Line we take a DataTable and stored ServerInstances into that. Rest is Simple, We added all items into Listbox. Column 0 contains Machine name and Column 1 contains Instance Name. So we combined that at the Time of Adding to ComboBox Control. Now Click the Connect Button:

 private void button1_Click(object sender, EventArgs e)
        {
            if (checkBox1.Checked == true && textBox1.Text.Trim().Length > 0)
            {
                MessageBox.Show("Please Uncheck Integrated Security or Clear UserName!", this.Text + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else if (checkBox1.Checked == false && textBox1.Text.Trim().Length == 0)
            {
                MessageBox.Show("Please Check Integrated Security or Enter UserName!", this.Text + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
 
            else if (textBox1.Text.Trim().Length > 0 && textBox2.Text.Trim().Length == 0)
            {
                errorProvider1.SetError(textBox2, "Please enter password for Username = " + textBox1.Text + "!");
            }
            else if (comboBox1.Text.Trim().Length == 0)
            {
                MessageBox.Show("Please select Server from List. If You can not see any Instance in Servers Combobox, Contact your Network Administrator!", this.Text + " - Message", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }
            else
            {
                errorProvider1.Clear();
                if (MessageBox.Show("Connection Successfull!, You are Now Connected to Server: " + comboBox1.Text.ToString() + "!", this.Text + " - Alert", MessageBoxButtons.OK, MessageBoxIcon.Information) == DialogResult.OK)
                {
                    comboBox2.Items.Clear();
                    if (checkBox1.Checked == true)
                    {
                        ConStr = "Data Source = " + comboBox1.Text.ToString() + ";Integrated Security = true;";
                    }
                    else
                    {
                        ConStr = "Data Source = " + comboBox1.Text.ToString() + ";UID=" + textBox1.Text + ";pwd=" + textBox2.Text + ";";
                    }
                    SqlConnection Conexion = new SqlConnection(ConStr);
                    Conexion.Open();
                    label9.Visible = false;
                    panel2.Visible = false;
                    button2.Visible = true;
                    panel1.Visible = true;
                    groupBox1.Visible = true;
                    DataTable tblDatabases = Conexion.GetSchema("Databases");

                    for (int i = 0; i <= tblDatabases.Rows.Count - 1; i++)
                    {
                        comboBox2.Items.Add(tblDatabases.Rows[i][0].ToString());
                    }
                    Conexion.Close();
                }
            }
        }

Description of Code: We Put some Validation and Message box to make some user friendly environment. After getting Proper Username and password, we make a Connection String and Used Username and password in Connection String. We build a connection, Make Servers List, Username and password and Connect button invisible and Disconnect button Visible. Then First we loaded all Databases of Selected Instance using Connection.GetSchema() method. How it look after connecting.

For now, its your turn to code something for this. I'll be back soon with Rest Part of This Program. Comments are Welcome. Feedback keep the Important Role in Improvement. So Expecting those from you. Happy Learning!

John Bhatt
Glad to Know, Free to Share.....

Read More

Implement Same WebMethod for AutoComplete in Multiple Textbox

Hi,


While Answering a question at Forum, I wrote this post.


Suppose We have Multiple Textbox in Same Page and we want to Implement AutoSuggest/AutoComplete feature in All Textbox and of same type. Suppose Multiple Google Search Boxes.


For that case, we have separate Textboxs, Separate AutoComplete Extenders and Separate Methods. We are trying to Consume same PageMethod in All Textbox.


Front End Code:

Product One:

                    DelimiterCharacters="" Enabled="True" ServiceMethod="GetProducts" MinimumPrefixLength="2"
            ServicePath="" TargetControlID="TextBox1" UseContextKey="True">
        
    
Product Two:
    

                    DelimiterCharacters="" Enabled="True" ServicePath="" TargetControlID="TextBox2"
            ServiceMethod="GetProducts" MinimumPrefixLength="2" UseContextKey="True">
        
    
Product Three:
    

                    DelimiterCharacters="" Enabled="True" ServicePath="" TargetControlID="TextBox3"
            ServiceMethod="GetProducts" MinimumPrefixLength="2" UseContextKey="True">
        
    

BackEnd Code:

[System.Web.Services.WebMethodAttribute(), System.Web.Script.Services.ScriptMethodAttribute()]
    public static string[] GetProducts(string prefixText, int count, string contextKey)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConToStore"].ConnectionString);
        SqlDataAdapter adp = new SqlDataAdapter("Select * from Products where UnitsInStock>0 and ProductName like '%" + prefixText + "%'", con);
        DataSet ds = new DataSet();
        adp.Fill(ds);
        string[] Products = new string[ds.Tables[0].Rows.Count];
        for (int j = 0; j <= ds.Tables[0].Rows.Count - 1; j++)
        {
            Products.SetValue(ds.Tables[0].Rows[j][1].ToString(), j);
        }
        return Products;
    }

Lets Look at the Output.

Hope this will be Helpful.
John Bhatt
Glad to Know, Free to Share.....
http://www.johnbhatt.com

Read More

AutoCompleteExtender in Textbox

Hi,

We will learn how to enable AutoCompleteExtender in Textbox Control.


Here, We will learn to Map data from SQL Database to Textbox within Same Page. WebMethod in .cs Page.

Directly to Code:

ASPX Page Code

             


Vehicle No :

             








         

Now Code in ASPX.CS Page

Add these Namespages for below Code.

using System.Data; // For Dataset
using System.Data.SqlClient; // For SQlConnection/Adaptor
using System.Configuration; // Access Consting from Web.Config.
using System.Collections.Generic; 

[System.Web.Services.WebMethodAttribute(), System.Web.Script.Services.ScriptMethodAttribute()]
    public static string[] GetVehicleList(string prefixText, int count, string contextKey)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConToJohn"].ConnectionString);
        SqlDataAdapter adp = new SqlDataAdapter("Select distinct VehicleNo from VehicleDispatch where VehicleNo like '%" + prefixText + "%'", con);
        DataSet ds = new DataSet();
        adp.Fill(ds, "Vehicles");
        string[] VehicleNo = new string[ds.Tables["Vehicles"].Rows.Count];
        for (int i = 0; i <= ds.Tables["Vehicles"].Rows.Count - 1; i++)
        {
            VehicleNo.SetValue(ds.Tables["Vehicles"].Rows[0][0].ToString(), i);
        }
   return VehicleNo;
    }

I have Selected Only Unique Value from Database using SELECT DISTINCT .... command. If you want all records, Simply Change SQL Query to SELECT ..... Will be glad to hear from you... This Post is copied from DotNetFunda where I wrote in Code section.

Read More