Connection within connection

I have a table that stores general information on employees. I have a related table that stores actions of those employees. What I would like to do is make a list of employees + their actions.

I wanted to do something like this:
Dim cnAccess As new OleDbConnection("provider=...")
cnAccess.Open()
Dim sSelect As String
sSelect = "SELECT * FROM tblEmployees"
Dim cmdSelect as New OleDbCommand(sSelect, cnAccess)
Dim drEmployees As OleDbDataReader
drEmployees = cmdSelect.ExecuteReader()
Do While drEmployees.Read()
'get name
'get address
...
'open a new connection to the database, select * From tblActions, executereader, etc.
Loop

Now, if I try this, the moment it comes to the second executereader, it gives an error that there can only be one executereader open at a time.
How can I make this work?
Thanks,
Tom
0
TomWouters
8/30/2004 1:28:45 PM
asp.net.access-datasource 4679 articles. 0 followers. Follow

4 Replies
1039 Views

Similar Articles

[PageSpeed] 1

Are you SURE you are using a new conneciton?  What EXACTLY is the error message?


Starting with ASP.NET 2.0? Look at:
Programming Microsoft Web Forms
My Blog
0
douglas
8/30/2004 3:43:17 PM
First off - the rule is

One DataReader - One Connection
What would be a more efficient solution would be to do an inner join of the two tables in your sql statement
Something like:
SQL = "Select tblEmployees.FirstName, tblEmployees.LastName, tblActions.Actions from tblEmployees INNER JOIN tblActions on tblEmployees.ID=tblActions.ID"

David Wier
MCP/ASPInsider
ASPNet101.com - where to look first!
Control Grouper - easily control properties for multiple controls with one control!
Calendar Express - The Best HTML Calendar Generator on the web!
(Please 'Mark as Answer' when it applies)
0
augustwind
8/30/2004 10:07:26 PM
Dim cnAccess As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\HSF\AIVC_websites_v05.mdb")

cnAccess.Open()
Dim sSelect As String
sSelect = "SELECT tblWebsites.WebsiteID, tblKeywords.KeywordEN, tblKeywords.KeywordID FROM tblKeywords INNER JOIN (tblWebsites INNER JOIN tblWebsiteKeywords ON tblWebsites.WebsiteID = tblWebsiteKeywords.WebsiteID) ON tblKeywords.KeywordID = tblWebsiteKeywords.KeywordID"
Dim cmdSelect As New OleDbCommand(sSelect,cnAccess)
Dim drWeb As OleDbDataReader, sbResults As New StringBuilder()
drWeb = cmdSelect.ExecuteReader()
Do While drWeb.Read()
sbResults.Append(drWeb.GetInt32(0))
sbResults.Append("<br>")
sbResults.Append(drWeb.GetString(1))
sbResults.Append("<br>")
Dim intWeb As Integer
intWeb = drWeb.GetInt32(0)
Dim cnAccess2 As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\HSF\AIVC_Websites_v05.mdb")
cnAccess2.Open()
Dim sSelect2 As String
sSelect2 = "SELECT tblWebsites.WebsiteID, tblCountries.Country, tblOrganisationTypes.OrganisationTypeNL, tblWebsites.WebsitePublications, tblWebsites.WebsitePayment, tblLanguages.[Language], tblInformation.WebsiteAddress, tblInformation.OrganisationName, tblInformation.WebsiteComment, tblInformation.SoftwareTools, tblInformation.ElectronicNewsletter, tblKeywords.KeywordNL, tblWebsiteKeywords.Relevance FROM (tblOrganisationTypes INNER JOIN (tblKeywords INNER JOIN (tblCountries INNER JOIN (tblWebsites INNER JOIN tblWebsiteKeywords ON tblWebsites.WebsiteID = tblWebsiteKeywords.WebsiteID) ON tblCountries.CountryID = tblWebsites.WebsiteCountry) ON tblKeywords.KeywordID = tblWebsiteKeywords.KeywordID) ON tblOrganisationTypes.OrganisationTypeID = tblWebsites.WebsiteOrganisationChar) INNER JOIN (tblLanguages INNER JOIN tblInformation ON tblLanguages.[LanguageID] = tblInformation.[LanguageID]) ON tblWebsites.WebsiteID = tblInformation.WebsiteID WHERE ((tblWebsites.WebsiteID) = " & intWeb & ")"
sbResults.Append(sSelect2)
Dim cmdSelect2 As New OleDbCommand(sSelect2,cnAccess2)
Dim drDetails As OleDbDataReader
drDetails = cmdSelect2.ExecuteReader()
sbResults.Append(drDetails.GetString(1))
sbResults.Append("<br>")
sbResults.Append(drDetails.GetString(2))
sbResults.Append("<br>")
sbResults.Append(drDetails.GetString(6))
sbResults.Append("<br>")
sbResults.Append(drDetails.GetString(7))
cnAccess2.Close
drDetails.Close
Loop
lblResults.Text = sbResults.ToString()


