Connection Pool Error

Hello,
I'm having a recurring error whenever I try to debug a
asp.net web page is retrieving data from a SQL server.
The error msg is:
"Timeout expired. The timeout period elapsed prior to
obtaining a connection from the pool. This may have
occurred because all pooled connections were in use and
max pool size was reached."
Any ideas?There were some connection pooling problems in the earlier versions of .NET.
Have you applied the .NET service packs to your boxes?
--Brian
(Please reply to the newsgroups only.)
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:041501c3d7ba$5694ff40$a001280a@.phx.gbl...

quote:

---

> Hello,
> I'm having a recurring error whenever I try to debug a
> asp.net web page is retrieving data from a SQL server.
> The error msg is:
> "Timeout expired. The timeout period elapsed prior to
> obtaining a connection from the pool. This may have
> occurred because all pooled connections were in use and
> max pool size was reached."
> Any ideas?
>

---

|||Brian (anonymous@.discussions.microsoft.com) writes:

quote:

---

> I'm having a recurring error whenever I try to debug a
> asp.net web page is retrieving data from a SQL server.
> The error msg is:
> "Timeout expired. The timeout period elapsed prior to
> obtaining a connection from the pool. This may have
> occurred because all pooled connections were in use and
> max pool size was reached."

---

ADO.Net is not my best game, but I know that there are pitfalls, so
if you are not careful, you will fill up your connection pool. One
way to diagnose this is to run sp_who from Query Analyzer, and see
how many connections you have open. If you think you have none,
but there are hundred you know that 1) you have filled up the pool
2) you have a leak.
Explicitly closing your connections is probably a good idea.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Connection Mystery

I am having trouble connecting to an SQL Server from an ASP.NET pagewritten in C#. I have reduced the code in my page to this:
public void Page_Load( object s, EventArgs e ) {

SqlConnection conn = new SqlConnection( "Server=server;User ID=user;Password=pass;" );
conn.Open();

}
and I get the following error:

System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.

Thisworks for other server/user/pass except the one I am having troublewith. The SQL Server is using SQL & Windows Authentication. I amable to connect from the machine that is running the webserver viaosql, ODBC, and MS Access using the same arguments (server, user, andpass), so I know that the SQL server does exist, and the User/Passwordshould give me access.
Does anyone have any ideas why thisisn't working or what ASP.NET is does differently from the other accessmethods? Any ideas are greatly appreciated.
Thanks,
Mark Daneyou havent specified what database to connect to..check out thiswebsite for connection strings|||I have tried it with and without using a database name in theconnection string. It does not work either way.
I have tried a variety of connection strings options but none of thecombonations seem to work. However they work connecting to adifferent SQL Server, which makes me think that it is something withthis specific SQL Server. However I am able to connect to theserver through osql, ODBC, or MS Access all without a problem, so Idon't belive it is a problem with the SQL Server accepting the login.Since it doesn't seem to be a problem with the connection stringattributes, or the server, I am not sure what could be causing it.
|||are you sure you have the values properly like the server name, database name uid, pwd all correct ? and can you connect to the sql server from query analyzer using the same credentials ?|||

Try this url for the database permissions setup, if no money transactions passes through the site. Hope this helps.

http://duhnetnuke.net/DesktopDefault.aspx?tabid=40

Kind regards,

Gift Peddie

|||

The info in this link uses the Master as the default database for your Asp.net account permissions. If you are still having problems with that, then it could be a bug. Hope this helps.

http://duhnetnuke2.net/Default.aspx?tabid=64

|||I found out what the problem was. The SQL Server I was trying toconnect to didn't have the latest service pack installed. Iupgraded the SQL Server to SP3a and now it works fine.

Connection Issues

I didn't know where else to put this. I'm only getting this error on one page of my application.

I'm running sql server2000 and 2005 on the same server. The db is sqlserver 2000.

It's driving me nuts...

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

any help is always welcomed...

On the server concerned, you need to get your DBA to go:

StartAll ProgramsMicrosoft SQl Server 2005Configuration ToolsSql Server Surface Area ConfigurationSurface Area Configuration for Services and ConnectionsSelect server instance (if more than one)Select Database EngineSelect Remote Connections nodeSelect the Local And Remote ConnectionsClick Apply
|||

