How to insert statement in SQL with variable containing quotes from a procedure fired in C#?

Question

I need help, I have a problem while inserting a statement in SQL. I call a SQL statement from my ASP.NET program, some variables contain quotes so when the insert is fired I have an exception like:

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'xxxxx'. Unclosed quotation mark after the character string ''.

I don't want the content of my variable to be changed...

Any idea how to handle this?

The C# part :

        SqlCommand cmdInsertAssessment = new SqlCommand("xxxxxxx", sqlCnx);

        cmdInsertAssessment.CommandType = CommandType.StoredProcedure;
        cmdInsertAssessment.Parameters.AddWithValue("@templateID", templateID);
        cmdInsertAssessment.Parameters.AddWithValue("@companyID", companyID);
        cmdInsertAssessment.Parameters.AddWithValue("@userID",userID);
        cmdInsertAssessment.Parameters.AddWithValue("@opn",opn);
        cmdInsertAssessment.Parameters.AddWithValue("@mn",Mm);
        cmdInsertAssessment.Parameters.AddWithValue("@max",max);
        cmdInsertAssessment.Parameters.AddWithValue("@remarque",remarque);
        cmdInsertAssessment.Parameters.AddWithValue("@templateTheme",templateTheme);
        cmdInsertAssessment.Parameters.AddWithValue("@name", sName);
        cmdInsertAssessment.Parameters.AddWithValue("@finished", iFinished);
        cmdInsertAssessment.Parameters.AddWithValue("@datenow", dtNow); 
        try
        {
            cmdInsertAssessment.ExecuteNonQuery();
        }
        catch (Exception e)
        {

        }

SQL part :

CREATE PROCEDURE ["xxxxxxx"] @templateID int, 
@companyID int,
@userID int,
@opn nvarchar(255),
@mn nvarchar(255),
@max int,
@remarque nvarchar(255),
@templateTheme nvarchar(255),
@name nvarchar(255),
@finished int,
@datenow datetime

AS
BEGIN
DECLARE
@points AS FLOAT
SET @points=0

IF(@mn='M')
    BEGIN
        IF(@opn='O')
        BEGIN
            SET @points=10
        END 
        IF(@opn='P')
        BEGIN
            SET @points=2
        END 
    END 

IF(@mn!='M')
    BEGIN
        IF(@opn='O')
        BEGIN
            SET @points=2
        END
        if(@opn='P')
        BEGIN
            SET @points=1
        END 
    END
IF(@remarque=NULL)
    BEGIN
        SET @remarque='nothing'
    END

MERGE INTO [dbo].[Assessment] as target
USING (SELECT         @templateID,@companyID,@userID,@opn,@points,@max,@remarque,@templateTheme,@datenow,@name,@finished)
As source (_templateID,_companyID,_userID,_opn,_points,_max,_remarque,_templateTheme,_datenow,_name,_finished)
ON target.TemplateID=source._templateID
AND target.TemplateTheme=source._templateTheme
AND target.NameAssessment=source._name
WHEN MATCHED THEN
UPDATE SET Points = source._points, Remarque = source._remarque, FillDate= source._datenow, Finished = source._finished, OPN = source._opn
WHEN NOT MATCHED THEN
INSERT (TemplateID, CompanyID, UserID, OPN, Points, Max, Remarque, TemplateTheme, FillDate, NameAssessment,Finished) 
VALUES (source._templateID,source._companyID,source._userID,source._opn,source._points,source._max,source._remarque,source._templateTheme,source._datenow,source._name,source._finished);


END




GO

Thanks :)

Use Parameters, do not concatenate. Beware of SQL Injection. Check MSDN example: https://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.110).aspx — Paweł Dyl, Aug 09 '16 at 14:22
Need to be called in a procedure, because i check the values before the insert, like a trigger — Rachid Quenelle, Aug 09 '16 at 14:24
http://www.asp.net/web-forms/overview/data-access/accessing-the-database-directly-from-an-aspnet-page/using-parameterized-queries-with-the-sqldatasource-vb Check this website — Felipe Deguchi, Aug 09 '16 at 14:26

bbsimonbb · Answer 1 · 2016-08-09T15:56:06.257

Taking things from the begining ! Your procedure calculates a number of points, based on parameters you supply (@mn, @opn), then inserts or updates table Assessment. The first thing to say is that this is not a job for Merge. Merge is intended to operate on two tables, and using it for a row and a table is using a sledgehammer to crack a nut. You should really use

IF EXISTS( SELECT ID FROM ASSESSMENT WHERE... )

then write a classic insert and a classic update. Your procedure will be easier to understand, easier to maintain, and likely run much faster.

If you're still reading, I'll keep going. The calculation of points, business logic that uses nothing from the DB, will be much happier in the C#. Wherever you put it, you can use ternary operators to shorten those either-or choices. The following replaces 20 lines in your procedure.

var points = (mn == 'm')?(opn == 'O'?10:2):(opn == 'O'?2:1);

The assignment starting IF( @remarque = null ) can be done with a null coalescing operator ISNULL() in sql, ?? in C#.

And if you're still still reading, grab QueryFirst. You'll get a v.clean separation between SQL and C# and all your parameter creation will be done for you.

thx i u=have used something else but i keep your advice on my mind — Rachid Quenelle, Aug 10 '16 at 09:17

score -1 · Answer 2 · answered Aug 09 '16 at 14:32

-1

Because you said you wanted to use stored procedures

using (SqlConnection cnn = new SqlConnection(/*Connection String*/))
{
    using (SqlCommand cmd = new SqlCommand("MyStoredProcedure", cnn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@param1", "Value 1");
        cmd.Parameters.AddWithValue("@param2", "xxxxxx");

        cnn.Open();
    }
}

answered Aug 09 '16 at 14:32

Felipe Deguchi

586
1
7
25

thx ! but that's exactly what i am doing, but when a value of a paramater has quotes i have an exception (values are variable) – Rachid Quenelle Aug 09 '16 at 14:36
you need to post the code of your stored procedures and the c# in your question – Ronaldinho Learn Coding Aug 09 '16 at 14:41
It's not uncommon to see REPLACE(@SomeInput,'''',''''''). Use it if you need it. But if you follow my answer and use parameters directly in an insert or update, this should not be necessary I think. – bbsimonbb Aug 09 '16 at 16:00

How to insert statement in SQL with variable containing quotes from a procedure fired in C#?

2 Answers2