getting a out of the database

I know this is possible but I am having a foggy brain moment, I need tobe able to retrieve an ' out of the database for example the nameO' Grady or O' Neil programaticaly. So would someone please throwme a bone and point me in the right direction?
You shouldn't have any trouble. It's the same as any other character.
SELECT someFieldContainingAnApostrophe FROM someTable
Where exactly are you having trouble?
|||for example I have a file saved with the title O'Connor M M 08-08-05.html the results I get with the program would be O.
I may have to do some further investigating but at this point the only results that fail are those similar to above

|||You will have to do more investigating. SQL Server in itself is not truncating your data.
|||I don't know about truncating the data but quoted identifiers are ANSI SQL and when you have the option turned on SQL Server follows the ANSI SQL 1992 guidelines. The following text is from the BOL (books online). Hope this helps.



Quoted identifiers are valid only when the QUOTED_IDENTIFIER option is set to ON. By default, the Microsoft OLE DB Provider for SQL Server and SQL Server ODBC driver set QUOTED_IDENTIFIER ON when they connect. DB-Library does not set QUOTED_IDENTIFIER ON by default. Regardless of the interface used, individual applications or users may change the setting at any time. SQL Server provides a number of ways to specify this option. For example, in SQL Server Enterprise Manager and SQL Query Analyzer, the option can be set in a dialog box. In Transact-SQL, the option can be set at various levels using SET QUOTED_IDENTIFIER, the quoted identifier option of sp_dboption, or the user options option of sp_configure.

