The DataGrid does not show data

sams · June 19th, 2007, 04:44 AM

I want to call a stored procedure named select_prod and the DataGrid should be filled with the result of the stored procedure. But it doesn't happen. The DataGrid appears empty.Please go through the code given below and help me out.

********
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;
using System.Web.Configuration ;
using System.Data.SqlClient;
using System.Configuration;
namespace Practice
{
    /// <summary>
    /// Summary description for WebForm2.
    /// </summary>
    public class WebForm2 : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.DataGrid DataGrid1;

        private void Page_Load(object sender, System.EventArgs e)
        {
            if(!Page.IsPostBack)
            {
                BindData();
            }

        }
        public void BindData()
        {

            string connstr = (string)ConfigurationSettings.AppSettings["ConString"];
            SqlConnection con = new SqlConnection();
            con.ConnectionString=connstr;
            con.Open();

            SqlCommand myCommand = new SqlCommand("select_prod", con);
            myCommand.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
            DataSet ds = new DataSet();
            myAdapter.Fill(ds,"prod");
            //con.Open();
            int n=myCommand.ExecuteNonQuery();
            DataGrid1.DataSource = ds;
            DataGrid1.DataBind();
            con.Close();
        }

        #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.Load += new System.EventHandler(this.Page_Load);

        }
        #endregion
    }
}
*********
the stored procedure is as follows

create procedure select_prod as select * from products

mike_remember · June 19th, 2007, 04:57 AM

Hi

The code that you have sent works fine at my end, seems some problem with the HTML part, please copy & paste the entire HTML code for the page.

Regards
Mike

Don't expect too much, too soon.

sams · June 19th, 2007, 11:06 PM

Hello Mike

The HTML code is shown below:
* * * * * *
<%@ Page language="c#" Codebehind="WebForm2.aspx.cs" AutoEventWireup="false" Inherits="Practice.WebForm2" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
    <HEAD>
        <title>WebForm2</title>
        <meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
        <meta content="C#" name="CODE_LANGUAGE">
        <meta content="JavaScript" name="vs_defaultClientScript">
        <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
    </HEAD>
    <body MS_POSITIONING="GridLayout">
        <form id="Form1" method="post" runat="server">
            <asp:datagrid id="DataGrid1" style="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 27px" runat="server"
                AutoGenerateColumns="False" Width="432px" Height="268px" BorderColor="White" BorderStyle="Ridge"
                CellSpacing="1" BorderWidth="2px" BackColor="White" CellPadding="3" GridLines="None">
                <SelectedItemStyle Font-Bold="True" ForeColor="White" BackColor="#9471DE"></SelectedItemStyle>
                <ItemStyle ForeColor="Black" BackColor="#DEDFDE"></ItemStyle>
                <HeaderStyle Font-Bold="True" ForeColor="#E7E7FF" BackColor="#4A3C8C"></HeaderStyle>
                <FooterStyle ForeColor="Black" BackColor="#C6C3C6"></FooterStyle>
                <Columns>
                    <asp:BoundColumn HeaderText="Product Id"></asp:BoundColumn>
                    <asp:BoundColumn HeaderText="Name"></asp:BoundColumn>
                    <asp:BoundColumn HeaderText="Price"></asp:BoundColumn>
                    <asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update" CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
                </Columns>
                <PagerStyle HorizontalAlign="Right" ForeColor="Black" BackColor="#C6C3C6"></PagerStyle>
            </asp:datagrid></form>
    </body>
</HTML>

* * * * * *

zaghmout · June 20th, 2007, 12:43 AM

does your dataset contains more than one table?
and are you sure ur procedure returns data?

I believe you should check those both issues :)

Nothing is impossible. The impossible only takes longer. "Digital Fortress, Dan Brown"

mike_remember · June 20th, 2007, 01:05 AM

Well, look at the bound fields that you have created as below:

<asp:BoundColumn HeaderText="Product Id"></asp:BoundColumn>
<asp:BoundColumn HeaderText="Name"></asp:BoundColumn>
<asp:BoundColumn HeaderText="Price"></asp:BoundColumn>

In the above columns, you have set only the HeaderText of columns, but where is the DataField??? The column should actually look like below:

<asp:BoundColumn HeaderText="Product Id" DataField="ProductID"></asp:BoundColumn>
where ProductID will be the column name of Product ID in database.

Hope you understood the problem, do let me know if you still have any issues.

Regards
Mike

Don't expect too much, too soon.

sams · June 20th, 2007, 01:26 AM

Thanks a lot for your valuable help. It is now working but now there is problem with updation.
I am writing the code below:
* * * * * *
private void DataGrid1_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
        {
            TextBox cname=new TextBox();
            cname=(TextBox) e.Item.Cells[1].Controls[0];
            string connstr = (string)ConfigurationSettings.AppSettings["ConString"];
            SqlConnection con = new SqlConnection();
            con.ConnectionString=connstr;
            SqlCommand myCommand = new SqlCommand("update_prod", con);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.Add(new SqlParameter("@ProductName",SqlDbType.NVarChar,50) );
            myCommand.Parameters["@ProductName"].Value = cname.Text;
            con.Open();
            myCommand.ExecuteNonQuery();
            con.Close();
            DataGrid1.EditItemIndex=-1;
            BindData();

        }
* * * * * *
The above code uses a stored procedure 'update_prod' as shown bwlow:
CREATE PROCEDURE SP_UpdateProd
@ProductName nvarchar(50)
AS
UPDATE products SET ProductName = @ProductName WHERE ProductName = @ProductName;

mike_remember · June 20th, 2007, 02:19 AM

What is the problem that you are facing? Any error? Please let us know in detail?

Regards
Mike

Don't expect too much, too soon.

sams · June 20th, 2007, 02:34 AM

There is no error reported but after typing a new value for Product Name, the grid is not refreshed . It still show the old values.
Thanks,
Sams

mike_remember · June 20th, 2007, 02:51 AM

Did you check the database, is the new value being updated there?

Regards
Mike

Don't expect too much, too soon.

sams · June 20th, 2007, 03:11 AM

The table is also not updated.

Thanks
Sams