Bind DropDown Under GridView

Bind DropDown Under GridView

lets go from basic...

How to bind dropdown?

Normaly We Bind A Drop Downdown In Asp.net Page Like below....

.aspx

<asp:DropDownList ID="DropDownListGroup" runat="server"
DataTextField="GroupNm" DataValueField="GroupID" Height="18px"
style="margin-bottom: 0px" Width="205px">
</asp:DropDownList>

.cs
        SqlDataAdapter adp2 = new SqlDataAdapter("select * from Group", con);
        DataSet ds2 = new DataSet();
        adp2.Fill(ds2);
        DropDownListGroup.DataSource = ds2;
        DropDownListGroup.DataBind();      
        DropDownListGroup.Items.Insert(0, "---Select---");

but in case of Grid View Thing are bit different..

You need to workout in RowDataBound Click Event

1.Bind DropDown Under GridView In Item Template

 if (e.Row.RowType == DataControlRowType.DataRow)
     {
              DropDownList drdList = (DropDownList)e.Row.FindControl("DropDownListGroup");
             SqlDataAdapter adp = new SqlDataAdapter("select * from MST_Group", con);
             DataSet ds = new DataSet();
             adp.Fill(ds);
             drdList.DataSource = ds;
             drdList.DataBind();
             drdList.Items.Insert(0, "---Select---");

             }

2.Bind DropDown Under GridView In EditItem Template

 if (e.Row.RowType == DataControlRowType.DataRow)
     {
         if ((e.Row.RowState & DataControlRowState.Edit) > 0)
         {
             DropDownList drdList = (DropDownList)e.Row.FindControl("DropDownListGroup");
             SqlDataAdapter adp = new SqlDataAdapter("select * from MST_Group",con);
             DataSet ds = new DataSet();
             adp.Fill(ds);
             drdList.DataSource = ds;
             drdList.DataBind();
             drdList.Items.Insert(0, "---Select---");
         }
     }

3.Bind DropDown Under GridView In Footer Template

  if (e.Row.RowType == DataControlRowType.Footer)
     {
       ((Label)e.Row.FindControl("lblQty")).Text = totqty.ToString();
       TxtTotal.Text = totqty.ToString();
     
     }

The type or namespace name 'Linq' does not exist in the namespace 'System.Data'

The type or namespace name 'Linq' does not exist in the namespace 'System.Data' (are you missing an assembly reference?)

 

<add assembly="System.Data.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>

Setting up SQL Server 2008 for an ASP.NET website on IIS 7.0

Setting up SQL Server 2008 for an ASP.NET website on IIS 7.0 

 

SQL Server 2008 Express edition with Tools has finally been released (It was launched on August 11th, but only today are the management tools available).  As I’ve been building web servers today, I’ve put together this comprehensive guide to setting up your website to work with SQL Server 2008.

 

 Step 1: Prerequisities

1. Firstly you need Windows 2008 Server and IIS 7.0 installed. Installing Windows Server 2008 is pretty straight-forward and I won’t cover it here (there is an excellent article here), although be aware that as you are using ASP.NET and SQL Server you cannot install server core.  Installing the IIS 7.0 server role is also quite easy, (another great article here)
2. Install Windows PowerShell from the Features section in the Windows Server 2008 Server Manager.
3. Install .NET Framework 3.5 SP1 from here.
4. Install Windows Installer 4.5 from here.
5. If you have AJAX enabled sites running on ASP.NET 2.0 then Install ASP.NET AJAX Extensions 1.0 from here.
6. Download SQL Server 2008 Express with Tools from here.  This version includes:
   • SQL Server database engine – create, store, update and retrieve your data
   • SQL Server Management Studio Basic – visual database management tool for creating, editing and managing databases

While this guide concentrates on the Express version of SQL Server 2008, you need to ensure that it meets the requirements in your specific environment.  The SQL Server 2008 blog has an article which discusses this issue (SQL Server Express and Hosting)

Step 2: Installing SQL Server 2008

