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 10 1952
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
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****@NOSPAMdevelop.com> wrote in message
news:32**********************@msnews.microsoft.com ... 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,
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:
<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>
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*********@yahoo.com> wrote in message
news:11**********************@l41g2000cwc.googlegr 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:
<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>
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*********@yahoo.com> wrote in message
news:11*********************@l41g2000cwc.googlegro 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
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=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:
<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,
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
in the search name field on your form type:
a'' delete NCLand select * from NCLand where name=''a
-- bruce (sqlwork.com)
"DBLWizard" <ib*********@yahoo.com> wrote in message
news:11**********************@g14g2000cwa.googlegr 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
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*******@gmail.com> wrote in message
news:e%****************@TK2MSFTNGP10.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*********@yahoo.com> wrote in message news:11*********************@l41g2000cwc.googlegro 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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Lachlan Hunt |
last post by:
Hi,
I have recently downloaded and experemented with IBM HPR 3.0, and
Opera 8 with text-to-speech, and have come to realise some fairly
annoying issues regarding punctuation marks.
I've found,...
|
by: Lars E. |
last post by:
Hi all.
I am trying to combine data from 2 tables in one datagridview.
I have tables: "Customer" and "CustomerContact".
I want to display Customer information (displaying all fields in...
|
by: tshad |
last post by:
I have about 10 functions that are identical except for one variable name
that I am using in my "for" loop:
for (ktr1=1;ktr1<=nhDataBean.projectCodeList.GetUpperBound(0);ktr1++)
for...
|
by: zwieback89 |
last post by:
Hi,
I am still not able to get this working in a simple page. Please help me. I am badly stuck....
I am trying to view the contents of the Beverage Category. I have built it like a hierarchy...
|
by: dayhill |
last post by:
I am trying to combine three columns (month, day, year) to make one date (month-day-year).
Here is the intital inforation when I hit the SQL button in Microsoft Query ...
|
by: denny1824 |
last post by:
Hi everyone,
Here is my problem. I have a Table Variable (I could easily turn it into a Temp Table instead) that will sometimes have rows that are identical except for one specific column. The...
|
by: bluereign |
last post by:
Thank you for your assistance. I am a novice looking to JOIN and append or combine records from 2 current Tables into 2 new Tables named below. I have been able to JOIN Tables with the script...
|
by: vineetbindal |
last post by:
Hi All.
I have two Columns column1 and column2. i have to run a query with some value from colum1 depending on it will select result from coloumn2 and if that result is present in coloumn 1 it...
|
by: stateemk |
last post by:
I have a db with a table that has many columns of info for various entities. In this table, there are three columns called, entity name, second entity name and third entity name. I need to combine...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |