Sunday, February 12, 2012

Commad works but 0 rows effected

What does it mean when you have a querey like this

Update tblWatchInstance SET [NewInvoiceGen] = '1' Where tblWatchInstance.WatchID = '%" & txtWatchID.Text & "%'"

But when you run in the Qanalyzer it says 0 rows effected...why is that? could it be something i am missing.....

If you use equal sign in the where clause it will do a exact match. The watch ID has to match '%something%'

You might want to use like instead or skip the percent signs.

"Update tblWatchInstance SET [NewInvoiceGen] = '1' Where tblWatchInstance.WatchID like '%" & txtWatchID.Text & "%'"

"Update tblWatchInstance SET [NewInvoiceGen] = '1' Where tblWatchInstance.WatchID = ' & txtWatchID.Text & '"

First statement might match 0 or more (even all) while last only matches 0 or 1

NEVER concatenate user input directly into a SQL statement!!!

You open up for SQL Injection attack.

Edit: Have a look at parameterized queries instead. It says ASP.NET but it work in any .NET app. I choose this as it was #1 when I search for a link.

http://aspnet101.com/aspnet101/tutorials.aspx?id=1

No comments:

Post a Comment