Hi There! I'm having a hard time

[superdog4492]

Hi There!

I'm having a hard time with my ASP connection to SQL Server 6.5. I'm looping through a recordset, and each iteration sets and frees a couple of other recordsets. (Code below.)

I think I'm being really good about creating as late as possible and freeing as soon as possible, but I'm still getting this error sometimes:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect. The maximum number of '25' configured user connections are already connected. System Administrator can configure to a higher value with sp_configure.

I only get this code when the Answers recordset gets too big (but I haven't been able to identify any threshold -- it's way lower that 25!

Can anyone see what I'm doing wrong in my code? I've heard connection.execute can be a little unruly - is that it?

Thanks in advance for your help!
Dave

'connect to database
Dim objConn
set objConn = server.CreateObject("ADODB.Connection&quot
objConn.Open "Driver={SQL Server};Server=MyServer;Uid=SA;Pwd=;Database=myDatabase"

'SAVE ANSWERS
QUESTION = "SELECT question.question_id, inputType "
QUESTION = QUESTION & "FROM question"
QUESTION = QUESTION & " WHERE question.subProcess_id = " & request.form("SelectedSubProcess&quot

set Answers=objConn.execute(QUESTION)

do while not Answers.eof

AnswerText = request.form(Answers(0))

EACH_ANSWER = "UPDATE Answer_" & Session("EnteredAlias&quot & " SET submitted_on = '" & FormatDateTime(Date) & "'"
EACH_ANSWER = EACH_ANSWER & ", submitted_by = '" & Session("UserName&quot & "'"
EACH_ANSWER = EACH_ANSWER & ", answerText = '" & AnswerText & "'"
EACH_ANSWER = EACH_ANSWER & " WHERE question_ID = " & Answers(0)

set UpdateAnswers=objConn.execute(EACH_ANSWER)
set UpdateAnswers = nothing

Answers.movenext
loop
Answers.close

' clean up
set Answers = nothing
objConn.close
set objConn = nothing

 

[mwolf00]

"set UpdateAnswers=objConn.execute(EACH_ANSWER)
set UpdateAnswers = nothing"

isn't necessary - just use:
objConn.execute(EACH_ANSWER)

I believe that you are only opening and using one connection in the code, are there others using the same server? -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[superdog4492]

Hey mwolf00,

Thanks for the response. I am the only one using this server -- wacky, eh? I'm not sure what the problem is.

The only workaround I've found is to create a second connection that the re-iterating recordset uses. This solution tolerates a slightly larger Answers recordset, but not by much. (And besides, one connection *should* be able to handle quite a few recordsets!)

Sheesh!

superdog4492
p.s.Thanks for the tip on the Update execution!

 

[mwolf00]

I don't know how big your answers recordset is, but it seems that it is large enough that you should write a stored procedure to do the select and update in one client/server interaction.... No sense in having the db return things to the asp server just so the asp server can send 'em back to the db server.... I'll look at it more and get an answer to you... -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[mwolf00]

You should be able to execute this string once and have the database do all of the work....

sqlStr = &quot;UPDATE Answer_&quot; & Session(&quot;EnteredAlias&quot & &quot; SET submitted_on = '&quot; & FormatDateTime(Date) & &quot;'&quot; &_
&quot;, submitted_by = '&quot; & Session(&quot;UserName&quot & &quot;'&quot; &_
&quot;, answerText = '&quot; & AnswerText & &quot;'&quot; &_
&quot; WHERE question_ID IN (&quot;&_
&quot;SELECT question.question_id, inputType FROM question &quot;&_
&quot;WHERE question.subProcess_id = &quot; & request.form(&quot;SelectedSubProcess&quot &_
&quot&quot; -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[superdog4492]

That's fantastic!

See, I'm new to SQL Server (and on the beginner side of ASP). I've just upsized this from Access to SQL Server, and I've been wondering which executions would be prime candidates for stored procedures -- sounds like this is one!

Time to learn about stored procedures What's a good resource? (I better put on a pot of coffee...)

Thanks again for all the info.
D

 

[mwolf00]

You don't need a stored procedure. You can use your current code with my SQL string!

'connect to database
Dim objConn
set objConn = server.CreateObject(&quot;ADODB.Connection&quot
objConn.Open &quot;Driver={SQL Server};Server=MyServer;Uid=SA;Pwd=;Database=myDatabase&quot;

AnswerText = request.form(Answers(0))
sqlStr = &quot;UPDATE Answer_&quot; & Session(&quot;EnteredAlias&quot & &quot; SET submitted_on = '&quot; & FormatDateTime(Date) & &quot;'&quot; &_
&quot;, submitted_by = '&quot; & Session(&quot;UserName&quot & &quot;'&quot; &_
&quot;, answerText = '&quot; & AnswerText & &quot;'&quot; &_
&quot; WHERE question_ID IN (&quot;&_
&quot;SELECT question.question_id, inputType FROM question &quot;&_
&quot;WHERE question.subProcess_id = &quot; & request.form(&quot;SelectedSubProcess&quot &_
&quot&quot;
objConn.execute(sqlStr)
' clean up
objConn.close
set objConn = nothing -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com