Hi Ryan,
First, try to put all of your information in the same message, please!
Now, in your previous message, you said that when you "F10 over the
ExecuteScalar, it throws me to the Catch statement." This means that
SOMETHING being done in that line is throwing an exception, not necessarily
the SQL Statement, which in fact, since you tested it in Query Analyzer
(which you mentioned in the message previous to your previous message),
works. In fact, the SQL Statement that you originally said was causing the
problem will never be executed using the Try/Catch block, so I'm supposing
you recently added the Try/Catch block.
So, first, let me introduce you to the F11 (Step Into) key. This will trace
the execution thread into whatever method calls it visits (that have
debugging symbols) while executing a single line of code. Your erroneous
line of code reads:
intOrderID = objUtil.ToSQLValidInteger(comFields.ExecuteScalar())
But before we begin to analyze that, we need to take a look at the setup for
this. You have created a Connection using your objUtil instance. Since no
code from that class was posted, I can't guarantee that, while the
Connection was certainly created and opened (no exceptions thrown yet), I
don't know everything about the Connection. For example, a SQL Server
Connection string contains the user login information for the user account
(Windows or SQL Server) that is being connected. Therefore, it could be a
simple matter of the user account not having the necessary permissions to
perform the operation requested in the command.
Assuming that that is not the case, what else could it be? Well, you're
calling a method of the objUtil object that is probably expecting an
Integer. But what if the field is null? I can't assume that the "OrderId"
field in the table is an Identity field, a Primary Key field, or any kind of
field that requires a value to be in it. IOW, it could return a null value.
If so, null (Nothing) is not an Integer, and that could throw an exception.
Hence, my introduction to the F11 key, which would further narrow down where
the exception occurred.
Now, on to another debugging tip. It's no wonder you consider a Try/Catch
block to be "evil" (mentioned in your first message). You're not doing
anything useful with it. For example, one excellent use of a Catch block is
to log the Exception details. The Exception details would probably have
given you the information you seek. At the very least, you should have put a
break point in that Catch block, so you could do a Quick Watch and see the
Exception details for yourself.
Also, in the same "misuse of Try/Catch" department, your Catch block is
setting the return value of your function, but note that when an Exception
is handled, execution continues. This means that the last line of code in
the function,
AddNewFieldToType = true
IS executed, RE-setting the return value of the function to true, and
thereby defeating the purpose of the line of code in the Catch block that
sets the return value to false. IOW, your function will ALWAYS return true,
unless an unhandled exception occurs OUTSIDE of the Try/Catch block.
Finally, I'm not sure what made you think that your database was being
inserted into 3 times. In the code you posted, the database would not have
ANY records inserted into it, as the exception occurs while SELECTING a
record, at which point the rest of the code in that block is NOT executed,
but execution skips right down to the Catch block. I suspect that since the
function always returns true, you may be getting the wrong impression from
the return value. But that is pure speculation.