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.
--
HTH,
Kevin Spencer
Microsoft MVP
..Net Developer
What You Seek Is What You Get.
Now, accoriding to the code you posted, objUtil is a custom class you
created, or at least used in this function.
"Ryan Ternier" <rt******@icompasstech.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...
Kevin Spencer wrote: If it's updating the database 3 times, it's not the SQL Statement. It's
something in your code that is executing the statement 3 times.
Here is the entire Function that is called:
Public Function AddNewFieldToType(ByVal intTypeID As Integer, ByVal
intFieldID As Integer) As Boolean
'When we add a field we must also update ALL Items of the
current Item Type in tblItemRecords with the change
'This means we have to add a BLANK field to the DB for this
field.
Dim strSQL As String
Dim comFields As New SqlClient.SqlCommand
Dim astrTemp As String()
Dim objUtil As New Utility
Dim intOrderID As Integer
comFields.Connection = objUtil.GetConnection()
comFields.Connection.Open()
Try
'Get the OrderID
strSQL = "SELECT MAX(OrderID) FROM tblFieldLayouts WHERE
TypeID = " & intTypeID
comFields.CommandText = strSQL
intOrderID =
objUtil.ToSQLValidInteger(comFields.ExecuteScalar( ))
strSQL = "INSERT INTO tblFieldLayouts(TypeID, FieldID,
OrderID, Hidden) VALUES(" & intTypeID & ", " & intFieldID & ", " &
intOrderID & ",0)"
comFields.CommandText = strSQL
comFields.ExecuteNonQuery()
strSQL = "INSERT INTO tblItemRecords(ItemID, FIeldID,
FieldValue) VALUES(" & intTypeID & ", " & intFieldID & ",'')"
comFields.CommandText = strSQL
comFields.ExecuteNonQuery()
Catch ex As Exception
AddNewFieldToType = False
Finally
comFields.Connection.Close()
comFields = Nothing
objUtil = Nothing
End Try
AddNewFieldToType = trueEnd Function
