ADDING COMMENTS IN A SINGLE STRING
Apr 23, 2007
Hello,
I would like to know if it is possible to add comments from different columns from table in sql into a single column. Please, see the attached table. I want all comments to be in one column. The outcome should be:
CONFIRMED NEED TO CONFIRM SIGNER AT <CR><LF> received e-mail:<CR><LF> Called Imran again today to see if .
Ideally, I would like to receive all comments in a single string of characters, perhaps separating the comments by <CR><LF>
Here is the table. Sorry my table doesnt look good, but it has the following fields: DATE, LEXNEX_DECISION, COMMENT
DATE LEXNEX_DECISIONCOMMENT
4/3/2007COMPLETECONFIRMED NEED TO CONFIRM SIGNER AT 4/3/2007COMPLETEreceived e-mail:
4/3/2007COMPLETECalled Imran again today to see if .
Help!
View 1 Replies
ADVERTISEMENT
Mar 15, 2008
Did some searching and didn't seem to find what I'm looking for. I'm pretty new to SQL Server (most of my experience is on DB2 for z/OS).I'm building some new tables, and want to find a way to add comments to the metadata for the column. In DB2 the syntax is:COMMENT ON COLUMN TB_CREATOR.TB_NAME.COLUMN_NAME IS 'comments here';ORCOMMENT ON TB_CREATOR.TB_NAME (COLUMN1 IS ' comment here',COLUMN2 IS ' comment here', );Is there anything like this in SQL Server?Thanks!
View 9 Replies
View Related
Jan 7, 2007
I would like to allow users to add a comment to a report. What is the best way to do this? When the report is run I would like users to be able to see all comments and which user reported them and the date and time the comment was entered
View 1 Replies
View Related
Jul 20, 2005
Newbie question:Aside from the single quote (i.e. chr(39)) what other characters can causeMS-SQLserver to cough-up the insertion / update back in your face?TIA
View 1 Replies
View Related
Mar 12, 2008
Hello, I am tring to add a string my database. Info is added, but it is the name of the string, not the data contained within. What am I doing wrong? The text "Company" and "currentUserID" is showing up in my database, but I need the info contained within the string. All help is appreciated!
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClientPartial Class _DefaultInherits System.Web.UI.Page
Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'First Command DataDim Company As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Company"), TextBox)).Text)
Dim insertSQL1 As StringDim currentUserID As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox)).Text)
insertSQL1 = "INSERT INTO Company (CompanyName, UserID) VALUES ('Company', 'currentUserID')"Dim cmd1 As New SqlCommand(insertSQL1, con)
'2nd Command Data
Dim selectSQL As String
selectSQL = "SELECT companyKey FROM Company WHERE UserID = 'currentUserID'"Dim cmd2 As New SqlCommand(selectSQL, con)
Dim reader As SqlDataReader
'3rd Command Data
Dim insertSQL2 As String
insertSQL2 = "INSERT INTO Company_Membership (CompanyKey, UserID) VALUES ('CompanyKey', 'currentUserID')"Dim cmd3 As New SqlCommand(insertSQL2, con)
'First CommandDim added As Integer = 0
Try
con.Open()
added = cmd1.ExecuteNonQuery()
lblResults.Text = added.ToString() & " records inserted."Catch err As Exception
lblResults.Text = "Error inserting record."
lblResults.Text &= err.Message
Finally
con.Close()
End Try
'2nd Command
Try
con.Open()
reader = cmd2.ExecuteReader()Do While reader.Read()
Dim CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
lbl1Results.Text = "Error selecting record."
lbl1Results.Text &= err.Message
Finally
con.Close()
End Try
'3rd Command
Try
con.Open()
added = cmd3.ExecuteNonQuery()
lbl2Results.Text = added.ToString() & " records inserted."Catch err As Exception
lbl2Results.Text = "Error inserting record."
lbl2Results.Text &= err.Message
Finally
con.Close()End Try
End Sub
End Class
View 3 Replies
View Related
May 21, 2008
I have 3x6 textbox lay out, QTY, Descp, Cost, with submit button with followin code...., insert function has too many function, anyone know how to work thiis type of data entery?? Thanks in advanc.Dim Quntityx() As Integer = {txtQ1.Text, txtQ2.Text, txtQ3.Text, txtQ4.Text, txtQ5.Text, txtQ6.Text}
Dim xDex() As String = {txtD1.Text, txtD2.Text, txtD3.Text, txtD4.Text, txtD5.Text, txtD6.Text}Dim xcost() As Decimal = {txtC1.Text, txtC2.Text, txtC3.Text, txtC4.Text, txtC5.Text, txtC6.Text}
Dim i As IntegerFor i = 0 To 5
itemInput.InsertParameters.Add("RequestN", RequestN)itemInput.InsertParameters.Add("Description", xDex(i))
itemInput.InsertParameters.Add("Cost", xcost(i))itemInput.InsertParameters.Add("Quantity", Quntityx(i))
itemInput.Insert(i)
Next i
View 5 Replies
View Related
Jan 16, 2002
We just changed over our phone system and the new system uses all of the old extensions except it adds a 1 to the beginning of them. I know that there is a relatively simple way to update my phone extension column to show this, but I can't for the life of me remember what I need to do. Any help?
View 2 Replies
View Related
Oct 1, 2007
Hi all,
I have state, day_date, error, and text column. If there is data then it is showing all the columns. But if there is no comments I would like to show no comments in the text field. Currently I have this store procedure.
CREATE PROCEDURE dbo.up_daily_quad_text
@DAY_DATE datetime
AS
BEGIN
SELECT
dbo.adins_database.ZONE_NAME + ', ' + dbo.adins_database.ZONE_STATE AS [STATE AND LOCATION],
dbo.COMMENT_CATEGORY.NAME,
dbo.COMMENT.TEXT
FROM
dbo.adins_database,
dbo.COMMENT_CATEGORY,
dbo.COMMENT,
dbo.DIM_DATE
WHERE
( dbo.adins_database.adins_id=dbo.COMMENT.adinsdb_id OR (dbo.COMMENT.adinsdb_id is Null) )
AND ( dbo.COMMENT.comment_dt=dbo.DIM_DATE.DAY_DATE )
AND ( dbo.COMMENT_CATEGORY.category_id=dbo.COMMENT.category_id )
AND
dbo.DIM_DATE.DAY_DATE = @DAY_DATE
END
GO
GRANT EXECUTE ON dbo.up_daily_quad_text TO AdIns_SSRS
GO
How can I modify do that.
Thanks
Rozar2007
View 7 Replies
View Related
Nov 9, 2006
hi,
i am having 2 columns in a table in a dataset.
i want to add those two columns and bind the resultant total as a single column to the datagrid.
is it possible.
if yes, how o acheive this?
please help me.
thanks in advance.
muppidi.
View 1 Replies
View Related
Sep 27, 2006
The goal is to produce a single PDF consisting of a number of subreports. Some are landscape, others are portrait. The subreports may also be run as independent reports. The master report defaults to the width of the widest subreport, which is landscape. This causes all portrait subreports to spill over. Your suggestions / comments are appreciated.
Thanks!
View 1 Replies
View Related
Jun 28, 2007
I have a ssis package created in VS2005 that transforms a SQL query result into an excel spreadsheet. The DataFlow uses a DataReaderSource to query the data and an ExcelDestination to transform the data. The problem is that in the resulting spreadsheet any columns containing string data have a single quote inserted in front of the data. So a string like [My data] becomes ['My data]. When I preview it in the Excel Destination Editor it looks right (without the single quote). What is happening here, is it a bug or does something need to be configured. I checked all the properties that I could find. This is working nice, if I could only overcome this problem.
View 1 Replies
View Related
Nov 6, 2015
I have come up with one scenarios where I have three table like Product, Services and Subscription. I have to create one table say Bundle where I can have some of the product id , service id and Subscription id , i.e. a bundle may contains sum prduct , services and subscription . How I can design these relations ?
View 3 Replies
View Related
Apr 20, 2006
I have two tables. UserIds is a collection of users, with UserId as the primary key. Contacts simply maps pairs of users. Think of it like IM, where one user can have multiple contacts.
UserIds
----------
UserId - int, primary key
Username etc
Contacts
-------------
UserId - int, the UserId of the user who has the contact
ContactUserId - int, the UserId of the contact
I also have a stored procedure named GetConnectedUserIds that returns all the contacts of a given user. It takes a single parameter, @UserId, the UserId of the user whose contacts I want to get. That's pretty simple:
SELECT ContactUserId FROM Contacts WHERE UserId=@UserId.
Now here's where I get over my head. I actually want that stored procedure to return the user's contacts AND the user's own ID. I want the SELECT statement above, but tack on @UserId to the end of it.
How do I do that?
Thanks in advance for any help. Feel free to answer here or to point me to a useful resource.
Nate Hekman
View 5 Replies
View Related
Oct 5, 2015
I would like to add database to single user mode to enable broker. So, i have tried this:
ALTER DATABASE myDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE myDb SET ENABLE_BROKER
ALTER DATABASE myDb SET MULTI_USER
But the query works more than half an hour then i have stopped it. i guess it has infinite loop, I get also many of this messages:
Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
There is no other transaction also there is no other user connections. So, what happens?
View 5 Replies
View Related
May 22, 2008
My task is to add income by taking few variables from webpage. I had take User ID(From database), Field value by selecting it from DropDownBox( Which value is once again taken from database), Income Description, Date, Amount . I had completed this task successfully by binding DropDownBox to database by query string and added income using stored procedure as below.
I need this to be done using only single Stored Procedure for binding Field Value to DropDownBox and for adding income. Plz tell me how to do this?
ASPX.CS file
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = null;
con = DataBaseConnection.GetConnection();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select PA_IFName from PA_IncomeFields where PA_UID=@PA_UID", con);
da.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"];
da.Fill(ds);
IFDdl.DataSource = ds;
IFDdl.DataTextField= ds.Tables[0].Columns[0].ToString();
IFDdl.DataValueField = ds.Tables[0].Columns[0].ToString();
IFDdl.DataBind();
}
protected void IncAddBtn_Click(object sender, EventArgs e)
{
SqlConnection con = null;
try
{
con = DataBaseConnection.GetConnection();
SqlCommand cmd = new SqlCommand("AddIncome", con);
cmd.CommandType = CommandType.StoredProcedure;
//SqlCommand cmd = new SqlCommand("",con);
//cmd.CommandText = "insert into PA_Income values(@PA_UID,@PA_IFName,@PA_IDesc,@PA_IDate,@PA_IAmt)";
cmd.Parameters.Add("PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"];
cmd.Parameters.Add("@PA_IFName", SqlDbType.VarChar, 10).Value = IFDdl.SelectedValue;
cmd.Parameters.Add("@PA_IDesc", SqlDbType.VarChar, 50).Value = IFDescTB.Text;
cmd.Parameters.Add("@PA_IDate", SqlDbType.DateTime).Value = Convert.ToDateTime(IFDateTB.Text);
cmd.Parameters.Add("@PA_IAmt", SqlDbType.Money).Value = Convert.ToDecimal(IFAmtTB.Text);
cmd.ExecuteNonQuery();
IFLabelMsg.Text = "Income Added Successfully!";
} // end of try
catch (Exception ex)
{
IFLabelMsg.Text = "Error : " + ex.Message;
}
finally
{
con.Close();
}
}
Stored Procedure
ALTER PROCEDURE dbo.AddIncome (@PA_UID int,@PA_IFName varchar(10),@PA_IDesc varchar(50),@PA_IDate datetime,@PA_IAmt money)
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
/*SET NOCOUNT ON*/
insert into PA_Income values(@PA_UID,@PA_IFName,@PA_IDesc,@PA_IDate,@PA_IAmt)
ASPX File
<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="AddIncome.aspx.cs" Inherits="AddIncome" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<h2>
Add Income </h2>
<br />
<table>
<tr>
<td>
Select Income Field</td>
<td>
<asp:DropDownList ID="IFDdl" runat="server" Width="247px" >
</asp:DropDownList>
<a href="addincomefield.aspx">Add Income Field</a>
</td>
</tr>
<tr>
<td>
Enter Income Amount
</td>
<td>
<asp:TextBox ID="IFAmtTB" runat="server" Width="96px"></asp:TextBox>
Date <asp:TextBox ID="IFDateTB" runat="server" Width="93px"></asp:TextBox>(MM/DD/YY)</td>
</tr>
<tr>
<td>
Enter Income Description
</td>
<td>
<asp:TextBox ID="IFDescTB" runat="server" Width="239px"></asp:TextBox></td>
</tr>
</table>
<br />
<asp:Button ID="IncAddBtn" runat="server" Text="Add Income" OnClick="IncAddBtn_Click" /><br />
<br />
<asp:Label ID="IFLabelMsg" runat="server"></asp:Label>
</asp:Content>
I need this to be done using only single Stored Procedure for binding Field Value to DropDownBox and for adding income. Plz tell me how to do this?
View 3 Replies
View Related
Mar 8, 2000
I would like to add an apostrophe in a string. eg. The boy's shoes.
Everytime I insert the record, the apostrophe gets drop. eg. The boys shoes.
Any suggestions??
Thanks, Vic
View 2 Replies
View Related
Aug 17, 2012
Basically I need to insert underscore in between a string when there is a space in the content of the string. For Example say we have string 'ABC XYZ', I've to convert it into something like this 'ABC_XYZ'. Some strings do not have space in between and I should not add underscore in such cases. I'm working with MSSQL Server 2008 version.
View 2 Replies
View Related
Sep 13, 2007
Hi,
I'm not a programmer by trade, so please be gentle with me! I am trying to add or insert data to the beginning of an existing string of data. The string is in one field in a table, with various options seperated by [ ].
An example of the current data is:
[Eye Color:TEST][Hair Color:TEST]
and I would like to add the data [# Persons:1] to the beginning of the string so it looks like this:
[# Persons:1][Eye Color:TEST][Hair Color:TEST]
Can anyone help me with the best way to do this?
View 6 Replies
View Related
Mar 9, 2006
Hi,
i am trying to add a single quote to a string. This is a must because i am making a full select statement in which i need the single quote to compare values. Obviously this breaks my string invalidating my query.
ej:
SELECT avg(tabla.ip_trend_value) as valor, FLOOR(Cast(tabla.ip_trend_time AS FLOAT)) as tiempo
FROM TESTLAB5.dbo.CE02_L21_916AI31_43 tabla, TESTLAB5.dbo.CE02_L21_916XI31_4 t2
WHERE t2.ip_trend_value = 'Alimentacion Digestores' and t2.ip_trend_time = tabla.ip_trend_time
group by FLOOR(Cast(tabla.ip_trend_time AS FLOAT))
and this will become something like this.
SELECT @TableName = 'TESTLAB5.dbo.'+@TableName
SELECT @SQL = 'SELECT avg(tabla.ip_trend_value), FLOOR(Cast(tabla.ip_trend_time AS FLOAT)) FROM '
SELECT @SQL = @SQL + @TableName
SELECT @SQL = @SQL + ' tabla, TESTLAB5.dbo.CE02_L21_916XI31_4 t2'
SELECT @SQL = @SQL + ' WHERE t2.ip_trend_value = '@NombreVar'and t2.ip_trend_time = tabla.ip_trend_time'
SELECT @SQL = @SQL + ' group by FLOOR(Cast(ip_trend_time AS FLOAT))'
the @NombreVar is the equivalence of 'Alimentacion Digestores'.
is there something i can add or change to make it work ?
View 2 Replies
View Related
Jan 27, 2006
I'm looking for a string function (or any other quick way) for adding a leading zero to make a string two characters long. For example, if the input is '12' the result will be '12' but if the input is '1', the result will be '01'.
It's actually about making a month number two characters long but I've understood there is no way to make Datediff() to fix it for me.
I thought there might be a string function for this, like there is in many programming languages, but I can't find anything in BOL. I want to keep it simple, because it will be used in a Select and a Group By for aggregating data based on time intervals (year + month).
And, I assume I can't make Datepart() to return year + month directly, only one of the parts at a time.
View 14 Replies
View Related
Dec 28, 2006
My code results in SQL statements like the following one - and it gives an error because of the extra single-quotes in 'it's great':
UPDATE Comments SET Comment='it's great' WHERE UserID='joe' AND GameID='503'
Here's the error I get when I try this code in SQL Server:
Msg 102, Level 15, State 1, Line 1Incorrect syntax near 's'.Msg 105, Level 15, State 1, Line 1Unclosed quotation mark after the character string ''.
I need to know how I can insert a string such as 'it's great' - how do I deal with the extra quotes issue? is there a way to ecape it like this 'it/'s great' ? This doesn't seem to work.
Here's the code that generates the SQL. I'm using a FCKeditor box instead of a TextBox, but I got the same error when I was using the TextBox:
string strUpdate = "UPDATE Comments SET Comment='";strUpdate = strUpdate + FCKeditor1.Value;//strUpdate = strUpdate + ThisUserCommentTextBox.Text;strUpdate = strUpdate + "' WHERE UserID='";strUpdate = strUpdate + (string)Session["UserID"];strUpdate = strUpdate + "'";strUpdate = strUpdate + " AND GameID='";strUpdate = strUpdate + Request.QueryString["GameID"];strUpdate = strUpdate + "'";
SqlConnection myConnection = new SqlConnection(...);SqlCommand myCommand = new SqlCommand(strUpdate, myConnection);
try{myCommand.Connection.Open();myCommand.ExecuteNonQuery();}catch (SqlException ex){ErrorLabel.Text = "Error: " + ex.Message;
}finally{myCommand.Connection.Close();}
I'm using SQL Server 2005 and ASP.NET 2.0
Much thanks
View 5 Replies
View Related
Aug 28, 1998
Hi,
How to insert a string value with quotes in it in SQL Server?
This is not working:
insert table_name values(`abc`d`)
I tried to put escape in front of `, still failed.
Thanks in advance.
-Jenny Wang
View 2 Replies
View Related
Dec 14, 2005
I'm constructing a SQL string that needs single quotes in the WHERE clause. How do I encapsulate them in a string variable. I looked into ESCAPE and SET QUOTED_IDENTIFIER, but i don't really see any examples using string Concatenation. I'm trying to filter out the zls (0 length strings)
This doesn't work (all single quotes):
@sqlString = ' SELECT * FROM myTbl '
@sqlString = @sqlString + 'WHERE fld1 <>'' '
Thanks,
Carl
View 5 Replies
View Related
Apr 8, 2004
I am not close to an sql server today and this question was posed to me. can someone hook me up?
" I want to query a column of values and place them into a single string seperated by commas" (as a function)
Table a
123
456
789
321
654
987
output
123,456,789,321,654,987
thanks in advance
View 14 Replies
View Related
Dec 8, 2007
I wish to return a list of selected integers as a single string. So instead of getting
24
36
48
I get 24/36/48.
My select query:
SELECT CONVERT (varchar(10), quantity) + '/' FROM flexing_stock_transactions WHERE item = 'CH' AND week = 35 GROUP BY quantity ORDER BY quantity
returns
24/
36/
48/
How can I achieve my goal please?
View 11 Replies
View Related
Sep 20, 2005
How do I get a single quote (') in a NVARCHAR string in MS SQL Server?e.g. SELECT @strsql = "SELECT * FROM tblTest WHERE Field1 Like 'blah''Obviously this is invalid as the single quote before "blah" would end thevarchar string.How do I get round this?
View 8 Replies
View Related
Nov 1, 2005
Hi,
I need to return multiple rows into one single string
Declare @String varchar(1000)
Create table Cus (CusId Int,CusName varchar(10))
Insert into Cus Select 1,'John'
Union All
Select 2,'Bob'
Select * from Cus returns
1John
2 Bob
I need to return the all the rows from Cus table into a single string. The return is dynamic.
I do not know the number of rows returned
My result should be
@String = 1,John,2,Bob
How can i do that ?
View 2 Replies
View Related
Sep 20, 2015
How to retrieve all possible sets of values from a table, with each set having a unique identifier.
Here's sample data, where any number of rows can be in the "animal" set:
select animal.name
from (
select 'Cat' as name
union all select 'Dog'
union all select 'Bird'
) animalHere's what I'm trying to get:
[Code] ....
It'd be an easy task if I knew how many rows were in the set, but without knowing how many (and being able to do x number of cross joins and CUBE/ROLLUP to produce the list of permutations) or writing a horrid complex of while loops, I'm at a loss.
A single column. All permutations of the values in a single column. Perhaps I should stay off here nearer the end of the day .
View 2 Replies
View Related
Jan 10, 2014
I have a program which feeds back information to a table within my database. The annoying thing is that only 1 of the table cells has all of the information I need so essentially I have a large string of data I need to retrieve values from.I retrieve my cell with a simple select details from alerts where ID = (alert number).The results I have are as follows:
[INFO] Dashboard Output Information:
[LastUpdated]
Time=09:59
[LastProcessed]
LastProcessed=*2911
Updated=09:59
[LastReceived]
LastReceived=#10
Updated=09:59
As you can see I have a list (its larger but this is a sample), the only thing I need are the values for each section so for last processed i want the 2911 so I can use it on my dashboard and the last received I want the 10 for that section.i have read about using substrings but am really not sure where to start and how I'd go about getting these values to then use elsewhere?
View 3 Replies
View Related
Oct 7, 2007
I have a table having Style Nos (VarChar Col), how I can return values from multiple rows in a single string.
for Example if table is having 3 records :-
1. Style 1
2. Style 2
3. Style 3
It should return single value in this way
Style 1, Style 2, Style 3
View 10 Replies
View Related
Sep 20, 2007
Hi
I need to export data to CSV file from Sql Reporting Services.
I am including single quotations in my view to display in SRS for string fields.
After export if we open in Notepad, for string field it is adding multiple quotations.
How to add single quotations for string fields - CSV files,
Please let me know if anybody knows.
Thx
Vijji
View 4 Replies
View Related
Jun 4, 2015
I have a query that pulls back task and user assigned. Each task can have multiple users assigned. I want to pull back the single task and all the users assigned in one row.
Current Query:
select
t.Name 'Task',
d.FirstName + d.LastName 'User'
from [dbo].[Tasks_TemplateAssignTo] a join
Task_Template t on a.template_id = t.ID join
Doctor d on d.id = a.provider_id
Results from query above:
TaskUser
Call CustomerJohn Smith
Call CustomerBetty White
Call CustomerTammy Johnson
Order suppliesGreg Bullard
Order suppliesJosephine Gonzalez
Expected Results:
TaskUser
Call CustomerJohn Smith, Betty White, Tammy Johnson
Order SuppliesGreg Bullard, Jospehine Gonzalez
View 4 Replies
View Related
