• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

Do SQL Insert or Update statements need to be closed in ASP

When you do an SQL INSERT or UPDATE from an ASP page, do you need to do a:

Record.Close()
Set Record = Nothing

In Dreamweaver it seems like it only closes the SELECT statements but I'm having a lot of open connections to SQL that never close.

Thanks.
Jon
0
Jon DeVito
Asked:
Jon DeVito
  • 6
  • 6
  • 2
  • +1
1 Solution
 
Paul JacksonCommented:
Eventually sql will time out idle connections but it is preferable that they are closed in the code so that they are release into the connection pool. The other reason your connections may be remaining open is that sql server is keeping the connections open for pooling.
0
 
Jon DeVitoAuthor Commented:
Thanks Jacko, if I have something like the attached code, how would I close it properly in the code?

Thanks.
Jon
Set dbConn=Server.CreateObject("ADODB.Connection")

dbConn.open "PROVIDER=SQLOLEDB;DATASOURCE=xxxxxxx;UID=xxxxxx;PWD=xxxxxxxxx;DATABASE=TEST;"

		SQL = "INSERT INTO dbo.Style(Active_Style, AL_Style, License, Description, Color, Fiber_Material, Vendor_Factory, Vendor_Reference, Minimum, First_Cost, Duty, HTS_Code, Weight, Notes, CPSIA, Social_Compliance, F_W) VALUES('"
																				
		SQL = SQL & Replace(Upload.Form("Active_Style"), "'", "''") & "', '"
		SQL = SQL & Replace(Upload.Form("AL_Style"), "'", "''") & "', '"
		SQL = SQL & Replace(Upload.Form("License"), "'", "''") & "', '"		
		SQL = SQL & Replace(Upload.Form("Description"), "'", "''") & "', '"
		SQL = SQL & Replace(Upload.Form("Color"), "'", "''") & "', '"
		SQL = SQL & Replace(Upload.Form("Fiber_Material"), "'", "''") & "', '"
		SQL = SQL & Replace(Upload.Form("Vendor_Factory"), "'", "''") & "', '"
		SQL = SQL & Replace(Upload.Form("Vendor_Reference"), "'", "''") & "', '"
		SQL = SQL & Replace(Upload.Form("Minimum"), "'", "''") & "', '"
		SQL = SQL & Replace(Upload.Form("First_Cost"), "'", "''") & "', '"
		SQL = SQL & Replace(Upload.Form("Duty"), "'", "''") & "', '"
		SQL = SQL & Replace(Upload.Form("HTS_Code"), "'", "''") & "', '"
		SQL = SQL & Replace(Upload.Form("Weight"), "'", "''") & "', '"
		SQL = SQL & Replace(Upload.Form("Notes"), "'", "''") & "', '"
		SQL = SQL & Replace(Upload.Form("CPSIA"), "'", "''") & "', '"
		SQL = SQL & Replace(Upload.Form("Social_Compliance"), "'", "''") & "', '"
		SQL = SQL & Replace(Upload.Form("F_W"), "'", "''") & "')"
		
		dbConn.Execute(SQL)

Open in new window

0
 
Wayne BarronCommented:
You only close a RecordSet, and to properly code a
Update, Delete, insert statement
You do not use Recordsets

Example
http://ee.cffcs.com/Q_24801116/Q_24801116.asp
Code
http://ee.cffcs.com/Q_24801116/Q_24801116.zip

Carrzkiss
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Paul JacksonCommented:
Just need to

dbConn.Close
Set dbConn = Nothing

for the above after the execute
0
 
Jon DeVitoAuthor Commented:
Carrzkiss, what your saying is that the Insert or Update auto closes after it finishes, but a RS needs to be closed after a select?
0
 
Wayne BarronCommented:
When you create a RecordSet, it needs to be closed.
When you do an Insert, update, delete statement like what is in the example
That I just provided, then there is no recordset to close.

Now, some of the old crap code that is online that REALLY needs to be removed, shows a RecordSet being created to do an insert, update statement, this is pour coding and resource wasteful.

As long as you code similar to what I have in the example, then
There is nothing to close, as it is a simple write TO the database
NOT a Read.

Just remember this

Read = rs.close
Write = nothing needs to be done.

And yes to Jacko
you will need to close your database connection at the end of your file.

I create a Footer.asp page, and that file catches ALL my database close.

Good Luck
Carrzkiss
0
 
swatidhumalCommented:
Update , Delete and Insert thes DML instructions automatically closes as database has automicity feature it either commits or rolls back the transcation so there is no need to close it.

But opened connection should be closed if u r opening it again within same application.
0
 
Jon DeVitoAuthor Commented:
Thanks for the clarification. The only thing that isn't clear is:

"you will need to close your database connection at the end of your file."

Are you still talking about rs.close for the read & do nothing for the write? Or do I need to do something even if I open the connection for the write?

I just want to be sure.

Thanks.
Jon
0
 
Wayne BarronCommented:
Your database connection is something like this

<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("Ee.mdb") & ";"
objConn.Open
%>
The above will open the connection for you.

<%
objConn.close
set objConn = nothing
%>
The above will close your connection for you.

Take Care
Carrzkiss
0
 
Wayne BarronCommented:
Also.
Please view my code that I provided in the link in my first comment.
That shows the opening and closing of Recordsets
And how to properly use the Insert, update and delete statements.
0
 
Jon DeVitoAuthor Commented:
Ok, I think I got it. I checked out your link & went through the files in the zip.

If there is a RS:
Open connection.
Create the RS.
Close the RS.
Close the connection.

If there is no RS (Update, Insert, Delete, etc)
Open Connection.
Insert, etc.
Close the connection.

Do I have it correct?

Thanks again for all of the help.
Jon
0
 
Wayne BarronCommented:
If there is a RS: (Select Statements)
Open (Database) connection.
Create the RS.
Close the RS.
Close the (Database) connection.


If there is no RS (Update, Insert, Delete)
Open (Database) Connection.
Update, Insert, Delete.
Close the (Database) connection.

That is it.
Carrziss
0
 
Jon DeVitoAuthor Commented:
Awesome, thanks as always for the help.
0
 
Wayne BarronCommented:
Your Welcome Jon.
If you have any programming issues, check out my Knowledge Base
http://kb.cffcs.com
Has 133 articles in it so far, and whenever I come across an issue
Or feel like writting something that I think someone will like
I will write the Article.

Also.
Just got finished updated my Companies Coding Source site
(Educational Tutorial site for source codes and projects for all sorts of categories, not only web development, but other area's as well.
Anything that deals with the computer, will be on the site)
www.cffcs.com
Nice site, hoping that it will grow into something really special within time.

Have a good one Jon
Carrzkiss
0
 
Jon DeVitoAuthor Commented:
Pretty awesome. I will definitely use this. Thanks again Carrzkiss, have a great one!

Jon
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now