How to replace single quotes in SP

Question

Hi this is my query SELECT AccountNo BusinessName FROM CustomerDetails WITH (NOLOCK) WHERE BusinessName like @BusinessName + '%' ... When i execute my SP output Shouldbe like this AccountNo BusinessName 10509 Anderson's The Grocery Store....... But when iexecut emy SP --EXEC DBSP_SF_GetAccountsLikeAccNo '10509''Anderson's The Grocery Store' Getting error like........... Incorrect syntax near 's'. Unclosed quotation mark after the character string ''. So when i execute my SP adding another Apostrophe after anderson like EXEC DBSP_SF_GetAccountsLikeAccNo '10509''Anderson''s The Grocery Store' ite executed so i want to replace how to replace using replace function in SP plz help me

John Penn · Answer

When you use: REPLACE(@BusinessName '''' '''''') You're telling the db engine to find instances of single quotes in the string replace them with two single quotes and return the results. In your example Anderson's becomes Anderson''s which is what you want to search by. '''' is interpreted by the engine as a string that contains one single quote. '''''' is interpreted by the engine as a string that contains two single quotes.

John Penn · Answer

Sorry Karthik... this is what happens when I post answers without testing them first. Apologies for wasting your time. I tried this and it worked: BusinessName = BusinessName.Replace(' \\');

John Penn · Answer

Try changing the hfAccount.Attributes.Add statement to the following: hfAccount.Attributes.Add(onclick javascript: UpdateParentWindow('+hfAccount.Text+''+ lblBusinessName.Text.Replace(' \') +''+ViewState[CtrlAcc]+''+ViewState[CtrlBName]+')); You have to escape the single quotes in the business name because you're using single quotes to delimit the strings in the javascript statement.

John Penn · Answer

When you want to show a single quote in a SQL string constant you have to use two single quotes as in: DECLARE @foo VARCHAR(100) SET @foo = 'Anderson''s' Try the REPLACE function in your procedure as follows: ALTER PROCEDURE [dbo].[DBSP_SF_GetAccountsLikeAccNo] @AccountNo VARCHAR(20) @BusinessName VARCHAR(40) @Type VARCHAR(2) AS BEGIN IF @Type = 'AN' BEGIN SELECT AccountNo BusinessName FROM CustomerDetails WITH (NOLOCK) WHERE AccountNo like REPLACE(@AccountNo '''' '''''') + '%' END ELSE IF @Type = 'BN' BEGIN SELECT AccountNo BusinessName FROM CustomerDetails WITH (NOLOCK) WHERE BusinessName like REPLACE(@BusinessName '''' '''''') + '%' END END

Satyapriya Nayak · Answer

Hi karthik Here is your solution SELECT * FROM CustomerDetails WHERE (BusinessName LIKE '%''%') OR (BusinessName LIKE '%%') Thanks If this post helps you mark it as answer

karthik parcha · Answer

Hi John This is my query SELECT AccountNo BusinessName FROM CustomerDetails WITH (NOLOCK) it retreving the data but it retreving only the data with out single and double quotes In my database there are Businessnames like single quotes and double quotes 109 rows are there ... For retreving only sigle and double quotes query i select * from CustomerDetails where BusinessName like '%%' the data with single quotes and double quotes are not retreving Plz help me how to retreive normal data and single and double quotes data also from data base

karthik parcha · Answer

HI john Thank u for your patience sometimes it happens john i can understand your problem .......... its working john....................... thank u thank u so much john its displaying in textbox now link button is also firing.......you are helping me alot john ...........

karthik parcha · Answer

plz plz is there another way to replace single quotes

karthik parcha · Answer

same problem john ' is also inserrting in textbox

karthik parcha · Answer

hi i will try this if there any issues i will contact you thanks karthik

John Penn · Answer

If you're using .Net 4 you can try the HttpUtility.JavaScriptStringEncode method.

John Penn · Answer

Try this instead: BusinessName = BusinessName.Replace(' ');

karthik parcha · Answer

Are you there?

karthik parcha · Answer

Ya Itried your code HyperLink hfAccount = (HyperLink)e.Row.FindControl(hfgvAccountNo); Label lblBusinessName = (Label)e.Row.FindControl(lblgvBusinessName); string BusinessName = Convert.ToString(lblBusinessName.Text); BusinessName = BusinessName.Replace(' \'); //// BusinessName = BusinessName.Replace(' '); hfAccount.Attributes.Add(onclick javascript:return UpdateParentWindow(' + hfAccount.Text + '' + BusinessName + '' + ViewState[CtrlAcc] + '' + ViewState[CtrlBName] + ')); not replacing when i debug through the application iam getting text like lblbusinessname={Text=Anderson's\ The Grocery Store\} i have to place Anderson's The Grocery Store into textbox plz help me I dont know why the link button is not firing when the text is like Anderson's The Grocery Store But when the text is like Andersons the grocery store with out any single quotes its displaying in textbox

John Penn · Answer

Did you try the replace I code I suggested? What was the result?

karthik parcha · Answer

Since browsers display HTML ASCII as regular text you can simply transform all your single and double quotes that appear in text for display into their ASCII equivalent. The ASCII code for a single quote is ' and similarly a double quote can be represented in HTML as &#34;. Using these values it is easy to display both single and double quotes in HTML output without interfering with any programmatic use of the quotes. This means that quotes in display text won't interfere with any string delimiting quotes for display. When i changed my code like this its working and linkbutton is firing displaying text in textbox but it displaying &#34; also in textbox....... John i tried your code not replacing singlequotes and link button is not firing plz do u have any other idea plz can change code for me

John Penn · Answer

Why are you using &#39? Just replace the single quotes in the business name with \' so that javascript knows that you want to embed a single quote within the string.

karthik parcha · Answer

Hi John I tried Your COde sory its not working Itried like this HyperLink hfAccount = (HyperLink)e.Row.FindControl(hfgvAccountNo); Label lblBusinessName = (Label)e.Row.FindControl(lblgvBusinessName); string BusinessName = Convert.ToString(lblBusinessName.Text); BusinessName = BusinessName.Replace(' '); hfAccount.Attributes.Add(onclick javascript:return UpdateParentWindow(' + hfAccount.Text + '' + BusinessName + '' + ViewState[CtrlAcc] + '' + ViewState[CtrlBName] + ')); When i click thelink button which contains businessname single quotes and double quotes the businessname is displaying in textbox like Anderson ' s The Grocery Store... where should i have to change the code plz help me from 2 days iam trying this

karthik parcha · Answer

plz helpme

karthik parcha · Answer

Hi john i have one issue inmy application in my gridview there so many fileds like 1.AccountnO 2.Businessname these are columns in that for accountnumber i have textbox then clicked on the (...) button immediate to the right of the account number field when i clicked this button businessname is retreived in one window in that accountnumber is link button and businessname is label. when i click the linkbutton that businesname should be placed in businesscolumn textbox... so my issue is when the text with single quotes Anderson's The Grocery Store is not placing in text box but when the text with Andersons The Grocery Store is placing in textbox can u plz help me this is my code iam getting error like javascript error occured protected void gvAccounts_RowDataBound(object sender GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { DataRowView dr = (DataRowView)e.Row.DataItem; if (dr != null) { HyperLink hfAccount = (HyperLink)e.Row.FindControl(hfgvAccountNo); Label lblBusinessName = (Label)e.Row.FindControl(lblgvBusinessName); //string BusinessName = lblBusinessName.ToString(); //string newBusName=BusinessName.Replace(\ ); hfAccount.Attributes.Add(onclick javascript: UpdateParentWindow('+hfAccount.Text+''+lblBusinessName.Text+''+ViewState[CtrlAcc]+''+ViewState[CtrlBName]+')); } } }

karthik parcha · Answer

Hi John penn Its working John (@BusinessName '''' '''''') Why you written this many single quotes can u explain john plz it will be easy to remember for me again when this issue comes to me thanks alot john karthik

karthik parcha · Answer

This is my sp ALTER PROCEDURE [dbo].[DBSP_SF_GetAccountsLikeAccNo] @AccountNo VARCHAR(20) @BusinessName VARCHAR(40) @Type VARCHAR(2) AS BEGIN IF @Type = 'AN' BEGIN SELECT AccountNo BusinessName FROM CustomerDetails WITH (NOLOCK) WHERE AccountNo like @AccountNo + '%' END ELSE IF @Type = 'BN' BEGIN SELECT AccountNo BusinessName FROM CustomerDetails WITH (NOLOCK) WHERE BusinessName like @BusinessName + '%' END END