ASP.NET

Datagrid; using multiple tables and getting blank row

14-Jun-06 16:18

I guess you create css classes for the hyperlink column, the sample can be found from here[^]:

which is better ?

anj198314-Jun-06 9:10

anj1983

14-Jun-06 9:10

I recently discovered so much can be done by using xml and xslt transformation. But i wanted which would be more efficient desiging pages using xml and xml transformation or using datagrid and other control while displaying data

leo
Kathmandu, Nepal

Re: which is better ?

Elina Blank14-Jun-06 9:27

Elina Blank

14-Jun-06 9:27

Depends on your requirements.
If you want someone, who has no idea about datogrid to be able to transform your HTMl, use xsl.
Datagrid is more powerful, much easier to do sorting and paging for example.

Sincerely,
Elina

Life is great!!!
Enjoy every moment of it! Blush | :O

leckey14-Jun-06 8:45

leckey

14-Jun-06 8:45

I also posted this message at the C# forum as I am programming in that so if this post looks familiar....

I have a project where the user enters a part number. Based on that part number a datagrid will return associated info. The main row will return 1-to-1 items such as part description, drawing location, etc. I plan to use nested datagrids as there will be multiple subparts per part, multiple costs per part etc.

Anyhow, all total I have about 11 tables I need to use. The problem I am facing is that not all parts have a drawing location. When this is blank, the entire row turns up blank.

How do I fix it so that the grid returns any data that is present, and ignores null information?

Here is the C# code:

using System;<br />
using System.Collections;<br />
using System.ComponentModel;<br />
using System.Data;<br />
using System.Drawing;<br />
using System.Web;<br />
using System.Web.SessionState;<br />
using System.Web.UI;<br />
using System.Web.UI.WebControls;<br />
using System.Web.UI.HtmlControls;<br />
using System.Data.SqlClient;<br />
using System.Configuration;<br />
<br />
namespace WebBasedPartsDB<br />
{<br />
	/// <summary><br />
	/// Summary description for WebForm2.<br />
	/// </summary><br />
	public class WebForm2 : System.Web.UI.Page<br />
	{<br />
		protected System.Web.UI.WebControls.TextBox txtSearchPart2;<br />
		protected System.Web.UI.WebControls.DataGrid DataGrid2;<br />
		protected System.Web.UI.WebControls.Button btnSearch2;<br />
		protected string strPartNumberInput2;<br />
<br />
		//Get the SQL connection string from the web.config file<br />
		public String strConnectSQL = (ConfigurationSettings.AppSettings["ConnectionString"]);<br />
	<br />
		private void Page_Load(object sender, System.EventArgs e)<br />
		{<br />
			// Put user code to initialize the page here<br />
		}<br />
<br />
		#region Web Form Designer generated code<br />
		override protected void OnInit(EventArgs e)<br />
		{<br />
			//<br />
			// CODEGEN: This call is required by the ASP.NET Web Form Designer.<br />
			//<br />
			InitializeComponent();<br />
			base.OnInit(e);<br />
		}<br />
		<br />
		/// <summary><br />
		/// Required method for Designer support - do not modify<br />
		/// the contents of this method with the code editor.<br />
		/// </summary><br />
		private void InitializeComponent()<br />
		{    <br />
			this.btnSearch2.Click += new System.EventHandler(this.btnSearch2_Click);<br />
			this.DataGrid2.SelectedIndexChanged += new System.EventHandler(this.DataGrid2_SelectedIndexChanged);<br />
			this.Load += new System.EventHandler(this.Page_Load);<br />
<br />
		}<br />
		#endregion<br />
<br />
		private void btnSearch2_Click(object sender, System.EventArgs e)<br />
		{<br />
			strPartNumberInput2 = txtSearchPart2.Text;<br />
			string SQLString = "SELECT c.cost, c.costtypeid, ct.description, pt.description as ptDescription FROM costs c INNER JOIN Parts p ON c.PartID = p.Id INNER JOIN PartTypes pt on pt.ID = p.PartTypeID INNER JOIN CostTypes ct on ct.ID = c.costtypeid WHERE p.PartNumber = @PartID";<br />
			//string SQLString = "SELECT c.cost, pt.description, dd.DrawingNumber, dd.DrawingRevision, dd.DwgPath FROM costs c INNER JOIN Parts p ON c.PartID = p.Id INNER JOIN PartTypes pt on pt.ID = p.PartTypeID INNER JOIN DraftingData dd on dd.PartID = p.ID WHERE p.PartNumber = @PartID";<br />
			SqlCommand cmd = new SqlCommand();<br />
			cmd.Connection = new SqlConnection(strConnectSQL);<br />
			cmd.CommandText = SQLString;<br />
			cmd.Parameters.Add ("@PartID", strPartNumberInput2);<br />
			BindGrid(cmd, DataGrid2);<br />
		}<br />
<br />
	<br />
		//**********************************************************<br />
		//BindGrid()<br />
		//**********************************************************<br />
		private void BindGrid (SqlCommand command, System.Web.UI.WebControls.DataGrid DGrid)<br />
		{<br />
			SqlDataAdapter adapter = new SqlDataAdapter(command);<br />
			DataSet ds = new DataSet();<br />
			adapter.Fill(ds);<br />
			DGrid.DataSource = ds;<br />
			DGrid.DataBind();<br />
<br />
		}<br />
<br />
		private void DataGrid2_SelectedIndexChanged(object sender, System.EventArgs e)<br />
		{<br />
		<br />
		}<br />
			<br />
	}<br />
}

