cmd.Execute twice? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

cmd.Execute twice?

Author

Topic

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-28 : 14:14:48

Hey gals and guys. I've got a quick question I'd like answered b4 I go one programming. There are seveal sprocs that i have That return a number of records based on a WHERE clause, and at times I may get 0 records. Currently I have every sproc returning the recordset along with a return value of the number of rows returned, since I need this info for some code and not for others.

Currently here is how I access the sprocs thru ASP classic:

		Set cmd = Server.CreateObject("ADODB.Command")
		With cmd
			.ActiveConnection = dbc
			.CommandText = "proc_ShowModel"
			.CommandType = adCmdStoredProc
		End With
			
		Set rs = Server.CreateObject("ADODB.Recordset")
		Set rs = cmd.Execute
		
		If NOT rs.BOF AND NOT rs.EOF Then
			
			Do While NOT rs.EOF
				fncID = rs("ModelID")
				fncModel = rs("Model")
				fncMake = rs("Make")
				
				If fncAttrID = fncID Then
					fncText = fncText & "<option value=" & fncID & " selected>" & fncMake & " " & fncModel & "</option>" & vbCrLf
				Else
					fncText = fncText & "<option value=" & fncID & ">" & fncMake & " " & fncModel & "</option>" & vbCrLf
				End If
				rs.MoveNext
			Loop
			
		Else
			
			fncText = fncText & "<option value=0 selected>-- No Models --</option>" & vbCrLf
			
		End If

My question is refers to the 'If NOT rs.BOF AND NOT rs.EOF Then' line. Since I do return the number of rows, is it an easier process to check for the value to be greater than 0 before creating the recordset object as far as overhead is concerned? I was thinking something like this:

		Set cmd = Server.CreateObject("ADODB.Command")
		With cmd
			.ActiveConnection = dbc
			.CommandText = "proc_ShowModel"
			.CommandType = adCmdStoredProc
			
			.Parameters.Append .CreateParameter("@RetVal", adInteger, adParamReturnValue, 4)
			
			.Execute
		End With
		
		If cmd("@RetVal") = 0 Then
			Set rs = Server.CreateObject("ADODB.Recordset")
			Set rs = cmd.Execute
			
			Do While NOT rs.EOF
				fncID = rs("ModelID")
				fncModel = rs("Model")
				fncMake = rs("Make")
				
				If fncAttrID = fncID Then
					fncText = fncText & "<option value=" & fncID & " selected>" & fncMake & " " & fncModel & "</option>" & vbCrLf
				Else
					fncText = fncText & "<option value=" & fncID & ">" & fncMake & " " & fncModel & "</option>" & vbCrLf
				End If
				rs.MoveNext
			Loop
			
		Else
			
			fncText = fncText & "<option value=0 selected>-- No Models --</option>" & vbCrLf
			
		End If

I'm thinking this is not good as the .Execute has to access the SQL twice, but the web server and sql server reside in the same facility. Well, since I don't have a definate answer, i decided to post it here to see if I can get some feedback. Thanks to all. Any questions, just ask.

- RoLY roLLs

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-28 : 15:00:59

I would go with your first.
The second calls the SP twice and I suspect this will have a big overhead on the database. Also round trips take a lot of effort and you will also be holding the connection for longer than necessary when you do get results.

Must admit I'm a bit surprised the second one works but I've never tried an execute to get parameters and discarded the resultset.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-28 : 15:11:28

No? How do you do it then? I'm willing to change my ways for the better. let me know.. Thanks

- RoLY roLLs

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-28 : 15:17:43

Like your first way - except that I have an inc file for database access to stop people holding connections and so I can add tracing easily.
http://www.nigelrivett.net/DBAccess.inc.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-28 : 18:00:45

Nice method(s). I been meaning to write a three universal functions. One to add, one to update and one to delete data, by calling the function and passing info over. But I can across a problem I did not like, WHat I would so to populate the params was list them in an array and then pass the array to the function but it came out to be a lil ugly, so I aborted that. But I like ur method WAY better by setting up the object and it's params and calling one function to do the adding, I have a page with 12000 lines of code to add data. One function per sprocs that adds data. Yeah i know, I'm such a noob. Actually, I learned one way and have been doing it one way and slowly improving upon it as I go. As I used to have the adding in each individual page, and that made code nasty. If you have other places or have your own code which do stuff like that, I'd greatly appreciate it. I hope you wouldn't mind me borrowing them and/or improving them for my use. Thanks.

- RoLY roLLs

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-29 : 05:11:59

For VB I have built classes which receive a collection of parameters (not ado) and the class turns them into ado parameters for the call and returns a collection of rows.
This was when MS moved from odbc - rdo - ado and I thought they were going to keep going so wanted something flexible.
ADO's been around for a while so I usually don't bother encapsulating it to that extent. I do implement a dal to stop developers holding on to connections and to force client side cursors though. For a client server system I do the same but make the connection global.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-29 : 11:16:11

Well I don't know if what I do is what you say you do as far as stop users/developers from holding connections. At the top of every page I call a function, similar to yours, that opens the connection. While at the bottom I have one that closes the coonection, like so:

Call OpenConn()
(code and everything)
Call CloseConn()

Then I have this include file along with each page with the following code:

	Sub OpenConn()
		strConn = "DSN=DBDNS;User Id=USERNAME;Password=USERPASSWORD;Initial Catalog=DBNAME;"
		
		Set dbc = Server.CreateObject("ADODB.Connection")
		dbc.open strConn
	End Sub
	
	Sub CloseConn()
		If IsObject(dbc) Then
			If dbc.State = adStateOpen Then
				dbc.Close
			End If
			Set dbc = Nothing
		End If
	End Sub

Does this seem good? Is yours better? Anyone out there have other methods?

- RoLY roLLs

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-30 : 01:47:55

If you are holding the connection for the page then you are holding it longer than necessary and potentially using more connections than necessary and possibly holding locks or resources on the database server.
It is best to open the connection when you need to access the database, disconnect the resultset and close the connection then process the resultset.
Connection pooling will ensure that connections are reused.
That's why I have this inc file that opens, executes, closes and returns a disconnected resultset.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-30 : 09:59:22

Ok sounds good. Thanks nr!

- RoLY roLLs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources