ADO.NET :: Convert SQL Concatenate To LINQ Concatenate Query?
Oct 20, 2010
convert the code below to LINQ, converting my project to a wseb version usimg LINQ To SQL. The project contains 10 textboxes to possibly select from, but I'm only providing the first two textboxes to simplify my question.
mySQL_Statement = "SELECT IDENTIFICATION_DATA.NSC,ITEMISCD.RNC,ITEMISCD.NSC1 FROM IDENTIFICATION_DATA LEFT OUTER JOIN ITEMISCD on IDENTIFICATION_DATA.NIIN = ITEMISCD.NIIN"
If Not ((TextBox1.Text = String.Empty) And (TextBox2.Text = String.Empty) Then
mySQL_Statement = mySQL_Statement + " where "
If Not (TextBox1.Text = String.Empty) Then
mySQL_Statement = mySQL_Statement + "IDENTIFICATION_DATA.NSC IN (" + TextBox1.Text & ")"
End If
If (TextBox1.Text = String.Empty) Then
mySQL_Statement = mySQL_Statement + "isnull(IDENTIFICATION_DATA.NIIN) = FALSE"
End If
If Not (TextBox2.Text = String.Empty) Then
If astrixState = 0 Then
If Not (TextBox1.Text = String.Empty) Then
mySQL_Statement = mySQL_Statement + " AND IDENTIFICATION_DATA.NIIN IN (" + TextBox2.Text & ")"
ElseIf (TextBox1.Text = String.Empty) Then
mySQL_Statement = mySQL_Statement + " AND IDENTIFICATION_DATA.NIIN IN (" + TextBox2.Text & ")"
MsgBox(mySQL_Statement)
End If
End If
If astrixState = 1 Then
If Not (TextBox1.Text = String.Empty) Then
mySQL_Statement = mySQL_Statement + " OR IDENTIFICATION_DATA.NIIN IN (" + TextBox2.Text & ")"
Else
mySQL_Statement = mySQL_Statement + " AND IDENTIFICATION_DATA.NIIN IN (" + TextBox2.Text & ")"
End If
End If
If astrixState = 2 Then
If Not (TextBox1.Text = String.Empty) Then
mySQL_Statement = mySQL_Statement + "AND IDENTIFICATION_DATA.NIIN NOT IN (" + TextBox2.Text & ")"
Else
mySQL_Statement = mySQL_Statement + " AND IDENTIFICATION_DATA.NIIN NOT IN (" + TextBox2.Text & ")"
End If
End If
End If
I need to concatenate address string. Is it better to concatenate using the formula field or concatenate in the Sql query itself? which one would be faster?
I am working on an application that requires a lot of data sets being sent to the client when application starts. To avoid multiple trips to the server I want to combine all the data together and send it as one piece. I cannot build one single SQL statement, it's going to be many. Should I just put SQL results together like strings?
when I do so it simply concats the thing like value of rblxx.SelectedIndex "0" and 1 makes 01 and not 1.suggest some function and trick to add the numeric values not concat. I tried using System.Convert.ToInt32 but not worked for me.
i need to concatenate the html tag like <br/> and to the textbox.text in asp.net textbox. i have used this txtMessage.Text + <br/> + strgetlist; but it is displaying TaskName<br/>Project1,project2.. how to give break and space between thest two.
Does anyone know how I can concatenate two columns in my subsonic datasource? I want to display a first and last name in this dropdownlist but I don't want to put them in the same SQL column.
I am using the agility pack to do some screens scraping and my code so far to get titles is:
foreach (HtmlNode title in root.SelectNodes("//html//body//div//div//div[3]//div//div//div//div[3]//ul//li[1]//h4")) { string titleString = "<div class="show">" + title.InnerText + "</div>"; shows.Add(titleString); }
Before the title I want a timestamp related to the title and it has the node /html/body/div/div/div[3]/div/div/div/div[3]/ul/li[1]/ul/li/span. How can I get this value next to the title? So something like: string titleString = "<div class="show">" + time.InnerText + " - " + title.InnerText + "</div>";
I have a stored procedure named spGetCustomerByCustID that returns all of the fields for a customer whose CustID matches the parameter. I'm using this to populate a drop down with the last names of the customers.
I want the drop down list to be populated with a concatenation of the LastName and the FirstName in that order with a comma and space separating them. So it would be like "Smith, John". It should be easy since my procedure is returning both values. I don't understand how to do this though. I'm not sure if it should be done with the DataTextField or the SelectedValue.
I Have a DataTable with a lot of Rows and Columns.
Some of these Rows have the Same "OrderId", actually these rows are almost the same, but I have a specific value in the "TagList" Column that I have to put in the previous row.
Making a unique Row with all the TagList values inside the TagList Column.
I'll give a example below:
Line 1: ID: 1 Name: John OrderId: 1500 TagList: AG75
Line2: ID: 2 Name: John OrderId: 1500 TagList: BG99
I wanna do it: Line: ID: 1 Name: Mark OrderId: 1500 TagList: AG75, BG99
ie: Concatenate the TagList's values when they have the same OrderId.
How would I go about accessing these data items in code behind so I can concatenate them into one easy variable and miss out columns that have no data in, i have fields:
address address1 town county postcode
I don't have a problem with the concatenation just accessing the data items.
I am getting data out via a dataset and binding it to a listview.
Is it possible to access data items in the code behind to format or do whatever i want with them then showing it in the list view such as, concatenating the address fields into one variable?
I am trying to concatenate two columns (text and date) in a dropdownlist. In sql query: SelectCommand="SELECT [name] + ' , ' + [date] AS abc FROM [table] ">It gives this error: The data types nvarchar(max) and date are incompatible in the add operator.
I made a thread about getting all values of checkbox in my database, fortunately it was an easy task. I have 12 list items and the last item is "Others, specify:", wherein there would be a textbox near it for input. Obviously, it will be use to make additional data that is not included in the list.
Here's it is:
[Code]....
I made my server side code to concatenate multiples values that will be sent into my database from the user's checked items. Here it is:
[Code]....
My question is, how could I concatenate the inputted values that would be made by user on the textbox to the checkbox column on my database. I'm thinking like this:
[Code]....
Now if this is a decent method, then I would like to ask how could I set a parameter on otherattainment.Text without making a new column for it in my database. Obviously to avoid SQL Injection.
EDIT:
I just put cmd.Parameters.Add("@Others", SqlDbType.NVarChar).Value = otherattainment.Text; above, I believe this setting of parameters is only applicable if only the element will be included in the SQLCommand query and or in the database. Do you think that it has been set to parameters by adding this?
what it should output is my enable image with the text on the right saying 'enable' if i only have Text='<img src="app_images/enable.png" />' i have the image, just a question of how to add the resource entry at the end of it...
Im having a go at doing a report for my asp.net application. I have 2 tables, one which lists a loan we have given to a customer, and another table which lists repayments against the loans.
I want to display each loan and how much is outstanding against it. My problem is that each loan is repeated in the table for each repayment against it. So when i try to sum up the loans awarded to a customer, if they only have one loan and have made 3 repayments against it, it sums up the amount awarded against each record.
eg, if i award a customer a loan of £200, and they make 3 repayments of £50, 3 records appear in the table. The amount awarded is repeated against each repayment, and when i total a customers loan amounts, it totals 3 x £200.
I have put the details into the grouping row, where it is grouped by loan_id, displaying the toal of repayments instead of each repayment. This stops it repeating in the table, but when i total the amount awarded field it still calculates 3 x £200.
I tried calculating the total on the report item after grouping but you cannot calculate on a report item.
Is there a way to stop the table from repeating the amount awarded against each repayment? Or even a way in the SQL statement of the dataset to make it display a total of repayments rather than each repayment. Cant see how to calculate a total repaid as i would need to pass a Loan_id in as a parameter.
I've a table where one person teaches multiple classes. It looks like this.
Table1
Id Name Class Subject ---------------------------------------- 2 Sam 12 English 2 Sam 10 Maths 2 Sam 10 English 2 Sam 8 History 2 Sam 12 Economics
Here Sam teaches Class 12 - English & Economics, Class 10 - Maths & English, Class 8 - History
I want to display the result like this -
Id Name Class Subject --------------------------------------------------------------- 2 Sam 12 English, Economics 2 Sam 10 Maths, English 2 Sam 8 History
How do I include the value of a session variable in a header text.
For example. I have "grouping text" property of panel control set to "Number of users:"
I want the heading to be "Number of users : 22" where 22 is the value of the session variable. How to concatenate the caption string for panel with session var value.
I'm using Visual Web Developer Express 2008 and SQL Management Studio Express 2005. I need to link 2 tables to either present in a Master/Detail or GridView/Form view.The goal is to allow the user to view time card information from the AS400 (read only) and then enter any corrections into a SQL table. My problem is in trying to connect the two tables.I've asked the AS400 administrator to add a unique identifier field to the table (HAPOINT), but he says he can't. I've tried to concatenate a field within Visual Web Developer's Query Builder but keep getting errors.Table 1 - exists in AS400, is non-permanent (cleared every 3 months) and can't be modifiedEmpl ID WO Date In Time In Date Out Time Out Table 2 - exists in SQL Server, newly created for this project (no data yet, so can be modified)Rev ID Empl ID Date (of revision entry) X (Revise - y/n) Revised Time In Revised Time Out I tried finding a solution through the SQL Forum, but haven't gotten a solution, so I'm posting here. There are additional details at that post:http://forums.asp.net/p/1516929/3658517.aspx#3658517Can anyone tell me how to concatenate "Empl ID /WO / Date In / Time In" from Table 1 to produce a unique identifier that I can then use in Table 2, thus providing the link between the two tables? Or, is there another way?