When QUOTED_IDENTIFIER is ON, SQL Server follows the SQL-92 rules for the use of double quotation marks and the single quotation mark (') in SQL statements:

Double quotation marks can be used only to delimit identifiers. They cannot be used to delimit character strings.
To maintain compatibility with existing applications, SQL Server does not fully enforce this rule. Character strings can be enclosed in double quotation marks if the string does not exceed the length of an identifier; this practice is not recommended.

Single quotation marks must be used to enclose character strings. They cannot be used to delimit identifiers.
If the character string contains an embedded single quotation mark, insert an additional single quotation mark in front of the embedded mark:

SELECT * FROM "My Table"
WHERE "Last Name" = 'O''Brien'

|||Thank you all for you help I guess I am going to have to do someadditional testing of my code, and investigate furthure funny how alittle ' can caause such a big pain :-D I will monititor the forums ifanyone has some experience with this particular problem.

Thanks
|||Try the link below for possible solution I think the trick is to use double single qoutes like 'O''Neil' instead of just O'Neil. Hope this helps.
http://www.aspfaq.com/params.htm|||

rkwalters wrote:

I will monititor the forums ifanyone has some experience with this particular problem.

It's not that we don't have the experience and capability to help you,it's that you haven't supplied enough information for us to be able tohelp, nor have you provided any code. So we are guessing blindly.
Do you know exactly where the problem is happening?
Are you SURE the expected data is making it into your database?
Are you SURE the data is not being extractly correctly?

|||If you look at the data directly in your DB what do you see? I have a feeling you are trying to put the field (O'grady) into something else. Can you show your entire SQL statement you are using to pull and display the field contents?
Nick|||Nick the data is indeed in the DB, below I have posted some datafrom a query from SQL Server directly, the problem must lay inside theC# code I have written to populate the actual string. So I haveincluded the code as well as the stored proc's that I use. Hopethat gives you enough information.
The ouput of the sample data is as follows when the code is ran.
O'
Coleman Garry Carl 08-08-05.html
Sample of the data in the rl_resume table:
resume_id cd_key stored link
810 9644 O'Connor Petrina M08-08-05.html
788 9622 Coleman Garry Carl 08-08-05.html
C# code to get the data back for populating a string:
private string getResumes(int cdKey)
{
string cdResume="";
try
{

SqlConnection tConn = newSqlConnection(dbConnection);

SqlCommand SqlCmd = tConn.CreateCommand();

SqlCmd.CommandType =CommandType.StoredProcedure;
SqlCmd.CommandText = "ml_GetResumeLink";
SqlCmd.Parameters.Add("@.cd_key", cdKey);
tConn.Open();
SqlDataReader tSqlDr= SqlCmd.ExecuteReader();
while(tSqlDr.Read())
{
cdResume=tSqlDr.GetSqlString(0).ToString();
}
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
return cdResume;
}
}
stored procedure to retrive the data:
CREATE PROCEDURE [dbo].[ml_GetResumeLink]
(
@.cd_key BIGINT
)
AS
SELECT
cd_link
FROM
rl_resumes
WHERE
cd_key=@.cd_key
GO
Stored procedure to poputlate the data:
CREATE PROCEDURE [dbo].[rl_InsertCustomerDetails]
(
@.cd_am_key BIGINT,
@.cd_upload_date DATETIME,
@.cd_fname VARCHAR(50),
@.cd_lname VARCHAR(50),
@.cd_address1 VARCHAR(100),
@.cd_address2 VARCHAR(100),
@.cd_city VARCHAR(50),
@.cd_state VARCHAR(50),
@.cd_phone1 VARCHAR(50),
@.cd_phone2 VARCHAR(50),
@.cd_fax VARCHAR(50),
@.cd_mobile VARCHAR(50),
@.cd_email VARCHAR(50),
@.cd_notes VARCHAR(250),
@.cd_marketupdates BIT,
@.cd_ignore_flag BIT,
@.cd_zip VARCHAR(10),
@.cd_link VARCHAR(150),
@.my_Ident BIGINT

)
AS
INSERT INTO cd_customer_data
(
cd_am_key,
cd_upload_date,
cd_fname,
cd_lname,
cd_address1,
cd_address2,
cd_city,
cd_state,
cd_phone1,
cd_phone2,
cd_fax,
cd_mobile,
cd_email,
cd_notes,
cd_marketupdates,
cd_ignore_flag,
cd_zip
)
VALUES
(
@.cd_am_key,
@.cd_upload_date,
@.cd_fname,
@.cd_lname,
@.cd_address1,
@.cd_address2,
@.cd_city,
@.cd_state,
@.cd_phone1,
@.cd_phone2,
@.cd_fax,
@.cd_mobile,
@.cd_email,
@.cd_notes,
@.cd_marketupdates,
@.cd_ignore_flag,
@.cd_zip
)
SET @.my_Ident = SCOPE_IDENTITY()

INSERT INTO rl_resumes
(
cd_key,
cd_link
)
VALUES
(
@.my_Ident,
@.cd_link
)
GO
|||Ok in query Analyzer, if you run:
EXEC ml_GetResumeLink 9644
What do you see? If you see only O', then its a problem in SQL, if not, its a problem in your .NET code. Just trying to limit the issue.
I see you have a line: cdResume=tSqlDr.GetSqlString(0).ToString();
What are you doing with cdResume? have you stepped through the code to make sure you only get O'?
Nick|||This is what I get: O'Connor Petrina M 08-08-05.html
So I am sure that it has to be the code. I am adding the cd_resume tothe string http://myurl.com/webarea/ the result isthat a link is created and mailed out. All links work with theexception of those that have an O' in them. So I guess mynext step is to find out if there is a way within .net to actuallyretrieve and populate a string with that type of data.
cdResume=tSqlDr.GetSqlString(0).ToString(); maybe the wrongsyntax to use here, maybe a differnt sql reader function is theappropriate method.
Any thoughts on that?


|||

In your HTML, do you happen to have
"<a href='http://myurl.com/webArea" & cdResume & "'>"
by any chance? Recognize that above is using single quotes around the href instead of double qoutes.
Nick

|||the cdResume value is passed to another method and assigned to theappResume variable. below is the code that adds the appResume tothe string.
myString.Append("<a href='http://myurl.com/content/" + appResume + "'>");

Russ
|||OK thats the problem. Think about how that will look when you put in O'connor (or whatever it was)
<a href='http://myurl.com/content/o'connor.html'>
If you notice, there are now 3 single quotes, so the HTML thinks that the second single quote was the end of the URL. Try:
myString.Append("<a href="http://links.10026.com/?link="http://myurl.com/content/" + appResume + """>");
This should work for you.
Nicksql