1. Start the install from your CD or downloaded file.
2. Select New SQL Server stand-alone installation of add features to an existing installation
3. The setup program will check that you have the necessary pre-requisities in place for the SQL Server setup support files.  Click OK.
4. Click Install to install the SQL Server setup support files.
5. SQL Setup will then check that you have all the necessary pre-requisities in place for the SQL Server installation.  I received a warning that Windows Firewall needs port 1433 open for remote TCP/IP connection to SQL Server.  Click Next.
6. Nothing to do on the next step if you are using the Express Edition, other you need to enter a valid product key.  Click Next.
7. Accept the license terms and click Next.
8. Select the features you want to install and cick Next.  At a minimum I recommend installing the Database Engine Services and the Management Tools.
9. Select an instance to install your database server into and a location.  In the web.config file for your ASP.NET web site, if SQL is not installed in the default instance then you need to set Data Source = SERVERNAME\INSTANCENAME for your database connection string.
10. SQL Setup will confirm you have enough disk space. Click Next.
11. Configure your service account and collation settings and click Next. Use the built-in System account and don’t change the default collation unless you have databases with special requirements.
12. Configure Mixed Mode authentication and provide a password for the sa account.  This allows you to use either windows or sql authentication depending on your requirements.  Choose which accounts have administrative privilages to the SQL Server database engine.  Click Next.
13. Tick the checkboxes if you would like to send error reports and usage data to Microsoft, and click Next.
14. Setup will then check if the installation process will be blocked.  Check the report and click Next.
15. Verify the SQL Server features to install and click Install. 
16. Once the setup finishes, check Windows Update to see if there are any patches that need to be installed.

Step 3: Allowing TCP/IP Connections to SQL Server

By default, only the Named Pipes protocal is allowed to connect to the SQL Server.  To allow TCP/IP connections, perform the following steps:

1. Open the SQL Server Configuration Manager under the Microsoft SQL Server 2008, Configuration Tools start menus.
2. Expand the SQL Server Network Configuration node and select Protocols for <InstanceName>.  Double click on the TCP/IP Protocol and change it to Enabled.
3. Restart SQL Server for the changes to take effect.

Step 4: Attaching a Database

1. The SQL Server 2008 Management tools are almost identical to the SQL Server 2005 ones.  Open up the Management Tools and login to your database.  You will then see the following:
2. Right-click on the Databases node and select New Database to create a new database, or Attach to attach an existing one.   If you are attaching a database then click the Addbutton, browse for your .mdf file and click OK twice to attach the database.  You will be asked to also add the location of any Full-Text catalogs if they exist.

Step 5: Creating a Login for your database

By default, only Administrators have access to your database.  You need to now configure your database to allow Internet users access to read and (if required) write to your data.  You can either use Windows authentication or SQL Server authentication.  Windows authentication uses your local server or Active Directory user account to connect, and doesn’t require a password to be kept in the web.config file.  SQL Server authentication is easier to seperate your user accounts for different databases and applications easily.

To set up Windows Authentication:

On a standalone Windows 2008 Web Server, the ASP.NET service runs as the NT AUTHORITY\NETWORK SERVICE account.  You need to map this account to SQL Server:

1. In the SQL Server Management Tools, Right click on Logins under the Security Nodeand select New Login.
2. Type NT AUTHORITY\NETWORK SERVICE in the Login Name box and select Windows Authentication.  Click Ok.

To set up SQL Authentication:

1. In the SQL Server Management Tools, Right click on Logins under the Security Nodeand select New Login.
2. Type the name of your database user in the Login Name box and select SQL Authentication.  Set a password and untick Enforce Password Policy. Click Ok.

Step 6: Assigning the user permissions to your database

1. Now you need to assign this login the appropriate permissions in your database. To do this, expand the Databases node and then expand Security. Right-click the Usersnode and choose New User.
2. Give your Login whatever name you like and in the Login Name box type in either the name of your database user if using SQL authentication or NT AUTHORITY\NETWORK SERVICE if you are using Windows authentication (or browse to select your account).  Under Database Role Membership select db_datareader (and db_datawriter if your users are performing INSERT and UPDATE SQL queries).
3. Click OK.

Step 7: Configuring your database connection string

You can set your database connection string by either manually editing the web.config file, or using the IIS built in Connection Strings applet.

