Solved

DataBind to DataGrid

Posted on 2004-10-28
737 Views
Last Modified: 2008-02-01
Hi

I'm currently taking a crash course with Visual Studio, can anyone tell me how to do the following:

1)DataBind the code below to a datagrid with ID=SearchResults
2)Is there anyway to drag and drop the values of a stored procedure into the datagrid

Code

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace VisualStudioWeb.search
{
      /// <summary>
      /// Summary description for results.
      /// </summary>
      public class results : System.Web.UI.Page
      {
            protected System.Data.SqlClient.SqlConnection SQLConnection;
            protected System.Data.SqlClient.SqlCommand QueryResults;
            protected System.Web.UI.WebControls.DataGrid MyGrid;
      
            private void Page_Load(object sender, System.EventArgs e)
            {
                  

            }

            #region Web Form Designer generated code
            override protected void OnInit(EventArgs e)
            {
                  //
                  // CODEGEN: This call is required by the ASP.NET Web Form Designer.
                  //
                  InitializeComponent();
                  base.OnInit(e);
            }
            
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InitializeComponent()
            {    
                  this.SQLConnection = new System.Data.SqlClient.SqlConnection();
                  this.QueryResults = new System.Data.SqlClient.SqlCommand();
                  //
                  // SQLConnection
                  //
                  this.SQLConnection.ConnectionString = "workstation id=\"*****\";user id=********;data source=\"*******\";persist security info=True;initial catalog=*******;" +
                        "password=**********";
                  //
                  // QueryResults
                  //
                  this.QueryResults.CommandText = "dbo.[sp_TDQueryResults]";
                  this.QueryResults.CommandType = System.Data.CommandType.StoredProcedure;
                  this.QueryResults.Connection = this.SQLConnection;
                  this.QueryResults.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
                  this.QueryResults.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SearchPhrase", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, "harry potter"));
                  this.Load += new System.EventHandler(this.Page_Load);

            }
            #endregion
      }
}




         <body>
            <form id="ReturnResults" method="post" runat="server">
                  <asp:DataGrid id="SearchResults" runat="server"></asp:DataGrid></form>
      </body>
