none
command.Parameters, what am I doing wrong? RRS feed

  • Question

  • I'm writing an App that needs to take data passed to it and insert it into an SQL-db

     

    I'm using

    SqlConnection objConn = new SqlConnection(_ConnectionString);

    strSQL = "INSERT INTO [PASS].[dbo].[client] ( [correspondence_address1] ) VALUES (@address1);";

    SqlCommand command = new SqlCommand(strSQL, objConn);

    command.Parameters.Add("@address1", SqlDbType.VarChar).Value = _address1;

    objConn.Open();

    _clientID = Convert.ToInt32(command.ExecuteScalar().ToString());

     

    But if _address1 = "1 St John's Road" I get an error of :- Unclosed quotation mark after the character string '

     

    I thought that using parameters stopped the need for escaping strings and the like 

    OR

    Am I doing something wrong?

    Thursday, September 27, 2007 1:37 PM

Answers

  •  Dave.H. wrote:

    Manish,

     

    Well done your the first person to understand what I was trying to say.

     

    The point was that I was told that using parameters would mean that I didn't have to escape the ' and that this would get round the code injection problem that could happen in web Apps and the SQL errors that crop up from things like "Don't" &  "I'll".

     

    Sorry for confusing people but the "SELECT SCOPE_IDENTITY() AS 'Identity'" works fine as for the semicolon at the end of an SQL statement I have been doing that since MS-SQL 6.5 and also in mySQL it's an SQL thing if you look real carefully there's one at the end of the INSERT statement and before the SELECT.

     

    I've edited my original question to remove the confusing code.

    using parameters indeed avoids you having to escape manually, and avoids SQL injection, etc...    I am 100% sure that your parameter should not be the cause of this problem.
    I use parameters for years, and i've never had problems with quotes or other things in my paramter-values.

    The problem is also not on the semi-colon at the end of the select statement (i believe), but, have you already tried executing your SQL statement without the select scope_identity, and does the problem occurs as well ?

    I think, you have to escape the quotes around the identity alias you give in the select statement.   It's a C# string, and there, you have to escape quotes if you do not use the @ operator in front of the string.
    Friday, September 28, 2007 2:50 PM
  •  Frederik Gheysels wrote:
    using parameters indeed avoids you having to escape manually, and avoids SQL injection, etc...    I am 100% sure that your parameter should not be the cause of this problem.
    I use parameters for years, and i've never had problems with quotes or other things in my paramter-values.

    The problem is also not on the semi-colon at the end of the select statement (i believe), but, have you already tried executing your SQL statement without the select scope_identity, and does the problem occurs as well ?

    I think, you have to escape the quotes around the identity alias you give in the select statement.   It's a C# string, and there, you have to escape quotes if you do not use the @ operator in front of the string.

     

    You're right I was looking at the wrong part of my code for the error I had received, the error was in some legacy code a few lines up from the newer parameter based code.

     

    Sorry for the run around but it's interesting to see all the wrong answers :-)

    Wednesday, October 17, 2007 3:34 PM

