Combine 2 Columns to one with punctuation

[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

[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:TemplateCo lumn>
<ItemTemplate >
<%# GetFirstLastNam e(Container.Dat aItem) %>
</ItemTemplate>
</asp:TemplateCol umn>
</Columns>
</asp:DataGrid>

And then some code like this:

<script runat="server">
string GetFirstLastNam e(object row)
{
string fname = DataBinder.Eval (row, "FirstName" ) as string;
string lname = DataBinder.Eval (row, "LastName") as string;
if (fname == null || fname.Trim().Le ngth == 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]

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" <ba****@NOSPAMd evelop.com> wrote in message
news:32******** **************@ msnews.microsof t.com...

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

<asp:DataGrid Runat=server ID=_grid>
<Columns>
<asp:TemplateCo lumn>
<ItemTemplate >
<%# GetFirstLastNam e(Container.Dat aItem) %>
</ItemTemplate>
</asp:TemplateCol umn>
</Columns>
</asp:DataGrid>

And then some code like this:

<script runat="server">
string GetFirstLastNam e(object row)
{
string fname = DataBinder.Eval (row, "FirstName" ) as string;
string lname = DataBinder.Eval (row, "LastName") as string;
if (fname == null || fname.Trim().Le ngth == 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

[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.EventArg s e)
{
// string sConnection = "Integrated Security=SSPI;P ersist
Security Info=False;data base=LSICountyW eb;server=REVEL ATIONS;Connect
Timeout=30";
string sConnection = "user
id=username;pas sword=something ;database=LSICo untyWeb;server= REVELATIONS;Con nect
Timeout=30";
SqlConnection myConn = new SqlConnection(s Connection);
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.SelectComm and = myCommand;
myConn.Open();
myDS = new DataSet();
myDA.Fill(myDS, "SearchResults" );
grdMatching.Dat aSource =
myDS.Tables["SearchResu lts"].DefaultView;
grdMatching.Dat aBind();
grdMatching.Vis ible = 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.Leng th)
{
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:TemplateCo lumn>
<ItemTemplate >
<%# GetName(Contain er.DataItem) %>
</ItemTemplate>
</asp:TemplateCol umn>
<asp:TemplateCo lumn>
<ItemTemplate >
<%# DataBinder.Eval (Container.Data Item,
"Matches") %>
</ItemTemplate>
</asp:TemplateCol umn>
</Columns>
</asp:DataGrid></form>

[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)
"DBLWizard" <ib*********@ya hoo.com> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.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.EventArg s e)
{
// string sConnection = "Integrated Security=SSPI;P ersist
Security Info=False;data base=LSICountyW eb;server=REVEL ATIONS;Connect
Timeout=30";
string sConnection = "user
id=username;pas sword=something ;database=LSICo untyWeb;server= REVELATIONS;Con nect
Timeout=30";
SqlConnection myConn = new SqlConnection(s Connection);
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.SelectComm and = myCommand;
myConn.Open();
myDS = new DataSet();
myDA.Fill(myDS, "SearchResults" );
grdMatching.Dat aSource =
myDS.Tables["SearchResu lts"].DefaultView;
grdMatching.Dat aBind();
grdMatching.Vis ible = 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.Leng th)
{
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:TemplateCo lumn>
<ItemTemplate >
<%# GetName(Contain er.DataItem) %>
</ItemTemplate>
</asp:TemplateCol umn>
<asp:TemplateCo lumn>
<ItemTemplate >
<%# DataBinder.Eval (Container.Data Item,
"Matches") %>
</ItemTemplate>
</asp:TemplateCol umn>
</Columns>
</asp:DataGrid></form>

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

"DBLWizard" <ib*********@ya hoo.com> wrote in message
news:11******** *************@l 41g2000cwc.goog legroups.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

[Brock Allen]

Tell the DataGrid to not generate all the columns automatically:

<asp:DataGrid AutoGenerateCol umns=false>

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

<Columns>
<asp:BoundColum n HeaderText="MyC olumn" DataField="DBCo lumnName" />
</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.EventArg s e)
{
// string sConnection = "Integrated Security=SSPI;P ersist
Security Info=False;data base=LSICountyW eb;server=REVEL ATIONS;Connect
Timeout=30";
string sConnection = "user
id=username;pas sword=something ;database=LSICo untyWeb;server= REVELATION
S;Connect
Timeout=30";
SqlConnection myConn = new SqlConnection(s Connection);
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.SelectComm and = myCommand;
myConn.Open();
myDS = new DataSet();
myDA.Fill(myDS, "SearchResults" );
grdMatching.Dat aSource =
myDS.Tables["SearchResu lts"].DefaultView;
grdMatching.Dat aBind();
grdMatching.Vis ible = 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.Leng th)
{
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:TemplateCo lumn>
<ItemTemplate >
<%# GetName(Contain er.DataItem) %>
</ItemTemplate>
</asp:TemplateCol umn>
<asp:TemplateCo lumn>
<ItemTemplate >
<%# DataBinder.Eval (Container.Data Item,
"Matches") %>
</ItemTemplate>
</asp:TemplateCol umn>
</Columns>
</asp:DataGrid></form>

[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

[Bruce Barker]

in the search name field on your form type:

a'' delete NCLand select * from NCLand where name=''a
-- bruce (sqlwork.com)


"DBLWizard" <ib*********@ya hoo.com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.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

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

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net
"The Developer" <pi*******@gmai l.com> wrote in message
news:e%******** ********@TK2MSF TNGP10.phx.gbl. ..

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" <ib*********@ya hoo.com> wrote in message
news:11******** *************@l 41g2000cwc.goog legroups.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