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.SqlC onnection SQLConnection;
protected System.Data.SqlClient.SqlC ommand 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.SqlC onnection( );
this.QueryResults = new System.Data.SqlClient.SqlC ommand();
//
// SQLConnection
//
this.SQLConnection.Connect ionString = "workstation id=\"*****\";user id=********;data source=\"*******\";persist security info=True;initial catalog=*******;" +
"password=**********";
//
// QueryResults
//
this.QueryResults.CommandT ext = "dbo.[sp_TDQueryResults]";
this.QueryResults.CommandT ype = System.Data.CommandType.St oredProced ure;
this.QueryResults.Connecti on = this.SQLConnection;
this.QueryResults.Paramete rs.Add(new System.Data.SqlClient.SqlP arameter(" @RETURN_VA LUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirec tion.Retur nValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion .Current, null));
this.QueryResults.Paramete rs.Add(new System.Data.SqlClient.SqlP arameter(" @SearchPhr ase", System.Data.SqlDbType.VarC har, 100, System.Data.ParameterDirec tion.Input , false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion .Current, "harry potter"));
this.Load += new System.EventHandler(this.P age_Load);
}
#endregion
}
}
<body>
<form id="ReturnResults" method="post" runat="server">
<asp:DataGrid id="SearchResults" runat="server"></asp:DataG rid></form >
</body>
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.SqlC
protected System.Data.SqlClient.SqlC
protected System.Web.UI.WebControls.
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.SqlC
this.QueryResults = new System.Data.SqlClient.SqlC
//
// SQLConnection
//
this.SQLConnection.Connect
"password=**********";
//
// QueryResults
//
this.QueryResults.CommandT
this.QueryResults.CommandT
this.QueryResults.Connecti
this.QueryResults.Paramete
this.QueryResults.Paramete
this.Load += new System.EventHandler(this.P
}
#endregion
}
}
<body>
<form id="ReturnResults" method="post" runat="server">
<asp:DataGrid id="SearchResults" runat="server"></asp:DataG
</body>
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
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.StoredProcedur e;
cmd.Connection = cn;
cmd.Parameters.Add(new System.Data.SqlClient.SqlP arameter(" @RETURN_VA LUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirec tion.Retur nValue,
false, ((System.Byte)(0)),
((System.Byte)(0)), "", System.Data.DataRowVersion .Current, null));
cmd.Parameters.Add(new System.Data.SqlClient.SqlP arameter(" @SearchPhr ase",
System.Data.SqlDbType.VarC har, 100, System.Data.ParameterDirec tion.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;
}
}
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
"user id=********;data source=\"*******\";persist
"initial catalog=*******;password=*
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "dbo.[sp_TDQueryResults]";
cmd.CommandType = CommandType.StoredProcedur
cmd.Connection = cn;
cmd.Parameters.Add(new System.Data.SqlClient.SqlP
System.Data.SqlDbType.Int,
false, ((System.Byte)(0)),
((System.Byte)(0)), "", System.Data.DataRowVersion
cmd.Parameters.Add(new System.Data.SqlClient.SqlP
System.Data.SqlDbType.VarC
false, ((System.Byte)(0)),
((System.Byte)(0)), "", System.Data.DataRowVersion
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.StoredProcedur e;
cmd.Connection = cn;
cmd.Parameters.Add(new System.Data.SqlClient.SqlP arameter(" @RETURN_VA LUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirec tion.Retur nValue,
false, ((System.Byte)(0)),
((System.Byte)(0)), "", System.Data.DataRowVersion .Current, null));
cmd.Parameters.Add(new System.Data.SqlClient.SqlP arameter(" @SearchPhr ase",
System.Data.SqlDbType.VarC har, 100, System.Data.ParameterDirec tion.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;
}
}
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
"user id=********;data source=\"*******\";persist
"initial catalog=*******;password=*
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "dbo.[sp_TDQueryResults]";
cmd.CommandType = CommandType.StoredProcedur
cmd.Connection = cn;
cmd.Parameters.Add(new System.Data.SqlClient.SqlP
System.Data.SqlDbType.Int,
false, ((System.Byte)(0)),
((System.Byte)(0)), "", System.Data.DataRowVersion
cmd.Parameters.Add(new System.Data.SqlClient.SqlP
System.Data.SqlDbType.VarC
false, ((System.Byte)(0)),
((System.Byte)(0)), "", System.Data.DataRowVersion
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
dg.DataSource = ds;
dg.DataBind();
}
catch(SqlException ex)
{
throw ex;
}
}
ASKER
Hi sukumar_diya
I tried your code and receive the following error:
c:\***\VisualStudioWeb\sea rch\result s.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\se arch\resul ts.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
I tried your code and receive the following error:
c:\***\VisualStudioWeb\sea
c:\****\VisualStudioWeb\se
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
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
ASKER
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
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 :-)
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.
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 :-)
ASKER
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.SqlD ataAdapter sqlDataAdapter1;
protected System.Data.SqlClient.SqlC ommand sqlSelectCommand1;
protected System.Data.SqlClient.SqlC onnection sqlConnection1;
protected VisualStudioWeb.search.Ret urnQueryRe sults returnQueryResults1;
protected System.Data.SqlClient.SqlC onnection 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.SqlC onnection( );
this.sqlDataAdapter1 = new System.Data.SqlClient.SqlD ataAdapter ();
this.sqlSelectCommand1 = new System.Data.SqlClient.SqlC ommand();
this.sqlConnection1 = new System.Data.SqlClient.SqlC onnection( );
this.returnQueryResults1 = new VisualStudioWeb.search.Ret urnQueryRe sults();
((System.ComponentModel.IS upportInit ialize)(th is.returnQ ueryResult s1)).Begin Init();
//
// sqlDataAdapter1
//
this.sqlDataAdapter1.Selec tCommand = this.sqlSelectCommand1;
this.sqlDataAdapter1.Table Mappings.A ddRange(ne w System.Data.Common.DataTab leMapping[ ] {
new System.Data.Common.DataTab leMapping( "Table", "sp_TDQueryResults", new System.Data.Common.DataCol umnMapping [] {
new System.Data.Common.DataCol umnMapping ("PageTitl e", "PageTitle"),
new System.Data.Common.DataCol umnMapping ("PageDesc ription", "PageDescription"),
new System.Data.Common.DataCol umnMapping ("PageSize ", "PageSize"),
new System.Data.Common.DataCol umnMapping ("PageURL" , "PageURL")}),
new System.Data.Common.DataTab leMapping( "Table1", "Table1", new System.Data.Common.DataCol umnMapping [] {
new System.Data.Common.DataCol umnMapping ("PageTitl e", "PageTitle"),
new System.Data.Common.DataCol umnMapping ("PageDesc ription", "PageDescription"),
new System.Data.Common.DataCol umnMapping ("PageSize ", "PageSize"),
new System.Data.Common.DataCol umnMapping ("PageURL" , "PageURL")})});
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.Com mandText = "[sp_TDQueryResults]";
this.sqlSelectCommand1.Com mandType = System.Data.CommandType.St oredProced ure;
this.sqlSelectCommand1.Con nection = this.sqlConnection1;
this.sqlSelectCommand1.Par ameters.Ad d(new System.Data.SqlClient.SqlP arameter(" @RETURN_VA LUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirec tion.Retur nValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion .Current, null));
this.sqlSelectCommand1.Par ameters.Ad d(new System.Data.SqlClient.SqlP arameter(" @SearchPhr ase", System.Data.SqlDbType.VarC har, 100));
//
// sqlConnection1
//
this.sqlConnection1.Connec tionString = "workstation id=\"SERVER\";packet size=4096;user id=****************;data so" +
"urce=\"***********\";pers ist security info=True;initial catalog=***********;" +
"password=**********";
//
// returnQueryResults1
//
this.returnQueryResults1.D ataSetName = "ReturnQueryResults";
this.returnQueryResults1.L ocale = new System.Globalization.Cultu reInfo("en -GB");
this.Load += new System.EventHandler(this.P age_Load);
((System.ComponentModel.IS upportInit ialize)(th is.returnQ ueryResult s1)).EndIn it();
}
#endregion
}
}
<form id="Form1" method="post" runat="server">
<asp:datagrid id=SearchResults runat="server" DataSource="<%# returnQueryResults1 %>" ShowHeader="False" DataMember="sp_TDQueryResu lts1" AllowCustomPaging="True" AllowPaging="True" CellPadding="0" GridLines="None">
<Columns>
<asp:BoundColumn DataField="PageTitle" SortExpression="PageTitle" HeaderText="PageTitle"></a sp:BoundCo lumn>
<asp:BoundColumn DataField="PageDescription " SortExpression="PageDescri ption" HeaderText="PageDescriptio n"></asp:B oundColumn >
<asp:BoundColumn DataField="PageSize" SortExpression="PageSize" HeaderText="PageSize"></as p:BoundCol umn>
<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
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.
protected System.Data.SqlClient.SqlD
protected System.Data.SqlClient.SqlC
protected System.Data.SqlClient.SqlC
protected VisualStudioWeb.search.Ret
protected System.Data.SqlClient.SqlC
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.SqlC
this.sqlDataAdapter1 = new System.Data.SqlClient.SqlD
this.sqlSelectCommand1 = new System.Data.SqlClient.SqlC
this.sqlConnection1 = new System.Data.SqlClient.SqlC
this.returnQueryResults1 = new VisualStudioWeb.search.Ret
((System.ComponentModel.IS
//
// sqlDataAdapter1
//
this.sqlDataAdapter1.Selec
this.sqlDataAdapter1.Table
new System.Data.Common.DataTab
new System.Data.Common.DataCol
new System.Data.Common.DataCol
new System.Data.Common.DataCol
new System.Data.Common.DataCol
new System.Data.Common.DataTab
new System.Data.Common.DataCol
new System.Data.Common.DataCol
new System.Data.Common.DataCol
new System.Data.Common.DataCol
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.Com
this.sqlSelectCommand1.Com
this.sqlSelectCommand1.Con
this.sqlSelectCommand1.Par
this.sqlSelectCommand1.Par
//
// sqlConnection1
//
this.sqlConnection1.Connec
"urce=\"***********\";pers
"password=**********";
//
// returnQueryResults1
//
this.returnQueryResults1.D
this.returnQueryResults1.L
this.Load += new System.EventHandler(this.P
((System.ComponentModel.IS
}
#endregion
}
}
<form id="Form1" method="post" runat="server">
<asp:datagrid id=SearchResults runat="server" DataSource="<%# returnQueryResults1 %>" ShowHeader="False" DataMember="sp_TDQueryResu
<Columns>
<asp:BoundColumn DataField="PageTitle" SortExpression="PageTitle"
<asp:BoundColumn DataField="PageDescription
<asp:BoundColumn DataField="PageSize" SortExpression="PageSize" HeaderText="PageSize"></as
<asp:TemplateColumn HeaderText="PageURL">
<ItemTemplate>
<asp:Label runat="server" Text='<%# DataBinder.Eval(Container,
</asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" Text='<%# DataBinder.Eval(Container,
</asp:TextBox>
</EditItemTemplate>
</asp:TemplateColumn>
</Columns>
<PagerStyle NextPageText="Next" PrevPageText="Previous"></
</asp:datagrid></form>
Caz
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.AutoGenerate
SearchResults.DataSource = dtTbl;
SearchResults.DataBind();
}
Sukumar :-)