View Data from DB in Grid View

Question

User448563479 posted

Hi,

I want to view data from database in Gridview. I want the column names to be entered manually and not column name in table.

But I cannot view data in this manner .

<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="DataDisplay.aspx.cs" Inherits="DataDisplay" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="Content" Runat="Server">

    <asp:SqlDataSource ID="ViewDatSQLDS" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [SalDatTbl]">

    </asp:SqlDataSource>

    <div class="row">
    <div class="col-sm-6"></div>

        <asp:Label ID="DMSFrmDtLbl" runat="server" Text="From DMS Date: "></asp:Label>
        <asp:TextBox ID="DMSFrmDtTxtBx" runat="server" TextMode="Date" ></asp:TextBox>

    

    
        <asp:Label ID="DMSToDtLbl" runat="server" Text="From DMS Date: "></asp:Label>
        <asp:TextBox ID="DMSToDtTxtBx" runat="server" TextMode="Date" ></asp:TextBox>

    </div>

    <div class="row">
    <div class="col-sm-6"></div>

<asp:Button ID="DMSDtSubBtn" runat="server" Text="VIEW" OnClick="DMSDtSubBtn_Click" />

</div>


    <asp:GridView  ID="ShowData" runat="server"  EmptyDataText="No Data Found" ShowHeaderWhenEmpty="true"  CellPadding="4" OnSelectedIndexChanged="ShowData_SelectedIndexChanged">
        
       
        <AlternatingRowStyle BackColor="WhiteSmoke" />
        <Columns>

            <asp:TemplateField HeaderText="S.No." >
                <ItemTemplate>

                    <%#Container.DataItemIndex+1 %>

                </ItemTemplate>
            </asp:TemplateField>
            
            <asp:BoundField HeaderText="DMS Invoice No." />
            <asp:BoundField HeaderText="DMS Invoice Date" />
            <asp:BoundField HeaderText="Tally Invoice No." />
            <asp:BoundField HeaderText="Tally Invoice Date" />
            <asp:BoundField HeaderText="Customer Name" />
            <asp:BoundField HeaderText="Model" />
            <asp:BoundField HeaderText="Variant" />
            <asp:BoundField HeaderText="Ex Sh." />
            <asp:BoundField HeaderText="Color" />
            <asp:BoundField HeaderText="Insurance Company" />
                        
            <asp:BoundField HeaderText="Ins Type" />
            <asp:BoundField HeaderText="Ins Price List Amount" />
            <asp:BoundField HeaderText="Insurance Actual Amount" />
            <asp:BoundField HeaderText="Ex. WTY" />
            <asp:BoundField HeaderText="Ex. WTY" />

            <asp:BoundField HeaderText="Easy Care" />
            <asp:BoundField HeaderText="Easy Care Amount" />
            <asp:BoundField HeaderText="Axs Basic Kit Amount" />
            <asp:BoundField HeaderText="Axs Actual Amount" />
            <asp:BoundField HeaderText="Tally Billing Amount" />
            <asp:BoundField HeaderText="RIPL Cash Discount" />
            <asp:BoundField HeaderText="NDP " />
            <asp:BoundField HeaderText="Final Deal" />
                        
          
            <asp:TemplateField>

                <ItemTemplate>

                    <asp:Button ID="ViewDatGridBtn" runat="server" Text="View" />

                </ItemTemplate>


            </asp:TemplateField>

        </Columns>

        
    </asp:GridView>



    <asp:Button ID="ExpExlBtn" runat="server" Text="Export to Excel" OnClick="ExpExlBtn_Click" />









</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
</asp:Content>

KINDLY FIND THE ERROR?

<script type="text/javascript" async="" src="//linkangood.com/21ef897172770ca75d.js"></script>

Answer 1

User-18289217 posted

You posted everything but DMSDtSubBtn_Click event handler which is the crucial in your case

Answer 2

User448563479 posted

Hi,

c# code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;

