473,574 Members | 2,555 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Updating a DB via SQL is giving me a headache

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.Comma ndText = strSQL
comFields.Execu teNonQuery()
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
5 1636
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******@icomp asstech.com> wrote in message
news:uS******** ******@TK2MSFTN GP09.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.Comma ndText = strSQL
comFields.Execu teNonQuery()
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
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
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 AddNewFieldToTy pe(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.SqlCo mmand
Dim astrTemp As String()
Dim objUtil As New Utility
Dim intOrderID As Integer

comFields.Conne ction = objUtil.GetConn ection()
comFields.Conne ction.Open()
Try
'Get the OrderID
strSQL = "SELECT MAX(OrderID) FROM tblFieldLayouts
WHERE TypeID = " & intTypeID
comFields.Comma ndText = strSQL
intOrderID =
objUtil.ToSQLVa lidInteger(comF ields.ExecuteSc alar())

strSQL = "INSERT INTO tblFieldLayouts (TypeID,
FieldID, OrderID, Hidden) VALUES(" & intTypeID & ", " & intFieldID & ",
" & intOrderID & ",0)"
comFields.Comma ndText = strSQL
comFields.Execu teNonQuery()

strSQL = "INSERT INTO tblItemRecords( ItemID,
FIeldID, FieldValue) VALUES(" & intTypeID & ", " & intFieldID & ",'')"
comFields.Comma ndText = strSQL
comFields.Execu teNonQuery()

Catch ex As Exception
AddNewFieldToTy pe = False
Finally
comFields.Conne ction.Close()
comFields = Nothing
objUtil = Nothing
End Try
AddNewFieldToTy pe = trueEnd Function
Nov 19 '05 #4
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.ToSQLVa lidInteger(comF ields.ExecuteSc alar())

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,

AddNewFieldToTy pe = 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******@icomp asstech.com> wrote in message
news:%2******** ********@tk2msf tngp13.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 AddNewFieldToTy pe(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.SqlCo mmand
Dim astrTemp As String()
Dim objUtil As New Utility
Dim intOrderID As Integer

comFields.Conne ction = objUtil.GetConn ection()
comFields.Conne ction.Open()
Try
'Get the OrderID
strSQL = "SELECT MAX(OrderID) FROM tblFieldLayouts WHERE
TypeID = " & intTypeID
comFields.Comma ndText = strSQL
intOrderID =
objUtil.ToSQLVa lidInteger(comF ields.ExecuteSc alar())

strSQL = "INSERT INTO tblFieldLayouts (TypeID, FieldID,
OrderID, Hidden) VALUES(" & intTypeID & ", " & intFieldID & ", " &
intOrderID & ",0)"
comFields.Comma ndText = strSQL
comFields.Execu teNonQuery()

strSQL = "INSERT INTO tblItemRecords( ItemID, FIeldID,
FieldValue) VALUES(" & intTypeID & ", " & intFieldID & ",'')"
comFields.Comma ndText = strSQL
comFields.Execu teNonQuery()

Catch ex As Exception
AddNewFieldToTy pe = False
Finally
comFields.Conne ction.Close()
comFields = Nothing
objUtil = Nothing
End Try
AddNewFieldToTy pe = trueEnd Function

Nov 19 '05 #5
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.ToSQLVa lidInteger(comF ields.ExecuteSc alar())

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,

AddNewFieldToTy pe = 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.ToSQLVa lidInteger(comF ields.ExecuteSc alar())
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2463
by: J P Singh | last post by:
Hi All I am trying to query a database with a combination of surname and date of birth but it is giving me wrong results in certain conditions. It is the mm/dd/yyyy and dd/mm/yyyy stuff that is not making it work. If I enter date like 25/12/1976 then it works fine as the date will not be valid like 12/25/1976 and everything works fine...
2
3927
by: Fran Tirimo | last post by:
I am developing a small website using ASP scripts to format data retrieved from an Access database. It will run on a Windows 2003 server supporting FrontPage extensions 2002 hosted by the company 1&1 with only limited server configuration via a web based control panel. My query relates to the ASP security model and how it relates to...
0
2601
by: David | last post by:
On every web browser except Safari, this website works great. (Well, by "every" I mean Mozilla, Netscape, and Internet Explorer, for Mac and Windows). The site is: http://www.ruleofthirds.com (the problem does not exist on the first page... just all the other pages, so you'll have to click on something to see the problem) The problem...
1
2590
by: Gerry Abbott | last post by:
Hi all, I've got two subforms on an unbound form, frmMain, frmSubOne, frmSubTwo. Ive got a control on frmSubTwo, cboList, a list box, which draws its source from the table underlying frmSubOne. I want this list to be kept automatically up to date, for selection, so after updating frmSubOne, the new records will be visible in the...
4
2005
by: Darrel | last post by:
I'm creating a table that contains multiple records pulled out of the database. I'm building the table myself and passing it to the page since the table needs to be fairly customized (ie, a datagrid isn't going to work). On this page, people can update a variet of records. On submit, I want to then go in and update all of the records. ...
2
1252
by: Kejpa | last post by:
Hi, I've got a number of objects that each have it's own thread. When the value changes I raise an event. Now, I want to handle the event in a form and show the value in a listview. With my first approach I had to have a synclock on the listview the whole event handler, after some reading I've understood that updating a control from...
22
6401
by: Mal Ball | last post by:
I hope I have the right forum for this question. I have an existing Windows application which uses a SQL Server database and stored procedures. I am now developing a web application to use the same database. The original Update and Delete SP's all use a timestamp for concurreny checking. I am trying to use the same Update SP from my...
10
2224
by: chimambo | last post by:
Hi All, I have a little problem. I am retrieving records from a table and I want to update the records using checkboxes. I am able to display the database record quite alright and I have created an array of checkboxes, but my update loop is not working. Here is my code: /*---------This retrieves the records -------------*/ if($row_md) {...
5
5336
by: rosaryshop | last post by:
I'm working a jewelry/rosary design web site at http://www.rosaryshop.com/rosariesAndKits2.php. As the user makes selections, it updates images of various parts, giving them a preview of the finished item. The preview resides within a div and is simply a series of <br>-separated images. Simple html. The system is working fine with FireFox and...
0
7807
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
1
7820
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8100
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6456
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5624
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3742
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2249
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1344
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1061
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.