How can I alter the datatype of the data quiry from a database and store it into a gridview?

5 Oct 2006 3:47 AM

khoaha

Thank you in advance.
This is my problem.
Assume I have a select statement SELECT Name, Gender FROM 'Database'
Name is a (varchar) and Gender is an (int)
The Gender column stored value of '1' and '0'
Now when I use the select statement to bind the value to the gridview
I wanted it to store "Male" for '1' and "Female" for '0'

Database                    This should be what to be store in the
gridview
abc   1                        abc    Male
def    0                        def     Female

I have tried to use the CASE statement in the select query to alter the
data. However I can only alter it to the same type 1->100 (int to int)
but cannot
alter it to a different type 1->Male (int to varchar)

Does anybody know if there is a why to do this? Alter the value type as
well as the value.

Thanks

6 Oct 2006 4:40 AM

Ken Cox [Microsoft MVP]

Hi,

If you're only concerned about the display, you can insert a function into
the GridView to handle that. In this case, I'd use an IIF() as a helper
function:

      <asp:label id="Label1" runat="server" text='<%#
iif(Eval("Gender"),"Male","Female") %>'></asp:label>

See the full code below.

Let us know if this helps?

Ken
Microsoft MVP [ASP.NET]

<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

    Function CreateDataSource() As Data.DataTable
        Dim dt As New Data.DataTable
        Dim dr As Data.DataRow
        dt.Columns.Add(New Data.DataColumn _
        ("Gender", GetType(Int32)))
        dt.Columns.Add(New Data.DataColumn _
        ("Name", GetType(String)))
        Dim i As Integer
        For i = 0 To 5
            dr = dt.NewRow()
            dr(0) = i
            dr(1) = "Item " + i.ToString()
            dt.Rows.Add(dr)
        Next i
        Return dt
    End Function

    Protected Sub Page_Load _
    (ByVal sender As Object, _
    ByVal e As System.EventArgs)
        If Not IsPostBack Then
            GridView1.DataSource = CreateDataSource()
            GridView1.DataBind()
        End If
    End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Gridview Helper function</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:gridview id="GridView1" runat="server"
autogeneratecolumns="False">
            <columns>
                <asp:templatefield headertext="Name" sortexpression="Name">
                    <itemtemplate>
                        <asp:label id="Label2" runat="server" text='<%#
Bind("Name") %>'></asp:label>
                    </itemtemplate>
                </asp:templatefield>
                <asp:templatefield headertext="Gender"
sortexpression="Gender">
                    <itemtemplate>
                        <asp:label id="Label1" runat="server" text='<%#
iif(Eval("Gender"),"Male","Female") %>'></asp:label>
                    </itemtemplate>
                </asp:templatefield>
            </columns>
        </asp:gridview>

    </div>
    </form>
</body>
</html>

<kho***@gmail.com> wrote in message
Show quoteHide quote

news:1160020074.348770.67490@i42g2000cwa.googlegroups.com...
> Thank you in advance.
> This is my problem.
> Assume I have a select statement SELECT Name, Gender FROM 'Database'
> Name is a (varchar) and Gender is an (int)
> The Gender column stored value of '1' and '0'
> Now when I use the select statement to bind the value to the gridview
> I wanted it to store "Male" for '1' and "Female" for '0'
>
> Database                    This should be what to be store in the
> gridview
> abc   1                        abc    Male
> def    0                        def     Female
>
> I have tried to use the CASE statement in the select query to alter the
> data. However I can only alter it to the same type 1->100 (int to int)
> but cannot
> alter it to a different type 1->Male (int to varchar)
>
> Does anybody know if there is a why to do this? Alter the value type as
> well as the value.
>
> Thanks
>