1. Open Internet Information Services (IIS) Manager from the Administrative Toolsstart menu.
2. Browse to your website and double-click on Connection Strings in the right hand pane.
3. Either Add a new connection string or right click an existing one to Edit.
4. Set the name of your server and database appropriately, and then choose the credentials to match your database user.  If you are using SQL Authentication then you need to set the username and password you configured.

That’s it!  I hope this guide helps, and please let me know if I’ve missed anything!

 

The Report Viewer Web Control HTTP Handler has not been registered in the application's web.config file

Add <add verb="*" path="Reserved.ReportViewerWebControl.axd" type = "Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" /> to the system.web/httpHandlers section of the web.config file.

If you are facing such error this may help you.just change your web config with this.

<?xml version="1.0"?>
<!--
    Note: As an alternative to hand editing this file you can use the
    web admin tool to configure settings for your application. Use
    the Website->Asp.Net Configuration option in Visual Studio.
    A full list of settings and comments can be found in
    machine.config.comments usually located in
    \Windows\Microsoft.Net\Framework\v2.x\Config
-->
<configuration>
    <configSections>
        <sectionGroup name="system.web.extensions"

type="System.Web.Configuration.SystemWebExtensionsSectionGroup, System.Web.Extensions,

Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
            <sectionGroup name="scripting"

type="System.Web.Configuration.ScriptingSectionGroup, System.Web.Extensions, Version=3.5.0.0,

Culture=neutral, PublicKeyToken=31BF3856AD364E35">
                <section name="scriptResourceHandler"

type="System.Web.Configuration.ScriptingScriptResourceHandlerSection, System.Web.Extensions,

Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false"

allowDefinition="MachineToApplication"/>
                <sectionGroup name="webServices"

type="System.Web.Configuration.ScriptingWebServicesSectionGroup, System.Web.Extensions,

Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
                    <section name="jsonSerialization"

type="System.Web.Configuration.ScriptingJsonSerializationSection, System.Web.Extensions,

Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false"

allowDefinition="Everywhere"/>
                    <section name="profileService"

type="System.Web.Configuration.ScriptingProfileServiceSection, System.Web.Extensions,

Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false"

allowDefinition="MachineToApplication"/>
                    <section name="authenticationService"

type="System.Web.Configuration.ScriptingAuthenticationServiceSection, System.Web.Extensions,

Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false"

allowDefinition="MachineToApplication"/>
                    <section name="roleService"

type="System.Web.Configuration.ScriptingRoleServiceSection, System.Web.Extensions,

Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false"

allowDefinition="MachineToApplication"/>
                </sectionGroup>
            </sectionGroup>
        </sectionGroup>
    </configSections>
    <system.net>

    </system.net>
  <appSettings/>
   <connectionStrings/>
    <system.web>
        <!--
            Set compilation debug="true" to insert debugging
            symbols into the compiled page. Because this
            affects performance, set this value to true only
            during development.
        -->
    <machineKey

validationKey='4C8B22744D71B73CE6B16262A97CF3862E584829229EDE45C9DF207DDF5F3507782A3F2F650608502

8628868F6AFFC25D16BE4AD8BB677637B11C892F95FE819'  

decryptionKey='53EA49CFEC5C5A1E4720DE83E1CC75337674757144EB1DCB'   validation='SHA1'/>
        <compilation debug="true">
            <assemblies>
                <add assembly="System.Core, Version=3.5.0.0, Culture=neutral,

PublicKeyToken=B77A5C561934E089"/>
                <add assembly="System.Web.Extensions, Version=3.5.0.0,

Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
                <add assembly="System.Data.DataSetExtensions, Version=3.5.0.0,

Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
                <add assembly="System.Xml.Linq, Version=3.5.0.0,

Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
                <add assembly="System.Design, Version=2.0.0.0, Culture=neutral,

PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="System.Web.Extensions.Design, Version=3.5.0.0,

Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
                <add assembly="System.Windows.Forms, Version=2.0.0.0,

Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
                <add assembly="System.Data.Linq, Version=3.5.0.0,

Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
                <add assembly="Microsoft.ReportViewer.WebForms, Version=9.0.0.0,

Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="Microsoft.ReportViewer.Common, Version=9.0.0.0,

Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="System.Net, Version=3.5.0.0, Culture=neutral,

PublicKeyToken=B03F5F7F11D50A3A"/></assemblies>
            <buildProviders>
                <add extension=".rdlc"