On you server got to:

Start > Programs>SQL Server 2005>Configuration Tools>SQL Server Configuration Manager

then expend the SQL Server 2005 Network Configuration node and it will show all the instance of sql server runing on this machine.

Select your desired instance and make sure that Shared Memory and TCP/IP protocols would be enabled.

|||

why would this connection issue start all of a sudden?

this code has been out for over a month? And why would I be able access the the db a good half dozen times prior to this connection issue?

kg

|||

Somebody might have changed your server configurations :)

|||

And why would I be able access the the db a good half dozen times prior to this connection issue

|||

Below is my last select statement beofre the connection issue. It goes thru all of the code and populates the values. It seems like this is happening when the postback is happening.

kg

Dim connectionStringAsString = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString

Dim conSQLAsNew SqlConnection(connectionString)

Dim sqlStrAsString ="select transition_type, transitioner, convert(char(8),transition_date,112) as transition_date, " _

&" agreement_type_code, agreement_status_code, sandt_effort, sandt_code, sort_order, code as ttaCode " _

&" from transition_gp " _

&" where program_code = @.programcode and gap_code = @.gapCode " _

&" order by sort_order "

Dim cmdSqlAs SqlCommand =New SqlCommand(sqlStr, conSQL)

cmdSql.Parameters.AddWithValue("@.programCode", programCode)

cmdSql.Parameters.AddWithValue("@.gapCode", gapCode)

conSQL.Open()

Dim readerAs SqlDataReader = cmdSql.ExecuteReader()

DoWhile reader.Read()

transitionType = reader("transition_type").ToString

transitionDate = reader("transition_date").ToString

transitioner = reader("transitioner").ToString

agreementTypeCode = reader("agreement_type_code").ToString

agreementStatusCode = reader("agreement_status_code").ToString

santEffort = reader("sandt_effort").ToString

sandtCode = reader("sandt_code").ToString

maxSort =CInt(reader("sort_order").ToString)

ttaCode = reader("ttaCode").ToString

Loop

reader.Close

cmdSql.Dispose()

conSQL.Close()

Connection from ASP page to SQL Server Express

Hi!

I have two servers, the web (with IIS - win2003) and the test data
server (winXPpro with SQL Express)
I'm trying to connect to the db but with no success...
here is the connection string I'm using:
sConn = "Driver=SQLNCLI;
Server=marco-server\SQLEXPRESS;
Database=grimp;
Uid=grimpuser;
Pwd=somePassword;"

I get this:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified

I've tried also with Driver={SQL Native Client} but I get the same
error.

do I have to install something on the web server?(ma.giorgi@.gmail.com) writes:

Quote:

Originally Posted by

I have two servers, the web (with IIS - win2003) and the test data
server (winXPpro with SQL Express)
I'm trying to connect to the db but with no success...
here is the connection string I'm using:
sConn = "Driver=SQLNCLI;
Server=marco-server\SQLEXPRESS;
Database=grimp;
Uid=grimpuser;
Pwd=somePassword;"
>
I get this:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
>
I've tried also with Driver={SQL Native Client} but I get the same
error.

Try using "Provider", rather than "Driver".

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

connection error

Hi guys, my asp.net web page was working fine on my laptop. Until i transferred it into my friend's.. an error msg popped out. My SQL Northwind database was been copied over but still there seems to be a problem with the connection, can any one help me?
Personal guess: You are using an sa username/password in the connection string, and the other server you are connecting to is not set up for SQL security, only Windows security.
Show the connection string you are using (with any passwords obscured, of course).
|||

SqlConnection conn=new SqlConnection("server=localhost;uid=sa;pwd=;database=Northwind");

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "SELECT * FROM tblDoor";

conn.Open();
//how to find out what user id to use?

|||Do you have SQL Server or MSDE? If SQL Server, then you should be able to use Enterprise Manager to try and figure out what the SA password is, as well as determining whether SQL Server security is enabled. If you have Enterprise manager, right click the SQL Server and select Porperties. In the properties window, go to the Security tab make sure authentication is set to SQL Server and Windows.|||oh it is working already, thanks man!

