Add Apostrophe To Column In Result Set
I have a brain teaser for you all.
The end result: I need one of the columns in my result set (col2) to have a preceeding apostrophe (i.e., '001234 ).
The problem: I am building my query as a string and then using the EXEC() function to execute it. For example:
SET @strSQL = 'SELECT col1, col2 FROM mytable'
EXEC(@strSQL)
Just to tame any answers that might not be directed to the specific question, the query Must be built as a string and then executed.
I have been unable to obtain the solution and I am wondering if it is even possible?
TIA
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Need Help On Getting Result From Row To Column
Hi I need help to build the below data to my requirement i have table having the following data's NoofLive Action Sold Ratio EffDt 10-49 43 0 0 1/1/2008 50-99 62 0 0 1/1/2008 100-199 73 0 0 1/1/2008 200-299 17 1 0.059 1/1/2008 300-499 25 0 0 1/1/2008 500-999 21 0 0 1/1/2008 1000+ 45 0 0 1/1/2008 100-199 11 1 0.091 2/1/2008 1000+ 1 0 0 2/1/2008 100-199 17 0 0 3/1/2008 500-999 2 0 0 3/1/2008 EffDt = (Jan-08,Feb-08,Mar-08) I want creat result as follows from the above data's Jan-08 Feb-08 Mar-08 Eligible Lives Quoted Sold Close Ratio Quoted Sold Close Ratio Quoted Sold Close Ratio 10 - 49 43 0 0 0 0 0 0 0 0 50 - 99 62 0 0 0 0 0 0 0 0 100 - 199 73 0 0 11 0 0 0 0 0 200 - 299 17 1 0.059 0 0 0 17 0 0 300 - 499 25 0 0 0 0 0 0 0 0 500 - 999 21 0 0 0 0 0 0 0 0 1000+ 45 0 0 1 0 0 2 0 0 Please anybody can help build a query to generate the above result. Thanks in advance sthanika
View Replies !
View Related
Error: Column Name &"X&" Appears More Than Once In The Result Column List.
Hello,I am trying to follow along with the Data Access tutorial under the the "Learn->Videos" section of this website, however I am running into an error when I try to use the "Edit -> Update" function of the Details View form: I'll post the error below. Any clues on how to fix this? Thanks in advance!!! ~DerrickColumn name 'Assigned_To' appears more than once in the result column list. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Column name 'Assigned_To' appears more than once in the result column list.Source Error: Line 1444: } Line 1445: try { Line 1446: int returnValue = this.Adapter.UpdateCommand.ExecuteNonQuery(); Line 1447: return returnValue; Line 1448: }
View Replies !
View Related
Column Name As The Result Of A Query?
Simple example would look like that in MS SQL SELECT 'a' AS (SELECT language_name FROM language WHERE language_id = 1) So that the display is English a as we assume that SELECT language_name FROM language WHERE language_id = 1 returns only English I have tried to that with a variable but it does not work declare @that as varchar(15); set @that = (select language_name_u from language where language_id = 1); select 'a' as @that LOL I just tried another way as i was going to post declare @that as varchar(15); set @that = (select language_name_u from language where language_id = 1); select 'a' as "@that" and it worked! Posting anyway so people might answer with a better solution with no variable Thanks a lot Mordan
View Replies !
View Related
Aggregate (SUM) A Column And Then Use The Result?
Hi, I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data. Thanks
View Replies !
View Related
How To Display Result Of Different Year In Different Column Instead Of Different Row?
I am trying to display component usage in January for the past year, if I want to display the year in different column, what should I do? eg. component_id component_description qty_used_on_2005_Jan qty_used_on_2006_Jan C58B0BDD tape drive 2 3 Currently I am using this sql: select cast(year(date_complete) as varchar(10)) + ' Jan' as Year, component_id, component_description,sum(component_qty) as total_qty_used from view_jobComponent where month(date_complete) = 1group by component_id, component_description, cast(year(date_complete) as varchar(10)) + ' Jan'order by component_id, component_description which I will get something like this: Year component_id component_description total_qty_used 2005 Jan C58B0BDD tape drive 22006 Jan C58B0BDD tape drive 3
View Replies !
View Related
Using IS NOT NULL On Column Result From Subquery
I have a query where one or more of the columns returned is a resultfrom a subquery. These columns get their own alias. I want to filterout the rows containing NULL from the subqueries but it just won'twork. When running columnAlias IS NOT NULL i just get the error"Invalid column name 'columnAlias'.This is the query:SELECT k.UserId, k.Lastname, k.Firstname,(SELECT kscr.answer FROM Results kscr WHERE kscr.UserID =k.UserID) AS myColumnAliasFROM Users kWHERE myColumnAlias IS NOT NULL)When running without the WHERE clause, I get the following results:UserId Lastname Firstname myColumnAlias113 Norman Ola jepps820 Karlsen Kjell830 Pens Jens juubidooWhat I want is to get rid of UserId=820. What am I doing wrong?
View Replies !
View Related
Identity Column In Query Result
I am firing a query like:SELECT TaskName, StartDate FROMTasksWHERE StartDate >= '01-Aug-2003'Now the result comes as:TaskName StartDate--------------------------Task1 01-Aug-2003Task5 10-Oct-2003etc.Now what I want is this:Sl. No. TaskName StartDate----------------------------------1 Task1 01-Aug-20032 Task5 10-Oct-2003How do I get the Sl. No. field (it does not exist in the table).Can I do it?-surajit
View Replies !
View Related
Multiple Column Result From One Table
Hi,I have a table like this :TimeIDApplicationLoginState1App1login101App2login211App3login311App1login401App1login511App4login102App1login102App2login202App3login312App1login402App1login512App4login103App1login103App2login213App3login313App1login403App1login513App4login10And I want a result like thisTimeID (state=0) (state=1)1 3 32 4 23 3 3I use this code :set nocount ondeclare @timeid1 intdeclare @timeid2 intdeclare @timeid intdeclare @sessionstate intdeclare @count1 intdeclare @count2 intdeclare @count intdeclare @aux intdeclare txt_cursor cursor forselect timeid, sessionstate , count(login) from metromasterwhere sessionstate = 0group by timeid, sessionstateUNIONselect timeid, sessionstate , count(login) from metromasterwhere sessionstate = 1group by timeid, sessionstateorder by timeidopen txt_cursorselect @aux = 0fetch next from txt_cursor into @timeid, @sessionstate, @countwhile (@@fetch_status = 0)beginif @aux = 0beginselect @timeid1 = @timeidselect @count1 = @countselect @aux =1endelsebeginselect @timeid2 = @timeidselect @count2 = @countselect @aux = 2endif @aux = 2begin--select @timeid1, @count1, @sessionstate, @count2, @timeid2select @timeid1, @count1, @count2select @aux = 0endfetch next from txt_cursor into@timeid, @sessionstate, @countendclose txt_cursordeallocate txt_cursorset nocount offBut it create a lot of blank row and field header.Does anyone know an other methode ??Thanks.
View Replies !
View Related
SQL 7: One Column's Value Is Repeated Throughout Entire Result Set
I'm using the following query to look in a log file and show somestatistics. It counts the total number of views and the total numberof unique users who have viewed a particular item (the "id" and"title" fields are associated with the item).SELECT title, COUNT(id) AS NumberViews, COUNT(DISTINCT UID) ASNumberUniqueUsers, Type, idFROM ActivityLogWHERE dtTime >= 'Nov 1 2004 12:00AM' AND DtTime <= 'Nov 1 200512:00AM'GROUP BY Title, Type, hdnIdORDER BY TopViewed descThis works fine on SQL Server 2000 (our development machine), but onSQL Server 7 (our production machine), the title column has the samevalue for every row. The other columns show the correct values.If I remove the "ORDER BY" clause, then it works fine. If I removethe "COUNT(DISTINCT UID)" column, it works fine. If I totally removethe WHERE clause, it works fine.Any ideas? It seems like a bug since it works fine on sql2k. I'vetried adding OPTION (MAXDOP 1) to the end of the query, but thatdidn't help.We're using SQL Server 7.0 sp1, and my boss doesn't want to riskupgrading to sp4 because it might screw up all of our otherapplications. I looked through all of the sp2 through sp4 bug fixes,and I didn't see anything specifically mentioning this.Thanks.
View Replies !
View Related
Dynamic Name Of Result Column In S-proc?
How can I make this stored procedure concept work: ----------------------------------------------- Create Procedure MyProc @MyValue varchar(5) As Declare @ColumnName as varchar(11) Set @ColumnName = 'Price_' + @MyValue Select Sum(Price) As @ColumnName --???? From MyTable where Item = @MyValue Return ----------------------------------------------- Using @MyValue = 23 should make the Sum(Price) column name 'Price_23'. It's not working. What am I doing wrong? Bjorn
View Replies !
View Related
Store Query Result To Column
Hi, I'm wondering is it possible to store query result to column directly? for example I have 2 tables Cache ---------- Cache_ID (int) Cache_content (Text) cache_created (Datetime) Orders ------------- Order_ID Order_amount Order_Date Order_Delivered and the query is select * from order where order_date = '2007-11-23' and 'Order_delivered='N' I want the result to be stored in cache table either csv or xml I'm using sql 2000 server. any idea?
View Replies !
View Related
Stuck To Arrange Result In Column
Last query, mr. visakh help to produce a result as follow Date_Taken | Main_ID | Daily_Rainfall ------------------------------------------- 5/1/2005 | 194 | 142 6/1/2005 | 194 | 155 7/1/2005 | 194 | 132 8/1/2005 | 194 | 199 5/1/2005 | 196 | 333 6/1/2005 | 196 | 544 7/1/2005 | 196 | 722 8/1/2005 | 196 | 845 ... ... *Combination of Date_Taken and Main_ID generate unique row *Above data consist of 1 May 2005 to 1 AUG 2005 data (4 month data for each Main_ID), which is Date_Taken start from 1st day every month. *Date_Taken | Main_ID | Daily_Rainfall ------------------------------------------- 5/1/2005 | 194 | 142 ---> Mean, In May 2005 Main_ID=194 having 142 Daily_Rainfall 6/1/2005 | 196 | 544 ---> Mean, In Jun 2005 Main_ID=196 having 544 Daily_Rainfall Let's say, current month is 8 I stuck to do the query to display result like as follow Date_Taken | Main_ID | CurrentMonth_DR | LastMonth_DR | Last2Month_DR| Last3Month_DR -------------------------------------------------------------------------------------- 8/1/2005 | 194 | 199 | 132 | 155 | 142 8/1/2005 | 196 | 845 | 722 | 544 | 333 ... ... *CurrentMonth_DR is 8/1/2005, LastMonth_DR is 7/1/2005, Last2Month_DR is 6/1/2005 and so on ( this can be harcoded in sql) Plz help me
View Replies !
View Related
How Do I Split A Column Result By A Nonalphanumeric Character?
I have a column that returns client numbers. The client numbers are 4-6 characters in length. A period (.) is added to the end of the client number, and then one last digit (1-4) is affixed at the end to denote a categorization. In SQL, I need to figure out how to divide these results into two columns, one for the client number and one for the categorization number. EG: client #4334.1 would become 4334 for client # and 1 for categorization number or Client #424561.3 would become 424561 for Client # and 3 for categorization number. I have to strip out the period in the process and leave myself with just the numeric characters divided into two columns. Ive been researching my brains out on string queries and substring queries and I can't figure out how to parse out the period and/or to have SQL understand that I need everything BEFORE the period for one column and everything AFTER the period for the second. Is it possible to do this? I really need help on this one. Thank you :)
View Replies !
View Related
Inserting Serial No Column In Result Of A Query
Hello Friends My problem is Suppose Query is - Select * from tbl_Employee TBL_EMPLOYEE HAS ONLY TWO COLUMNS NAME,POST I need that an extra column get inserted in result through query showing serial Number with each row.So that query result look like this. Serial Name Post 1 XYZ QER 2 SDF OPO 3 WER IPO If any body knows please post the solution its urgent.
View Replies !
View Related
Cannot Copy Column Names View Result Set
In SQL 2005 - when I display the results of a View and Copy all rows and columns, the resulting Paste in Excel does not include the column names. How can I set SQL 2005 so the names of the columns will come along with the content of the copy function? Background: When I am using a SQL Query (instead of a view) I have the ability to control whether or not I am able to Include the Column Headers when copying or saving results. The control exists in the Options > Query Results > Results to Grid > Include column headings etc. My question is how to get this same ability when attempting to copy the results of a VIEW vs. a Query. Thank you, Poppa Mike
View Replies !
View Related
How Count Column In Pivot Table- And Add Result Row
how count column in pivot table- and add result row i need to calculate each column for example day1 day2 day3 day4 day5 ------------------------------------------------------------------------- 1 2 1 2 3 1 2 3 2 2 2 3 2 1 2 2 3 0 0 0 -----------------------------------------------------------new result row ok ok 1|2|3 1 3 i need to check each column if i have twice each number if not show the missing number TNX Code Block DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT) DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP @BaseDate SMALLDATETIME, @NumDays TINYINT SELECT @WantedDate = '20080301', -- User supplied parameter value @BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'), @NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate)) IF @Numdays > 28 BEGIN SELECT p.ID, p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11], p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21], p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30], p.[31] FROM ( SELECT ID, DATEPART(DAY, Date) AS theDay, ShiftID FROM v_Employee WHERE Date >= @BaseDate AND Date < DATEADD(MONTH, 1, @BaseDate) ) AS y PIVOT ( min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]) ) AS p END
View Replies !
View Related
Looping Through Query Result Column And PRINT To Log File....
i am creating a log file, and at 1 point I record how many rows are deleted after an update process.. I want to also record which productID are actually deleted... how would I write that?! Basically I need know how I would get the list, and I am using PRINT command... I need to somehow write a loop that works through the list and PRINTS to the file.... Thanks in advance
View Replies !
View Related
Please Help Unpivoted Pivot Check Each Column Succession-and Add Result Row At END
nedd help from the from the wonderful people here i am stuck in ! i need to check each column succession the chacking is for 3 columns (day1+day11+day111) (day2+day22+day222) (day3+day33+day333) (day4+day44+day444) (day5+day55+day555) and if i don't have the value than add to a new result row if not show the missing value in the result row at END if i have in the fields columns A B C than ok-A ok-B ok-C if i don't have the value A B C than than show it like this no-A no-B no-Cif i don't have the value c than than show it like this no-C (on the left ) if i don't have the value a than than show it like this no-A (on middle) if i don't have the value b than than show it like this no-B (on right) IF have duplicity a or b or c show in the result row at END 2-C=(thre is tow time the C) ignore the character '*' and '-' and Q and Y id fname val day1 day11 day111 day2 day22 day222 day3 day33 day333 day4 day44 day444 day5 day55 day555 111 aaaa 2 111 aaaa 1 A NULL NULL B C NULL NULL - C - - - C 222 bbbb 2 222 bbbb 1 B NULL NULL C B NULL NULL B C NULL NULL 333 cccc 2 333 cccc 1 C NULL NULL * 444 dddd 2 444 dddd 1 * A B NULL NULL 555 EEE 2 - - - - Q 555 EEE 1 * * * Q 0 ok-A ok-B ok-C ok-A ok-B ok-C ok-A ok-B NO-C NO-A ok-B ok-C NO-A NO-B 2-C i have similar code i send it TNX for the help
View Replies !
View Related
Derived Column Based On Result Of Oracle Query
Hi, I need to create a derived column for each row in a SQL dataset. This derived column needs to be created by passing across two values from the SQL dataset and querying an Oracle table based on those parameters. If the Oracle query returns a record(s) then the derived column should be set to 1 otherwise leave it as default (0). One of these parameters needs to check a date range so I can't use a Lookup Transformation...any ideas how I can accomplish this ? Thanks
View Replies !
View Related
Convert Row Data Into Column [Didnt Get The Exact Result......]
how we get the text() value. your always give half answer, ? ---------------------------------------------------------------------------------------------------- Youe Solution declare @query varchar(8000) select @query = 'select month ' + ( SELECT distinct ',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' + status + ']' AS [text()] FROM sometable s FOR XML PATH('') ) + ' from SomeTable group by month' select @query exec (@query) Mohsin
View Replies !
View Related
Column Order/presentation In Virtual Table (result Set From Viewor UDF)
I was just messing around with some ad hoc views and table returningUDFs today so I could look at and print out data from a small tableand noticed something strange.If I stick my select statement into a View the columns are returned inthe order I specify in the SELECT, but if the same statement is in a UDF(so I can specify a parameter), the columns are not returned in theorder specified in statement.I know that relations don't have a specified column order, but it was myunderstanding that a SELECT statement could be used to define how youwant your data presented. Views seem to respect the order specified inthe SELECT, but functions don't.What am I missing? Is there some way to force the order of the columnsreturned from a SELECT?View:CREATE VIEW dbo.View1ASSELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt,ThenStmt, ElseStmt, NextStmtFROM dbo.tblStmtWHERE (Ident LIKE '4.2.%')Column order from this view:Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt, ThenStmt,ElseStmt, NextStmtFunction:ALTER FUNCTION dbo.Function1(@SearchPrm varchar(255))RETURNS TABLEASRETURN ( SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt,ChildStmt, ThenStmt, ElseStmt, NextStmtFROM dbo.tblStmtWHERE (Ident LIKE @SearchPrm) )Column order from this function:Type, Text, ElseStmt, NextStmt, IfStmt, ChildStmt, ThenStmt, Ident,ParentStmt, ForStmtTable:(I know that this table isn't entirely normalized, but it serves mypurposes to have a matrix instead of a fully normalized relation):CREATE TABLE dbo.tblStmt (StmtID INT IDENTITY(1,1) CONSTRAINT PK_Stmt PRIMARY KEY,Ident VARCHAR(255),Text TEXT,ErrorText TEXT,Type INT,ParentStmt VARCHAR(255),ChildStmt VARCHAR(255),IfStmt VARCHAR(255),ForStmt VARCHAR(255),ThenStmt VARCHAR(255),ElseStmt VARCHAR(255),NextStmt VARCHAR(255),FullName VARCHAR(255),LocalName VARCHAR(255),Method INT)INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmtVALUES('4.2.1', 'LineNumberOfResp := EMPTY' 64, '4.2', '4.2.2')INSERT INTO tblStmt Ident, Text, Type, ParentStmt, ChildStmt, ForStmt,NextStmtVALUES('4.2.2', 'FOR K:= 1 TO 2', 128, '4.2', '4.2.3','4.2.7')INSERT INTO tblStmt Ident, Text, Type ParentStmt, ChildStmt, ForStmt,NextStmtVALUES('4.2.3', 'Person[K].KEEP', 16, '4.2', '4.2.3.1', '4.2.2', '4.2.4')INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmtVALUES('4.2.3.1' 'AuxInterviewerName := DOSENV', 64, '4.2.3', '4.2.3.2')
View Replies !
View Related
Can A Column Be Derived Using Substring But The Parameters Are A Result Of A Select Statement?
I have a table which has a field called Org. This field can be segmented from one to five segments based on a user defined delimiter and user defined segment length. Another table contains one row of data with the user defined delimiter and the start and length of each segment. e.g. Table 1 Org aaa:aaa:aa aaa:aaa:ab aaa:aab:aa Table 2 delim Seg1Start Seg1Len Seg2Start Seg2Len Seg3Start Seg3Len : 1 3 5 3 9 2 My objective is to use SSIS and derive three columns from the one column in Table 1 based on the positions defined in Table 2. Table 2 is a single row table. I thought perhaps I could use the substring function and nest the select statement in place of the parameters in the derived column data flow. I don't seem to be able to get this to work. Any ideas? Can this be done in SSIS? I'd really appreciate any insight that anyone might have. Regards, Bill
View Replies !
View Related
Apostrophe
i'm using delphi 7 and have a query in which i'm trying to find namesthat have an apostrophe in them, i.e. "o'mally". my problem is thatwhen i write my select statement i can't get the quotes right. i getall types of errors no matter what i try. i get "missing right quote","invalid token" etc. i've tried using QuotedStr and nothing works.here is my current attempt in which i get an "invalid use of keyword.token: n'%'"sSQL := 'SELECT statenotified, notary_id, LastName, FirstName,' +' MiddleInitial, Indep, Book_number, Page_number,' +' CloseRec, TermBegins, TermEnds, DatePickedUp,FailedToQualify,' +' Notes, SOSLtrSent FROM notaries WHERE LastName LIKE '''+''+ edtLastName.Text+'+''%'' AND CloseRec = 1 order bylastname';
View Replies !
View Related
Apostrophe In Cell
I need help with a simple query. We have 86 entries with the City of O'Fallon in our db. How do I do this with the apostrophe in O'Fallon? Below is just to give an idea of what I want. Thanks. SELECT * FROM Organization WHERE City=O'Fallon
View Replies !
View Related
SQL Query: That Contain Apostrophe
Hi Friends, I have stored names with ' s (Apostrophes) and without 's in the database(sqlserver).Example:O'Relly I tried to get the values with select statement like Dim str As String = Request.QueryString("Title") Dim query As String = "SELECT * FROM Test where title like '" + str + "'" Problem is getting the all the values from database except O'Relly just getting O other word Relly no..... What should do I and what code do I need ..? I tried with the below code also Dim x As Long For x = 1 To Len(str) If Mid$(str, x, 1) = "'" Then str = str & "''" Else str = str & Mid(title1, x, 1) End If Next Need help , Thanks.
View Replies !
View Related
Will Not Accept Apostrophe
I get this error when the user inputs a word with an apostrophe: Incorrect syntax near 't'. Unclosed quotation mark before the character string ' Using c# this is the input command: oCom=new SqlCommand(string.Format("INSERT INTO [database] ([name],[address], [issue],[comments],[timestamp]) VALUES({0},'{1}','{2}','{3}','{4}",val[0],val[1],val[2],txtComment.Text,tmStamp),oCon); When users enter a comment with an aprostrophe it gives me an error, using a session array and convert it to a string[]. Any ideas? Thanks
View Replies !
View Related
Apostrophe Usage
Ok, I still have some uncertainty as to just exactly how this whole apostrophe thing works with databases. I understand that it is a reserved character and so when a sql query runs into one of these creatures it looks at it as something other than a normal character. I am working primarily in vb/asp/sql server with a little bit of access. I am familiar with the instrinsic 'Replace' function and I use it but I still have occassional problems. I would like any information I can get on just exactly why/how this thing works and how to work-around the apostrophe when writing to, reading from, and validating data from sql server/access/any databases. Thanks!
View Replies !
View Related
SQL Replace Apostrophe
Hello Can anyone tell me how to replace a single apostrophe in a record with a double apostrophe (in Query Analyzer, SQL7)? I've tried "select replace("d'","d'","d''")FROM RESORT" but it doesn't UPDATE the table. Suggestions gratefully received! Mark
View Replies !
View Related
Problem With Apostrophe
I am having weird trouble with READING data from my Access database where the field has an apostrophe - but only when I display the field in a textbox. For example, the field "Don't do this" becomes "Don" when my SQL query outputs the result: campaignID = rscampaign("campaign") response.write "<input type='hidden' name='campaignid1' value='" & campaignID & "'>" If I output to regular HTML (e.g. in a <P> tag), the field displays correcly. Has anyone ever encountered this problem? I can't figure out why the textbox would be creating this problem...
View Replies !
View Related
Problem Inserting Name With Apostrophe
Hi all,I am not sure this question will belong to a sql group or .net.but the problem I have is, my data entry forms crashes when some enter a apostrophe character in one of the field. that field is of type varchar in sql server 2000.for ex, Ryan O'neill will crash the application. I have a drop down list of all my crew names in my form.what should I need to do to allow user to add apostrophe and not to have application crash?Please help it is very urgent.Thank You,
View Replies !
View Related
SqlDataSource : Dynamic SQL : Apostrophe
OK so I need to use a dynamic SQL statement in a SqlDataSource object because I need to pass in the column name. I'm having trouble accounting for the apostophes I have to interpret literals within the statement. This is connecting to an Oracle database. This is what I originally tried... <asp:SqlDataSource ID="SqlDataSourceMine" runat="server" ConnectionString="<%$ ConnectionStrings:My_Connection_String %>" ProviderName="<%$ ConnectionStrings:My_Connection_String.ProviderName %>" SelectCommand="SELECT m.YIE, :selecteditem, m.ENDTIME FROM MyMAP.MAP_M_SUM m WHERE (m.GROUPID LIKE 'YC%' AND m.GROUPID NOT LIKE 'YCX%' AND m.ENDTIME >= SYSDATE-14)"> <SelectParameters> <asp:ControlParameter Name="selecteditem" ControlID="itemlabel" PropertyName="Text" Type="String" /> </SelectParameters></asp:SqlDataSource> And the second column returned as :selecteditem for the column name and the value of itemlabel.text (what I wanted to be the column that was queried) as the value in each of the rows of that column. So I tried dynamic SQL to put the value of itemlabel.txt as the column to be queried, but I'm not sure how to get the query to read the literals. How can I accomplish this? <asp:SqlDataSource ID="SqlDataSourceMine" runat="server" ConnectionString="<%$ ConnectionStrings:My_Connection_String %>" ProviderName="<%$ ConnectionStrings:MY_Connection_String.ProviderName %>" SelectCommand="EXEC('SELECT m.YIE, '+selectedbin+', m.ENDTIME FROM MMAP.MAP_M_SUM m WHERE (m.GROUPID LIKE '+paramlike+' AND m.GROUPID NOT LIKE '+paramnotlike+' AND m.ENDTIME >= SYSDATE-14)')"> <SelectParameters> <asp:ControlParameter Name="selectedbin" ControlID="binlabel" PropertyName="Text" Type="String" /> <asp:ControlParameter Name="paramlike" ControlID="Label1" PropertyName="Text" Type="String" /> <asp:ControlParameter Name="paramnotlike" ControlID="Label2" PropertyName="Text" Type="String" /> </SelectParameters></asp:SqlDataSource> This errors to "illegal variable name" Can someone help me out please? Thanks a lot. -steve
View Replies !
View Related
Escaping An Apostrophe In MSSQL
Hi, i'm having problems executing the followingSET @SQLAH = 'SELECT sub_id WHERE 'SET @SQLAH = @SQLAH + 'VENUE_TYPE = Hotel'EXEC(@SQLAH)Its getting stuck at Hotel. I realise that it should include an apostrophe either side like so:..SET @SQLAH = @SQLAH + 'VENUE_TYPE = 'Hotel' '..But this escapes the string, how would i escape an apostrophe in a string?I thought maybe:SET @SQLAH = @SQLAH + 'VENUE_TYPE = 'Hotel' 'But no joy :confused: Thanks
View Replies !
View Related
How To Insert Text That Contains An Apostrophe??
Hi there, I have an access 2000 db and in one of my tables I need to store some text that sometimes contains an apostrophe. My problem is, everytime my program tries to insert text containing an apostrophe, the program crashes... The insert statments I am using look like this: CurrentDb.Execute "INSERT INTO myTable (Text) VALUES ('" & stringContainingApostrophe & "')" I've tried checking the text for apostrophes and replacing them with an ecape character + apostrophe ("'") but that doesn't work either... It seems like I should be able to store text that contains an apostrophe... Am i missing something here? Does anyone have any ideas?? (Thanks in advance)
View Replies !
View Related
Replace A Caracter Like Apostrophe
Hai, In my datedabase I have some customers with the next name: 't Woud. The problem is the apostrophe in my query for my drill through. This is my query: SELECT DISTINCT c.alias AS Client, p.Alias AS Periode, u.alias as Product, v.alias as Verschil, CAST(s.indicatie AS decimal(6, 2)) AS Indicatie, CAST(s.realisatie AS decimal(6, 2)) AS Realisatie, CAST(s.real_indic AS decimal(6, 2)) AS [Realisatie -/- Indicatie], CAST(s.indicatie_fin AS decimal(6,0)) AS [Indicatie Fin], CAST(s.realisatie_fin AS decimal(6,0)) AS [Realisatie Fin], CAST(s.real_indic_fin AS decimal(6,0)) AS [Realisatie -/- Indicatie Fin] FROM [BI_TZR].dbo.fact_clientsignaal s INNER JOIN [BI_TZR].dbo.dim_verschil v ON s.verschil = v.verschil INNER JOIN [BI_TZR].dbo.Dim_Periodewk p ON s.periode = p.Periode INNER JOIN [BI_TZR].dbo.dim_client_uren c ON s.client_id = c.id INNER JOIN [BI_TZR].dbo.dim_product u ON s.product = u.product WHERE (LEFT(p.Alias, 1) = 'p') AND (c.alias like '%794735%') and s.kplts <> '1583' ORDER BY s.product, s.periode I try this: SELECT DISTINCT replace ( c.alias, 'd', '') AS Client, this works, for the caracter D. But know I want it for the caracter apostrophe. Can someone help me?? TNX
View Replies !
View Related
Find Field That Has Apostrophe
Getting an error when i execute the query. select * from trio where ad_str1 like '%'' Server: Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark before the character string '%' '. Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '%' '.
View Replies !
View Related
Using Single Apostrophe While Inserting Data
Dear fellows, I want to insert the data in a field which contains apostrophe['] in it, but the insert command does not stores the test after apostrophe e.g Insert into test_tbl(id,name) values (32,'This is a test of apostrophe's ...'); the above command does not store after first apostrophe. Please suggest -Faisal
View Replies !
View Related
VB - Stored Procedure - Apostrophe Issue
Hello, I'm using Visual Basic 6 to insert data into SQL Server 2000 via a stored procedure. Apostrophes are giving me problems. Using T-SQL it works fine. Following is the code: ======================= cn.ConnectionString = "Driver=" & cnDriver & ";Server=" & cnServer & ";Database=" & cnDatabase & ";UID=" & cnUID & ";PWD=" & cnPWD cn.Open 'TURNING QUOTES OFF allows a literal string to be delimited by double quotes and to accept an apostrophe 'cn.Execute "SET QUOTED_IDENTIFIER OFF" (This works using T-SQL statements only) 'set the reference to the ADO Command object Set cmd = New ADODB.Command 'Set the command object properties Set cmd.ActiveConnection = cn pDocClass = "Apostrophe's test" 'THIS STORED PROC. INSERTS DATA INTO THE 'INDEX_DTL' AND 'CSC_TRANS' TABLES cmd.CommandText = "Insert_Email_Data ('" & pDocID & "', '" & pLoginID & "', '" & pCustNum & "', '" & pDocClass & _ "', '" & pJCIRepCode & "', '" & pInitiallyRoute & "','" & pDocRecDate & "', '" & _ pCustPONum & "', '" & pJCIOdrNum & "', '" & strDateTime & "', '" & strTime & "', '" & _ strEmailDate & "', '" & strEmailTime & "', '" & strBatchCreationID & "', '" & _ "X" & "', '" & strEventType & "')" MsgBox cmd.CommandText cmd.CommandType = adCmdStoredProc cmd.Execute ======================= The above works great as long as the pDocClass field does not contain an apostrophe. In the stored procedure, pDocClass is defined as @pDocClass char (40). Following is the error I receive: "Line 1: Incorrect syntax near '{' Any help with handling an apostrophe in the CommandText above would be greatly appreciated! Thanks, aml
View Replies !
View Related
Apostrophe In Comment Confuses ADODB
We just encountered an odd failure in ADODB. If it gets an SQL query with a comment embedded in it, and the comment has an apostrophe as one of the characters, ADODB gets confused as it tries to plug in parameters for placeholders later in the query. For example: SELECT id, name FROM doc_type /* this won't work! */ WHERE name LIKE ? If you take out the apostrophe ("this will not work!") or move the comment to follow the placeholder, the query works. Is this a known bug (couldn't find it in the KB)? Thanks, Bob Kline
View Replies !
View Related
Replace A Single Apostrophe With Double - In Sql
I have the following: ----------------- WHILE PATINDEX('%,%',@Columns)<> 0 BEGIN SELECT @Separator_position = PATINDEX('%,%',@Columns) SELECT @array_Value = LEFT(@Columns, @separator_position - 1) SET @FieldTypeID = (SELECT FieldTypeID FROM [Form].[Fields] WHERE FieldID = (CAST(@array_Value AS INT))) SET @FieldName = (SELECT [Name] FROM [Form].[Fields] WHERE FieldID = @array_Value) print 'arry value' + CONVERT(VarChar(500), @array_value) print 'FieldTypeID: ' + CONVERT(VARCHAR(500), @FieldTypeID) PRINT 'FieldName: ' + @FieldName BEGIN IF @FieldTypeID = 1 OR @FieldTypeID = 2 OR @FieldTypeID = 3 OR @FieldTypeID = 9 OR @FieldTypeID = 10 OR @FieldTypeID = 7 BEGIN SET @InnerItemSelect = ' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[ItemDetailFieldRecords] IDFR WHERE IDFR.ItemDetailID = ID.ItemDetailID AND IDFR.FieldID = ' + @array_Value + ') AS ''' + @FieldName + ''' ' SET @InnerTaskSelect = ' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[TaskFieldRecords] TFR WHERE TFR.TaskID = T.TaskID AND TFR.FieldID = ' + @array_Value + ') AS ''' + @FieldName + ''' ' END ELSE IF @FieldTypeID = 4 OR @FieldTypeID = 8 --DropDownList/RadioButtonlist BEGIN SET @InnerItemSelect = ' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[ItemDetailFieldListRecords] IDFLR ON FFLV.FieldListValueID = IDFLR.FieldListValueID WHERE IDFLR.ItemDetailID = ID.ItemDetailID AND FFLV.FIeldID = ' + @array_value + ') AS ''' + @FieldName + ''' ' SET @InnerTaskSelect = ' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[TaskFieldListRecords] TFLR ON FFLV.FieldListValueID = TFLR.FieldListValueID WHERE TFLR.TaskID = T.TaskID AND FFLV.FIeldID = ' + @array_value + ') AS ''' + @FieldName + ''' ' END ELSE IF @FieldTypeiD = 5 --Cascading BEGIN SET @InnerItemSelect = ' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[ItemDetailFieldCascadingRecords] IDFCR ON IDFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE IDFCR.ItemDetailID = ID.ItemDetailID AND LT.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' ' SET @InnerTaskSelect = ' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[TaskFieldCascadingRecords] TFCR ON TFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE TFCR.TaskID = T.TaskID AND LT.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' ' END ELSE IF @FieldTypeiD = 6 --ListBox BEGIN SET @InnerItemSelect = ' (SELECT i.[CSV] FROM @ItemDetailLV i WHERE i.ID = ID.ItemDetailID AND i.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' ' SET @InnerTaskSelect = ' (SELECT it.[CSV] FROM @TaskLV it WHERE it.ID = T.TaskID AND it.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' ' END ELSE IF @FieldTypeID = 11 --Users BEGIN SET @InnerItemSelect = ' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[ItemDetailUserRecords] IDUR ON SU.UserID = IDUR.UserID WHERE IDUR.ItemDetailID = ID.ItemDetailID AND IDUR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' ' SET @InnerTaskSelect = ' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[TaskUserRecords] TUR ON SU.UserID = TUR.UserID WHERE TUR.TaskID = T.TaskID AND TUR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' ' END ELSE IF @FIelDTypeID = 12 --Group BEGIN SET @InnerItemSelect = ' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[ItemDetailGroupRecords] IDGR ON SG.GroupID = IDGR.GroupID WHERE IDGR.ItemDetailID = ID.ItemDetailID AND IDGR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' ' SET @InnerTaskSelect = ' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[TaskGroupRecords] TGR ON SG.GroupID = TGR.GroupID WHERE TGR.TaskID = T.TaskID AND TGR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' ' END END PRINT 'Inner Item Select:' + @InnerItemSelect PRINT 'Inner Task Select:' + @InnerTaskSelect SET @IDSelect = @IDSelect + @InnerItemSelect + ', ' SET @TSelect = @TSelect + @InnerTaskSelect + ', ' SELECT @Columns = STUFF(@Columns, 1, @separator_position, '') END --------------- That is only part of a large query that writs a SQL Query to a column in a Database. That Query (in the column) is just ran normally so I don't need to compile it each time I want to run it. THe problem I have is @FieldName might be: ryan's field. That apostrophe is killing me because the SQL keeps it as ryan's field, not ryan''s field(note the 2 apostrophes). I cannot do: REPLACE(@FieldName, ''', '''') because it's not closing the apostrophes. Is there an escape character that I can use to say only one: ' ? I tried: DECLARE @t VARCHAR(500) SET @t = (SELECT [Name] FROM [Form].[Fields] WHERE FieldID = 1) print @t -- @t will print: Ryan's Field PRINT QUOTENAME('' + @t + '', '''') but that gives me: 'Ryan''s Field' Any help would rock. If I make any changes to the way this field is input into the DataBase, I know I will need to do a lot of re-work in many spots. WHich is why i'm trying to solve this on the SQL side.
View Replies !
View Related
|
TRACKER
