SQL Datasource and 'Apostrophes'

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>

0
SmokinJoe
1/2/2008 6:51:51 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

5 Replies
971 Views

Similar Articles

[PageSpeed] 27

You will need to esape your single quotes on both insert and select.  If you don't escape the single quotes during insert your Insert command will break after the first single quote i.e. O'Reily will need to be inserted as O''Reily.  Also your Select (search) statement will also need to escape single quotes if they exist or the sql command will break after the first single quote.

You may want to consider having the SQL Datasource use a Session variable instead of a formfield value.  You can set your onclick event after they have entered in the name they want to search for.  Your onchange event handler could look something like this:

Dim strSearch as String = Replace(Request.Form("FormField"), "'", "''")

Session("LNameSearch") = strSearch

 

0
Loganix77
1/2/2008 7:07:33 PM

Using the code snip supplied, would you be able to advise how to exactly do this...?

thanks again!!

0
SmokinJoe
1/2/2008 7:09:25 PM

Something Like This:

<script runat="server">

Protected Sub TextBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs)

Session("LNameSearch") = Replace(Me.TextBox1.Text, "'", "''")

End Sub

 

</script> 

<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:SessionParameter Name="LName" SessionField="LNameSearch" Type="String" />

</SelectParameters>

</asp:SqlDataSource>

 

<asp:TextBox ID="TextBox1" runat="server" OnTextChanged="TextBox1_TextChanged"></asp:TextBox>

<asp:Button ID="Button1" runat="server" Text="Search" /></div>

0
Loganix77
1/2/2008 7:20:44 PM

Thank YOU!

0
SmokinJoe
1/2/2008 8:38:11 PM

Loganix77:

You will need to esape your single quotes on both insert and select.  If you don't escape the single quotes during insert your Insert command will break after the first single quote i.e. O'Reily will need to be inserted as O''Reily.  Also your Select (search) statement will also need to escape single quotes if they exist or the sql command will break after the first single quote.

This is actually incorrect.  You only need to worry about escaping quotes if you are using string concatenation.  The SQL Datasource you had does not use string concatenation, and uses a parameterized query.

<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:ControlParameter Name="LastName" ControlID="txtTextbox1" Property="Text" Type="String" />

</SelectParameters>

</asp:SqlDataSource>

 

Doing it this way has a number of advantages.

First, you aren't storing anything in the Session State.  That will reduce the amount of resources consumed by the webserver for each client accessing your site.  If you don't need write access anywhere else on the page, then you can mark the page as only needing read access to the Session State, allowing it to be processed on the server concurrently with other requested resources rather than forcing the requests to be processed sequentially.

Secondly, because you aren't incorrectly padding quoted data with useless quotes, any other system (Reporting Services, Export Wizard, etc) you may use to extract/use the data in the future won't have to be specifically told to remove these extraneous quotes.

Third, you can easily join to data entered through other means, or bulk imported, etc.

0
Motley
1/2/2008 10:21:31 PM
Reply:

Similar Artilces:

SQL replace '-' by ''
Hi all i have a question regarding sql, i want to replace some characters...   any knows simply how to do this?   I want to replace "999-25000-69" by "9992500069"   grtzDon't forget to click "Mark as Answer" on the post that helped you. SELECT Replace('999-25000-69','-', '') or SELECT Replace(col1,'-', '') as col1 FROM yourTableLimno...

AdventureWorks DB
I'm trying to load the 'contacts' table from SQL2005 AdventureWorks database with a SQL Datasource control and when i try to use the "Configure Data Source" wizard i select the table and i get the following error: "Invalid object name: 'Contact'."I think i know the reason for the error but i don't know how to fix it.  What i think is happening is SQL 2005 makes use of 'schemas' to organize tables (similar to namespaces) so the object 'Contact' is actually 'Person.Contact'.  However the "Configure Data Sou...

''''''''''''''''''''
Name: haznen Email: haznenatyahoodotcom Product: Gran Paradiso Alpha 8 Summary: '''''''''''''''''''' Comments: '''''''''''''''''''''''''''''''''''' Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9a1) Gecko/20061204 UGES/1.7.2.0 GranParadiso/3.0a1 From URL: http://www.mozilla.org/projects/granparadiso/ Note to readers: Hendrix gives...

'''''
Name: mario Email: ramar17atfastwebnetdotit Product: Gran Paradiso Alpha 2 Summary: ''''' Comments: K: Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9a2) Gecko/20070206 GranParadiso/3.0a2 ...

how to use 'in' operate when create 'where' statement in SQl
In my DW ,I use 'in' operate to construct where statement in sql select and set range as dw argument.then I execute code as following: string ls_item[] ls_item[1]='...' ls_item[2]='...' .... dw_1.retrieve(ls_item) it work very well,however I want to set ls_item to all value that database have,does I need to set ls_item value one by one? can I use 'in all' ? What you are saying is that the value you are selecting on doesn't matter, so why include it in the where clause? wxs <wxs163@163.net> wrote in article <G4PXeuqD#GA....