Connection Error

I have an asp page that opens up a crystal report...however; randomly
I either get the report or I get this error:

ADO Error Code: 0x80004005 Source: Microsoft OLE DB Provider for SQL
Server Description: Connection failure SQL State: 08S01

and its happening alot...

Where should I begin looking to solve this..I dont see anything
particular in the event log in both client or server...

thanks

-JimJim (jim.ferris@.motorola.com) writes:
> I have an asp page that opens up a crystal report...however; randomly
> I either get the report or I get this error:
> ADO Error Code: 0x80004005 Source: Microsoft OLE DB Provider for SQL
> Server Description: Connection failure SQL State: 08S01
> and its happening alot...
> Where should I begin looking to solve this..I dont see anything
> particular in the event log in both client or server...

08S01 - Communication Link Failure. Often indicates that some accident
happened on the SQL Server, which forced SQL Server to sever the
connection. Usually accompanied by stack dumps in the SQL Server error log.
(That is not the eventlog.) Such crashes are due to bugs in SQL Server, and
should not occur in an ideal world.

But a flaky network could also be the cause.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Connecting to sql2005 using C#

Help... I am new to C# and .net and I am trying to build a insert page with a couple of drop down controls where I pull a categoryID and subcategoryID to populate my dropdown controls from a MS sql2005 express database. I am using a book that only shows how to build the script and access a access database and I am getting this error when trying to pull up the page:

Description:An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message:CS0246: The type or namespace name 'OleDbConnection' could not be found (are you missing a using directive or an assembly reference?)

Source Error:

Line 3: Line 4: <script runat="server" language="C#">Line 5: OleDbConnection objConn = newOleDbConnection(Line 6: "Server=SIMBA\\NETSDK;" +Line 7: "Database=btuniverse;" +

Source File: c:\Inetpub\wwwroot\_addnews.aspx Line: 5

Below is my code and I am not sure what the syntax needs to be when connecting to a ms sql datasource. Thanks in advanced :)

MYCODE:

<%@. Page Language="C#" MasterPageFile="~/main.master" Title="Untitled Page" %>

<script runat="server" language="C#">
OleDbConnection objConn = newOleDbConnection(
"Server=SIMBA\\NETSDK;" +
"Database=btuniverse;" +
"User ID=sa;Password=password");
OleDbCommand objCmd;
OleDbDataReader objRdr;

void Page_Load() {
if (!IsPostBack) {
objConn.Open();

objCmd = new OleDbCommand("SELECT * FROM dbo.tblNewsCategories", objConn);
objRdr = objCmd.ExecuteReader();
ddlCategory.DataSource = objRdr;
ddlCategory.DataValueField = "CategoryID";
ddlCategory.DataTextField = "CategoryName";
ddlCategory.DataBind();
objRdr.Close();

objCmd = new OleDbCommand("SELECT * FROM dbo.tblSubCategories", objConn);
objRdr = objCmd.Executereader();
ddlSubCategory.DataSource = objRdr;
ddlSubCategory.DataValueField = "SubCategoryID";
ddlSubCategory.DataTextField = "SubCategoryName";
ddlSubCategory.DataBind();
objRdr.Close();

objConn.Close();
}
}

</script>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

<table>
<tr>
<td align="right">Title:</td>
<td align="left"><asp:TextBox ID="txtArticleTitle" CssClass="textbox" runat="server" />
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" ControlToValidate="txtArticleTitle" ErrorMessage="*" runat="server" />
<br />
</td>

<!-- <asp:CompareValidator ID="cvArticleTitle" ControlToValidate="txtArticleTitle" Operator="DataTypeCheck" Type="String" ErrorMessage="No numbers allowed" />-->
</tr>
<tr>
<td align="right"><p>Article Category:</td><td align="left"><asp:DropDownList ID="ddlCategory" CssClass="dropdownmenu" runat="server" /></td>
</tr>
<tr>
<td align="right"><p>Article Sub Category:</td><td align="left"><asp:DropDownList ID="ddlSubCategory" runat="server" /></td>
</tr>
<tr><td align="right">News Article:</td><td align="left"><asp:TextBox ID="txtArticleDesc" CssClass="textbox" Columns="40" Rows="4" TextMode="MultiLine" runat="server" />
<asp:RequiredFieldValidator ID="rfvArticleDesc" ControlToValidate="txtArticleDesc" ErrorMessage="*" runat="server" />
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
</table>