This is what I did, and it works fine until it reaches the drDetails.GetString(1)). Then I get the following error:
No data exists for the row/column.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: No data exists for the row/column.
Source Error:

Line 38: drDetails = cmdSelect2.ExecuteReader()
Line 39:
Line 40: sbResults.Append(drDetails.GetString(1))
Line 41: sbResults.Append("<br>")
Line 42: sbResults.Append(drDetails.GetString(2))



If i take the query from sSelect2 and copy it in an access query, it works. I don't get it, what am i doing wrong?
Tom
0
TomWouters
8/31/2004 7:28:59 AM
never mind, I added a Do While to the second datareader and it works perfectly now. 

Thanks for the help,
Tom
0
TomWouters
8/31/2004 1:26:49 PM
Reply:

Similar Artilces:

not connected when I am connected
What do I need to change on my 'puter when the icon in the toolbar shows and tells me I am connected and the error message tells me I am not and I can not go to web pages, get or send emails. I have to close down and restart to get connected again. This only happens if I am on line and leave the 'puter for an hour or so. As long as I am using it I don't have a problem. This just started about a month ago. I am on very slow dialup, DSL and Cable not in my area, thanks you SBC/ATT tks Elaine wrote: > What do I need to change on my 'puter when the icon in ...

Using VS.Net Data Connection Tool to Connect to Access Dbase?
I am trying to use the Visual Studio.Net data connection tool to connect to an Access database on a network server, not on my client machine. I've tried everything I can think of but nothing works. Can anyone provide steps/instructions/example/link about how to do this. My Access dbase, named sample.mdb, is located on a server at http://IP/AccountFolder, so the complete location is http://IP/AccountFolder/sample.mdb It is not password protected, but access to the account folder requires a username and password. The connection tool seems to be oriented to connecting to MSDK on th...