type="Microsoft.Reporting.RdlBuildProvider, Microsoft.ReportViewer.Common, Version=9.0.0.0,

Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
            </buildProviders>
        </compilation>
        <!--
            The <authentication> section enables configuration
            of the security authentication mode used by
            ASP.NET to identify an incoming user.
        -->
        <authentication mode="None"/>
        <customErrors mode="Off">
        </customErrors>
        <!--
            The <customErrors> section enables configuration
            of what to do if/when an unhandled error occurs
            during the execution of a request. Specifically,
            it enables developers to configure html error pages
            to be displayed in place of a error stack trace.

        <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
            <error statusCode="403" redirect="NoAccess.htm" />
            <error statusCode="404" redirect="FileNotFound.htm" />
        </customErrors>
        -->
        <pages>
            <namespaces>
                <clear/>
           
                <add namespace="System"/>
                <add namespace="System.Collections"/>
                <add namespace="System.Collections.Generic"/>
                <add namespace="System.Collections.Specialized"/>
                <add namespace="System.Configuration"/>
                <add namespace="System.Text"/>
                <add namespace="System.Text.RegularExpressions"/>
                <add namespace="System.Linq"/>
                <add namespace="System.Xml.Linq"/>
                <add namespace="System.Web"/>
                <add namespace="System.Web.Caching"/>
                <add namespace="System.Web.SessionState"/>
                <add namespace="System.Web.Security"/>
                <add namespace="System.Web.Profile"/>
                <add namespace="System.Web.UI"/>
                <add namespace="System.Web.UI.WebControls"/>
                <add namespace="System.Web.UI.WebControls.WebParts"/>
                <add namespace="System.Web.UI.HtmlControls"/>
            </namespaces>
            <controls>
                <add tagPrefix="asp" namespace="System.Web.UI"

assembly="System.Web.Extensions, &#xA;            Version=3.5.0.0, Culture=neutral,

PublicKeyToken=31BF3856AD364E35"/>
                <add tagPrefix="asp" namespace="System.Web.UI.WebControls"

assembly="System.Web.Extensions, &#xA;                  Version=3.5.0.0, Culture=neutral,

PublicKeyToken=31BF3856AD364E35"/>
                <add tagPrefix="AjaxToolkit" namespace="AjaxControlToolkit"

assembly="AjaxControlToolkit, Version=3.5.40412.0, &#xA;         Culture=neutral,

PublicKeyToken=28F01B0E84B6D53E"/>
            </controls>
        </pages>
        <httpHandlers>
            <remove path="*.asmx" verb="*"/>
            <add path="*.asmx" verb="*"

type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0,

Culture=neutral, PublicKeyToken=31BF3856AD364E35" validate="false"/>
            <add path="*_AppService.axd" verb="*"

type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0,

Culture=neutral, PublicKeyToken=31BF3856AD364E35" validate="false"/>
            <add path="ScriptResource.axd" verb="GET,HEAD"

type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0,

Culture=neutral, PublicKeyToken=31BF3856AD364E35" validate="false"/>
            <add path="Reserved.ReportViewerWebControl.axd" verb="*"

type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms,

Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" validate="false"/>
        </httpHandlers>
        <httpModules>
            <add name="ScriptModule" type="System.Web.Handlers.ScriptModule,

System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
        </httpModules>
    </system.web>
    <system.codedom>
        <compilers>
            <compiler language="c#;cs;csharp" extension=".cs"

type="Microsoft.CSharp.CSharpCodeProvider,System, Version=2.0.0.0, Culture=neutral,

PublicKeyToken=b77a5c561934e089" warningLevel="4">
                <providerOption name="CompilerVersion" value="v3.5"/>
                <providerOption name="WarnAsError" value="false"/>
            </compiler>
            <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb"

type="Microsoft.VisualBasic.VBCodeProvider, System, Version=2.0.0.0, Culture=neutral,

PublicKeyToken=b77a5c561934e089" warningLevel="4">
                <providerOption name="CompilerVersion" value="v3.5"/>
                <providerOption name="OptionInfer" value="true"/>
                <providerOption name="WarnAsError" value="false"/>
            </compiler>
        </compilers>
    </system.codedom>
    <system.webServer>
        <validation validateIntegratedModeConfiguration="false"/>
        <modules>
            <remove name="ScriptModule"/>
            <add name="ScriptModule" preCondition="managedHandler"