<asp:Button ID="btnSubmit" CssClass="button" runat="server" Text="Submit" />
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder2" Runat="Server">
</asp:Content>

Use SQLDBConnection.
Why dont you check out the tutorials on these forums. They have good explanation with sample code too.|||thank you.

Now that I am able to query certail tables and create drop down menus i am getting an error when trying to insert my record:

Exception Details:System.Data.OleDb.OleDbException: Must declare the scalar variable "@.NewsID".

Source Error:

Line 50: objCmd.Parameters.Add("@.ArticleLink", txtArticleLink.Text);
Line 51: objConn.Open();
Line 52: objCmd.ExecuteNonQuery();
Line 53: objConn.Close();
Line 54: Response.Redirect("mynews.aspx");

HERE IS M CODE:
void SubmitNewsArticle(Object s, EventArgs e)
{
objCmd = new System.Data.OleDb.OleDbCommand(
"INSERT INTO tblNews (NewsID, ArticleDate, Title, " +
"CategoryID, SubCategoryID, ArticleDesc, ArticleLink) " +
"VALUES (@.NewsID, @.Title, @.CategoryID, " +
"@.SubCategoryID, @.ArticleDesc, @.ArticleLink)", objConn);
objCmd.Parameters.Add("@.NewsID", 1);
objCmd.Parameters.Add("@.ArticleDate", txtArticleDate.Text);
objCmd.Parameters.Add("@.Title", txtArticleTitle.Text);
objCmd.Parameters.Add("@.CategoryID",
ddlCategory.SelectedItem.Value);
objCmd.Parameters.Add("@.SubCategoryID",
ddlSubCategory.SelectedItem.Value);
objCmd.Parameters.Add("@.ArticleDesc", txtArticleDesc.Text);
objCmd.Parameters.Add("@.ArticleLink", txtArticleLink.Text);
objConn.Open();
objCmd.ExecuteNonQuery();
objConn.Close();
Response.Redirect("mynews.aspx");
}