Connect to LAN (Was connect to NET)
Almost time for a new header, the old was getting too long and the focus is now different. So .... Story so far - Mepis 6 - setting LAN IP would upset external site addressing but the ISP hung in there without dropping the line. I have, at last, succeeded in connecting to the web via dial up AND keeping it running while seeing a LAN. I can't point directly to what I did but I did keep notes along the way as to what NOT to touch. Call it pure luck, deft fingerwork, incantations and imprecations. Next problem - 5 PC's. (5 Machine names) A, B, C, D, E. (4 are Win XP...

Timeout connecting with .NET connection object
I have a SQL Anywhere 9 database that I can connect to fine via Sybase Central or ISQL. However, when I attempt to connect using the AsaConnection object from the IAnywhere.Data.AsaClient namespace, the open connection call times out. The code works fine on my development box, but I cannot get the connection to work on the production box. Any ideas? Thanks, David ...

The connection is already open and the connection state is connecting.
Hello, I am getting this error time to time randomly. My web application is built on asp.net 3.5 which uses ODBC 5.1 driver to connect to mysql database. I have a separate database class which handles the connection, execute, fetching and all. The application runs fine for sometime then suddenly the users start getting this error after which the application fails to insert select from database. I have posted in this forum couple of times now and no one is really contributing or helping me. Please..Any help will be highly appreciated.. Many thanks, Viv The database abstract loo...

SFtp connect
The following Ftp connect to a sftp site has the right port/usrname/password [verified thru filezilla], but when I try to connect via TIDFTP -- I get "Connection Closed Gracefully" upon ftp->Connect();; The ftp properties are below the code. I have no other Indy objects on the form. It ocurred to me that I may need something for sftp. ftp->Connect(); if (ftp->Connected()) { ::Sleep(1); } object ftp: TIdFTP AutoLogin = True Host = 'ftp.dailyaccess.com' Password ='XXXXX' Port = 21 Username = 'charles.leggette' ProxySet...

problems with connections to access via ADO.NET in VS.NET
For anyone familiar with this book, I am having trouble with some code in Chris Payne's "SAMS Teach Yourself ASP.NET in 21 Days", which calls for me to use ADO.NET to connect to an Access database I had to create for the "In Review" project after the second week. The code goes like this (within the first subroutine): dim Conn as new OleDbConnection("Provider= Microsoft.Jet.4.0; Data Source=C:\Inetpub\wwwroot\TYASPNET\banking.mdb") Now I've tried this, along with setting up a connection through the server explorer in Visual Studio.NET. When I go to run the page, I...

Connecting to an access databse useing an OLEBD connection
  <% @ Import namespace=System.data.Oledb %>  string strDsn = "Trusted_Connection=Yes;DSN=beautyaids"; OleDbConnection conn = new OleDbConnection(strDsn); conn.Open();   I have written the following code, above.  This is in an ASPX page.  The code runs until it gets to the conn.Open.  All the books and documents state to create the string as done above.  When the compiler gets to the conn.Open spits out the following error message " An error occurred during the compilation of a resource required to service this request. Please review the fol...

Recycling connections in a ADO.Net connection pool
I have a requirement where I will need to get a handle on the existing connections in a particular ADO.Net connection pool (identified by the unique connection string) and close and open them one by one. This is because whenever we compile a package or stored procedure, ADO.Net does not realise this and keeps giving "Package is invalid" error. I am using ODP.Net Client 9.2.0.4. The current solution is to restart the application pool to flush the connection pool. Does anyone know how to get a list of all connection objects in the ADO.Net connection pool given a connection stri...

Access a TCP connection from an HTTP server connection?
I have two Component::Server::TCP servers running (A and B). Server B needs to write to a connection on server A. A's connections have a unique md5 id assigned to their heap. B should located a connection on A based on it's unique id, then write some data to it. I'm able to locate the server as follows: $server = $poe_kernel->alias_resolve('tcp_server'); I'd then planned to do something equivalent to the following: foreach my $conn ($server->getConnections()) { if ($conn->{heap}->{'uniqueID'} eq $requestedID) { $c...

Number of connections connected
hi, all I can limit the user connections to my sybase. But how can I know how many connections currently connected to my sybase ? Regards, Keene Hui Kenneth Hui wrote: > I can limit the user connections to my sybase. Do you mean on a per user basis? If so, no. > But how can I know how many connections currently connected > to my sybase ? 'sp_who' or 'select * from sysprocesses' will show the current connections. -am � 2001 ...

OpenVPN no connectivity after connecting
My goal with the OpenSuSE machine I have set up is to be an OpenVPN server. So far so good, I have started the server and I am able to connect a Mac client (running TunnelBlick) to the server. However the client doesn't have any connectivity, I can't ping any addresses for example. The server is set up for bridging instead of routing as I want the remote client to get an IP address in the same range as the rest of the LAN. The client is getting an IP address from the range specified in my server.conf file, so that's a start, but the client can't talk to anyone. ...

@@connections and user connections
Hi! I've a doubt, I hope you can help me. I've ASE 11.9.3 on HP-UX 11.0. The problem is at times, when an user run an aplication, soon afterwards system log out him. I checked the log and I found the next error: 01:00000:00008:2000/06/28 11:15:27.32 server Error: 1601, Severity: 21, State: 3 01:00000:00008:2000/06/28 11:15:27.33 server There are not enough 'user connections' available to start a new process. Retry when there are fewer active users, or ask your System Administrator to reconfigure SQL Server with more user connections. But, the point is I...

XP Remote Access Connection Manager required to connect?
I don't want anyone remotely accessing my computer. However, this service must be running in order to dial with my dialup modem (SanyoUSB modem, a cellphone connected to the USB port). Is this service necessary only for the SanyoUSB, or for other dialup connections too? Yep, Remote access is so that your computer can connect to another computer. For some one to connect to your computer you'd need to have software running that would answer the phone connected to your computer and start a session. "geekhead" <noone@nowhere.net> wrote in message news:...

Web resources about - Connection within connection - asp.net.access-datasource

Connection - Wikipedia, the free encyclopedia
Text is available under the Creative Commons Attribution-ShareAlike License ;additional terms may apply. By using this site, you agree to the ...

iMedia Connection: Interactive Marketing News, Features, Podcasts and Video - iMediaConnection.com
High-quality data, if not used properly, can still lead marketers to make bad decisions. Consider these common ways that numbers are used to ...

HTTP persistent connection - Wikipedia, the free encyclopedia
... tacked on to an existing protocol. If the browser supports keep-alive, it adds an additional header to the request: Following this, the connection ...

CareerSonar Turns Facebook Friends Into Job Connections
Looking for a job ? Among your Facebook friends lies the potential for employment. CareerSonar , a new service, brings together a person’s connections ...

Kings Cross identities arrested in connection with murder
Two Kings Cross identities&nbsp;have been arrested in connection with the murder of a man at Sydney hotel.

Faith: Spirits lift in connection with home
We need to more adequately understand the spirituality of our unique place on earth.

FBI mulls connection between Super Bowl, CA fiber optic cable cuttings
... authorities, who have been baffled by more than a dozen attacks on San Francisco Bay Area data lines, are probing whether there is any connection ...

Tons Of Methane Are Spewing Out Of California, And There’s A Connection To The Governor
The out of control leak has forced thousands of residents to flee their homes. Gov. Brown's sister is a paid board member at the company that ...

Report: Man arrested in connection with Ashley Olsen's murder
Italian media agency reports that a non-EU male has been arrested in the murder of the 35-year-old American

Toyota and Kymeta rely on satellites to power this Mirai's data connection
They're hoping to achieve gigabit wireless speeds within the next few years.

Resources last updated: 1/22/2016 11:15:51 AM