Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Combine 2 Columns to one with punctuation

Reply
Thread Tools

Combine 2 Columns to one with punctuation

 
 
DBLWizard
Guest
Posts: n/a
 
      04-01-2005
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

 
Reply With Quote
 
 
 
 
Brock Allen
Guest
Posts: n/a
 
      04-01-2005
You will need a template to do this. I assume you're using a DataGrid?

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

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
>




 
Reply With Quote
 
 
 
 
Karl Seguin
Guest
Posts: n/a
 
      04-01-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) ...
> You will need a template to do this. I assume you're using a DataGrid?
>
> <aspataGrid Runat=server ID=_grid>
> <Columns>
> <asp:TemplateColumn>
> <ItemTemplate>
> <%# GetFirstLastName(Container.DataItem) %>
> </ItemTemplate>
> </asp:TemplateColumn>
> </Columns>
> </aspataGrid>
>
> 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
> >

>
>
>



 
Reply With Quote
 
DBLWizard
Guest
Posts: n/a
 
      04-01-2005
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=REVELATION S;Connect
Timeout=30";
string sConnection = "user
id=username;password=something;database=LSICountyW eb;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:

<aspataGrid 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>
</aspataGrid></form>

 
Reply With Quote
 
Bruce Barker
Guest
Posts: n/a
 
      04-01-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.com...
> 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=REVELATION S;Connect
> Timeout=30";
> string sConnection = "user
> id=username;password=something;database=LSICountyW eb;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:
>
> <aspataGrid 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>
> </aspataGrid></form>
>



 
Reply With Quote
 
The Developer
Guest
Posts: n/a
 
      04-01-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) ups.com...
> 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
>



 
Reply With Quote
 
Brock Allen
Guest
Posts: n/a
 
      04-01-2005
Tell the DataGrid to not generate all the columns automatically:

<aspataGrid 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=REVELATION S;Connect
> Timeout=30";
> string sConnection = "user
> id=username;password=something;database=LSICountyW eb;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:
>
> <aspataGrid 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>
> </aspataGrid></form>




 
Reply With Quote
 
DBLWizard
Guest
Posts: n/a
 
      04-01-2005
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

 
Reply With Quote
 
Bruce Barker
Guest
Posts: n/a
 
      04-01-2005
in the search name field on your form type:

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


-- bruce (sqlwork.com)




"DBLWizard" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.com...
> 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
>



 
Reply With Quote
 
Steve C. Orr [MVP, MCSD]
Guest
Posts: n/a
 
      04-02-2005
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" <(E-Mail Removed)> wrote in message
news:e%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed) ups.com...
>> 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
>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
CSS Problem: How to combine three columns with round corner box? loudking HTML 1 06-04-2007 12:57 PM
Finding a sentence (more than one word & punctuation (, . ;)) ina string? Kev Jackson Ruby 12 01-12-2006 05:43 PM
Re: combine datagrid columns Davide Vernole [MVP] ASP .Net 0 09-08-2004 06:48 PM
Re: Regular expression for punctuation Chris R. Timmons ASP .Net 0 07-10-2003 03:57 AM
Regular expression for punctuation Chris Leffer ASP .Net 0 07-09-2003 02:48 PM



Advertisments