.NET SQL Parameters

[Kntx]

Hey guys,

I have an app that's been deployed a while and today I get some complaint that it's crashing. So I go and investigate and narrow down the problem to a single quote that is finding it's way into a sql query.

This perplexes me as I use parameterized stored procedures for every query in the program. I always thought that if you use parameters, then all special characters are escaped automatically. Not so it seems, despite numerous google searchs claiming otherwise.

I add my parameters like this...

C.Parameters.AddWithValue("@TRXDATE", TrxnDate); C.Parameters.AddWithValue("@CURNCYID", currency);
...

and execute like so...

lock(m_QueryPadlock) {
jrnEntry = Convert.ToInt32(C.ExecuteScalar());
}


The exception message is:

Incorrect syntax near 'd'. Unclosed quotation mark after the character string ')'.

The string that caused the problem is: "March-Funds Rec'd"

What gives???

 

[Markbnj]

Single quotes are always read as syntax unless escaped.

http://www.codetoad.com/asp/da...cing_single_quotes.asp

 

[Kntx]

Lol,

The problem wasn't the SqlParameters. The stored procedure I'm calling builds dynamic SQL. Doh!

Changed the dynamic sql within the SP to be parameterized and it works like a charm now.

 

[Gunslinger08]

Originally posted by: Kntx
Lol,

The problem wasn't the SqlParameters. The stored procedure I'm calling builds dynamic SQL. Doh!

Changed the dynamic sql within the SP to be parameterized and it works like a charm now.

Dynamic SQL inside an SP is usually a bad idea. Just build the SQL in code, if you're going to do that. You get none of the performance benefits if the SP can't be precompiled and building logic into an SP is not a best practice.

 

[Kntx]

Originally posted by: joshsquall

Originally posted by: Kntx
Lol,

The problem wasn't the SqlParameters. The stored procedure I'm calling builds dynamic SQL. Doh!

Changed the dynamic sql within the SP to be parameterized and it works like a charm now.

Dynamic SQL inside an SP is usually a bad idea. Just build the SQL in code, if you're going to do that. You get none of the performance benefits if the SP can't be precompiled and building logic into an SP is not a best practice.

I'm afraid I'm going to have to go ahead and disagree with you on this point. For my purposes, the primary benefit of using an SP rather than SQL in code is not performance. The benefits come from maintaining consistancy and control of how queries are performed across the enterprise.