what am I missing?? sorry for the posts :( so new to C# and .net -- I am a coldfusion web developer (1 year exp)

thanks again in advanced|||looks like you are still using OLEDB connection. What is your backend? SQL Server or Access/something else? Check ifthis articlehelps (its in VB.NET but you could get an idea of how to declare parameters and set the values).

Connecting to SQL Server through firewall

I have a simple asp page that queries a database inside our firewall. It works fine from the inside, and only uses port 1433. But if i put it out on our web server it won't connect and tries to use ports 139 and 445. Has anybody ran across this before?
Thanks,
JohnAre those ports that you see registered with the firewall to allow traffic? You can explicitly specify the port number in your app connection string.|||I have a simple asp page that queries a database inside our firewall. It works fine from the inside, and only uses port 1433. But if i put it out on our web server it won't connect and tries to use ports 139 and 445. Has anybody ran across this before?You are using Named Pipes instead of TCP/IP for the ODBC connection on the ASP server.

-PatP|||is that in the connection string like:

Data Source=172.25.24.30:1433

or is there some other syntax.

Thanks again,

John|||You are using Named Pipes instead of TCP/IP for the ODBC connection on the ASP server.

-PatP

Do you still need to do that for a nonDSN connection?

Thanks Pat,

John|||My first guess would be to go to the ASP server, run the SQL Client Configuration to change the default connection type to TCP/IP. I think that should fix you right up.

-PatP|||Thanks for all your help. It was set to named pipes. Does anyone know if that is the default setting? I always thought the machine came up with TCP/IP as default.

Thanks again.

John|||Different MDAC, SQL Tool, etc combinations have different defaults, and some applications change the default as part of their installation (for which I'd like to fry the developer that came up with that idea!). I don't know of any way to concretely identify just how this came to be, so I just fix it when it bytes me and move on...

-PatP|||I have a similar question. I have installed MSDE 2000 on a Win2k3 Server and everything is working fine with the DB. The MSSQLSERVER servoce has been running for 60 days straight with no issue. MSDE works great. However, I cannot connect to it on port 1433 through the firewall. Using netstat I cannot see that it is not listening on port 1433. I ran the CLICONFG utility and I can see that it has TCP/IP at the top of the list with Named Pipes below it. I have other servers ( Only Win2k servers) with MSDE 200 installed and configured similarily and these servers are listening on port 1433. I looked at the registry settings and they seem OK - nothing out of place.

The DB is now in a production environment and I cannot bounce the MSSQLSERVER service till the weekend during scheduled maintenance.

How can I ensure when I stop/start the MSSQLSERVER service that it is listening on port 1433? Would it be wise using CLICONFG to remove Named Pipes (IIS is the only user of the Database) ? I would prefer not to do that as I believe that NP is faster SQLServer and IIS are on the same box.

Does the version of MDAC have anything to do with this issue?

--Steve|||I can't think of any case where Named Pipes would be faster than TCP/IP that would occur in the real world (yes, I know of a way to construct one, but it would never occur naturally).

You can certainly do as you see fit, but I would recommend using TCP/IP as your default connection type.

If you've recently applied either sp3a to SQL Server, or sp2 to Server 2k3, they will automajikally block port 1433. Read up on the service packs to determine how you want to configure your machine to accomodate the access that you need.

-PatP|||I believe that the problem was that the CLICONFIG was not what I should have been using. There is a SVRNETCN.EXE utility in the C:\Program Files\Microsoft Sql Server\80\Tools\Binn directory that is the program to use. It turns out that the box had no IPC set at all. I added TCP/IP and it won't use the new options till I bounce MSQSQLSERVER service.

--Steve

connecting to sql server from asp page

Hi,
I'm trying to establish a connection to Sql Server from an asp page using a System DSN.
I setup the System DSN to use Sql Server authentication; the userid and password
were stored with the ODBC configuration utility. Sql Server is configured to used both Windows and Sql Server authentication.
I was not able to connect using the DSN from an asp page - the exception was as follows
Message "ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login
failed for user '(null)'. Reason: Not associated with a trusted SQL Server
connection.
ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed
for user '(null)'. Reason: Not associated with a trusted SQL Server
connection.
The code snippet:
Dim connString As String = "DSN=myDSN;"
Dim conn As OdbcConnection = New OdbcConnection(connString)
Try
conn.Open()
Catch ex As Exception
End Try
Any help or article would be appreciated.
Ronny,
you'll need to complete the DNS connection string with login info. For
example:
oConn.Open "DSN=mySystemDSN;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
Also, check that SQL Server in in mixed authentication mode.HTH,Paul Ibison
|||In your DSN, if you include the login and password, then you are specifying
that the dsn should not use integrated authentication. However, the login
name and password in the login request from a DB-Library or Open Database
Connectivity (ODBC) client application are always ignored by SQL Server
(149398).
You have two choices - either supply the uid and pwd in the connection open
(as per Paul) or switch to integrated security and allocate IUSR_xxxx
appropriate permissions.
Bern
"Ronny Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:5B207EDD-6761-48CF-A924-0E3CA4756C94@.microsoft.com...
> Hi,
> I'm trying to establish a connection to Sql Server from an asp page using
a System DSN.
> I setup the System DSN to use Sql Server authentication; the userid and
password
> were stored with the ODBC configuration utility. Sql Server is configured
to used both Windows and Sql Server authentication.
> I was not able to connect using the DSN from an asp page - the exception
was as follows
> Message "ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL
Server]Login
> failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> connection.
> ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed
> for user '(null)'. Reason: Not associated with a trusted SQL Server
> connection.
> The code snippet:
> Dim connString As String = "DSN=myDSN;"
> Dim conn As OdbcConnection = New OdbcConnection(connString)
> Try
> conn.Open()
> Catch ex As Exception
> End Try
> Any help or article would be appreciated.

connecting to sql server from asp page

Hi,
I'm trying to establish a connection to Sql Server from an asp page using a
System DSN.
I setup the System DSN to use Sql Server authentication; the userid and pass
word
were stored with the ODBC configuration utility. Sql Server is configured t
o used both Windows and Sql Server authentication.
I was not able to connect using the DSN from an asp page - the exception was
as follows
Message "ERROR [28000] [Microsoft][ODBC SQL Server Driver][S
QL Server]Login
failed for user '(null)'. Reason: Not associated with a trusted SQL Server
connection.
ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server
]Login failed
for user '(null)'. Reason: Not associated with a trusted SQL Server
connection.
The code snippet:
Dim connString As String = "DSN=myDSN;"
Dim conn As OdbcConnection = New OdbcConnection(connString)
Try
conn.Open()
Catch ex As Exception
End Try
Any help or article would be appreciated.Ronny,
you'll need to complete the DNS connection string with login info. For
example:
oConn.Open "DSN=mySystemDSN;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
Also, check that SQL Server in in mixed authentication mode.HTH,Paul Ibison|||In your DSN, if you include the login and password, then you are specifying
that the dsn should not use integrated authentication. However, the login
name and password in the login request from a DB-Library or Open Database
Connectivity (ODBC) client application are always ignored by SQL Server
(149398).
You have two choices - either supply the uid and pwd in the connection open
(as per Paul) or switch to integrated security and allocate IUSR_xxxx
appropriate permissions.
Bern
"Ronny Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:5B207EDD-6761-48CF-A924-0E3CA4756C94@.microsoft.com...
> Hi,
> I'm trying to establish a connection to Sql Server from an asp page using
a System DSN.
> I setup the System DSN to use Sql Server authentication; the userid and
password
> were stored with the ODBC configuration utility. Sql Server is configured
to used both Windows and Sql Server authentication.
> I was not able to connect using the DSN from an asp page - the exception
was as follows
> Message "ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL[/vb
col]
Server]Login[vbcol=seagreen]
> failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> connection.
> ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Serv
er]Login failed
> for user '(null)'. Reason: Not associated with a trusted SQL Server
> connection.
> The code snippet:
> Dim connString As String = "DSN=myDSN;"
> Dim conn As OdbcConnection = New OdbcConnection(connString)
> Try
> conn.Open()
> Catch ex As Exception
> End Try
> Any help or article would be appreciated.

