How do I handle and apostrophes and other punctuation in stored procedure Sql statements:
SELECT L_ID, L_Name
FROM tblHVACContractors
WHERE (L_Name = 'Mare's Heating & Cooling Services')
Hello,
could someone help with this query in a stored proc.?
SET @SQL = 'SET ''' + @avgwgt + ''' = '
'(SELECT AVG(AverageWeight)
FROM CageFishHistory where CageID IN (' + @cagearray + ')
and ItemDate =''' + CONVERT(varchar(23),@startdate) + ''')'
EXEC @SQL
I'm trying to get an average value across dynamically selected rows. (I'm using a list array to deliver the selection to the stored proc). I need to re-use the average value within the procedure,so it's not enough to output it as a column of the resultset - EG. 'Select AVG(AverageWeight) as AvgWgt' . If I take out the @avgwgt line it works fine, but otherwise I'm getting this error:
"Incorrect syntax near '(SELECT AVG(AverageWeight)
FROM CageFishHistory where CageID IN ('."
It may be that I can access a column of the resultset in the rest of the procedure, and that would help avoid the use of pesky apostrophes, but I don't know how to do it.
I am using a SQLDatasource to populate a dataview as illustrated below. I run into a problem when I search for a "LastName" that includes an (') Apostrophe ( e.g. O'Reilly) . Searchin for the whole name there is no problem, but when I search for simply O, or O' I get errors.
I am not sure ...when to address names with an apostrophe...
1) On insertion to the database (using a "Replace(LastName, "'", "''") 2) or after they have been entered and they are to be searched for.
If scenario 1, can anyone provide the best way to do this...If scenario 2, how would that be worked into the SQL Datasource code below....
I have tried several variations with the times SQL Datasource to no avail....
I would appreciate any help !
Thanks !
<asp:SqlDataSource ID="SqlDataSource1" runat="server"ConnectionString="<%$ ConnectionStrings:ClinicTest2ConnectionString1 %>"
SelectCommand="SELECT [PatientID], [Accession], [FirstName], [LastName], [Address1], [City], [strddlPatientState], [ZIP], [DOB] FROM [ClinicalPatient] WHERE [LastName] LIKE @LastName ORDER BY [LastName],[FirstName]ASC">
<SelectParameters>
<asp:QueryStringParameter Name="LastName" QueryStringField="NameSearch_Result" />
</SelectParameters>
</asp:SqlDataSource>
I've tried everything I can think of to find all the records in a table column (lastname) that contain an apostrophe. I know they are there (O'Brian, D'Marcus, etc.) However, I keep getting syntax errors.
Could someone PLEASE help?!!
Thanks,
Karen
I am currently migrating our Intranet from SQL Server 7 to SQL Server2000 and have hit a problem with one of the applications. Theapplication in question executes the folloiwing query against thedatabase:select * from employee where emp_id = 760In SQL Server 7 this works without a problem, however, in SQL server2k the following error is returned:[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error convertingthe varchar value 'Con25' to a column of data type int.This is clearly because the column emp_id is a varchar, I understandthat this code has not exactly followed best practices but do not wantto have to trawl through the application and surround all instanceswith apostrophes.Is it possible to make SQL 2k act in the same way as SQL 7 or will Ihave to trawl through the code and correct the SQL statements.Thanks for any help.John
View 2 Replies View RelatedAfter creating an IN clause from a bunch of character strings created by aWord macro, Query Analyzer complains about a syntax error. The macro takes acolumn full of character strings and wraps apostrophes (single-quotes)around each string and adds a comma to the end of each line, ready to pasteinto a Query Analyzer session. The problem is that Query Analyzer doesn'trecognize the MS-Word apostrophes. It has nothing to do with the standardapostrophe/quote problems that some people face when dealing with embeddedquotes.Can anyone suggest how to produce single-quote characters in MS-Word thatQuery Analyzer understands?Thank you.
View 2 Replies View RelatedHi I have an asp.net 2.0, web app with a sql 2000 db. I am having a problem when users cut and past content from Word and Excel and past it into a textbox apostrophes are replaced as question marks when saved to db. I have set the following line in my web.config which did not resolve the problem<globalization requestEncoding="iso-8859-1" responseEncoding="iso-8859-1" fileEncoding="iso-8859-1" culture="en-GB" uiCulture="en-GB"/> I have also read many posts, but have not found a resolutionhttp://www.codingforums.com/showthread.php?t=124609I cant do a find and replace of the character before insertion into DB, as this would involve visiting over 90 pages and changing every insert statement. Is there a way I a can force my sql server to only allow characters in a certain format, eg iso-8859-1 to resolve this problem Please help somebody must have a resolutionMany thanks in advance
When we insert text into field in a table, SQL SERVER apparentlyreplaces apostrophes with question marks -- is there a way to not havethis occur? We don't have this happen with the mySQL databases thatwe also support.Much help appreciated.
View 1 Replies View RelatedI work with both Oracle and SQL Server databases (most of the time Oracle) and I was wondering if there is a function that allows ampersands and apostrophes to be inserted into the database. Oracle has a function called SET DEFINE OFF and that allows for ampersands and apostrophes to be inserted without giving me an error. Is there something similar in SQL Server? I'm migrating some data and quite few rows have ampersands and apostrophes.
View 4 Replies View RelatedHi.
I have an update statement that accepts
a user's text input. problem is that the string
terminates the sql update statement if there is
an apostrophe in it. so, headline='i like my brother's car"
willl terminate after "brother". "s car" is read as sql.
how is this resolved?
Code:
Dim MyCmd2 As New Data.SqlClient.SqlCommand("SET ANSI_WARNINGS OFF " & _
"UPDATE EditProfile " & _
"SET headline='"+ Me.tb_headline.Value+"', about_me='"+ Me.ta_aboutme.Value+"', edit_date='"+System.DateTime.Now.ToString+"' WHERE email_address='"+Context.User.Identity.Name.ToString+"' " & _
"SET ANSI_WARNINGS ON ", MyConn)
Here is my problem:-
declare @test as varchar(32)
declare @test2 as varchar(32)
set @test='today''s problem'
set @test2='my <string> '
select @test as '@attribute' for xml path ('myrow')
select @test2 as '@attribute' for xml path ('myrow')
I want for xml path to correctly encode the single apostrophe as &apos but the single apostrophe doesn't get encoded. In the second example the greater and less than does get encoded.
Hi guys,
Need some help in some query processing...
Here goes,this is the results i have obtained so far
|id |cat | name |
1 .....a... apple
1 .....b .. banana
2 .....a ...austria
2 .....b ...brazil
2 .....c ...china
3 .....a ...abraham
3 .....c ...clinton
Column cat can have at most 3 different types of values..{a,b,c}
I wanna write a query such that the result comes out as such
<(....id ......a .......b......... c..) corresponding to these fields.>
|Col 1| Col 2 | Col 3 | Col 4|
....1 ...apple ...banana
....2 ...austria .brazil... china
....3 ...abraham ..........clinton
Anyone can help...thanks guys
I use SQL Server 2005.I want to create a SP "Search". With this function a user can serach the member database on several criteria: age minimal, age maximal, name, city..BUT these criteria do not necessarily have to be defined by the user, so it might be that a user searches for all members whose age is between ageMin and ageMax leaving the name and city criteria empty.So in my SP I have to check whether these values are empty or not. If a parameter is not empty I have to add it to my selection query.. BUT, I know that SQL Server makes an execution plan, and understood that when you use if-statements the procedure needs to be recompiled every time?Is this true? If so:how does this work then?Is there another way for me to still be able to create this SP?
View 2 Replies View RelatedHello guys. How is this declared in SQL?
Select * FROM my_table
if my_column = "1" UPDATE my_table SET my_other_column= "a" ////my_column and my_other_column belong to my_table
else if my_column="2" UPDATE my_table SET my_other_column= "b" ////my_column and my_other_column belong to my_table
Hi, I would like to know the number of SQL statements per second being generated by my web site. How can I know this? Best Regards,MeeNge
View 6 Replies View RelatedHello I have an SQL Data Source i want to compare 2 dates if one is equal to or less than the other i want to return either a string or a true i have been trying combinations for about an hour and it's getting a little frustrating i;ve tried searching for an example but cant find one so somesthing like Select ValuesWhere Date1 <= Date2 Return "String"(orTrue?) also IF date1 <= Today() Return "String OR True" thanks Chris
View 3 Replies View RelatedI have some trade data. One colum is tran_status_mtf. within that column is "settled/traded", "cancelled", and "revised". I want to write and SQL statement that says if the trade is "settled" or "traded" display "A" in my output. How do I do this? I am new to SQL. Thanks in advance guys!!!
View 14 Replies View RelatedI need to create a brief statement in Design View for SQL 2000.
I successfully wrote "isnull(dbo.Data.Parcel,'')in the Column in Design View, which would ensure that a null value would be listed as a blank. Now I need to have dbo.data.status show as 'C&G' in any instance of data, otherwise it will be listed as blank. I tried writing "isnotnull(db0.data.status,'Homestead', '')", but it does not work.
Is there a simple statement I can write in "column" of design view that can get the results that I need?
-Steve
I have written an sql statement thats using sum. The problem that I have is that when the value is zero it returns Null. Is there a way that I could return zero instead of null.
View 5 Replies View Related
I am totally new to creating IF statements inside stored procedures. I am passing some parameters from a ColdFusion form to a SP and if the form is empty for the small date I get an error. I would like to be able to check in the procedure to see if the parameter is empty and if it is set a default value for it. Here is my code.
@JOB1,
@COLLEGEDATE smalldatetime,
If(@COLLEGEDATE <> ''")
@COLLEGEDATE smalldatetime,
ELSE
@COLLEGEDATE = getDate(),
RETURN
@JOB2
Hi, i just want to know if the T-SQL statements in SQL server 2005 are different from SQL Server 2000???
If they are different, where can i find the Server 2005 T-SQL Statements?
Hello,
I have a report with 1 field that I need to sum into 2 different textboxes based on another field's value.
Specifically, Is it simply doing something like this below?
=Iif(Fields!iBB.Value=1, Sum(Fields!Billed.Value)
=Iif(Fields!iBB.Value=0, Sum(Fields!UnBilled.Value)
Which will not get past intellisense checking, -or- do I have to do something different here?
Thanks in advance.
Hi guys! I have these commands that insert into two tables, if condition 1 is met, it will insert into the first table, if the second condition is met, it will insert into the second table.
Is there a way for the insert statements to be merged so that I won't be executing two statements?
Dim update_phase_before As New SqlCommand("INSERT INTO TE_shounin_todokesho_jizen (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='before'", cnn)
Dim update_phase_after As New SqlCommand("INSERT INTO TE_shounin_todokesho_jigo (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='after'", cnn)
Thanks.
Hello, how can i merge together several select statements?
I have something like this:
CREATE PROCEDURE Forum_GetThreads @ID int,@AscDesc bitASBEGINSET NOCOUNT ON;SELECT * FROM forum_ansageSELECT * FROM forum_topics WHERE (status = 0) ORDER BY (created) DESCIF (@AscDesc = 0)BEGIN SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) DESCENDELSEBEGIN SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) ASCENDHere i want to merge them all together and return only one SELECT statement with all the data
I'm running a custom built report on .net page Basically there are a lot categories. I need to run SQL statements for and for each category, the catch is these statements are all in loops for i = 1 to 12. Therefore 108 categories X 12 iterations of SQL calls = a little more that 1200 sql calls from one .net page.
There is obviously a major problem with load time and running these reports are painful to the user and the system.
Is there any easier way to possibly pre-compile all the data into a temp database so I wouldn't need to make as many sql calls??????? If so, would a stored procedure handle something like this.
Sorry for being somewhat vague.
Hello,
I have SqlDataSource to retrieve a data into DetailsView, or GridView, whatever.
I want to use two Select command like this:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=AHMED-4714D04B3;Initial Catalog=mp;Integrated Security=True"
<%
if (( parID == 3) || (ParID ==4)
{
SelectCommand="SELECT [PID], [PageID], [PageContent], [ParID], [ChiID] FROM [mp_page] WHERE ([ParID] = @ParID)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ParID" PropertyName="SelectedValue" />
</SelectParameters>
}
else
{
SelectCommand="SELECT [PID], [PageID], [PageContent], [ParID], [ChiID] FROM [mp_page] WHERE ([ChiID] = @ChiID)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ChiID" PropertyName="SelectedValue" />
</SelectParameters>
}
</asp:SqlDataSource>
Is it possible to do something like that ?
Hey everyone,
I'm making a site and I need to write some code that will do the following things:
Write a statement for the Page_Load event to pull the information from the db and load it into a textbox and a textbox with multiline attribute.
I'm trying to combine the following two strings to create a single Insert statement (and thus only generate one record instead of two).
insertString = "Insert comments (uID) Select uID FROM users WHERE uName = @uName"
insertString2 = "INSERT comments (eventID, text) VALUES ( @eventID, @comment)"
I have tried:
Insert comments (uID, eventID, text) SELECT uID FROM users WHERE uName = @uName VALUES (uID, @eventID, @comment)
Individually they work fine, but I can't get the syntax correct to allow them to work together. As you can tell, I'm not very good with SQL, so any help would be greatly appreciated!
Thanks in advance.
I have a web application that has SQL7 as the back end. SQLServer and ORACLE have a feature that allows sql commands to be combined in one statement. Another words I am able to to
SELECT * FROM table_name WHERE id = 2 DROP TABLE other_table
If I type this in a query analyzer It will perform both commands. Is there a way to turn this 'feature' off.
The main reason I want to turn it off is so if a numerical value is sent as a url variable someone can't add the drop table statement or any other sql command to the value of the url variable and have it executed. We have added ample code to trap for this problem but I would like to also handle it at the database level.
Thanks,
Jeff
Folks,
I'm having some real problems using the OR condition in a very simple SQL statement and could use your help or insight on where the problem lies, or perhaps a workaround.
I have a large flat table in a SQL 7 database with 10 million + records called "HISTORY". I have not installed either service pack 1 or 2. I'm attempting to run a query that references the following four fields which are all non-clustered keys:
EQUIPMENT_NO TEXT 12
CHASSIS_IN TEXT 12
CHASSIS TEXT 12
SVC_DATE_TIME SMALLDATETIME
Here's the SQL statement:
SELECT * FROM HISTORY WHERE (HISTORY.EQUIPMENT_NO = 'XYZ123' OR HISTORY.CHASSIS = 'XYZ123' OR HISTORY.CHASSIS_IN = 'XYZ123') AND SVC_DATE_TIME >= '01/15/00 00:00:00 AM' AND SVC_DATE_TIME <= '02/28/00 23:59:59 PM'
ORDER BY EQUIPMENT_NO
This query takes 11 min. 5 sec. inder the Query Analyzer and ultimately returns the 31 desired records.
If you remove the SVC_DATE_TIME criteria, about 350 records are returned in a matter of seconds. I've also tried variations on the date syntax such as '01/15/00', etc. with no change in the amount of time to execute.
Other queries such as a simple AND condition combining EQUIPMENT_NO and SVC_DATE_TIME are snappy.
Are there known problems/bugs with "OR" conditions in queries that anyone is aware of, particularly with parentheses; am I composing this query incorrectly? Is there some alternate syntax that would work as expected? I can't see where the query shouldn't execute quickly as expected, particularly with all indexed fields involved. I'm stumped! Lend me your expertise. Thanks much.
Clark R. Farabaugh, Jr.
Financial Systems Analyst
VIT
Norfolk, VA
Hi,
I am having some problems trying to build an sql statement from more than one statement.
Here is the statement
select 'Insert App_Column (Table_ID, Column_Type_Transformation, Column_Name, )
Values (@table_ID,' ,'NULL,', name from payatwork..syscolumns where id in (
select id from payatwork..sysobjects where name like 'Employee_Profile')
order by colorder, ')'
What I am finding is that the bracket at the end of the statement is not appearing - how do I append statements to the end of this sql statement (i've tried various combinations of the + sign and the comma without success.
thanks,
Jim
How to update a column in the table using the data from another column in the same table? Thanks.
View 1 Replies View RelatedI have a web application that has SQL7 as the back end. SQLServer and ORACLE have a feature that allows sql commands to be combined in one statement. Another words I am able to to
SELECT * FROM table_name WHERE id = 2 DROP TABLE other_table
If I type this in a query analyzer It will perform both commands. Is there a way to turn this 'feature' off.
The main reason I want to turn it off is so if a numerical value is sent as a url variable someone can't add the drop table statement or any other sql command to the value of the url variable and have it executed. We have added ample code to trap for this problem but I would like to also handle it at the database level.
Thanks,
Jeff