Moiz Saifuddin
asked on
datagrid
I have a stored procedure that accepts 3 param (userid,fromdate,todate) from table "JobCard"
and it displays 3 columns from this table(Ticket#,Description, Duration).
i want to call it from c#.net and pass these 3 params and fill the result dataset in a datagrid
and it displays 3 columns from this table(Ticket#,Description,
i want to call it from c#.net and pass these 3 params and fill the result dataset in a datagrid
CREATE PROCEDURE spGetJobCardDetails
@UserId INT,
@FromDate DATETIME,
@ToDate DATETIME
AS
BEGIN
SELECT [Ticket#], [Description], [Duration]
FROM JobCard
WHERE [UserId] = @UserId
AND [Date] BETWEEN @FromDate AND @ToDate
END
ASKER
I have posted code below for pulling data from stored proc into a dataset into grid.
The grid is not appearing and page is running without error. How can i check if the dataset is empty or not or if the grid property do i need to set any field for this.
The grid is not appearing and page is running without error. How can i check if the dataset is empty or not or if the grid property do i need to set any field for this.
SqlCommand com = new SqlCommand();
com.Connection = DB.Connection;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "TestProc";
com.Connection.Open();
SqlParameter UserID = new SqlParameter("@UserId", Session["UID"].ToString());
UserID.DbType = DbType.String;
com.Parameters.Add(UserID);
//parameter 2-Ticket Date
SqlParameter FromDate1 = new SqlParameter("@FromDate", DateTime.Now.ToString());
FromDate1.DbType = DbType.DateTime;
com.Parameters.Add(FromDate1);
//parameter 3-Ticket Time
SqlParameter ToDate1 = new SqlParameter("@ToDate", DateTime.Now.ToString());
ToDate1.DbType = DbType.DateTime;
com.Parameters.Add(ToDate1);
com.ExecuteNonQuery();
//DataTable dt = new DataTable();
DataSet ds = new DataSet();
// Create the data adapter that communicates with SQL server
SqlDataAdapter da = new SqlDataAdapter(com);
// Fill the data table
da.Fill(ds);
// Assign the data table to the data grid
GridView2.DataSource = ds;
GridView2.DataBind();
com.Connection.Close();
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked, points are yours buddy. One problem on allowpaging when i set to true, the error is produced on selecting the page 2. Screenshot attached saying table not found on selecting page >1
PS: The above code I am running on button_click
indexoutofrange.JPG
PS: The above code I am running on button_click
indexoutofrange.JPG
ASKER
Below is entire code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
namespace FunctionalTimeSheet
{
public partial class MyGrid : System.Web.UI.Page
{
public DBCon DB = new DBCon();
DataSet ds = new DataSet();
protected void Page_Load(object sender, EventArgs e)
{
DB.GetConnection();
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlCommand com = new SqlCommand();
com.Connection = DB.Connection;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "TestProc";
com.Connection.Open();
SqlParameter UserID = new SqlParameter("@UserId", Session["UID"].ToString());
UserID.DbType = DbType.String;
com.Parameters.Add(UserID);
//parameter 2-Ticket Date
SqlParameter FromDate1 = new SqlParameter("@FromDate", "2013-03-14 00:00:00.000");
FromDate1.DbType = DbType.DateTime;
com.Parameters.Add(FromDate1);
//parameter 3-Ticket Time
SqlParameter ToDate1 = new SqlParameter("@ToDate", "2014-03-14 00:00:00.000");
ToDate1.DbType = DbType.DateTime;
com.Parameters.Add(ToDate1);
//com.ExecuteNonQuery();
//DataTable dt = new DataTable();
/////DataSet ds = new DataSet();
// Create the data adapter that communicates with SQL server
SqlDataAdapter da = new SqlDataAdapter(com);
// Fill the data table
da.Fill(ds);
// Assign the data table to the data grid
GridView2.DataSource = ds.Tables[0];
GridView2.DataBind();
com.Connection.Close();
}
protected void GridView2_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView2.PageIndex= e.NewPageIndex;
//Bind the DataGrid again with the Data Source
GridView2.DataSource = ds.Tables[0];
GridView2.DataBind();
}
}
}
ASKER
Because a Web application is stateless you need to reload the data back in to the DataSet or store the data between invocation so that it is available when the page is reloaded on the Button click.
Something like the following code snippet should produce the wanted results
Open in new window