By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,742 Members | 1,044 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,742 IT Pros & Developers. It's quick & easy.

Updating a DB via SQL is giving me a headache

P: n/a
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?
Nov 19 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
What You Seek Is What You Get.

"Ryan Ternier" <rt******@icompasstech.com> wrote in message
news:uS**************@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?

Nov 19 '05 #2

P: n/a
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.

That's what I thought as well... however, when I F10 over the
ExecuteScalar, it throws me to the Catch statement... That's where my
confusion comes from.
Nov 19 '05 #3

P: n/a
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
Nov 19 '05 #4

P: n/a
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

Nov 19 '05 #5

P: n/a
Kevin Spencer wrote:
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.

Aw common Kevin, giving me a hard time haha.

I through the Try..Catch block there to see why it was erroring. The
Code calling that function had it's own try catch, however you are right
about returning true through the function even though it failed.
I never had an error with:

intOrderID = objUtil.ToSQLValidInteger(comFields.ExecuteScalar( ))
It was the First insert (2nd query ran). After I posted we did more
testing. On others development platforms and my Laptop, the program
worked as expected. Only on my development machine was it inserting (or
trying to) 3 times.

If I changed the table around I could see the 3 inserts. It's fixed now,
I went home, and came back today and it's working fine (I didn't even
change anything :( ) but man it gave me a headache trying to figure out
why it was doing that.

I've considered using the Error Logs within windows, but with over 200
client websites using this peice of software and all of them QueryString
manipulation happy, it would give us some fun.

We've tweaked the exception classes so it sends emails without us having
to call it. If we are expecting an error, we just turn it off before
hand, then re-initiate it.
Thanks for the help though man :D
/RT
Nov 19 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.