I am writing an SQL query in which that I will need to perform a sub select on a table, which will usually return multiple rows. I need to be able to join together the results of a certain field from all the rows into one field to output. Is this possible, and how?
For example, if the SQL query returns
id | field
1 | test1
2 | test2
3 | test3
I need the outputted field to be "test1 test2 test3".
I have a problem where I would like to get a comma-separated list of items from a couple of tables as part of a set of results. All the searching I've done so far has produced results for dealing with one or two tables. I have four, and can't get my head around how to adapt the sql to deal with it.
I have the following layout:
*Table_Castings*
+---- CastingID {PK}
| MouldID
| SerialNo
| CastRef
| MeltCode
| Signature
|
|
| *Table_CastCertificates* *Table_Certificates*
| CertificateID {PK} ----------- CertificateID {PK}
+---- CastingID {PK} Description
FileID -----------------+
|
|
*Table_CertificateFiles* |
FileID {PK} -------------+
FullLocation
The results I would like include the following columns:
CastingID, MouldID, SerialNo, CastRef, MeltCode, [Certificates],
Where Certificates is a comma separated list of Description + " " + FullLocation records. (What I'll actually do is add http:// and other html codes to make FullLocation a hyperlink, but I can do that.)
Some SQL I've already got is this:
DECLARE @str VARCHAR(500)
SELECT @str = COALESCE(@str + ', ','') + '<a href="http://' + Table_CertificateFiles.FullLocation + '">' + Table_Certificates.Description + '</a>'
FROM Table_CastCertificates INNER JOIN
Table_Certificates ON Table_CastCertificates.CertificateID = Table_Certificates.CertificateID INNER JOIN
Table_CertificateFiles ON Table_CastCertificates.FileID = Table_CertificateFiles.FileID
WHERE (CastingID = 45)
SELECT output = @str
This actually gives all records in the for the specific casting, but I keep getting an error at the "=" in @str = COALESCE when I try joining it to another query to get each casting.
how to display multiple records in a label from database through coding?
View 1 RepliesI currently have a datasource that displays several rows on an editable gridview where the user can update several rows at once. Each row contains an ActivityTotal column per row. I'd like to sum all the ActivityTotal rows displayed and have one totalValue.
for example
row 1 activityTotal = 4
row 2 activityTotal = 3
row 3 activityTotal = 1
totalValue = 8
How can I incorporate this into the code?
I have a gridview with textbox and that textbox is for searching. My question is do you know how to search with all fields in gridview.For example: I have a gridview contains, FIRST NAME, MIDDLE NAME, and LAST NAME, and if I type in the textbox the gridview will automatically get what I've inout in textbox.
View 1 Replieshow to put three rows of one table data in gridview one coloumn
how to add text1,text2,text3 rows data in upper as i write but the proper code
<asp:BoundField ItemStyle-Width = "150px" DataField = "LT_SHOWNAMETEXT1" HeaderText = "SHOW NAME"/>
<asp:BoundField ItemStyle-Width = "150px" DataField = "LT_TEXTTYPE" HeaderText = "TEXT TYPE"/>
<asp:BoundField ItemStyle-Width = "150px" DataField = "text1,text2,tex3 " HeaderText = "TEXT"/>
[Code].....
I saw post [URL] .... which is converting rows into column item,quantity ,price. I have one other image field .how can bind image in this program?
View 1 RepliesI have a gridview with 3 columns. In second column I want to display some text in 2 rows and then a gridview.
Here is my code:
[Code]....
The problem is my third row is displayed as a row under first column. How do I make it a separate column in my girdview. Not sure what is going wrong.
I need to produce a from where I can add multiple parts. Each time a user enters a part number, the description should be populated. The user can then click add and this part and description will be shown below. They have the option to add as many parts as they require. Once all parts are added the user fills in some general info eg description and then once a submit button is clicked all part info will be inserted into a table and general info will be inserted into another table with one unique ID.
View 5 RepliesI am listing about 20 rows, each row represents an Order.Each row needs to have 3 buttons, each button click will perform a different action.I have 3 actions to handle each button post request, I am just unsure how to setup the Html forms for each button.
<tr>
<td>
<form method="post" action="/orders/do1"><input type=button ... /></form>
<form method="post" action="/orders/do2"><input type=button ... /></form> [code]....
Should I create 3 forms for each button, per row in my listing?(that would mean 20 rows x 3 forms = 60 forms on a page)Is that 'ok' to do? (i.e. or is there a better way to do this?)
I am working on Crystal reports.My requirement is that i need to generate dynamic fields according to the count ofFamily_Members column in sql server database,for example if i have '4' in 'Family_Members' column i need to generate 4(Family members details)dynamically.
View 1 RepliesI have a GridView bound to an ObjectDataSource. Most of the columns are directly databound, however one column is not - it's value is determined in code. The value is being calculated correctly at the RowDataBound event, however when there is more than one data row in the GridView, the calculated fields for all of the rows is blank, but the top row displays the bottom row's calculated field
View 2 RepliesI have created DetailsView in designer page with SqlDataSource, Details view is with autoGenerateRows="false" i.e. I have declared my own fields. it is fine there, but what if I want to add these fields dynamically from codebehind with select & edit commands. I want to to use Details View in designer page and want to add fields from codebehind
View 1 RepliesI work with Visual studio 2005 and Sql server data base. I extract 1 million rows from a database and I put the rows in a Sql table. With a simple direct extraction, in a few minutes I complete the operation:
Dim cmd As New SqlCommand(query, cnSql)
cmd.ExecuteNonQuery()
cmd.Dispose().
But, if I must select and modify some fields between extraction and introduction, it takes a lot of time!
I work in this way. I extract rows from Dbase, Table1, and I put all into a Collection using dataReader:
Dim cmd As New SqlCommand(query, cnSql)
Dim coll As New Collection
coll.Clear()
Dim dr As SqlDataReader = cmd.ExecuteReader()
I replace some fields with the string "YES".
Dim toRepl As String = ""
If dr.HasRows Then
Do While dr.Read
toRepl = dr(5).ToString
If Len(toRepl) <= 3 Then ToRepl = "YES"
collPrel.Add(dr(0).ToString)
collPrel.Add(dr(1).ToString)
.................. ..............
collPrel.Add(toRepl)
collCount = collCount + 1
Loop
End If
dr.Close()
cmd.Dispose()
Now, I insert all into a SQL Table2:
Dim a As String = "", b As String = "", c As String = "" etc.
For i = 1 To collPrel.Count Step 6
a = collPrel.Item(i).ToString
b = collPrel.Item(i + 1).ToString
.............. ...............
f = collPrel.Item(i + 5).ToString
queryIns = "INSERT INTO Tab (name, adress, etc)
VALUES ('" & a & "', '" & b & "', '" & c & "', etc. )
Dim cmdIns As New SqlCommand(queryIns, cnSql)
cmdIns.CommandTimeout = 600
cmdIns.ExecuteNonQuery()
cmdIns.Dispose()
Next
This way to work goes right but it is necessary a lot of time for completing the operation.
I have a GridView with a SQLDatasource. All of the columns in the gridview are bound to a field. Now they want a column that is not a field returned but would be two of the fields combined together. How can I do this exactly? Can someone show a line of code to display this in a column?
View 8 RepliesIn sql table I have fields which needs to be mapped with ALIAS field names. How to do the mapping when coding. Currently I have
select *,,data_type from mappingtable,information_schema.columns where where mainfield='City' or aliasfield ='City'
execute query using reader
This table does not contain the actual data
Its contains onky the mainfield and its alias field name
I get City from a variable
I have a second table which contains mainfield ie the column names & the actual data then I use
if( reader["data_type"].ToString() == "numeric")
do something and so on
make label control visible true
else
make dropdown visible true
but there is lot of performance and speed issue. What would be the correct method so as to enhance the above...
I have a question in gridview, I want to combine my two or more records into a single cell with two rowsfor Example i have a ID, Name, Year Start, and Year End for Column name in database and i want call it into my gridview that the Year Start and Year End will combine to Year like:
In Database
ID Name Year Start Year End
1 Yourname 2010 2015
In Gridview:
ID Name Year
1 Yourname 2010 2015
in a year column i want it to two row..
I want to bind three column with one row in gridview in asp.net with c#,
i want like this,
Name | Info
----------------------
name | lastname
| phone number
| address
I get this by default
name | last name | phone number | address
I'm having a strange behavior in my .aspx page. I'm internationalizing some pages, but as I want to keep only entities or value object in my resource I want to have a key-value like:
(pt-br)
CITY - Cidade
STATE - Estado
But when I list the entities, I want to put the " : " at end. Like:
<asp:Label ID="LabelCity" runat="server" Text="<%$ Resources:Localizacao, CITY %>:"></asp:Label>
But, if I put the " : " after the resource in text property, the page only shows " : ".
My simple solution is put after all definition of label, but I think this too wrong:
<asp:Label ID="LabelCity" runat="server" Text="<%$ Resources:Localizacao, CITY %>"></asp:Label>:
I have a web page with an object data source that is connect to an object access layer which retrirevs an Id, Name_En and Name_Local from some table, the object data source is connected to a GirdView, In the girdview i have a template control with a label inside it, i want to change the binding expression for the label depending on the current states of localization so for example if the page is arabic the label should bind to "Name_Local", if it's english then it should bind to "Name_En"
View 1 Repliesi have the below query where MessageText column is of ntext datatype..whe n i run the below query it is giving me the below error. Error :The data types ntext and varchar are incompatible in the add operator.
select li.LookUpPageInfoId,
li.PageMessageID,
li.PathMessageID,
li.IsActive,
m.MessageID,
(select messagetext from messages where messageid=pagemessageid and channelid=m.ChannelID)+'/'+
m.MessageText as TotalText,
m.ChannelID
from
LookUpPageInfo as li left outer join
Messages as m on m.MessageID=li.PathMessageID and li.IsActive=1
Can some one suggest me how to concatenate the ntext column....but the above works good if the column MessageText is of nvarchar(max) datatype.
I have been led to believe that the performance benefits of using StringBuilder over normal concatenation means that you should always use stringbuilder when concatenating more than 2 strings. However when I test them this does not seem to be the case. Take these 2 methods
public void TestMethod5()
{
for (int i = 0; i < 1000; i++)
{
string t = string.Empty;
t = "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd"; ;
}
}
public void TestMethod6()
{
for (int i = 0; i < 1000; i++)
{
StringBuilder sb = new StringBuilder();
sb.Append("dddddddd");
sb.Append("dddddddd");
sb.Append("dddddddd");
sb.Append("dddddddd");
sb.Append("dddddddd");
sb.Append("dddddddd");
sb.Append("dddddddd");
sb.Append("dddddddd");
sb.Append("dddddddd");
sb.Append("dddddddd");
sb.Append("dddddddd");
sb.Append("dddddddd");
sb.Append("dddddddd");
string t = string.Empty;
t = sb.ToString();
}
}
TestMethod5 takes 0.03 milliseconds so complete but TestMethod6 takes 1.43 milliseconds What is going on?
I need to concatenate several columns of a table into a single value, then show that value in an asp dropdownlist. The SQL code I'm issuing is as follows:
SELECT UserID, CustomerNum, UserName + ' - ' + UserAddress + ',' + UserCity + ' ' + UserState AS UserInfo
FROM Users
WHERE (CustomerNum = @CustomerNum)
ORDER BY UserName
I then set 'UserInfo' as the text field in the dropdownlist.This generally works, except occasionally one of the columns in the database is null (for example, UserState). When that happens, the entire concatenation is null, and I get an empty entry in the dropdownlist.
Is there something in SQLServer that will allow me to ignore those NULL results, or will I have to code something up in the DataBind event?
Trying to localize text in ASP.NET labels... want to add a ':' after the localized text. I could just add the ':' to the localized text in the resource file, but that seems silly... there should be an easy way to do this.
<asp:Label id="RoleTypeLabel" runat="server" Text='<%$ Resources: GS3, RoleTypeLabel %>:' AssociatedControlID="RoleTypeDropDown"></asp:Label>
(note the ':' at the end of Text='...')
Of course, this doesn't work... and neither does anything I can think of to concatenate a ':' onto the end of the localized text.
I'm trying to use jQuery validation for a dynamic form I'm setting up.
In some cases this form contains a set of input boxes which are suppose to total 100.
An example might be:
indicate what percentage of students are in each grade?
Grade 9: TextBox1
Grade 10: TextBox2
Grade 11: TextBox3
Grade 12: TextBox4
I want to verify that TextBox1 + TextBox2 + TextBox3 + TextBox4 = 100%.
How do I go about this?