"Ryan Ternier" <rtern***@icompasstech.com> wrote in message
news:uSrZUUaVFHA.2684@TK2MSFTNGP09.phx.gbl...
> I'm having an issue with an SQL insert statement. It's a very simple
> statement, and it's causing too much fuss.
>
>
> strSQL = "INSERT INTO tblFieldLayouts(TypeID, FieldID, OrderID, Hidden)
> VALUES(" & intTypeID & ", " & intFieldID & ", " & intOrderID & ",0)"
> comFields.CommandText = strSQL
> comFields.ExecuteNonQuery()
>
>
> Pretty simple.
>
> Running this query in Query Analyzer will work perfect.
>
> However, when I run this while debugging my ASP.NET project, it updates
> the DB 3 times.
>
> There are no loops, no evil goto's etc. It's just a simple 3 update that
> is in the middle of a function.
>
>
> Anyone have any ideas before I just put a try..Catch [evil] around it then
> continue on?

"Ryan Ternier" <rtern***@icompasstech.com> wrote in message
news:%23$ozFgaVFHA.1400@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

> 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.
>