0
Question by:BeginningWebDesign
    10 Comments
     
    LVL 7

    Expert Comment

    by:sukumar_diya
    Hi BeginningWebDesign,

    Use the sqldataadapter to fill the datatable and bind the datatable to the datagrid.

    if u set the AutoGenerateColumns to true the columns will be automatically generated.

    check this....
    private void Page_Load(object sender, System.EventArgs e)
                      {
                   SqlDataAdapter daSP=new SqlDataAdapter(cmdObj);
                         DataTable dtTbl= new DataTable();
                         daSP.Fill(dtTbl);
                         SearchResults.AutoGenerateColumns=true;
                         SearchResults.DataSource = dtTbl;
                         SearchResults.DataBind();
                        

                      }


    Sukumar :-)
    0
     

    Author Comment

    by:BeginningWebDesign
    Thanks Sukumar

    I'll try that, problem with being thrown in at the deep end is it's hard to work out how to use the software and also how to use other peoples code, that's why I decided to work out how to create the connection and datagrid. Once I get that bit to work, I'll start submitting pieces of code and ask how to use it within my webpage so that I can work it out myself

    caz
    0
     
    LVL 13

    Expert Comment

    by:dungla
    Hi,

    I have following function maybe resolved your first question about binding data (customized for your needed). Can you explain more about the second question? What is your expectation?

    private void BindData(DataGrid dg)
                {
                      try
                      {
                            SqlConnection cn = new SqlConnection("workstation id=\"*****\";" +
                                  "user id=********;data source=\"*******\";persist security info=True;" +
                                  "initial catalog=*******;password=**********");
                            SqlCommand cmd = new SqlCommand();
                            cmd.CommandText = "dbo.[sp_TDQueryResults]";
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Connection = cn;
                            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
                                  System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
                                  false, ((System.Byte)(0)),
                                  ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
                            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SearchPhrase",
                                  System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input,
                                  false, ((System.Byte)(0)),
                                  ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, "harry potter"));

                            DataSet ds = new DataSet();
                            SqlDataAdapter da = new SqlDataAdapter(cmd);
                            da.Fill(ds);
                      }
                      catch(SqlException ex)
                      {
                            throw ex;
                      }
                }
    0
     
    LVL 13

    Expert Comment

    by:dungla
    Hi,

    I have following function maybe resolved your first question about binding data (customized for your needed). Can you explain more about the second question? What is your expectation?

    private void BindData(DataGrid dg)
              {
                   try
                   {
                        SqlConnection cn = new SqlConnection("workstation id=\"*****\";" +
                             "user id=********;data source=\"*******\";persist security info=True;" +
                             "initial catalog=*******;password=**********");
                        SqlCommand cmd = new SqlCommand();
                        cmd.CommandText = "dbo.[sp_TDQueryResults]";
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Connection = cn;
                        cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
                             System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
                             false, ((System.Byte)(0)),
                             ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
                        cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SearchPhrase",
                             System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input,
                             false, ((System.Byte)(0)),
                             ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, "harry potter"));

                        DataSet ds = new DataSet();
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(ds);
                            dg.DataSource = ds;
                            dg.DataBind();
                   }
                   catch(SqlException ex)
                   {
                        throw ex;
                   }
              }
    0
     

    Author Comment

    by:BeginningWebDesign
    Hi sukumar_diya

    I tried your code and receive the following error:

    c:\***\VisualStudioWeb\search\results.aspx.cs(25): The type or namespace name 'SqlDataAdapter' could not be found (are you missing a using directive or an assembly reference?)

    c:\****\VisualStudioWeb\search\results.aspx.cs(27): The type or namespace name 'daSP' could not be found (are you missing a using directive or an assembly reference?)

    Hi dungla

    I also receive the 1st error with your code, please note my experience with Visual Studio is 2 days, I have used Dreamweaver most of the time.

    Ref question2: 2)Is there anyway to drag and drop the values of a stored procedure into the datagrid

    In Dreamweaver to create a dataset all I needed to do was click on the dataset tab select the stored procedure enter the parameters and Dreamweaver would craete all the code as well as connect to the web config file, you could then select the dataset you created open it and drag and drop the columns from the dataset onto the web form, I cannot seem to be able to do that with Visual Studio

    Caz



    0
     
    LVL 7

    Expert Comment

    by:sukumar_diya
    Hi Caz,
    Add these name spaces at the top your code behind..

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


    U can see the dataset in the toolbox under "Data". you can mention the table name and columns for it and you can mention select the dataset name in datagrid's datasource properties. You can use the 'property builder' link under the datagrid properties window to select columns to display and give styles for each column.

    sukumar
    0
     

    Author Comment

    by:BeginningWebDesign
    Hi sukumar

    No matter how I try I cannot get it to work, althrough it throws no errors, the datagrid does not display any details.

    caz
    0
     
    LVL 7

    Expert Comment

    by:sukumar_diya
    hi caz,
    ok, try the following ...

    1) in codebehind go to the line  SearchResults.DataSource and put a break point.
        for a break point go to the line and press ctrl + F9
        you will see a brown background on that line
    2) run your application by pressing F5
    3) your application will break to the source when it tries to execute the particular line.
    4) Press ctrl+alt+I. You will be prombted with a command window from the bottom of the screen.
    5) type ?dtTbl.Rows.Count and press enter
        Note: dtTbl is the datatable name. if u r using dataset then datasename.Tables[0].Rows.Count
        when u r typing visual studio will help u with intelicense list.
    6) after pressing enter u will see number rows in the datatable.
    7) if it is 0 then there is no data returned from your datasource ( sql or oracle ..)
    8) if it is greater than zero then check whether u have SearchResults.DataBind().
    9) if it is there check if u have any try{....} catch block and catch block left without any code. If so try to find the exception

    still no luck then try to post some code to us. we will try with that ....


    Cheers !!!
    Sukumar :-)
    0
     

    Author Comment

    by:BeginningWebDesign
    Hi Sukumar

    I have enclosed full code behind below with datagrid, I have deleted the page and restarted trying to understand the code. It has no DataBind()

    using System;
    using System.Collections;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Web;
    using System.Web.SessionState;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.HtmlControls;

    namespace VisualStudioWeb.search
    {
          /// <summary>
          /// Summary description for results.
          /// </summary>
          public class results : System.Web.UI.Page
          {
                protected System.Web.UI.WebControls.DataGrid SearchResults;
                protected System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
                protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;
                protected System.Data.SqlClient.SqlConnection sqlConnection1;
                protected VisualStudioWeb.search.ReturnQueryResults returnQueryResults1;
                protected System.Data.SqlClient.SqlConnection DataBaseConnection;
          
                private void Page_Load(object sender, System.EventArgs e)
                {
                      // Put user code to initialize the page here
                }

                #region Web Form Designer generated code
                override protected void OnInit(EventArgs e)
                {
                      //
                      // CODEGEN: This call is required by the ASP.NET Web Form Designer.
                      //
                      InitializeComponent();
                      base.OnInit(e);
                }
                
                /// <summary>
                /// Required method for Designer support - do not modify
                /// the contents of this method with the code editor.
                /// </summary>
                private void InitializeComponent()
                {    
                      this.DataBaseConnection = new System.Data.SqlClient.SqlConnection();
                      this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
                      this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
                      this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
                      this.returnQueryResults1 = new VisualStudioWeb.search.ReturnQueryResults();
                      ((System.ComponentModel.ISupportInitialize)(this.returnQueryResults1)).BeginInit();
                      //
                      // sqlDataAdapter1
                      //
                      this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
                      this.sqlDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
                                                                                                                                                            new System.Data.Common.DataTableMapping("Table", "sp_TDQueryResults", new System.Data.Common.DataColumnMapping[] {
                                                                                                                                                                                                                                                                                                                                           new System.Data.Common.DataColumnMapping("PageTitle", "PageTitle"),
                                                                                                                                                                                                                                                                                                                                           new System.Data.Common.DataColumnMapping("PageDescription", "PageDescription"),
                                                                                                                                                                                                                                                                                                                                           new System.Data.Common.DataColumnMapping("PageSize", "PageSize"),
                                                                                                                                                                                                                                                                                                                                           new System.Data.Common.DataColumnMapping("PageURL", "PageURL")}),
                                                                                                                                                            new System.Data.Common.DataTableMapping("Table1", "Table1", new System.Data.Common.DataColumnMapping[] {
                                                                                                                                                                                                                                                                                                                             new System.Data.Common.DataColumnMapping("PageTitle", "PageTitle"),
                                                                                                                                                                                                                                                                                                                             new System.Data.Common.DataColumnMapping("PageDescription", "PageDescription"),
                                                                                                                                                                                                                                                                                                                             new System.Data.Common.DataColumnMapping("PageSize", "PageSize"),
                                                                                                                                                                                                                                                                                                                             new System.Data.Common.DataColumnMapping("PageURL", "PageURL")})});
                      //
                      // sqlSelectCommand1
                      //
                      this.sqlSelectCommand1.CommandText = "[sp_TDQueryResults]";
                      this.sqlSelectCommand1.CommandType = System.Data.CommandType.StoredProcedure;
                      this.sqlSelectCommand1.Connection = this.sqlConnection1;
                      this.sqlSelectCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
                      this.sqlSelectCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SearchPhrase", System.Data.SqlDbType.VarChar, 100));
                      //
                      // sqlConnection1
                      //
                      this.sqlConnection1.ConnectionString = "workstation id=\"SERVER\";packet size=4096;user id=****************;data so" +
                            "urce=\"***********\";persist security info=True;initial catalog=***********;" +
                            "password=**********";
                      //
                      // returnQueryResults1
                      //
                      this.returnQueryResults1.DataSetName = "ReturnQueryResults";
                      this.returnQueryResults1.Locale = new System.Globalization.CultureInfo("en-GB");
                      this.Load += new System.EventHandler(this.Page_Load);
                      ((System.ComponentModel.ISupportInitialize)(this.returnQueryResults1)).EndInit();

                }
                #endregion
          }
    }



    <form id="Form1" method="post" runat="server">
    <asp:datagrid id=SearchResults runat="server" DataSource="<%# returnQueryResults1 %>" ShowHeader="False" DataMember="sp_TDQueryResults1" AllowCustomPaging="True" AllowPaging="True" CellPadding="0" GridLines="None">
    <Columns>
    <asp:BoundColumn DataField="PageTitle" SortExpression="PageTitle" HeaderText="PageTitle"></asp:BoundColumn>
    <asp:BoundColumn DataField="PageDescription" SortExpression="PageDescription" HeaderText="PageDescription"></asp:BoundColumn>
    <asp:BoundColumn DataField="PageSize" SortExpression="PageSize" HeaderText="PageSize"></asp:BoundColumn>
    <asp:TemplateColumn HeaderText="PageURL">
    <ItemTemplate>
    <asp:Label runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.PageURL") %>'>
    </asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.PageURL") %>'>
    </asp:TextBox>
    </EditItemTemplate>
    </asp:TemplateColumn>
    </Columns>
    <PagerStyle NextPageText="Next" PrevPageText="Previous"></PagerStyle>
    </asp:datagrid></form>

    Caz
    0
     
    LVL 7

    Accepted Solution

    by:
    hi caz,
    you have to bind the data using datagridname.DataBind();
    Please check with this....I will answer your query tomorrow...
    Bye
    take care
    sukumar
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
    This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    This video discusses moving either the default database or any database to a new volume.

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now