How to convert MS SQL's 'smalldatetime' to PB 'date' in DW?
Suppose I have a Powerbuilder datawindow with a column of type 'date'. I also have a MS SQL 7 database table with a column of type 'date' where 'date' is a MS SQL's user defined type which maps to MS SQL's 'smalldatetime'. Now I retrieve the datawindow. On Windows XP Professional in the datawindow I have a beautiful date, let's say '1-2-03' but on Windows 95 I have garbage: '??-??-1241' or similar. Here is my question: how to convert the MS SQL's 'smalldatetime' value to the PB 'date' type 'on-the...

SQL query using 'UserID' or 'UserName' as parameter
What is the simplest SQL instruction that will perform a database query within a ‘SqlDataSource’ control based on the ‘UserID’ or ‘UserName’ of the logged-in user? Specifically, I don’t know how to read either of these values so that I can apply them in a ‘WHERE’ statement. I want to retrieve data from a table that is specific to the user who is currently logged-in. Thanks! Look at SUSER_SNAME() function in BOL Also HOST_Name() and HOST_ID()Beware of bugs in the above code; I have only proved it correct, not tried it. (Donald Knuth) You may find a solution from this thread: http://...

Can you use 'Pause' or 'Sleep' in SQL query?
 Can you use 'Pause' or 'Sleep' in SQL query?Success comes to those who prepare well and put in effort. Hi, can you explain it brieflyWith LuvDhanaDont forget to mark as answer if my reply helped you...  I seem its not possible in sql server although you can rotate a loop into stored procedure for delay but in sql query its not possibleShah Dharnendra GSr.Analyst Programmer,GTL-Ahmedabad Hi robinwilliams, You can try the "waitfor " sql statement. waitfor would block the execution of a batch, stored procedure, or transaction until a specified tim...

SQL incorrect syntax near 'id' and 'go'
Hello everyone,  I get a Incorrect sytax error near 'id' and 'go' when I try to execute my classified SQL scripts with godaddy. Here are a few posts that resemble the same problem as mine. I've tried all the tricks in these posts, but haven't had any luck. http://forums.asp.net/p/1320780/2624445.aspx#2624445 http://forums.asp.net/p/932670/1097213.aspx#1097213   Here is my SQL classified-add file. Sorry it's pretty long. Does anybody see anything wrong?  CREATE TABLE [classifieds_Ads] ( [Id] [int] IDENTITY (1, 1) NOT NULL...

Q: SQL constructs for 'match all' v 'match any'
Hi Folks Let's say I have search form with fields like these, for Organizational Entities: o Name o Code o Cost center o Fund My customer wants a search option with 2 choices: o Match all o Match any meaning that if the Entity's name and fund are entered, then= 'match all' will operate like AND on those 2 fields and 'match any' will= operate like OR. Now, has anyone developed logic to handle building the SQL in= cases like this? I searched CPAN for modules named 'match', and found 27 modules,= but none at first glance seeme...

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'WinFM SQL PFS'. Login fails.
HiASP pages were properly connecting to SQL DB until I restored from a backup DB.  As soon as I finished restoring from the backup DB, I am getting the following error message.   I did not make any changes on ASP side.  IIS Server and SQL 2K Server is running on a WIN2K Server Box.Please help.Thank you.HTTP 500.100 - Internal Server Error - ASP errorInternet Information ServicesTechnical Information (for support personnel)Error Type:Microsoft OLE DB Provider for ODBC Drivers (0x80004005)[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login...

T-SQL what that string ''99999999-9999-9999-9999-999999999999''' mean ?
hi, I have a question about one t-SQL. exec('use ['+@db_Name+'] update Account.Q6_Account set TaxCodeID=null where len(TaxCode)=0 and TaxCodeID=''99999999-9999-9999-9999-999999999999''');  that TaxCodeID field is guid what that string ''99999999-9999-9999-9999-999999999999''' mean ? that means if TaXcodeID that fields equal to that illegal  Vale ''99999999-9999-9999-9999-999999999999''  and  len(TaxCode)=0 then set TaxCodeID=null?  or is there anyother meaning?   I interpreted the SQL...

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Hello All, I'm Getting the following error when calling a dll from my vb.net application. [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. The dll was written in vb6 and accesses a sql db using the following connection string. Public Const GLOBAL_DNS As String = "DSN=TransSrvFinal;UID=sa;PWD=admin;" This DLL works fine when called from other Windows Apps. However I'm now trying to call this dll from with in an asp.net application. I'm using vb.net as the codebehind. I'm a...

An error occurred during the execution of the SQL file 'InstallRoles.sql'.
    Error text:An error occurred during the execution of the SQL file 'InstallRoles.sql'. The SQL error number is 446 and the SqlException message is: Cannot resolve collation conflict for equal to operation.Cannot resolve collation conflict for equal to operation.Error occurred when running 'aspnet_regsql -S servername -E -d database -A r'with previously installed mambership scheme on database.When googling on this issue, one post on forums.asp.net is listed but link is dead...please help...     Bartul Bonacic - NETMedia ADDITION:Also I forgot to ment...

Web resources about - SQL Datasource and 'Apostrophes' - asp.net.sql-datasource

Resources last updated: 2/3/2016 6:36:55 PM