All replies

  • hi Dave

    I think for the query u are creating remove this  ";" from the part of the query inside qotes.

    it looks like

     

    strSQL = "INSERT INTO [PASS].[dbo].[client] ( [correspondence_address1] ) ";

    strSQL += "VALUES (@address1)  SELECT SCOPE_IDENTITY() AS 'Identity' ";

     

    try this out ! May be it will help u out .

    and yes try to use stored procedure as they are fast and easy to modify without loking into the code.

    Happy coding

    Avinob

     

    Thursday, September 27, 2007 3:38 PM
  •  

    Avinob,

     

    I think you missed the point the error comes from the string I asign to @address containing a ' so the resulting query would be :-

    strSQL = "INSERT INTO [PASS].[dbo].[client] ( [correspondence_address1] ) ";

    strSQL += "VALUES ('1 St John's Road')  SELECT SCOPE_IDENTITY() AS 'Identity';";

     

    The result is Incorrect syntax near 's'.

     

    Thursday, September 27, 2007 4:39 PM
  •  

    have you tried @@Identity instead of 'Identity'. I think quote is fully supported in parameters.

     

    -L

    Thursday, September 27, 2007 6:49 PM
  •  

    At first glance, I see no problem with the parameters you're using.  And indeed, the use of parameters avoids the ' problem.

     

    However, maybe the problem is in the select scope_identity() as 'identity' statement.

    You'll have to escape the quotes around the alias you give to the scope_identity result:

     

    Code Block

    select scope_identity() as \'Identity\'

     

     

    Thursday, September 27, 2007 9:50 PM
  • strSQL += "VALUES ('1 St John's Road')  SELECT SCOPE_IDENTITY() AS 'Identity';";

    You can write above statement like

    strSQL += "VALUES ('1 St John''s Road')  SELECT SCOPE_IDENTITY() AS 'Identity';";

     

    instead of ' single quot you can have '' double quotes there

     

    Hope it helps

    Friday, September 28, 2007 5:02 AM
  • Manish,

     

    Well done your the first person to understand what I was trying to say.

     

    The point was that I was told that using parameters would mean that I didn't have to escape the ' and that this would get round the code injection problem that could happen in web Apps and the SQL errors that crop up from things like "Don't" &  "I'll".

     

    Sorry for confusing people but the "SELECT SCOPE_IDENTITY() AS 'Identity'" works fine as for the semicolon at the end of an SQL statement I have been doing that since MS-SQL 6.5 and also in mySQL it's an SQL thing if you look real carefully there's one at the end of the INSERT statement and before the SELECT.

     

    I've edited my original question to remove the confusing code.

    Friday, September 28, 2007 9:53 AM
  • Can you mark it as answer, it will help others to look where post got right answers
    Friday, September 28, 2007 12:08 PM
  •  Dave.H. wrote:

    Manish,

     

    Well done your the first person to understand what I was trying to say.

     

    The point was that I was told that using parameters would mean that I didn't have to escape the ' and that this would get round the code injection problem that could happen in web Apps and the SQL errors that crop up from things like "Don't" &  "I'll".

     

    Sorry for confusing people but the "SELECT SCOPE_IDENTITY() AS 'Identity'" works fine as for the semicolon at the end of an SQL statement I have been doing that since MS-SQL 6.5 and also in mySQL it's an SQL thing if you look real carefully there's one at the end of the INSERT statement and before the SELECT.

     

    I've edited my original question to remove the confusing code.

    using parameters indeed avoids you having to escape manually, and avoids SQL injection, etc...    I am 100% sure that your parameter should not be the cause of this problem.
    I use parameters for years, and i've never had problems with quotes or other things in my paramter-values.

    The problem is also not on the semi-colon at the end of the select statement (i believe), but, have you already tried executing your SQL statement without the select scope_identity, and does the problem occurs as well ?

    I think, you have to escape the quotes around the identity alias you give in the select statement.   It's a C# string, and there, you have to escape quotes if you do not use the @ operator in front of the string.
    Friday, September 28, 2007 2:50 PM
  •  Frederik Gheysels wrote:
    using parameters indeed avoids you having to escape manually, and avoids SQL injection, etc...    I am 100% sure that your parameter should not be the cause of this problem.
    I use parameters for years, and i've never had problems with quotes or other things in my paramter-values.

    The problem is also not on the semi-colon at the end of the select statement (i believe), but, have you already tried executing your SQL statement without the select scope_identity, and does the problem occurs as well ?

    I think, you have to escape the quotes around the identity alias you give in the select statement.   It's a C# string, and there, you have to escape quotes if you do not use the @ operator in front of the string.

     

    You're right I was looking at the wrong part of my code for the error I had received, the error was in some legacy code a few lines up from the newer parameter based code.

     

    Sorry for the run around but it's interesting to see all the wrong answers :-)

    Wednesday, October 17, 2007 3:34 PM