Re: Datagrid; using multiple tables and getting blank row

ToddHileHoffer14-Jun-06 9:07

Re: Datagrid; using multiple tables and getting blank row

14-Jun-06 9:07

Change INNER JOIN DraftingData
to Left Join DraftingData

how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06

leckey14-Jun-06 9:12

leckey

14-Jun-06 9:12

D'oh! This is what happens when you don't program for a couple years!!! I have forgotten all the simple things!

How to get selected values from dynamically created checklist?

omerrauf14-Jun-06 6:02

omerrauf

14-Jun-06 6:02

Hi I'm creating checkbox list dynamically and populate it with database values.As I want this list to be shown on first time i created it in if-else block of "Not page.ispostback". Now I want to get the values of checkboxes which user selected on post back. but when i try to get the values on post back, it gives me an error like "checkbox list doesnot exist" or "Object reference not set".Please help me Sniff | :^)

. Following is my code:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

if Not page.ispostback

Dim Reader As SqlDataReader
Dim uamenity_Chklist As CheckBoxList = New CheckBoxList
sqlStatement = "SELECT unitamenity_id,unitAmenity_name FROM UNITAMENITY_MASTER"
cmd.CommandText = sqlStatement
cmd.Connection = conn
Reader = cmd.ExecuteReader()
uamenity_Chklist.ID = "chkUamenity"
uamenity_Chklist.DataSource = Reader
uamenity_Chklist.RepeatColumns = 2
uamenity_Chklist.DataValueField = "unitamenity_id"
uamenity_Chklist.DataTextField = "unitAmenity_name"
uamenity_Chklist.DataBind()
Reader.Close()

Dim CBrow As New HtmlTableRow
UAmenity.Rows.Add(CBrow)
Dim CBCell As New HtmlTableCell
CBrow.Cells.Add(CBCell)
CBCell.Controls.Add(uamenity_Chklist)

end if
end sub

Re: How to get selected values from dynamically created checklist?

ToddHileHoffer14-Jun-06 7:50

Re: How to get selected values from dynamically created checklist?

14-Jun-06 7:50

You can try setting the enable viewstate to true; but I think the checkBox list will be gone by the time you get to the onclick event. Because the HTMLTable will be loaded into memory without the checkboxlist. Check the page init event to see if you can access the values of the checkbox then.

how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06

Mircea Grelus14-Jun-06 8:33

Mircea Grelus

14-Jun-06 8:33

The thing is that the postback resets the page to it's original state, so any information regarding the dynamically created controls is lost. Why is that? Because the Page class is stateless. The Page recreates child controls based on the tags in the aspx files, so your controls not being present there are not shown. What you need to do is recreate the controls in the OnInit event or any event that fires before PageLoad, or in PageLoad if you do a check to see is the page is posted back. See this article[^] as an example.

regards,
Mircea
Many people spend their life going to sleep when they’re not sleepy and waking up while they still are.

Re: How to get selected values from dynamically created checklist?