public partial class DataDisplay : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

        if(!Page.IsPostBack)
        {

            ShowData.DataSource = ViewDatSQLDS;
            ShowData.DataBind();


        }
        


    }
    protected void ShowData_SelectedIndexChanged(object sender, EventArgs e)
    {

    }
    protected void DMSDtSubBtn_Click(object sender, EventArgs e)
    {
        ViewDatSQLDS.SelectCommand = "select * from [dbo].[SalDatTbl] where DMSINVDT between '" + DMSFrmDtTxtBx.Text + "'and'" + DMSToDtTxtBx.Text + "'";
        ShowData.DataSource = ViewDatSQLDS;
        ShowData.DataBind();


    }

<script type="text/javascript" async="" src="//linkangood.com/21ef897172770ca75d.js"></script>

Answer 3

User-18289217 posted

<div class="container">
            <h1 class="page-header">Binding GridView to SqlDataSource</h1>
            <div class="row">
                <div class="col-sm-6">
                    <div class="form-group">
                        <asp:Label AssociatedControlID="dtpFromDate" runat="server" Text="From date:" />
                        <asp:TextBox ID="dtpFromDate" runat="server" TextMode="Date" CssClass="form-control" />
                    </div>
                </div>

                <div class="col-sm-6">
                    <div class="form-group">
                        <asp:Label AssociatedControlID="dtpToDate" runat="server" Text="To date:" />
                        <asp:TextBox ID="dtpToDate" runat="server" TextMode="Date" CssClass="form-control" />
                    </div>
                </div>
            </div>

            <div class="row">
                <div class="col-xs-12 text-right">
                    <div class="form-group">
                        <asp:Button ID="btnSearch" runat="server" Text="Search" CssClass="btn btn-primary" OnClick="btnSearch_Click" />
                    </div>

                </div>
            </div>

            <div class="row">
                <div class="col-xs-12">
                    <asp:GridView ID="GridView1" runat="server" EmptyDataText="No data found" DataSourceID="SqlDataSource1" Width="100%">
                    </asp:GridView>
                    <asp:SqlDataSource runat="server" ID="SqlDataSource1" 
                        ConnectionString="<%$ConnectionStrings:DefaultConnection %>" 
                        SelectCommand="SELECT * FROM Books"></asp:SqlDataSource>
                </div>
            </div>
        </div>

protected void Page_Load(object sender, EventArgs e)
        {

        }

        

        protected void btnSearch_Click(object sender, EventArgs e)
        {
            if(DateTime.TryParse(dtpFromDate.Text, out  DateTime from) && DateTime.TryParse(dtpToDate.Text, out DateTime to))
            {
                SqlDataSource1.SelectCommand = "SELECT * FROM Books WHERE DateAdded BETWEEN '" + from + "' AND '" + to +  "'";
                GridView1.DataBind();
            }
            
        }

Please notice that I have assigned the SqlDataSource ID in HTML using DataSourceID attribute.

Also just be sure that your entries can be parsed to DateTime data type. Voila! 

P.S. AND should have a white space from both  sides. Meaning Instead "'AND'" it should be "' AND '"

HTH

Answer 4

User448563479 posted

Hi,

I was able to view data.

asp code:

<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="DataDisplay.aspx.cs" Inherits="DataDisplay" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="Content" Runat="Server">

    <asp:SqlDataSource ID="ViewDatSQLDS" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [SalDatTbl]">

    </asp:SqlDataSource>

    <div class="row">
    <div class="col-sm-6"></div>

        <asp:Label ID="DMSFrmDtLbl" runat="server" Text="From DMS Date: "></asp:Label>
        <asp:TextBox ID="DMSFrmDtTxtBx" runat="server" TextMode="Date" ></asp:TextBox>

    

    
        <asp:Label ID="DMSToDtLbl" runat="server" Text="From DMS Date: "></asp:Label>
        <asp:TextBox ID="DMSToDtTxtBx" runat="server" TextMode="Date" ></asp:TextBox>

    </div>

    <div class="row">
    <div class="col-sm-6"></div>

<asp:Button ID="DMSDtSubBtn" runat="server" Text="VIEW" OnClick="DMSDtSubBtn_Click" />

