Combine 2 Columns to one with punctuation

D

DBLWizard

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
 
B

Brock Allen

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.
 
K

Karl Seguin

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!)
 
D

DBLWizard

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>
 
B

Bruce Barker

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)
 
T

The Developer

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]";
 
B

Brock Allen

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>
 
D

DBLWizard

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
 
B

Bruce Barker

in the search name field on your form type:

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


-- bruce (sqlwork.com)
 
S

Steve C. Orr [MVP, MCSD]

I agree. Doing this work in the query (preferably a stored procedure) is
the most efficient solution in most cases.




The Developer said:
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 said:
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
 
B

Brock Allen

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")
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,731
Messages
2,569,432
Members
44,832
Latest member
GlennSmall

Latest Threads

Top