ToddHileHoffer14-Jun-06 8:38

Re: How to get selected values from dynamically created checklist?

14-Jun-06 8:38

Nice post, great article. I was trying to explain that but couldn't.

how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06

Mircea Grelus14-Jun-06 8:45

Mircea Grelus

14-Jun-06 8:45

Thanks. That's why we're here Wink | ;)

regards,
Mircea
Many people spend their life going to sleep when they’re not sleepy and waking up while they still are.

Asp 2.0 Gridview/SqlDataSource update/delete problem

Fr23516614-Jun-06 4:00

Re: Asp 2.0 Gridview/SqlDataSource update/delete problem

14-Jun-06 4:00

Hi, I've created a standard GridView with an sqlDataSource, I've enabled update and delete and created the updateQuery and the DeleteQuery in the sqldatasource.
I tried it and it worked.
Then I tried to add a filter modifing the Sqldatasource proprieties in the Design:
FilterExpression = {0}
FilterParameters = {newpar,Request.Form("hdd_where")}
//hdd_where is an html hidden input
The select worked and showed me only the correct rows,but when I tried to delete or update a row nothing happened,and no error message was thrown.

I've observed that if you set the propriety ConflictDetection to CompareAllValues an error message is thrown:

"You have specified that your update command compares all values on SqlDataSource 'MySqlDataSource', but the dictionary passed in for oldValues is empty. Pass in a valid dictionary for update or change your mode to OverwriteChanges"

Now if the oldValues dictionary is empty every sql command that uses @original_KeyName parameter doesn't work.
ex:
"DELETE FROM [t_plro] WHERE [plro_ID] = @original_plro_ID" If @original_plro_ID doesn't exist no rows are deleted
the same goes for the update.

What I would like to know is : Why the oldValues Dictionary is empty if you specify a filterParameter? Is there a way to solve this situation?

Thank you for your help
Francy

p.s. I've also tried to modify on the fly the
sqlDataSource.SelectQuery but on the postback the sqldatasource execute the delete/update on the wrong row,because it uses the SelectQuery saved in the aspx file

ToddHileHoffer14-Jun-06 8:02

Re: Asp 2.0 Gridview/SqlDataSource update/delete problem

14-Jun-06 8:02

I suggesting using stored procs for your select and update, that will make it easier. For the select query, here is a trick for you.
In the where clause write [plro_ID] = isnull(@original_plro_ID, [plro_ID]) and set the default of the paramter = null in the create proc statement. Then if you have a filter add the parameter if not don't.

how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06

Fr23516614-Jun-06 22:28

Re: Asp 2.0 Gridview/SqlDataSource update/delete problem

14-Jun-06 22:28

Thank's for your answer.
I don't understand how the trick should work.

I modified the select query in this way:
"SELECT * FROM [t_plro_PriceListRoundings] WHERE [plro_ListName]=isnull(@ListName, [plro_ListName])"