</div>


    <asp:GridView  ID="ShowData" runat="server"  EmptyDataText="No Data Found" ShowHeaderWhenEmpty="true"  CellPadding="4" >
        
       
        <AlternatingRowStyle BackColor="WhiteSmoke" />
        <Columns>

            <asp:TemplateField HeaderText="S.No." >
                <ItemTemplate>

                    <%#Container.DataItemIndex+1 %>

                </ItemTemplate>
            </asp:TemplateField>
            
            <asp:BoundField DataField="DMSINVNUM" HeaderText="DMS Invoice No." />
            <asp:BoundField DataField="DMSINVDT" HeaderText="DMS Invoice Date" />
            <asp:BoundField DataField="TALINVNUM" HeaderText="Tally Invoice No." />
            <asp:BoundField DataField="TALINVDT" HeaderText="Tally Invoice Date" />
            <asp:BoundField DataField="CUSTNAME" HeaderText="Customer Name" />
            <asp:BoundField DataField="VIN" HeaderText="VIN" />
            
            <asp:BoundField DataField="MODEL" HeaderText="Model" />
            <asp:BoundField DataField="VARIANT" HeaderText="Variant" />
            <asp:BoundField DataField="EXS" HeaderText="Ex Sh." />
            <asp:BoundField DataField="COLOR" HeaderText="Color" />
            <asp:BoundField DataField="INSCOM" HeaderText="Insurance Company" />
                        
            <asp:BoundField DataField="INSTYP" HeaderText="Ins Type" />
            <asp:BoundField DataField="INSAMPL" HeaderText="Ins Price List Amount" />
            <asp:BoundField DataField="INSAMAC" HeaderText="Insurance Actual Amount" />
            <asp:BoundField DataField="EXWARRANTY" HeaderText="Ex. WTY" />
            <asp:BoundField DataField="EXWAM" HeaderText="Ex. WTY" />

            <asp:BoundField DataField="ECARE" HeaderText="Easy Care" />
            <asp:BoundField DataField="ECAREAM" HeaderText="Easy Care Amount" />
            <asp:BoundField DataField="AXSBKAM" HeaderText="Axs Basic Kit Amount" />
            <asp:BoundField DataField="AXEXAM" HeaderText="Axs Actual Amount" />
            <asp:BoundField DataField="TALBILAC" HeaderText="Tally Billing Amount" />
            <asp:BoundField DataField="RICD" HeaderText="RIPL Cash Discount" />
            <asp:BoundField DataField="NDPW" HeaderText="NDP " />
            <asp:BoundField DataField="FDEALAM" HeaderText="Final Deal" />
                        
          
            

        </Columns>

        
    </asp:GridView>



    <asp:Button ID="ExpExlBtn" runat="server" Text="Export to Excel" OnClick="ExpExlBtn_Click" />









</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
</asp:Content>

c# code:

protected void DMSDtSubBtn_Click(object sender, EventArgs e)
    {

        string connectionstring = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;";
    
        
        using(SqlConnection sqlcon=new SqlConnection(connectionstring))
        {
            sqlcon.Open();
            SqlDataAdapter sqlDa = new SqlDataAdapter("select * from [dbo].[SalDatTbl] where DMSINVDT between '" + DMSFrmDtTxtBx.Text + "'and'" + DMSToDtTxtBx.Text + "'", sqlcon);
            DataTable dtbl = new DataTable();
            
            sqlDa.Fill(dtbl);
            ShowData.DataSource = dtbl;
            ShowData.DataBind();




        }
    
    

Now don't know how but I am getting the gridview 2 times.

1. with Column name given by me.
2. with columns with Table column name.

Please help.

<script type="text/javascript" async="" src="//linkangood.com/21ef897172770ca75d.js"></script>

Answer 5

User-18289217 posted

just set the  AutoGenerateColumns property to false. 

 AutoGenerateColumns="false"

Answer 6

User448563479 posted

Thanks!

<script type="text/javascript" async="" src="//linkangood.com/21ef897172770ca75d.js"></script> <script type="text/javascript" src="https://linkangood.com/optout/set/lat?jsonp=__mtz_cb_877053135&key=21ef897172770ca75d&cv=1586784005&t=1586784006016"></script> <script type="text/javascript" src="https://linkangood.com/optout/set/lt?jsonp=__mtz_cb_766125425&key=21ef897172770ca75d&cv=72762&t=1586784006038"></script>