Connecting to SQL Server 2005 from Classic ASP

I am trying to return multiple recordsets to a classic ASP web page from SQL Server 2005 and then use GetRows() in ASP to fill 2 arrays with the data. I'm using a command object to run a stored procedure which performs 2 simple selects in SQL Server. The stored procedure works fine in SQL Server Management Studio so I'm guessing it's an ADO issue.

The ASP code looks like this:

strConnect = "DRIVER={SQL Native Client};SERVER=MyServer.IsAtMyIsp.com;DATABASE=MyDb;UID=Me;PWD=MyPwd; MARS Connection=True;"

Set conn = Server.CreateObject("ADODB.Connection")

conn.ConnectionString = strConnect

conn.Open

Set objCommand = Server.CreateObject("ADODB.Command")

Set objRecordset = Server.CreateObject("ADODB.Recordset")

Set objRs = Server.CreateObject("ADODB.Recordset")

With objCommand

.ActiveConnection = conn

.CommandText = "sp_GetShowList"

.CommandType = adCmdStoredProc

If Len(strBeginDate) And IsDate(strBeginDate) Then

.Parameters.Append .CreateParameter("@.i_DateStart",adDate,adParamInput,,strBeginDate)

If Len(strEndDate) And IsDate(strEndDate) Then

.Parameters.Append .CreateParameter("@.i_DateEnd",adDate,adParamInput,,strEndDate)

End If

End If

End With

Set objRs = objCommand.Execute

arrEvents = objRs.GetRows()

objRs.NextRecordset

arrSched = objRs.GetRows()

The error message below occurs at the 2nd GetRows() command.

Operation is not allowed when the object is closed.

Can anyone tell me what I might be doing wrong?

-Dan

You don't assign the second recordset returned by NextRecordset to the recordset variable

Try

Set objRs = objCommand.Execute
arrEvents = objRs.GetRows()
objRs = objRs.NextRecordset
arrSched = objRs.GetRows()

|||

That was so obvious I feel like an idiot. Thanks for the response!

-Dan