How to INSERT text that contains single quotes?

When users enter text into a textbox, to be INSERTed into my table, SQL Server throws an error if their text contains a single quote.

For example, if they enter "It's great!" then it causes this error:
Error: Incorrect syntax near 's'. Unclosed quotation mark after the character string ''.

How can I allow text with single quotes to be inserted into the table?

Here's my code:

string strInsert = "INSERT INTO [Comments] ([GameID], [UserID], [Comment]) VALUES (@.GameID, @.UserID, @.Comment)";

SqlConnection myConnection = new SqlConnection(<<myconnectionstuff>>);
SqlCommand myCommand = new SqlCommand(strInsert, myConnection);

myCommand.Parameters.Add( "@.GameID", Request.QueryString["GameID"] );
myCommand.Parameters.Add( "@.UserID", (string)Session["UserID"] );
myCommand.Parameters.Add( "@.Comment", ThisUserCommentTextBox.Text );

try {
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
}

catch (SqlException ex) {
ErrorLabel.Text = "Error: " + ex.Message;
}

finally {
myCommand.Connection.Close();
}

You write C# so they are called string literals in C# but in ANSI SQL they are Delimiters per ANSI SQL 92 and SQL Server is compliant so run a search for Qouted Identifier in SQL Server BOL(books online) and try the link below for SQL Server Delimiters. Hope this helps.

http://msdn2.microsoft.com/en-gb/library/ms176027.aspx

|||That code looks fine. Are you sure that is where the problem is?|||

(How can I allow text with single quotes to be inserted into the table?)

The person have not posted the code with the error but I replied the above and the error generated.

|||

Motley:

That code looks fine. Are you sure that is where the problem is?

Well it works fine unless I include a single quote in the text box somewhere.

Caddre, I've researched some more based on your reply, but I don't have easy access to the SQL Server settings - I'm on a shared hosting account. Is there some other workaround, maybe using a regular expression to replace a single quote with something else, at least temprarily while I'm processing the text? I tried entering /' instead, to see if an escape character would work, but SQL Server still won't take it.

|||

Yes there is SET QUOTED_IDENTIFIER ON/OFF will help you deal with it. Try the link below for details.

http://msdn2.microsoft.com/en-US/library/ms174393.aspx

|||

Caddre:

Yes there is SET QUOTED_IDENTIFIER ON/OFF will help you deal with it. Try the link below for details.

http://msdn2.microsoft.com/en-US/library/ms174393.aspx

I'm wondering how to send the "SET QUOTED_IDENTIFIER ON" command within my ASP.NET page.

I'm using a command called ExecuteNonQuery(); -- so does that mean I can just append it to the beginning of my INSERT string, like this?

string strInsert = "SET QUOTED_IDENTIFIER ON GO INSERT INTO [Comments] ([GameID], [UserID], [Comment]) VALUES ..."

Does it last only during this query or will it stay on somehow until I turn it off?

Sorry, I'm a newbie with SQL Server and ASP.NET.

|||You have to use it in your stored proc and ExecuteNonQuery takes stored proc. Hope this helps|||

Yes, I understand. It should work fine for any value you place within the textbox control. That includes quotes. I do this quite often with no problems.

As for can you put the SET statement before your query, the answer is yes, but do not separate the commands with GO. You may place a semi-colon between them if you want to however, like:

SET {stuff}; SELECT {stuff}

|||

OK, I am trying to make sense of this, and it's just not making any sense to me. (I have limited experience with SQL and SQL Server and VBScript; I paid a programmer to write a script and after delivering a partly-working script, he promised to fix it and then disappeared. I think he couldn't solve this problem himself.)

I am getting the error message "Unclosed quotation mark after the character string" when I execute this SQL statement from VBScript, butno error when I execute the same SQL statement from within SQL Management Studio. Any suggestions would be extremely welcome.

INSERT INTO Raw_Datafeeds (ProductID,Name,MerchantID,Merchant,Link,Thumbnail,BigImage,Price,RetailPrice,Category,SubCategory,Description,Custom1,Custom2,Custom3,Custom4,Custom5,LastUpdated,Status,Last_Loaded)
VALUES
('454848129','Healthy Ears Ear Cleanser - Buy 2 Get 1 Free','10052',
'Pet Nutrition Products','http://www.shareasale.com/m-pr.cfm?merchantID=10052&userID=YOURUSERID&productID=454848129',
'http://www.petnutritionproducts.com/images/products/100/cat_ears.jpg',
'http://www.petnutritionproducts.com/images/products/400/cat_ears.jpg',15.98,0,'Home/Family','Pets',
'Healthy Ears Ear Cleanser is a veterinarian formulated combination of gentle ingredients that aid in the removal of dirt, wax and other unwanted matter from your cat''s ears.',
'Dog','Ear Care',' ',' ',' ','6/6/2006 1:35:01 AM','instock','10/25/2006 4:06:00 PM')

|||

The original problem in this thread could probably be solved by specifying the type of each parameter rather than letting the system guess.

