Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DataBind to DataGrid

Posted on 2004-10-28
10
Medium Priority
?
759 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
Comment
Question by:BeginningWebDesign
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 7

Expert Comment

by:sukumar_diya
ID: 12441593
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
ID: 12441976
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
ID: 12442187
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 13

Expert Comment

by:dungla
ID: 12442276
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
ID: 12442503
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
ID: 12442759
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
ID: 12443026
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
ID: 12443232
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
ID: 12443653
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:
sukumar_diya earned 2000 total points
ID: 12444031
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

636 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