The following sql statement fails:
SELECT IdFlight, OrganizerLastName + ", " + OrganizerFirstName + "
Flt:" + FlightNumber As Concat FROM TableFlights Order By
OrganizerLastName, OrganizerFirstName
I'm trying to concatenate fields and words, but my asp.net page gives
me an error saying " Flt:" is not a field.
Thanks,
Marvin
I would like to concatenate text datatypes. Is this possible?? I'm using field1 + " " + field2 I cannot convert to varchar since the size is larger than 8000.
New to using MS SQL server! I have a requirements DB. Before I came on board, the DB was seeded with requirements text and a requirements ID in separate columns. I've been asked to append the ID in front of the text:
Text - The user shall be able.... ID - 1.01.01. Combined - 1.01.01.The user shall be able....
I've created the following query that displays the columns:
CREATE VIEW dbo.ReqID AS SELECT RQREQUIREMENTS.ID, RQREQUIREMENTS.REQUIREMENTNAME, RQUSERDEFINEDFIELDVALUES.FIELDVALUE
FROM RQREQUIREMENTS LEFT OUTER JOIN RQUSERDEFINEDFIELDVALUES ON RQREQUIREMENTS.ID = RQUSERDEFINEDFIELDVALUES.REQUIREMENTID
WHERE (RQUSERDEFINEDFIELDVALUES.FIELDID = 171)
The Text field is REQUIREMENTNAME The ReqId is FIELDVALUE
Should I create an UPDATE query based on this SELECT, updating the REQUIREMENTNAME column with FIELDVALUE + REQUIREMENTNAME?
Hi all, I am creating a search table where the keywords field is madeup of several text fields and this is causing me some problems. I canconcatentate the text ok but i can't seem to concatenate matchingrecords here is the cursor loop. I'm not a fan of cursors but alsodidn't see another way of achieving this.declare @ptr1 varbinary(16)declare @Ptr2 varbinary(16)declare @profileid intdeclare @x intset @profileid = 0while @profileid is not nullbeginselect@profileid = min([id]),@ptr1 = MIN(textptr(text1))from #holdingwhere [id] @profileiddeclare c2 cursor fast_forward forselect textptr(searchterms), datalength(searchterms)from searchwhere search.[id] = @profileidopen c2fetch c2 into @ptr2, @xwhile @@fetch_status = 0beginupdatetext search.searchterms @ptr2 null 0 #holding.text1 @ptr1fetch c2 into @ptr2, @xendclose c2deallocate c2endThe #holding table contains the fields that i want to concatenate andthe search table is the resulting table. This example would loopthrough search and find id 1 in search and then append another fieldmatching id 1 in holding then move onto the next field in turn goingthrough the whole table.i.e.search holding result after each loopid text id text1 abc 1 def abcdef2 ghi 2 jkl ghijklWhen I run this, some of the records concatenate properly but most dontwith the same text being appended to the end of searchterms. i.e loadsof results will end up with jkl tagged onto the end. I can't figure outwhen my loop is falliing over!!! Can anyone help?Dan
I am trying to take a value from at textbox and insert into a database. I think I need to convert the text to an integer - but I keep getting an error message saying I'm not allowed to use column names. Basically, I need to figure out how to take user input from these tet boxes and insert them into a database. Any ideas would be welcomed. the exact error is at the bottom the code.Here is the code:Imports System.DataImports System.Data.SqlClientPartial Class County_ConversionTest Inherits System.Web.UI.Page Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim v1 As Integer Dim v2 As Integer 'Dim v3 As Integer v1 = Integer.Parse(TextBox1.Text) v2 = Integer.Parse(TextBox2.Text) TextBox3.Text = (v1 + v2) End Sub Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click Dim strconn As String = "Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrootHCBSApp_DataDivAging_Data.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True" Dim cnnDivAging As SqlConnection = New SqlConnection(strconn) Dim v1 As Integer Dim v2 As Integer Dim v3 As Integer v1 = CInt(TextBox1.Text) v2 = CInt(TextBox2.Text) v3 = CInt(TextBox3.Text) Dim strsql As String = "INSERT INTO tblTest (Name, fv1,fv2,fv3)" + _ "Values ('George',v1,v2,v3)" Dim cmdUpdates As SqlCommand = New SqlCommand(strSQL, cnnDivAging) cmdUpdates.Connection = cnnDivAging cnnDivAging.Open() cmdUpdates.CommandType = CommandType.Text cmdUpdates.ExecuteNonQuery() cnnDivAging.Close() End SubEnd ClassHere is the error message -
The name "v1" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Can you guys help me out? I m trying to sum up some varchar-typed field. I need to convert it to float before doing the summing up so I m using "Cast".
I do get the answer but its not the correct figure. My SQL statement is as follow:
SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM TableName
Is it possible to combine fields and text in a select statement? In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this: SELECT DISTINCT GRP AS GroupName, "Year: " + YEAR + "Grade: " + GRD AS ShowMe FROM GE_Data WHERE (DIST = @DIST)
I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets. If this is possible, or there's a better way to do it, I'd love some more info. Thanks! -Mathminded
Hi everyone, So what I am trying to do is to have a simple textbox where the person enters a code. Then I need to have that code in an SQL statement like: SELECT Participant_Code FROM Contacts WHERE (this is where im stuck, I need to have something like WHERE Participant_code = code (code is the textbox ID)) After this is done I need to keep this code from page to page, because I will need to add data from other textboxes to the database. Thanks
I am trying to add a case/select/if statement in the text color property of reporting services. I want it so if the value of a variable is 1 then "GREEN" else "RED". Any ideas?
I received this error message in the application event logs. Can anyone tell me what this is pertaining to?Error: 7102, Severity: 20, State: 99SQL Server Internal Error. Text manager cannot continue with currentstatement.Thanks Barb
I wish I had a better idea about how to describe this but here goes. I'm trying to display a list of majors/minors/graduate programs for a particular user. Now, I've gotten it display perfectly and was able to append (Graduate), (Major), (Minor) to each of the 3 different sections. My question is is it possible to add like rows that don't actually exist in table to the beginning of the select? For instance I want to add All and General before the rest of the select. I was also wondering where I should do a group by cuz I'd like to keep it in blocks of like graduates then majors and finally minors but currently it is just doing it alphabetically...Anyways below are the tables and at the end the current stored procedure i'm using, which works great...just not to the degree I'm aiming for.
Code Snippet
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[GraduateDiscipline] Script Date: 03/30/2008 14:45:55 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[GraduateDiscipline]( [GraduateDisciplineID] [int] IDENTITY(0,1) NOT NULL, [DegreeID] [nchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [GraduateID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DisciplineName] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_GraduateDiscipline] PRIMARY KEY CLUSTERED ( [GraduateDisciplineID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO ALTER TABLE [dbo].[GraduateDiscipline] WITH CHECK ADD CONSTRAINT [FK_GraduateDiscipline_DegreeShortName] FOREIGN KEY([DegreeID]) REFERENCES [dbo].[Degree] ([DegreeID]) GO ALTER TABLE [dbo].[GraduateDiscipline] CHECK CONSTRAINT [FK_GraduateDiscipline_DegreeShortName] GO ALTER TABLE [dbo].[GraduateDiscipline] WITH CHECK ADD CONSTRAINT [FK_GraduateDiscipline_GraduateName] FOREIGN KEY([GraduateID]) REFERENCES [dbo].[Graduate] ([GraduateID]) GO ALTER TABLE [dbo].[GraduateDiscipline] CHECK CONSTRAINT [FK_GraduateDiscipline_GraduateName]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Student_Graduates] Script Date: 03/30/2008 14:46:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Student_Graduates]( [GraduateDisciplineID] [int] NOT NULL, [StudentID] [int] NOT NULL, CONSTRAINT [PK_Student_Graduates] PRIMARY KEY CLUSTERED ( [GraduateDisciplineID] ASC, [StudentID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO ALTER TABLE [dbo].[Student_Graduates] WITH CHECK ADD CONSTRAINT [FK_Student_Graduates_GraduateDisciplineID] FOREIGN KEY([GraduateDisciplineID]) REFERENCES [dbo].[GraduateDiscipline] ([GraduateDisciplineID]) GO ALTER TABLE [dbo].[Student_Graduates] CHECK CONSTRAINT [FK_Student_Graduates_GraduateDisciplineID] GO ALTER TABLE [dbo].[Student_Graduates] WITH CHECK ADD CONSTRAINT [FK_Student_Graduates_StudentID] FOREIGN KEY([StudentID]) REFERENCES [dbo].[Students] ([StudentID]) GO ALTER TABLE [dbo].[Student_Graduates] CHECK CONSTRAINT [FK_Student_Graduates_StudentID]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[MajorDiscipline] Script Date: 03/30/2008 14:46:13 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[MajorDiscipline]( [MajorDisciplineID] [int] IDENTITY(0,1) NOT NULL, [DegreeID] [nchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MajorID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DisciplineName] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_MajorDiscipline] PRIMARY KEY CLUSTERED ( [MajorDisciplineID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO ALTER TABLE [dbo].[MajorDiscipline] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_DegreeID] FOREIGN KEY([DegreeID]) REFERENCES [dbo].[Degree] ([DegreeID]) GO ALTER TABLE [dbo].[MajorDiscipline] CHECK CONSTRAINT [FK_MajorDiscipline_DegreeID] GO ALTER TABLE [dbo].[MajorDiscipline] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_MajorID] FOREIGN KEY([MajorID]) REFERENCES [dbo].[Majors] ([MajorID]) GO ALTER TABLE [dbo].[MajorDiscipline] CHECK CONSTRAINT [FK_MajorDiscipline_MajorID]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Student_Majors] Script Date: 03/30/2008 14:46:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Student_Majors]( [MajorDisciplineID] [int] NOT NULL, [StudentID] [int] NOT NULL, CONSTRAINT [PK_Student_Majors] PRIMARY KEY CLUSTERED ( [MajorDisciplineID] ASC, [StudentID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO ALTER TABLE [dbo].[Student_Majors] WITH CHECK ADD CONSTRAINT [FK_Student_Majors_MajorDisciplineID] FOREIGN KEY([MajorDisciplineID]) REFERENCES [dbo].[MajorDiscipline] ([MajorDisciplineID]) GO ALTER TABLE [dbo].[Student_Majors] CHECK CONSTRAINT [FK_Student_Majors_MajorDisciplineID] GO ALTER TABLE [dbo].[Student_Majors] WITH CHECK ADD CONSTRAINT [FK_Student_Majors_StudentID] FOREIGN KEY([StudentID]) REFERENCES [dbo].[Students] ([StudentID]) GO ALTER TABLE [dbo].[Student_Majors] CHECK CONSTRAINT [FK_Student_Majors_StudentID]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Minors] Script Date: 03/30/2008 14:46:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Minors]( [MinorID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_Minors] PRIMARY KEY CLUSTERED ( [MinorID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Student_Minors] Script Date: 03/30/2008 14:46:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Student_Minors]( [MinorID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [StudentID] [int] NOT NULL, CONSTRAINT [PK_Student_Minors] PRIMARY KEY CLUSTERED ( [MinorID] ASC, [StudentID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO ALTER TABLE [dbo].[Student_Minors] WITH CHECK ADD CONSTRAINT [FK_Student_Minors_Minors] FOREIGN KEY([MinorID]) REFERENCES [dbo].[Minors] ([MinorID]) GO ALTER TABLE [dbo].[Student_Minors] CHECK CONSTRAINT [FK_Student_Minors_Minors] GO ALTER TABLE [dbo].[Student_Minors] WITH CHECK ADD CONSTRAINT [FK_Student_Minors_StudentID] FOREIGN KEY([StudentID]) REFERENCES [dbo].[Students] ([StudentID]) GO ALTER TABLE [dbo].[Student_Minors] CHECK CONSTRAINT [FK_Student_Minors_StudentID]
ALTER PROCEDURE [dbo].[Student_Deciplines] @studid int AS BEGIN SELECT RTRIM(gradDis.DisciplineName) + ' (Graduate)' FROM GraduateDiscipline gradDis, Student_Graduates sGrads WHERE gradDis.GraduateDisciplineID = sGrads.GraduateDisciplineID AND sGrads.StudentID = @studid UNION SELECT RTRIM(majDis.DisciplineName) + ' (Major)' FROM MajorDiscipline majDis, Student_Majors sMajors WHERE majDis.MajorDisciplineID = sMajors.MajorDisciplineID AND sMajors.StudentID = @studid UNION SELECT RTRIM(mins.MinorID) + ' (Minor)' FROM Minors mins, Student_Minors sMinors WHERE mins.MinorID = sMinors.MinorID AND sMinors.StudentID = @studid
END Some sample data: GraduateDiscipline GraduateDisciplineID DegreeID GraduateID DisciplineName .... 0 M.B.A. 1 Master of Business Administration
I am doing a web app on the full text search.My select statement using CONTAINS but it doesn't seem to return all the string that contains the search word I included in the query.For example:
Select CustomerID, customername, from CustomerTable where CONTAINS ((CustomerID), '"430*")
the query result only return all customer id which has space in front 430 or start with 430 ex: "xxx 430xxx" or "430 xxx" or "xxx 430 xxx"
but did not return customerID which doesn't has space in front of it Ex: x430xxx
If I use LIKE '%430%', the result return are both have space in front and don't have space in front.
Hi,I have boolean values in a table for ex. Federation. And I want toselect followingSelect 'Insert into' + member + 'test' as test1from federationThen I get error messageServer: Msg 403, Level 16, State 1, Line 1Invalid operator for data type. Operator equals add, type equals bit.Someone can help me out of it.Thanks an advance- Loi -
I have such a problem. Need to add additional column to my query. The column should consist of set of fixed number (same as number of query rows) values (text). At start thought it's simple but now Im lost. Is there any chance to do it. Apreciate any help. I need to tell that I have only access to select on this database so no use of operation on tables.
I need to set a field value based on two date time parameter's.What is the correct syntax to allow me to pass the value into the field in my SSRS expression ?
Slow loading issue with an if statement. In the raw data the field [Location] is a text field e.g. 0010. I have a parameterised query that get a Location_Value from Excel and passes it to the PQ query using:
#"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns", each ([SalesMode] = 0) and ([SalesType] = 0) and ([Location] = Location_Value))
This works fine if you chose a single location. However I wanted to be able to select all locations and text is horrible to work with so in PQ I used the change type function to change the location column into whole numbers. I changed excel to also pass a number as Location_Value. I was therefore surprised when the same query took 2.5 times longer to refresh????
My PQ now looks like this
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Location", Int64.Type}}), #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([SalesMode] = 0) and ([SalesType] = 0) and ([Location] = Location_Value))
I'm wondering if I need to do something to the ([Location] = Location_Value) bit as maybe it still thinks [Loation] is text and it is trying to compare it to a number. I st assumed the step above meant that [Location] was now a number, but maybe you still have wrap it with some kind of VALUES or TEXT function?
is it possible to concatenate 2 recordsets ? i have 2 select, and i need then to be displayed one after other.using union all the results are gathered, ordered like only one select.thanks for all
Hi, I am trying to write some simple SQL to join two fields within a table, the primary key is an int and the other field is a varchar. But i am receiving the error:'Conversion failed when converting the varchar value ',' to data type int.The SQL I am trying to use is:select game_no + ',' + team_name as matchfrom result Thanks
I'm trying to use a variable (@sqlfields1) for building a select statement. Can someone please help me because the variable isn't set correctly when I'm trying to concatenate it with itself (the "@sqlfields1"-variable is declared as "char(50)":
If @showo1 = 1 select @sqlfields1 = 'o1,'
If @showo5 = 1 select @sqlfields1 = @sqlfields1 + 'o5'
Please help me with this if you can. I have one table with CustomerID and some other data. In other table i have CustomerID(the link with the first table) and Agent The relation of the first with the second one is ONE TO MANY. I want something like this: Customer,'Agent1,Agent2,Agent3'
Just like the title asks...Is it possible...I have a table that stores memos but breaks the memo text field up and assigns a key to associate it all as one big text field in the application. What I am needing to do is to create a custom table for a customization to have the memo txt in one row per customer, per memo number. So, is there any way to concatenate memotext into one row per memo?
Is there a way to concatenate the values of a particular field together into one field for all the records in each group of a GROUP BY without using XML?
Ideally, I want to accomplish this in a stored procedure. Normally, I would handle this in the front end, but my front end is Crystal ...
For example, if my data was: 1 "A" 1 "B" 1 "C" 2 "D" 2 "E"
I want to group by the first value and concatenate the second value: 1 "A,B,C" 2 "D,E"
Very easy using code - I simply add a function to the query in Access (not very efficient, but effective), or using recordsets in the app (which is actually very efficient) except my front end is Crystal in this case .