As for mark's problem, it's obviously related to "cat''s" in your insert string, however since you haven't posted the code you are using in VBScript, it'd be difficult to say where the problem is. As a quick hack, you can replace all quotes in any parameter with '+CHAR(39)+' so that cat's would then become cat'+CHAR(39)+'s.

How to insert numbers from a text box to a Sql database table column’s type numeric?

Hi,

I am getting an error when I try to insert a number typed in a text box control into a Sql database table column's type numeric(6,2). For example: If I type 35,22 into the text box, or 35, and then I submit the form to insert the data into database, I get the follow error:

System.FormatException: Input string was not in a correct format.
Line 428: CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.decimal, "Measure"))

A piece of the SP inside Sql server:

USE market26
GO
ALTER PROC new_offer
@.Offer_id bigint, @.Measure numeric(6,2) = null, …

What is wrong? Why it doesn' t accept the number typed in my text box?

Thank you,
CesarThis is not correct:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

This would probably work (not tested):

SqlParameter mParm = new SqlParameter("@.measure", SqlDbType.Decimal);
mParm.Size = 13; // Max size in Bytes of the Decimal number
mParm.Precision = 8; // Total number of digits allowed (right + left of decimal point
mParm.Scale = 2; // Set the number of decimal places the Parameter value is resolved
mParm.set_IsNullable(true);
mParm.Value = decimal.Parse(myTextBox.Text.Trim());
CmdInsert.Parameters.Add(mParm)

|||Hi,

I always write my ASP.NET commands to talk with Sql Stored Procedures thus:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

I think that it is correct, and it works fine.

I have never used a text field to insert numeric data into Sql database, but, I have to define all these parameter properties in order to pass a numeric data type to the database?

Thanks|||That's interesting. There is no Constructor attributed to the SqlParameter Class which accepts a Constructor looking like this:

New SqlParameter(<string>,<SqlDbType Enum>, <string>)

as far as I know...But hey, go for it!|||Take a look at this piece of an article and let me know what do you think about it:

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
cmdOrders.CommandType = CommandType.StoredProcedure
' Set up parameter for stored procedure
Dim prmCustomerID As New SqlParameter()
prmCustomerID.ParameterName = "@.CustomerID"
prmCustomerID.SqlDbType = SqlDbType.VarChar
prmCustomerID.Size = 5
prmCustomerID.Value = "ALFKI"
cmdOrders.Parameters.Add(prmCustomerID)
Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

This example makes all the parameter settings explicit. Some developers like this style, and it's good for instructional purposes. However, some developers prefer an equivalent alternative that has fewer lines of code:

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
cmdOrders.CommandType = CommandType.StoredProcedure
cmdOrders.Parameters.Add(New _
 SqlParameter("@.CustomerID", SqlDbType.VarChar, 5))
cmdOrders.Parameters("@.CustomerID").Value = "ALFKI"
Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

This code behaves exactly the same as the previous example. However, it only needs two lines of code for each parameter rather than six. When a stored procedure has a lot of parameters (as some of our later examples do), this can be quite a difference on lines of code required, so we'll use that form from this point onward.

The complete article is here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadvnet/html/vbnet09102002.asp|||Hey! Perhaps you have misunderstood me. I meant that I always use this structure:
(Which I knew that something was wrong)

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

Instead of this:

SqlParameter mParm = new SqlParameter("@.measure", SqlDbType.Decimal)
mParm.SourceColumn = "Measure"
CmdInsert.Parameters.Add(mParm)

I knew that something was wrong, I suspected that something it lacked in my SqlParameter, and you helped me with your example!, it lacked only the size property which in my case is 5. I am sorry, the only thing that I saw strange is the structure you wrote, and the amount of parameters which I don' t need to use (in this case). I always use the shorter alternative ;-).

So, the correct SqlParameter in my case is:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal,5, "Measure"))

And now works fine.
Thank you very much!
Cesar

how to insert more than once when processing one row in script component

I am trying use call DirectRowToOutput0 more than once to achieve the goal, but it gives an error of calling COM. I can create more than one destination to achieve it, but I need create 8 destinations for my situation. Is there any easy way?

Thanks

Is this any help to you: http://blogs.conchango.com/jamiethomson/archive/2005/09/05/2113.aspx

-Jamie

|||no, that is not what I am trying to do. In fact, I need insert to the same destination more than once.|||

In that case this will be an asynchronous component. You will then be able to do what you want with the rows.

-Jamie

|||That is it. Thank you so much.

How to INSERT a string that contains single-quotes?

My code results in SQL statements like the following one - and it gives an error because of the extra single-quotes in 'it's great':

UPDATE Comments SET Comment='it's great' WHERE UserID='joe' AND GameID='503'

Here's the error I get when I try this code in SQL Server:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 's'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''.

I need to know how I can insert a string such as 'it's great' - how do I deal with the extra quotes issue? is there a way to ecape it like this 'it/'s great' ? This doesn't seem to work.

Here's the code that generates the SQL. I'm using a FCKeditor box instead of a TextBox, but I got the same error when I was using the TextBox:

