Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

datagrid

Posted on 2014-03-12
7
Medium Priority
?
258 Views
Last Modified: 2014-03-14
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

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

Open in new window

0
Comment
Question by:Moizsaif123
[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
  • 3
7 Comments
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 39924954
Hi Moizsaif123;

Something like the following code snippet should produce the wanted results

// Set up the parameters to be passed into the stored procedure
int userId = 222;
DateTime from = DataTime.Parse("1/3/2014");
DateTime to = DataTime.Parse("2/4/2014");

// Create the connection string to connect to the database
string connStr = @"Your ConnectionString goes here";
// Create a connection to the database
using(SqlConnection conn = new SqlConnection(connStr))
{
	  // Create the command to execute on the server
    SqlCommand cmd = conn.CreateCommand();
    // The stored procedure to be called
    cmd.CommandText = "[dbo].[spGetJobCardDetails]";
    // Initialize the parameters to be sent
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@UserId", SqlDbType.Int);
    cmd.Parameters["@UserId"].Value = userId;
    cmd.Parameters.Add("@FromDate", SqlDbType.DateTime2);
    cmd.Parameters["@FromDate"].Value = from;
    cmd.Parameters.Add("@ToDate", SqlDbType.DateTime2);
    cmd.Parameters["@ToDate"].Value = to;
    // Initialize the DataTable that will hold the data
    DataTable dt = new DataTable();
    // Create the data adapter that communicates with SQL server
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    // Fill the data table
    da.Fill(dt);
    // Assign the data table to the data grid
    dataGridView1.DataSource = dt;
}

Open in new window

0
 
LVL 4

Author Comment

by:Moizsaif123
ID: 39926284
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.


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();

Open in new window

0
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 39926602
Hi Moizsaif123;

Please see the two parts of the code surrounded with //========= and //========= for needed changes.

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

// =====================================================================
// This should not be in the code. The SqlDataAdapter will be executing
// the Command on the server. 
// 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);

// =====================================================================
// The DataSet can hold many DataTable's so you will need to tell it which one
// Assign the data table to the data grid
GridView2.DataSource = ds.Tables[0];
// =====================================================================

GridView2.DataBind();
com.Connection.Close();

Open in new window

1
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Author Comment

by:Moizsaif123
ID: 39928388
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
0
 
LVL 4

Author Comment

by:Moizsaif123
ID: 39928414
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();
        }
    }
}

Open in new window

0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 39929216
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.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

722 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