Link to home
Start Free TrialLog in
Avatar of BeginningWebDesign
BeginningWebDesign

asked on

DataBind to DataGrid

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>
Avatar of sukumar_diya
sukumar_diya
Flag of India image

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 :-)
Avatar of BeginningWebDesign
BeginningWebDesign

ASKER

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
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;
                  }
            }
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;
               }
          }
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



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
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
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 :-)
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
ASKER CERTIFIED SOLUTION
Avatar of sukumar_diya
sukumar_diya
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial