Combine 2 Columns to one with punctuation

Discussion in 'ASP .Net' started by DBLWizard, Apr 1, 2005.

  1. DBLWizard

    DBLWizard Guest

    Howdy,

    I need to compine two columns (LastName, Firstname) with the comma.
    The only problem I have is the Firstname could be blank and in that
    case I don't want the "," appended to the last name.

    This data is coming out of a Sql Server data base. I am currently
    using a DataReader and simply binding it to a data grid to display the
    information.

    I realize that I could use a dataset/datatable instead of a reader and
    add a new column, read thorugh the datatable and build the new column.
    Then delete or hide the two original columns. But is that the
    best(most effecient) way to do it?

    Thanks

    dbl
    DBLWizard, Apr 1, 2005
    #1
    1. Advertising

  2. DBLWizard

    Brock Allen Guest

    You will need a template to do this. I assume you're using a DataGrid?

    <asp:DataGrid Runat=server ID=_grid>
    <Columns>
    <asp:TemplateColumn>
    <ItemTemplate>
    <%# GetFirstLastName(Container.DataItem) %>
    </ItemTemplate>
    </asp:TemplateColumn>
    </Columns>
    </asp:DataGrid>

    And then some code like this:

    <script runat="server">
    string GetFirstLastName(object row)
    {
    string fname = DataBinder.Eval(row, "FirstName") as string;
    string lname = DataBinder.Eval(row, "LastName") as string;
    if (fname == null || fname.Trim().Length == 0)
    {
    return lname;
    }
    return String.Format("{0}, {1}", lname, fname);
    }
    </script>

    Fill in your own logic (and testing too! -- I didn't run this code). You
    get the idea, though.

    -Brock
    DevelopMentor
    http://staff.develop.com/ballen



    > Howdy,
    >
    > I need to compine two columns (LastName, Firstname) with the comma.
    > The only problem I have is the Firstname could be blank and in that
    > case I don't want the "," appended to the last name.
    >
    > This data is coming out of a Sql Server data base. I am currently
    > using a DataReader and simply binding it to a data grid to display the
    > information.
    >
    > I realize that I could use a dataset/datatable instead of a reader and
    > add a new column, read thorugh the datatable and build the new column.
    > Then delete or hide the two original columns. But is that the
    > best(most effecient) way to do it?
    >
    > Thanks
    >
    > dbl
    >
    Brock Allen, Apr 1, 2005
    #2
    1. Advertising

  3. DBLWizard

    Karl Seguin Guest

    Just my $0.02...I think Brock's suggestion is the best, unless this is
    something that you'll be doing often. You mentioned a dataset and obviously
    taking advantage of the capability to cache them might be beneficial (merge
    the columns once into a new column and be done with it). Brock's way
    obviously does this work each time....since you didn't provide any broader
    scope about your usage, it's impossible to guess at which method might be
    best...but atleast wanted to point out the differences (even though they are
    somewhat obvious).

    Karl

    --
    MY ASP.Net tutorials
    http://www.openmymind.net/ - New and Improved (yes, the popup is
    annoying)
    http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
    come!)
    "Brock Allen" <> wrote in message
    news:...
    > You will need a template to do this. I assume you're using a DataGrid?
    >
    > <asp:DataGrid Runat=server ID=_grid>
    > <Columns>
    > <asp:TemplateColumn>
    > <ItemTemplate>
    > <%# GetFirstLastName(Container.DataItem) %>
    > </ItemTemplate>
    > </asp:TemplateColumn>
    > </Columns>
    > </asp:DataGrid>
    >
    > And then some code like this:
    >
    > <script runat="server">
    > string GetFirstLastName(object row)
    > {
    > string fname = DataBinder.Eval(row, "FirstName") as string;
    > string lname = DataBinder.Eval(row, "LastName") as string;
    > if (fname == null || fname.Trim().Length == 0)
    > {
    > return lname;
    > }
    > return String.Format("{0}, {1}", lname, fname);
    > }
    > </script>
    >
    > Fill in your own logic (and testing too! -- I didn't run this code). You
    > get the idea, though.
    >
    > -Brock
    > DevelopMentor
    > http://staff.develop.com/ballen
    >
    >
    >
    > > Howdy,
    > >
    > > I need to compine two columns (LastName, Firstname) with the comma.
    > > The only problem I have is the Firstname could be blank and in that
    > > case I don't want the "," appended to the last name.
    > >
    > > This data is coming out of a Sql Server data base. I am currently
    > > using a DataReader and simply binding it to a data grid to display the
    > > information.
    > >
    > > I realize that I could use a dataset/datatable instead of a reader and
    > > add a new column, read thorugh the datatable and build the new column.
    > > Then delete or hide the two original columns. But is that the
    > > best(most effecient) way to do it?
    > >
    > > Thanks
    > >
    > > dbl
    > >

    >
    >
    >
    Karl Seguin, Apr 1, 2005
    #3
  4. DBLWizard

    DBLWizard Guest

    Brock,

    I have it working sort of ... I get the columns I want but I also get
    all the other columns in the DataTable. Can you tell me what Im not
    doing or doing wrong? Here is my code that is on a button event:

    private void cmdSubmit_Click(object sender, System.EventArgs e)
    {
    // string sConnection = "Integrated Security=SSPI;Persist
    Security Info=False;database=LSICountyWeb;server=REVELATIONS;Connect
    Timeout=30";
    string sConnection = "user
    id=username;password=something;database=LSICountyWeb;server=REVELATIONS;Connect
    Timeout=30";
    SqlConnection myConn = new SqlConnection(sConnection);
    SqlCommand myCommand;
    SqlDataAdapter myDA = new SqlDataAdapter();
    DataSet myDS;
    string sName = "";
    string sSql = "";

    sName = txtName.Text;
    sSql = "Select top 100 [Name], [GivenName], Count(*) as
    Matches From NCLand Where [Name] Like '" + sName + "%' Group By [Name],
    [GivenName] Order by [Name], [GivenName]";

    if (sName.Length > 0)
    {
    myCommand = new SqlCommand(sSql, myConn);
    myDA = new SqlDataAdapter();
    myDA.SelectCommand = myCommand;
    myConn.Open();
    myDS = new DataSet();
    myDA.Fill(myDS, "SearchResults");
    grdMatching.DataSource =
    myDS.Tables["SearchResults"].DefaultView;
    grdMatching.DataBind();
    grdMatching.Visible = true;
    }
    }

    public string GetName(object row)
    {
    string sGivenName = DataBinder.Eval(row, "GivenName") as
    string;
    string sName = DataBinder.Eval(row, "Name") as string;

    if (0 != sGivenName.Length)
    {
    sName += "," + sGivenName;
    }

    return sName;
    }


    Here is the HTML code:

    <asp:DataGrid id="grdMatching" style="Z-INDEX: 105; LEFT:
    176px; POSITION: absolute; TOP: 176px"
    runat="server" Width="616px" Visible="False">
    <Columns>
    <asp:TemplateColumn>
    <ItemTemplate>
    <%# GetName(Container.DataItem) %>
    </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn>
    <ItemTemplate>
    <%# DataBinder.Eval(Container.DataItem,
    "Matches") %>
    </ItemTemplate>
    </asp:TemplateColumn>
    </Columns>
    </asp:DataGrid></form>
    DBLWizard, Apr 1, 2005
    #4
  5. DBLWizard

    Bruce Barker Guest

    you could have sql do the work also

    sSql = @"Select top 100
    [Name],
    [GivenName],
    case when GivenName <> '' then Name + ', ' + GivenName
    else Name
    ens as FullName,
    Count(*) as Matches
    From NCLand Where [Name] Like '" + sName + "%' Group By [Name],
    [GivenName] Order by [Name], [GivenName]";



    note: your sql allows sql injection which is a high secuirty risk.

    -- bruce (sqlwork.com)


    "DBLWizard" <> wrote in message
    news:...
    > Brock,
    >
    > I have it working sort of ... I get the columns I want but I also get
    > all the other columns in the DataTable. Can you tell me what Im not
    > doing or doing wrong? Here is my code that is on a button event:
    >
    > private void cmdSubmit_Click(object sender, System.EventArgs e)
    > {
    > // string sConnection = "Integrated Security=SSPI;Persist
    > Security Info=False;database=LSICountyWeb;server=REVELATIONS;Connect
    > Timeout=30";
    > string sConnection = "user
    > id=username;password=something;database=LSICountyWeb;server=REVELATIONS;Connect
    > Timeout=30";
    > SqlConnection myConn = new SqlConnection(sConnection);
    > SqlCommand myCommand;
    > SqlDataAdapter myDA = new SqlDataAdapter();
    > DataSet myDS;
    > string sName = "";
    > string sSql = "";
    >
    > sName = txtName.Text;
    > sSql = "Select top 100 [Name], [GivenName], Count(*) as
    > Matches From NCLand Where [Name] Like '" + sName + "%' Group By [Name],
    > [GivenName] Order by [Name], [GivenName]";
    >
    > if (sName.Length > 0)
    > {
    > myCommand = new SqlCommand(sSql, myConn);
    > myDA = new SqlDataAdapter();
    > myDA.SelectCommand = myCommand;
    > myConn.Open();
    > myDS = new DataSet();
    > myDA.Fill(myDS, "SearchResults");
    > grdMatching.DataSource =
    > myDS.Tables["SearchResults"].DefaultView;
    > grdMatching.DataBind();
    > grdMatching.Visible = true;
    > }
    > }
    >
    > public string GetName(object row)
    > {
    > string sGivenName = DataBinder.Eval(row, "GivenName") as
    > string;
    > string sName = DataBinder.Eval(row, "Name") as string;
    >
    > if (0 != sGivenName.Length)
    > {
    > sName += "," + sGivenName;
    > }
    >
    > return sName;
    > }
    >
    >
    > Here is the HTML code:
    >
    > <asp:DataGrid id="grdMatching" style="Z-INDEX: 105; LEFT:
    > 176px; POSITION: absolute; TOP: 176px"
    > runat="server" Width="616px" Visible="False">
    > <Columns>
    > <asp:TemplateColumn>
    > <ItemTemplate>
    > <%# GetName(Container.DataItem) %>
    > </ItemTemplate>
    > </asp:TemplateColumn>
    > <asp:TemplateColumn>
    > <ItemTemplate>
    > <%# DataBinder.Eval(Container.DataItem,
    > "Matches") %>
    > </ItemTemplate>
    > </asp:TemplateColumn>
    > </Columns>
    > </asp:DataGrid></form>
    >
    Bruce Barker, Apr 1, 2005
    #5
  6. Hi,
    You can change your SQL query to:

    Select top 100 [Name], [GivenName], [GivenName] + ISNULL(',' + [NAME],
    '') [FullName], Count(*) as Matches
    From NCLand
    Where [Name] Like '" + sName + "%'
    Group By [Name],[GivenName] Order by [Name], [GivenName]";

    "DBLWizard" <> wrote in message
    news:...
    > Howdy,
    >
    > I need to compine two columns (LastName, Firstname) with the comma.
    > The only problem I have is the Firstname could be blank and in that
    > case I don't want the "," appended to the last name.
    >
    > This data is coming out of a Sql Server data base. I am currently
    > using a DataReader and simply binding it to a data grid to display the
    > information.
    >
    > I realize that I could use a dataset/datatable instead of a reader and
    > add a new column, read thorugh the datatable and build the new column.
    > Then delete or hide the two original columns. But is that the
    > best(most effecient) way to do it?
    >
    > Thanks
    >
    > dbl
    >
    The Developer, Apr 1, 2005
    #6
  7. DBLWizard

    Brock Allen Guest

    Tell the DataGrid to not generate all the columns automatically:

    <asp:DataGrid AutoGenerateColumns=false>

    But then you'll have to tell it which columns to show:

    <Columns>
    <asp:BoundColumn HeaderText="MyColumn" DataField="DBColumnName" />
    </Columns>

    -Brock
    DevelopMentor
    http://staff.develop.com/ballen



    > Brock,
    >
    > I have it working sort of ... I get the columns I want but I also get
    > all the other columns in the DataTable. Can you tell me what Im not
    > doing or doing wrong? Here is my code that is on a button event:
    >
    > private void cmdSubmit_Click(object sender, System.EventArgs e)
    > {
    > // string sConnection = "Integrated Security=SSPI;Persist
    > Security Info=False;database=LSICountyWeb;server=REVELATIONS;Connect
    > Timeout=30";
    > string sConnection = "user
    > id=username;password=something;database=LSICountyWeb;server=REVELATION
    > S;Connect
    > Timeout=30";
    > SqlConnection myConn = new SqlConnection(sConnection);
    > SqlCommand myCommand;
    > SqlDataAdapter myDA = new SqlDataAdapter();
    > DataSet myDS;
    > string sName = "";
    > string sSql = "";
    > sName = txtName.Text;
    > sSql = "Select top 100 [Name], [GivenName], Count(*) as
    > Matches From NCLand Where [Name] Like '" + sName + "%' Group By
    > [Name],
    > [GivenName] Order by [Name], [GivenName]";
    >
    > if (sName.Length > 0)
    > {
    > myCommand = new SqlCommand(sSql, myConn);
    > myDA = new SqlDataAdapter();
    > myDA.SelectCommand = myCommand;
    > myConn.Open();
    > myDS = new DataSet();
    > myDA.Fill(myDS, "SearchResults");
    > grdMatching.DataSource =
    > myDS.Tables["SearchResults"].DefaultView;
    > grdMatching.DataBind();
    > grdMatching.Visible = true;
    > }
    > }
    > public string GetName(object row)
    > {
    > string sGivenName = DataBinder.Eval(row, "GivenName") as
    > string;
    > string sName = DataBinder.Eval(row, "Name") as string;
    > if (0 != sGivenName.Length)
    > {
    > sName += "," + sGivenName;
    > }
    > return sName;
    > }
    > Here is the HTML code:
    >
    > <asp:DataGrid id="grdMatching" style="Z-INDEX: 105; LEFT:
    > 176px; POSITION: absolute; TOP: 176px"
    > runat="server" Width="616px" Visible="False">
    > <Columns>
    > <asp:TemplateColumn>
    > <ItemTemplate>
    > <%# GetName(Container.DataItem) %>
    > </ItemTemplate>
    > </asp:TemplateColumn>
    > <asp:TemplateColumn>
    > <ItemTemplate>
    > <%# DataBinder.Eval(Container.DataItem,
    > "Matches") %>
    > </ItemTemplate>
    > </asp:TemplateColumn>
    > </Columns>
    > </asp:DataGrid></form>
    Brock Allen, Apr 1, 2005
    #7
  8. DBLWizard

    DBLWizard Guest

    Bruce,

    Where am I vulnerable here to sql injection? I was not too worried
    about it in this case becuase the account that is used for this is read
    only but I would like to know better how to handle sql injection and I
    didnt think I was open to that in this query.

    Thanks

    dbl
    DBLWizard, Apr 1, 2005
    #8
  9. DBLWizard

    Bruce Barker Guest

    in the search name field on your form type:

    a'' delete NCLand select * from NCLand where name=''a


    -- bruce (sqlwork.com)




    "DBLWizard" <> wrote in message
    news:...
    > Bruce,
    >
    > Where am I vulnerable here to sql injection? I was not too worried
    > about it in this case becuase the account that is used for this is read
    > only but I would like to know better how to handle sql injection and I
    > didnt think I was open to that in this query.
    >
    > Thanks
    >
    > dbl
    >
    Bruce Barker, Apr 2, 2005
    #9
  10. I agree. Doing this work in the query (preferably a stored procedure) is
    the most efficient solution in most cases.

    --
    I hope this helps,
    Steve C. Orr, MCSD, MVP
    http://SteveOrr.net


    "The Developer" <> wrote in message
    news:e%...
    > Hi,
    > You can change your SQL query to:
    >
    > Select top 100 [Name], [GivenName], [GivenName] + ISNULL(',' +
    > [NAME],
    > '') [FullName], Count(*) as Matches
    > From NCLand
    > Where [Name] Like '" + sName + "%'
    > Group By [Name],[GivenName] Order by [Name], [GivenName]";
    >
    > "DBLWizard" <> wrote in message
    > news:...
    >> Howdy,
    >>
    >> I need to compine two columns (LastName, Firstname) with the comma.
    >> The only problem I have is the Firstname could be blank and in that
    >> case I don't want the "," appended to the last name.
    >>
    >> This data is coming out of a Sql Server data base. I am currently
    >> using a DataReader and simply binding it to a data grid to display the
    >> information.
    >>
    >> I realize that I could use a dataset/datatable instead of a reader and
    >> add a new column, read thorugh the datatable and build the new column.
    >> Then delete or hide the two original columns. But is that the
    >> best(most effecient) way to do it?
    >>
    >> Thanks
    >>
    >> dbl
    >>

    >
    >
    Steve C. Orr [MVP, MCSD], Apr 2, 2005
    #10
  11. DBLWizard

    Brock Allen Guest

    Use parameters where ever you'd normally concatenate user input:

    cmd.CommandText = "update table set column = @newValue where x = 5"
    cmd.Parameters.Add("@newValue", "some value the user typed in")

    -Brock
    DevelopMentor
    http://staff.develop.com/ballen



    > Bruce,
    >
    > Where am I vulnerable here to sql injection? I was not too worried
    > about it in this case becuase the account that is used for this is
    > read only but I would like to know better how to handle sql injection
    > and I didnt think I was open to that in this query.
    >
    > Thanks
    >
    > dbl
    >
    Brock Allen, Apr 2, 2005
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Davide Vernole [MVP]

    Re: combine datagrid columns

    Davide Vernole [MVP], Sep 8, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    770
    Davide Vernole [MVP]
    Sep 8, 2004
  2. ad
    Replies:
    1
    Views:
    371
    =?Utf-8?B?dmVlcmEgc2VraGFyIGtvdGE=?=
    Jul 14, 2005
  3. David Lozzi

    Combine 2 or more XMLDocuments into one...

    David Lozzi, Oct 18, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    570
    Steven Cheng[MSFT]
    Oct 19, 2005
  4. loudking
    Replies:
    1
    Views:
    380
    Bergamot
    Jun 4, 2007
  5. Kev Jackson
    Replies:
    12
    Views:
    180
    Adam Sanderson
    Jan 12, 2006
Loading...

Share This Page