command.Parameters, what am I doing wrong?
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 =newSqlCommand(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?
[1710 byte] By [
Dave.H.] at [2008-1-10]
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
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'.
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\'
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
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.
 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.