ListName = Request.Form("hdd_Where")
ConvertEmptyStringToNull = True;
DefaultValue = null (I've tried leaving this propriety blank)
Direction = input
FormField = hdd_Where
Name = ListName
Size = 0
Type = String

When I load the page the gridview isn't there .If I open the page in html mode , the Gridview doesn't exist.

Could you tell me what I did wrong?
Thank you
Francy

minhpc_bk14-Jun-06 19:56

Re: Asp 2.0 Gridview/SqlDataSource update/delete problem

14-Jun-06 19:56

Could you post your code snippets ? I believe that you can use the FilterExpression with the SqlDataSource control and the update command can execute properly.

Fr23516614-Jun-06 22:00

Re: Asp 2.0 Gridview/SqlDataSource update/delete problem

14-Jun-06 22:00

Sorry but what code snipplets are you talking about?
I didn't write any code, I only modified the sqlDataSource proprieties:
Select query: "SELECT * FROM [t_plro_PriceListRoundings]"

Delete query: "DELETE FROM [t_plro_PriceListRoundings] WHERE [plro_ID] = @original_plro_ID"

Update query: "UPDATE [t_plro_PriceListRoundings] SET [plro_PriceMinValue] = @plro_PriceMinValue, [plro_PriceMaxValue] = @plro_PriceMaxValue, [plro_RoundingValue] = @plro_RoundingValue, [plro_ListName] = @plro_ListName, [plro_FixedAmmount] = @plro_FixedAmmount WHERE [plro_ID] = @original_plro_ID"

Filter expression: "{0}"

Filter parameters: "{newpar,Request.Form("hdd_where")}"

where hdd_where.value (hidden html input) can be "WHERE plro_ListName=\"Test\"" or something like it

I also specify on the GridView the propriety DataKeysNames = "plro_ID"

That's All
Thank's for you help
Francy

minhpc_bk14-Jun-06 22:19

Re: Asp 2.0 Gridview/SqlDataSource update/delete problem

14-Jun-06 22:19

I would like to see the markup (definition) of the SqlDataSource control in the web page if you don't mind.

Fr23516614-Jun-06 22:38

Re: Asp 2.0 Gridview/SqlDataSource update/delete problem

14-Jun-06 22:38

I hope this is what you asked:

<asp:SqlDataSource ID="MySqlDataSource" runat="server" ConflictDetection="CompareAllValues"
DeleteCommand="DELETE FROM [t_plro_PriceListRoundings] WHERE [plro_ID] = @original_plro_ID"
InsertCommand="INSERT INTO [t_plro_PriceListRoundings] ([plro_PriceMinValue], [plro_PriceMaxValue], [plro_RoundingValue], [plro_ListName], [plro_FixedAmmount]) VALUES (@plro_PriceMinValue,@plro_PriceMaxValue, @plro_RoundingValue, @plro_ListName, @plro_FixedAmmount)"
OldValuesParameterFormatString="original_{0}" ProviderName="System.Data.SqlClient"
SelectCommand="SELECT * FROM [t_plro_PriceListRoundings]"
UpdateCommand="UPDATE [t_plro_PriceListRoundings] SET [plro_PriceMinValue] = @plro_PriceMinValue, [plro_PriceMaxValue] = @plro_PriceMaxValue, [plro_RoundingValue] = @plro_RoundingValue, [plro_ListName] = @plro_ListName, [plro_FixedAmmount] = @plro_FixedAmmount WHERE [plro_ID] = @original_plro_ID"
EnableCaching="True"
OnSelecting="MySqlDataSource_Selecting"
OnUpdating="MySqlDataSource_Updating" FilterExpression="{0}">
<DeleteParameters>
<asp:Parameter Name="original_plro_ID" Type="Int32" DefaultValue="" />
<asp:Parameter Name="original_plro_PriceMinValue" Type="Decimal" />
<asp:Parameter Name="original_plro_PriceMaxValue" Type="Decimal" />
<asp:Parameter Name="original_plro_RoundingValue" Type="Decimal" />
<asp:Parameter Name="original_plro_ListName" Type="String" />
<asp:Parameter Name="original_plro_FixedAmmount" Type="Decimal" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="plro_PriceMinValue" Type="Decimal" />
<asp:Parameter Name="plro_PriceMaxValue" Type="Decimal" />
<asp:Parameter Name="plro_RoundingValue" Type="Decimal" />
<asp:Parameter Name="plro_ListName" Type="String" />
<asp:Parameter Name="plro_FixedAmmount" Type="Decimal" />
<asp:Parameter Name="original_plro_ID" Type="Int32" />
<asp:Parameter Name="original_plro_PriceMinValue" Type="Decimal" />
<asp:Parameter Name="original_plro_PriceMaxValue" Type="Decimal" />
<asp:Parameter Name="original_plro_RoundingValue" Type="Decimal" />
<asp:Parameter Name="original_plro_ListName" Type="String" />
<asp:Parameter Name="original_plro_FixedAmmount" Type="Decimal" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="plro_PriceMinValue" Type="Decimal" />
<asp:Parameter Name="plro_PriceMaxValue" Type="Decimal" />
<asp:Parameter Name="plro_RoundingValue" Type="Decimal" />
<asp:Parameter Name="plro_ListName" Type="String" />
<asp:Parameter Name="plro_FixedAmmount" Type="Decimal" />
</InsertParameters>
<FilterParameters>
<asp:FormParameter FormField="hdd_Where" Name="test" />
</FilterParameters>
</asp:SqlDataSource>

I don't use any event , if you see any of them it was just for testing.

ex:
hdd_Where.value = "WHERE plro_ListName = \"Test\""
Thank you
Francy

minhpc_bk14-Jun-06 23:06

Re: Asp 2.0 Gridview/SqlDataSource update/delete problem

14-Jun-06 23:06

That's what I would like to see and it's clear enough except a couple of redundant update/delete parameters. However, one thing that strikes me most is the value of the hidden element:

"<code>WHERE</code> plro_ListName = \"Test\""

.
Are you sure that you have the keyword WHERE in the filter expression?

Fr23516614-Jun-06 23:12

Re: Asp 2.0 Gridview/SqlDataSource update/delete problem

14-Jun-06 23:12

No , sorry my mistake. In the hidden element I only have plro_ListName =\"Test\"

minhpc_bk14-Jun-06 23:38

Re: Asp 2.0 Gridview/SqlDataSource update/delete problem

14-Jun-06 23:38

Unfortunately, I still cannot figure out the cause in your sample code, perhapse the rest of the web page may help. Anyway, below is a quick example I have created with the Northwind DB:

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title runat="server" id="title1">Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
       <div>
           <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CategoryID"
               DataSourceID="SqlDataSource1">
               <Columns>
                   <asp:CommandField HeaderText="Action" ShowEditButton="True" ShowDeleteButton="True" />
                   <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" InsertVisible="False"
                       ReadOnly="True" SortExpression="CategoryID" />
                   <asp:BoundField DataField="CategoryName" HeaderText="CategoryName" SortExpression="CategoryName" />
                   <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
               </Columns>
           </asp:GridView>
           <br />
           <asp:HiddenField ID="hdd_Where" runat="server" Value="CategoryName ='Test'" />
           <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
               DeleteCommand="DELETE FROM [Categories] WHERE [CategoryID] = @original_CategoryID" 
               InsertCommand="INSERT INTO [Categories] ([CategoryName], [Description]) VALUES (@CategoryName, @Description)"
               SelectCommand="SELECT [CategoryID], [CategoryName], [Description] FROM [Categories]"
               UpdateCommand="UPDATE [Categories] SET [CategoryName] = @CategoryName, [Description] = @Description WHERE [CategoryID] = @original_CategoryID "
               EnableCaching="True"
               FilterExpression="{0}" 
               ConflictDetection="CompareAllValues" 
               OldValuesParameterFormatString="original_{0}" 
               ProviderName="System.Data.SqlClient">
               <DeleteParameters>
                   <asp:Parameter Name="original_CategoryID" Type="Int32" />
               </DeleteParameters>
               <UpdateParameters>
                   <asp:Parameter Name="CategoryName" Type="String" />
                   <asp:Parameter Name="Description" Type="String" />
                   <asp:Parameter Name="original_CategoryID" Type="Int32" />
                   <asp:Parameter Name="original_CategoryName" Type="String" />
               </UpdateParameters>
               <InsertParameters>
                   <asp:Parameter Name="CategoryName" Type="String" />
                   <asp:Parameter Name="Description" Type="String" />
               </InsertParameters>
               <FilterParameters>
                   <asp:FormParameter FormField="hdd_Where" Name="test" /> 
               </FilterParameters>
           </asp:SqlDataSource>
           <asp:Button runat="server" ID="btnFilter" Text="Filter" />
           <br />
           <br />
           <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="CategoryID"
               DataSourceID="SqlDataSource1" DefaultMode="Insert" Height="50px" Width="125px">
               <Fields>
                   <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" InsertVisible="False"
                       ReadOnly="True" SortExpression="CategoryID" />
                   <asp:BoundField DataField="CategoryName" HeaderText="CategoryName" SortExpression="CategoryName" />
                   <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
                   <asp:CommandField ShowInsertButton="True" />
               </Fields>
           </asp:DetailsView>
           <br />
       </div>
    </form>
</body>
</html>

The DetailsView control is only used to insert new records.

Fr23516615-Jun-06 0:07

Re: Asp 2.0 Gridview/SqlDataSource update/delete problem

15-Jun-06 0:07

I tried your example but when updating,deleting,and inserting I recive the following error message:
The index "PK_Categories" for table "dbo.Categories" (RowsetId 72057594038517760) resides on a read-only filegroup, which cannot be modified.

Do you know what does it mean?

minhpc_bk15-Jun-06 0:37