type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral,

PublicKeyToken=31BF3856AD364E35"/>
        </modules>
        <handlers>
            <remove name="WebServiceHandlerFactory-Integrated"/>
            <remove name="ScriptHandlerFactory"/>
            <remove name="ScriptHandlerFactoryAppServices"/>
            <remove name="ScriptResource"/>
            <add name="ScriptHandlerFactory" verb="*" path="*.asmx"

preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory,

System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
            <add name="ScriptHandlerFactoryAppServices" verb="*"

path="*_AppService.axd" preCondition="integratedMode"

type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0,

Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
            <add name="ScriptResource" verb="GET,HEAD" path="ScriptResource.axd"

preCondition="integratedMode" type="System.Web.Handlers.ScriptResourceHandler,

System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
        </handlers>
    </system.webServer>
    <runtime>
        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
            <dependentAssembly>
                <assemblyIdentity name="System.Web.Extensions"

publicKeyToken="31bf3856ad364e35"/>
                <bindingRedirect oldVersion="1.0.0.0-1.1.0.0"

newVersion="3.5.0.0"/>
            </dependentAssembly>
            <dependentAssembly>
                <assemblyIdentity name="System.Web.Extensions.Design"

publicKeyToken="31bf3856ad364e35"/>
                <bindingRedirect oldVersion="1.0.0.0-1.1.0.0"

newVersion="3.5.0.0"/>
            </dependentAssembly>
        </assemblyBinding>
    </runtime>
    <system.serviceModel>
        <behaviors>
            <serviceBehaviors>
                <behavior name="ServiceBehavior">
                    <serviceMetadata httpGetEnabled="true"/>
                    <serviceDebug includeExceptionDetailInFaults="false"/>
                </behavior>
            </serviceBehaviors>
        </behaviors>
        <services>
            <service behaviorConfiguration="ServiceBehavior" name="Service">
                <endpoint address="" binding="wsHttpBinding"

contract="IService">
                    <identity>
                        <dns value="localhost"/>
                    </identity>
                </endpoint>
                <endpoint address="mex" binding="mexHttpBinding"

contract="IMetadataExchange"/>
            </service>
        </services>
    </system.serviceModel>
</configuration>

RDLC Basics

Basic Example Of  RDLC  (Report Definition Language Client-side)

To Understand From The Basic Let Start From Here..

            We have two tables

              1. MasterProduct

Brand	Product	ID
Brand1	Product1	101
Brand2	Product1	102
Brand3	Product2	103
Brand1	Product2	104
Brand4	Product3	105
Brand3	Product4	106

                  *ID is primary key here

       2. Transaction            

State	Quantity	ID
State1	10	101
State2	20	103
State3	40	104
State1	60	102
State2	40	101
State4	80	106

                         *Here ID is foreign key reference to ID of MasterProduct Table

Now Question is:

We want the below given output where State, Product1,2,3,4 are columns if we add product then column added and if we add state then it added to state column. It takes the quantity from Transaction and show in the rows accordingly.

And If You Have Opportunity to 

Use any of the data control of asp.net to show the output...I Suggest Use RDLC(report control of .net)

OUTPUT Will be:

State	Product1	Product2	Product3	Product4
State1	10+60=70
State2	40	20
State3		40
State4				80

use namespace...
using Microsoft.Reporting.WebForms;


Use function() Like

  public void BindReport()
    {
        SqlCommand cmd = new SqlCommand("spTest", con);
        cmd.CommandType = CommandType.StoredProcedure;
        if (con.State == ConnectionState.Closed)
            con.Open();

        DataSet ds = new DataSet();
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        adp.Fill(ds);

        ReportDataSource rds = new ReportDataSource("DataSet1_spTest", ds.Tables[0]);
      
        ReportViewer1.LocalReport.DataSources.Clear();

        ReportViewer1.LocalReport.DataSources.Add(rds);
      
        ReportViewer1.LocalReport.Refresh();
    }

But How to add change RDLC at RUN Time??