5 Replies - 4834 Views - Last Post: 19 November 2012 - 11:12 AM Rate Topic: -----

#1 wanabavbcoder  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 48
  • Joined: 08-October 12

Display data from sql table in a DATAGRID web form

Posted 18 November 2012 - 11:26 AM

Hello my fellow DICers.
I have a search function that I am trying to implement. I use two drop down lists that are populated from my table in sql. I am then trying to display the results in a datagrid. if this is possible.
I have been searching the net for some time now and i cannot find an answer to display data from sql to a datagrid in my c# asp.net web form. I have got a some code but this does not display any thing. The page refreshes and no display of the data.
		private void Page_Load(object sender, System.EventArgs e)
		
		{
			SqlConnection conn = new SqlConnection("workstation id=RIKESH;packet size=4096;user id=sa;data source=RIKESH;persist security info=False;initial catalog=FlyWaySystem");
			SqlDataAdapter da = new SqlDataAdapter ("Select * FROM FlightInformation ",	conn);
			DataSet ds = new DataSet();
			da.Fill (ds);
			DropDownList1.DataSource = ds;
			DropDownList1.DataValueField = "Origin";
			DropDownList1.DataBind () ;
			string selectedText1=DropDownList1.SelectedItem.Text;
			DropDownList2.DataSource = ds;
			DropDownList2.DataValueField = "Destination";
			DropDownList2.DataBind () ;
			string selectedText2=DropDownList2.SelectedItem.Text;
		
		}
		#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.Button1.Click += new System.EventHandler(this.Button1_Click);
			this.Load += new System.EventHandler(this.Page_Load);

		}
		#endregion

		private void Button1_Click(object sender, System.EventArgs e)
		{
		 

			string connectionString = "workstation id=RIKESH;packet size=4096;user id=sa;data source=RIKESH;persist security info=False;initial catalog=FlyWaySystem";
			string sql = "SELECT * FROM FlightInformation where Origin='selectedText1' and Destination='selectedText2' ";
			SqlConnection connection = new SqlConnection(connectionString);
			SqlDataAdapter dataadapter = new SqlDataAdapter(sql, connection);
			DataSet ds = new DataSet();
			connection.Open();
			dataadapter.Fill(ds, "FlightInformation");
			connection.Close();
			DataGrid1.DataSource = ds;
			DataGrid1.DataMember = "FlightInformation";

}



Is This A Good Question/Topic? 0
  • +

Replies To: Display data from sql table in a DATAGRID web form

#2 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6052
  • View blog
  • Posts: 23,487
  • Joined: 23-August 08

Re: Display data from sql table in a DATAGRID web form

Posted 18 November 2012 - 11:31 AM

Moved to ASP.NET
Was This Post Helpful? 0
  • +
  • -

#3 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 444
  • View blog
  • Posts: 1,492
  • Joined: 28-April 09

Re: Display data from sql table in a DATAGRID web form

Posted 18 November 2012 - 12:03 PM

ok, you've got a couple of problems. First, you need to know that the Page_Load event gets called everytime the page reloads. This includes the initial page load and during every post back event. The Page_Load event happens before any control events, such as the button's click event. When you databind controls in the Page_Load event then those controls are reloaded everytime a postback happens. The result of this is that your DropDownList controls are having their values reset before your button's click event gets executed. The way to prevent this is to wrap the databinding code in you Page_Load inside of an if(!IsPostback){} code block.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostback)
    {
        // Databinding code here
    }
}



another problem i'm seeing is your select statement in your button click event looks like it may be a little off.

SELECT * FROM FlightInformation where Origin='selectedText1' and Destination='selectedText2'


Are you really wanting to match the text "selectedText2" and "selectedText1"? Most likely you're looking to something more like

SELECT * FROM FlightInformation where Origin=@origin and Destination=@destination


Then you need to create two select parameters in you SqlDataAdapter

dataadapter.SelectCommand.Parameters.AddWithValue("origin", DropDownList1.SelectedValue);
dataadapter.SelectCommand.Parameters.AddWithValue("destination", DropDownList2.SelectedValue);



You should also probably think about renaming your server controls so that it's easier to tell what each one is for. Instead of DropDownList1, you might do something like ddlOriginList or something similar.
Was This Post Helpful? 1
  • +
  • -

#4 wanabavbcoder  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 48
  • Joined: 08-October 12

Re: Display data from sql table in a DATAGRID web form

Posted 18 November 2012 - 09:54 PM

