...

View Full Version : Changing the gridviews Boundfield datafield values?



binici
12-01-2006, 07:20 PM
Hello all:

I hope I can make sense of this. I have exhausted all resources, MSDN and books, but cannot understand or figure how I can programmatically change and write out a different value of a datafield?

One of my datafields returns an on/off switch, (0,1), so depending on the value, I want to then output a string to that qualified datafield. Here is my codebehind:

Private Function TestDataSet()

Dim iMemberId As String = Request.QueryString("member_id")

Dim connString As String = ConfigurationManager.AppSettings("connectionstring")
Dim con As New SqlConnection(connString)
Dim sql As String = "SELECT rm.recip_id, rm.member_id, rm.mls_id, rm.agent_name, rm.office_name, rm.street_number + ' ' + rm.street_name + ' ' + rm.city + ',' + rm.state AS recip_address, " _
& "rm.expiration_date, rm.received_by, rm.fee, rm.payment_type, rm.check_number, rm.recip_to, rm.recip_date, rm.last_updated, " _
& "rm.recip_type, rm.recip_status, m.Office_Number " _
& "FROM callcenterdev..tRecipMaster rm WITH(NOLOCK) LEFT OUTER JOIN rapdata..Member m WITH(NOLOCK) ON rm.member_id = m.Member_Number " _
& "WHERE rm.member_id = '" & iMemberId & "' "
Dim objDataAdapter As New SqlDataAdapter(sql, connString)
Dim objDataSet As New DataSet()
Dim objConnect As New SqlConnection(connString)
objDataAdapter.Fill(objDataSet, "Recip")

grdResults.DataSource = objDataSet.Tables("Recip").DefaultView
grdResults.DataBind()

Return objDataSet

End Function

here is the aspx:

<asp:GridView ID="grdResults" runat="server" Visible="False" AutoGenerateColumns="False" AllowPaging="True" PageSize="3" BorderWidth="1px" CellPadding="2" CellSpacing="2">
<Columns>
<asp:HyperLinkField HeaderText="View" NavigateUrl="/_MembersOnly/RecipListing/RecipListingForm.aspx"
Text="Listing In" DataNavigateUrlFields="recip_id" >
<ControlStyle CssClass="LinkNormal" />
<HeaderStyle HorizontalAlign="Center" />
</asp:HyperLinkField>
<asp:BoundField DataField="recip_to" HeaderText="To" />
<asp:BoundField DataField="recip_status" HeaderText="Status" />
<asp:BoundField DataField="agent_name" HeaderText="Agent" />
<asp:BoundField DataField="office_name" HeaderText="Office" />
<asp:BoundField DataField="recip_address" HeaderText="Address" />
<asp:BoundField DataField="mls_id" HeaderText="MLS #" />
<asp:BoundField DataField="recip_date" DataFormatString="{0:dd/MM/yyyy}" HeaderText="Date Entered" HtmlEncode="False" />
<asp:BoundField DataField="last_updated" DataFormatString="{0:d}" HeaderText="Last Updated"
HtmlEncode="False" />
</Columns>
</asp:GridView>

Any ideas or help would be appreciated! Thank you!

nikkiH
12-01-2006, 08:29 PM
If you're using gridview, use sqldatasource as your datasource, not a default view of a dataset. That's DataGrid style. ;)

You'll be able to ditch a bunch of boilerplate code, and using the datakeynames properties, will be able to find data in your rows easier.

See Programming Microsoft ASP.NET 2.0 Core reference by Dino Esposito (note that it is mostly C#, however, not VB) as a great book that will show you all kinds of tricks with the new GridView object. This object is really meant to be used with the new SqlDataSource object to really work well.

That said, your question doesn't totally make sense. If you mean to change it programmatically, you need to use your code behind and trap OnItemDataBound...
Can you clarify what you're going for?

binici
12-01-2006, 08:45 PM
If you're using gridview, use sqldatasource as your datasource, not a default view of a dataset. That's DataGrid style. ;)