string strUpdate = "UPDATE Comments SET Comment='";
strUpdate = strUpdate + FCKeditor1.Value;
//strUpdate = strUpdate + ThisUserCommentTextBox.Text;
strUpdate = strUpdate + "' WHERE UserID='";
strUpdate = strUpdate + (string)Session["UserID"];
strUpdate = strUpdate + "'";
strUpdate = strUpdate + " AND GameID='";
strUpdate = strUpdate + Request.QueryString["GameID"];
strUpdate = strUpdate + "'";

SqlConnection myConnection = new SqlConnection(...);
SqlCommand myCommand = new SqlCommand(strUpdate, myConnection);

try
{
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
ErrorLabel.Text = "Error: " + ex.Message;

}
finally
{
myCommand.Connection.Close();
}

I'm using SQL Server 2005 and ASP.NET 2.0

Much thanks

ok i wont go through your code...but i can tell you the key point

try run

SELECT 'AAAA' SQL retuen AAAA

SELECT '''AAAA''' SQL return 'AAAA' (you need 3 * ' + AAAA + 3 * ' )

hope this give u idea

|||

You should use parameterized query, this problem will go away.

You can find out why by searching this forum. If you still have question, please post back.

|||

limno:

You should use parameterized query, this problem will go away.

You can find out why by searching this forum. If you still have question, please post back.

This is the best advice you will get today. If you value your applications you will never ever build an Sql statement that way again

string strUpdate = "UPDATE Comments SET Comment= @.Comment WHERE UserID = @.UserID AND GameID = @.GameID'";

SqlConnection myConnection = new SqlConnection(...);
SqlCommand myCommand = new SqlCommand(strUpdate, myConnection);

myCommand.Parameters.AddWithValue( "@.Comment", FCKeditor1.Value );
myCommand.Parameters.AddWithValue( "@.UserID", Session["UserID"] );
myCommand.Parameters.AddWithValue( "@.GameID", Request.QueryString["GameID"]);

try
{
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
ErrorLabel.Text = "Error: " + ex.Message;

}
finally
{
myCommand.Connection.Close();
}

|||

Thanks, it works now.

how to indicate if error exisits

I have created a package.Is there an option if you can know if the package had executed successfully or with the error...how do i need to set?how are you planning to execute the package? I think SS Agent has alert/notifications you could use...|||ok with sql server agent how do i include email address on error in my package..And what i am intrested is how send email task works...it shows errors witht this task.|||

Well, my sugestion was simpler than that. I was thinking to let the SS agent to sen an email when the job fails; so you don't have to modify the packages. The only thing is that the SS agent may not give a very detail reason of the failure.

You are right, you could use event handlers and send email task to do this, but it requires to modify the packages. In both cases, you can find information in these forums.

How to ignore error and continue trasactional replication

Hi,
I have a trasactional replication setup in SQL 2000 with SP3. Accidentally,
I deleted a row in the suscriber table. When I deleted the same row in the
publisher database, I have error in the replication monitor "the row was not
found at the Subcriber when applying the replicated command".
Please let me know how do I get rid of this error and continue replication.
I don't want to reinitialize the subscriber again because the table is big
and takes lot of time.
I appreciate your help!!
Thanks
Chinna.
gonzo - cowboy- to hell with database consistency approach - right click on
your failed agent, select agent profiles, and then select the continue on
data consistency profile.
more cautious approach. enable logging for your distribution agent, and
restart it. Find out the row which you deleted. Get this row from teh
publisher and manually construst the insert statement and put it back in the
subscriber. Restart your distribution agent.
check out this kb article for more info on how to do the logging.
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chinna Kondaveeti" <Chinna Kondaveeti@.discussions.microsoft.com> wrote in
message news:4A2DEA01-1131-41EF-B432-6C26E923EBFA@.microsoft.com...
> Hi,
> I have a trasactional replication setup in SQL 2000 with SP3.
Accidentally,
> I deleted a row in the suscriber table. When I deleted the same row in the
> publisher database, I have error in the replication monitor "the row was
not
> found at the Subcriber when applying the replicated command".
> Please let me know how do I get rid of this error and continue
replication.
> I don't want to reinitialize the subscriber again because the table is big
> and takes lot of time.
> I appreciate your help!!
> Thanks
> Chinna.
>
|||Thanks to everyone who helped me. It worked!!. I have changed back to default.
I appreciate your help!!
Thanks
Chinna.
"Hilary Cotter" wrote:

> gonzo - cowboy- to hell with database consistency approach - right click on
> your failed agent, select agent profiles, and then select the continue on
> data consistency profile.
> more cautious approach. enable logging for your distribution agent, and
> restart it. Find out the row which you deleted. Get this row from teh
> publisher and manually construst the insert statement and put it back in the
> subscriber. Restart your distribution agent.
> check out this kb article for more info on how to do the logging.
> http://support.microsoft.com/default...&Product=sql2k
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Chinna Kondaveeti" <Chinna Kondaveeti@.discussions.microsoft.com> wrote in
> message news:4A2DEA01-1131-41EF-B432-6C26E923EBFA@.microsoft.com...
> Accidentally,
> not
> replication.
>
>