I Have tried what you suggested but to no avail. I realized that the framework is 1.1 and the gridview will not work. I have tried another concept and was wondering if this could work? I just cant seem to bind the data to the datagrid from my data reader.
here is the code:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;


namespace Logmein
{
	/// <summary>
	/// Summary description for Flightdetails.
	/// </summary>
	public class Flightdetails : System.Web.UI.Page
	{
		protected System.Web.UI.WebControls.DropDownList DropDownList1;
		protected System.Web.UI.WebControls.Label Label1;
		protected System.Web.UI.WebControls.Label Label2;
		protected System.Web.UI.WebControls.DropDownList DropDownList2;
		protected System.Web.UI.WebControls.Button Button1;
		protected System.Web.UI.WebControls.Label Label6;
		protected System.Web.UI.WebControls.DataGrid DataGrid1;
		protected System.Web.UI.WebControls.Image Image5;
		
		private void Page_Load(object sender, System.EventArgs e)
		
		{
{

            SqlConnection conn = new SqlConnection("workstation id=RIKESH;packet size=4096;user id=sa;data source=RIKESH;persist security info=False;initial catalog=FlyWaySystem");

            SqlDataAdapter da = new SqlDataAdapter ("Select * FROM FlightInformation ", conn);

            DataSet ds = new DataSet();

            da.Fill (ds);

            DropDownList1.DataSource = ds;

            DropDownList1.DataValueField = "Origin";

            DropDownList1.DataBind () ;

            string selectedText1=DropDownList1.SelectedItem.Text;

            DropDownList2.DataSource = ds;

            DropDownList2.DataValueField = "Destination";

            DropDownList2.DataBind () ;

            string selectedText2=DropDownList2.SelectedItem.Text;

         

        }


			if (Session["update"]==null) 

			{ 

				Session["update"] = Server.UrlEncode(System.DateTime.Now.ToString()); 

			} 
		}

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

		}
		#endregion

		private void Button1_Click(object sender, System.EventArgs e)
		{
			SqlConnection sqlconn; 

			SqlCommand sqlcomm; 

			sqlconn = new SqlConnection("workstation id=RIKESH;packet size=4096;user id=sa;data source=RIKESH;persist security info=False;initial catalog=FlyWaySystem"); 

			sqlconn.Open(); 

			sqlcomm = new SqlCommand(); 
				
			sqlcomm.Connection = sqlconn; 

			//Here I am definied command type is Stored Procedure. 

			sqlcomm.CommandType = CommandType.StoredProcedure; 

			//Here I mentioned the Stored Procedure Name. 

			sqlcomm.CommandText = "Searchflights"; 

			//Here I fix the variable values to Stored Procedure Parameters. You can easily understand if you can see the Stored Procedure Code. 
			sqlcomm.Parameters.Add("@origin", SqlDbType.NVarChar);  
			sqlcomm.Parameters["@origin"].Value = DropDownList1.SelectedItem.ToString();
			sqlcomm.Parameters.Add("@destination", SqlDbType.NVarChar);  
			sqlcomm.Parameters["@destination"].Value = DropDownList2.SelectedItem.ToString();
			sqlcomm.ExecuteNonQuery(); 
			SqlDataReader rdr = sqlcomm.ExecuteReader();
			//checking whether data reader contains any row.
			if (rdr.HasRows)
			{
				

			}
			sqlcomm.Dispose(); 
			sqlconn.Close(); 
		}
		
		
	}
}


Was This Post Helpful? 0
  • +
  • -

#5 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 444
  • View blog
  • Posts: 1,492
  • Joined: 28-April 09

Re: Display data from sql table in a DATAGRID web form

Posted 19 November 2012 - 07:30 AM

you should be able to set the datareader as the datasource of the DataGrid control. Then just databind it.

    if (rdr.HasRows)
    {
        DataGrid1.DataSource = rdr;
        DataGrid1.DataBind();      
    }



Something like that at least. Unfortunately, I have done much work with .net 1.1 and i'm not 100% sure what is available on that version compared to more up-to-date versions.
Was This Post Helpful? 0
  • +
  • -

#6 wanabavbcoder  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 48
  • Joined: 08-October 12

Re: Display data from sql table in a DATAGRID web form

Posted 19 November 2012 - 11:12 AM

it is alive. wahahaha. evil laugh. Thanks. I also made a little mistake in my stored procedure. but all is working now. Can we dynamically refresh the data grid every round trip?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1