You'll be able to ditch a bunch of boilerplate code, and using the datakeynames properties, will be able to find data in your rows easier.

See Programming Microsoft ASP.NET 2.0 Core reference by Dino Esposito (note that it is mostly C#, however, not VB) as a great book that will show you all kinds of tricks with the new GridView object. This object is really meant to be used with the new SqlDataSource object to really work well.

That said, your question doesn't totally make sense. If you mean to change it programmatically, you need to use your code behind and trap OnItemDataBound...
Can you clarify what you're going for?

Thank you for replying.

Sorry for the confusion, but I will make sense of it.
One of the fields that I have bound is a 'status' field in the db table.
The data is an integer and the values are either set to 1 or 0. When the datasource grabs the value, I want to be able to output a string "complete" if 1 and "incomplete" if 0 returns.

Right now, when the grid is outputted on the webpage, it bounds 1's and 0's to the column.

I am trying to figure out a codebind that will use logic to output the string.

Thanks!

nikkiH
12-01-2006, 10:53 PM
I'd do it in the SQL, it's faster and easier.
This looks like SQL Server.
If it is...

select case when rm.recip_status=1 then 'complete' else 'incomplete' end as recip_status

The new sqldatasource is nice if you read up on it; it makes your life a LOT easier. You should also be using views for your SQL if you can, because if a field name changes, it's a PITA to change ALL the code...spoken from experience :D

If this is for a real production app, you should not use any embedded sql in your code, or as little as possible. Use views and stored procedures if you don't have a business object / DAL. Hard-coded table and field names are a last resort, and should really be avoided.

If you really want to leave it as a integer (why isn't it a bit??) in the SQL and do this with the datagrid, it's more code.
Hang on, I'll get a quick test to show you.

binici
12-01-2006, 11:00 PM
Interesting, thanks btw! Much help, I was just testing the different types of datasources. Here is my updated code:

aspx:

<asp:GridView ID="grdRecipHistory" runat="server" DataSourceID="RecipHistory" AutoGenerateColumns="False" DataKeyNames="recip_id,member_id" DataMember="DefaultView" AllowPaging="True" CssClass="TextSmall" Width="555px" Visible="False">
<Columns>
<asp:HyperLinkField HeaderText="View" NavigateUrl="/_MembersOnly/RecipListing/RecipListingForm.aspx"
Text="Listing In" DataTextField="recip_id" >
<ControlStyle CssClass="LinkSmall" />
</asp:HyperLinkField>
<asp:BoundField DataField="recip_to" HeaderText="To" SortExpression="recip_to" />
<asp:BoundField DataField="recip_status" HeaderText="Status" SortExpression="recip_status" />
<asp:BoundField DataField="agent_name" HeaderText="Agent" SortExpression="agent_name" />
<asp:BoundField DataField="office_name" HeaderText="Office" SortExpression="office_name" />
<asp:BoundField DataField="recip_address" HeaderText="Address" SortExpression="recip_address" />
<asp:BoundField DataField="mls_id" HeaderText="MLS #" SortExpression="mls_id" />
<asp:BoundField DataField="recip_date" DataFormatString="{0:d}" HeaderText="Entered"
SortExpression="recip_date" HtmlEncode="False" />
</Columns>
<HeaderStyle BackColor="#5C6F8D" HorizontalAlign="Center" />
<AlternatingRowStyle BackColor="#8B9FC4" />
</asp:GridView>
<asp:SqlDataSource ID="RecipHistory" ProviderName="System.Data.SqlClient" ConnectionString="<%$ Appsettings:connectionstring %>" SelectCommand="callcenterdev..spGetRecipHistory" SelectCommandType="StoredProcedure" runat="server">
<SelectParameters>
<asp:QueryStringParameter QueryStringField="member_id" Name="MemberId" />
</SelectParameters>
</asp:SqlDataSource>

Stored proc in sqlserver:

CREATE PROCEDURE spGetRecipHistory @MemberId int

AS

SELECT
rm.recip_id,
rm.member_id,
rm.mls_id,
rm.agent_name,
rm.office_name,
rm.street_number + ' ' + rm.street_name + ' ' + rm.city + ',' + rm.state AS recip_address,
rm.expiration_date,
rm.received_by,
rm.fee,
rm.payment_type,
rm.check_number,
rm.recip_to,
rm.recip_date,
rm.last_updated,
rm.recip_type,
rm.recip_status,
m.Office_Number

FROM
callcenterdev..tRecipMaster rm WITH(NOLOCK) LEFT OUTER JOIN rapdata..Member m WITH(NOLOCK) ON rm.member_id = m.Member_Number

WHERE
rm.member_id = @MemberId
GO


Less mess on the codebehind. That field is going to be left that way because my boss wants it as an int.

I was planning to use stored procedures anyways, was just testing different methods. Thanks!

Now, if I can only figure it out, let me know if you want to see the pages I has setup. But this is what is looks like right now.

View To Status Agent Office Address MLS # Entered
6553 PWR 1 ,CA 6/2/2006
6554 PWR 1 ,CA 6/2/2006
6555 PWR 0 Philip Schaaf Realty Network ,FL 6/2/2006
6556 PWR 1 Philip Schaaf Realty Network ,CA 6/2/2006
6557 PWR 1 Phillip Schaefer Seven Gables Real Estate ,CA 6/2/2006

binici
12-01-2006, 11:05 PM
Yay, you helped out so much! I got it!

CREATE PROCEDURE spGetRecipHistory @MemberId int

AS

SELECT
rm.recip_id,
rm.member_id,
rm.mls_id,
rm.agent_name,
rm.office_name,
rm.street_number + ' ' + rm.street_name + ' ' + rm.city + ',' + rm.state AS recip_address,
rm.expiration_date,
rm.received_by,
rm.fee,
rm.payment_type,
rm.check_number,
rm.recip_to,
rm.recip_date,
rm.last_updated,
rm.recip_type,
CASE WHEN rm.recip_status=1 THEN 'complete' ELSE 'incomplete' END AS recip_status,
m.Office_Number

FROM
callcenterdev..tRecipMaster rm WITH(NOLOCK) LEFT OUTER JOIN rapdata..Member m WITH(NOLOCK) ON rm.member_id = m.Member_Number

WHERE
rm.member_id = @MemberId
GO

Thank you very much!

nikkiH
12-01-2006, 11:17 PM
You're welcome!

Just for kicks, and to show anyone along this thread later, for the grid view, it's RowDataBound, and it is as much of a pain as I remember, having to count cells and stuff.

Here's a full test code version, with Northwind. (connection string in web config used)



<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!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>Test</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" ShowHeader="true" DataKeyNames="customerID" AutoGenerateColumns="False">
<HeaderStyle Font-Bold="true" />
<Columns>
<asp:BoundField DataField="CompanyName" />
<asp:BoundField DataField="ContactName" />
<asp:BoundField DataField="ContactTitle" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>




Imports System.Data
Imports System.Data.SqlClient

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
Dim _connection As SqlConnection = New SqlConnection
Dim _dataAdapter As SqlDataAdapter = New SqlDataAdapter
Dim _dataSet As DataSet = New DataSet
Dim _dataView As DataView

Dim _sqlQuery As String = "SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax] FROM [Northwind].[dbo].[Customers]"
_connection.ConnectionString = ConfigurationManager.ConnectionStrings("NORTHWIND").ToString

Try
_connection.Open()
_dataAdapter.SelectCommand = New SqlCommand(_sqlQuery, _connection)
_dataAdapter.Fill(_dataSet, "customers")
_dataView = _dataSet.Tables("customers").DefaultView

Me.GridView1.DataSource = _dataView
Me.GridView1.DataBind()

Catch ex As Exception
Finally
_connection.Close()
End Try


End If
End Sub

Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then

' Display the company name in italics.
e.Row.Cells(1).Text = "<i>" & e.Row.Cells(1).Text & "</i>"

End If
End Sub
End Class

binici
12-02-2006, 01:09 AM
You're welcome!

Just for kicks, and to show anyone along this thread later, for the grid view, it's RowDataBound, and it is as much of a pain as I remember, having to count cells and stuff.

Here's a full test code version, with Northwind. (connection string in web config used)



<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!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>Test</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" ShowHeader="true" DataKeyNames="customerID" AutoGenerateColumns="False">
<HeaderStyle Font-Bold="true" />
<Columns>
<asp:BoundField DataField="CompanyName" />
<asp:BoundField DataField="ContactName" />
<asp:BoundField DataField="ContactTitle" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>




Imports System.Data
Imports System.Data.SqlClient

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
Dim _connection As SqlConnection = New SqlConnection
Dim _dataAdapter As SqlDataAdapter = New SqlDataAdapter
Dim _dataSet As DataSet = New DataSet
Dim _dataView As DataView

Dim _sqlQuery As String = "SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax] FROM [Northwind].[dbo].[Customers]"
_connection.ConnectionString = ConfigurationManager.ConnectionStrings("NORTHWIND").ToString

Try
_connection.Open()
_dataAdapter.SelectCommand = New SqlCommand(_sqlQuery, _connection)
_dataAdapter.Fill(_dataSet, "customers")
_dataView = _dataSet.Tables("customers").DefaultView

Me.GridView1.DataSource = _dataView
Me.GridView1.DataBind()

Catch ex As Exception
Finally
_connection.Close()
End Try


End If
End Sub

Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then

' Display the company name in italics.
e.Row.Cells(1).Text = "<i>" & e.Row.Cells(1).Text & "</i>"

End If
End Sub
End Class



so can you use logic to compare the row cells value?

because I tried this:

Protected Sub RecipHistoryGridView_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles RecipHistoryGridView.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then

' Change the first column (recip_id) to a status string.
If e.Row.Cells(8).Text = "1" Then
e.Row.Cells(0).Text = "Listing In"
Else
e.Row.Cells(0).Text = "Listing Out"
End If

If e.Row.Cells(2).Text = "1" Then
e.Row.Cells(2).Text = "complete"
Else
e.Row.Cells(2).Text = "Incomplete"
End If
End If
End Sub

and it didn't work well.

nikkiH
12-02-2006, 03:14 AM
What is "didn't work well"?
This is your gridview in the code you posted:

asp:GridView ID="grdResults"
Your handler needs to handle that grid.

Protected Sub RecipHistoryGridView_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles RecipHistoryGridView.RowDataBound

Did you change the ID?

Other than that...

Do you use Studio?
Debug and see what you're getting for e.Row and all the cells.
All bets are off for each control you have inside there. Then you start having to use FindControl and look at children.
It's a real pain, which is why I avoid it. God forbid you change the layout of the grid, too.

Note that columns that are set to not be visible still count in the row.

This worked fine for me, so it's something specific to your app not the code comparison itself. You'll need to trace it to see what it is comparing and what the values of variables are.
Note that I changed the gridview bound columns to reflect the new query, but I'm only posting the code behind.



Imports System.Data
Imports System.Data.SqlClient

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
Dim _connection As SqlConnection = New SqlConnection
Dim _dataAdapter As SqlDataAdapter = New SqlDataAdapter
Dim _dataSet As DataSet = New DataSet
Dim _dataView As DataView

Dim _sqlQuery As String = "SELECT [CategoryID], [CategoryName], [Description], [Picture] FROM [Northwind].[dbo].[Categories]"
_connection.ConnectionString = ConfigurationManager.ConnectionStrings("NORTHWIND").ToString

Try
_connection.Open()
_dataAdapter.SelectCommand = New SqlCommand(_sqlQuery, _connection)
_dataAdapter.Fill(_dataSet, "categories")
_dataView = _dataSet.Tables("categories").DefaultView

Me.GridView1.DataSource = _dataView
Me.GridView1.DataBind()

Catch ex As Exception
Finally
_connection.Close()
End Try


End If
End Sub

Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then
' category ID
If e.Row.Cells(0).Text = "4" Then
e.Row.Cells(0).Text = "FOUR"
End If

End If
End Sub
End Class

binici
12-04-2006, 06:12 PM
Thank you, I think I almost have it, but seems like there is a small problem, because I have a field that is visible set to false, I believe it is not finding the column.



<asp:GridView ID="RecipHistoryGridView" runat="server" DataSourceID="sqlRecipHistory" AutoGenerateColumns="False" DataKeyNames="recip_id,member_id" DataMember="DefaultView" AllowPaging="True" CssClass="TextSmall" Width="555px" Visible="False" AllowSorting="True" PageSize="5" OnRowDataBound="RecipHistoryGridView_RowDataBound">
<Columns>
<asp:HyperLinkField DataTextField="recip_id" HeaderText="View" NavigateUrl="/_MembersOnly/RecipListing/RecipListingForm.aspx"
SortExpression="recip_id" />
<asp:BoundField DataField="recip_to" HeaderText="To" SortExpression="recip_to" />
<asp:BoundField DataField="recip_status" HeaderText="Status" SortExpression="recip_status" />
<asp:BoundField DataField="agent_name" HeaderText="Agent" SortExpression="agent_name" />
<asp:BoundField DataField="office_name" HeaderText="Office" SortExpression="office_name" />
<asp:BoundField DataField="recip_address" HeaderText="Address" SortExpression="recip_address" />
<asp:BoundField DataField="mls_id" HeaderText="MLS #" SortExpression="mls_id" />
<asp:BoundField DataField="recip_date" DataFormatString="{0:d}" HeaderText="Entered"
HtmlEncode="False" SortExpression="recip_date" />
<asp:BoundField DataField="recip_type" HeaderText="RecipType" SortExpression="recip_type"
Visible="False" />
</Columns>
<HeaderStyle BackColor="#5C6F8D" HorizontalAlign="Center" />
<AlternatingRowStyle BackColor="#8B9FC4" />
<PagerSettings Mode="NextPreviousFirstLast" />
<PagerStyle HorizontalAlign="Center" />
</asp:GridView>

So the codebehind looks like this:



Protected Sub RecipHistoryGridView_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles RecipHistoryGridView.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then

' Change the first column (recip_id) to a status string.
If e.Row.Cells(8).Text = "1" Then
e.Row.Cells(0).Text = "Listing In"
ElseIf e.Row.Cells(8).Text = "0" Then
e.Row.Cells(0).Text = "Listing Out"
End If
End If
End Sub


As you said, I still have to account for hidden fields/columns in the case, which the Recip Type column would be the 8th object right? When I make the Recip Type row visible then the logic works.

Hope this is not confusing. :thumbsup:

binici
12-04-2006, 10:44 PM
:D
Ah hah! Thanks to the ASP.NET boards someone helped out.

Seems like a DataRowView is used to the displayed content with different attributes! Here is the VB Code:

Protected Sub RecipHistoryGridView_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles RecipHistoryGridView.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then

' Change the first column (recip_id) to a status string.

Dim d As DataRowView = e.Row.DataItem()
If d("recip_type").ToString = "1" Then
e.Row.Cells(0).Text = "Listing In"
ElseIf d("recip_type").ToString = "0" Then
e.Row.Cells(0).Text = "Listing Out"
End If

End If

End Sub

Thanks again!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum