How do I make multiple sqlDataReaders work

[reykentj]

Stored VB.Net procedures consisting of many subroutines and a few functions working with one SQL Server 2005 datbase. The problem is as follows: Sub A receives a table name, a query name, and a directory path name.Using Dim RecordTypeSet As SqlDataReader Dim RecordTypeConnection As New SqlConnection Dim RecordTypeCommand As New SqlCommand I open a reader from the sql located at directory path name & query name and While RecordTypeSet.Read() I create a record which I want to store in the table name provided. In order to do this I call a SUB B with the record and the Table name. Sub B builds an insert query and using Dim SummaryConnection As SqlConnection Dim SummaryCommand As SqlCommand Dim SummaryReader As SqlDataReader SummaryConnection = New SqlConnection("context connection = true") SummaryConnection.Open() SummaryCommand = New SqlCommand(sql, SummaryConnection) SummaryReader = SummaryCommand.ExecuteReader() errors out on SummaryConnection.Open() end sub B wend rkj

[Dave Kreskowiak]

reykentj wrote:

SummaryConnection = New SqlConnection("context connection = true")

What's the error?? Why does everyone leave out the error description when they post "I have a problem!"? Does the method that this error occurs in have the SqlProcedure attribute on it?

Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007

[reykentj]

Sub_A Dim sql As String = "" Dim rdr1 As SqlDataReader Dim conn1 As New SqlConnection Dim cmd1 As New SqlCommand sql = ReadAllText from textfile conn1 = New SqlConnection("context connection = true") conn1.Open() cmd1 = New SqlCommand(Sql, conn1) rdr1 = cmd1.ExecuteReader() While rdr1.Read() build record REC_A Sub_B (REC_A) SUB_B Dim sql As String = "" Dim rdr2 As SqlDataReader Dim conn2 As New SqlConnection Dim cmd2 As New SqlCommand Builds SQl Query to insert record into some table conn2 = New SqlConnection("context connection = true") conn2.Open() ABORTS HERE eND sUB_b wend End Sun_A With SqlProcedure Attribute I get a deployment error on SUB_A because it has a Byref Class instantiation to return data to the caller rkj

[Dave Kreskowiak]

This is utterly useless. Copy and Paste the code in a post. The "Sub_A" crap leaves out details that are required to see what's going on. You also didn't answer my question. Does the "sub" have an SqlProcedure attribute? Copying and pasting the code probably would have showed that! And, the EXACT error message would help more, not your interpretation of it. The "context connection = true" only works inside an SqlProcedure attributed function. Without it, you may as well use a seperate connection using a normal connection string.

Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007

[reykentj]

I did not originally have the sql server attribute declared Your first question prompted me to insert that and try again. When I did, I got a deployment error on the byref use. I am currently trying to find another attribute to solve that problem. Here is the code _ Public Shared Sub sbBuildRecordTypeProfitnLossTable(ByVal Account_Summary_Name As String, _ ByVal RecordTypeQueryName As String, _ ByVal DirectoryPath As String, _ ByVal Run_Select As Long, _ ByVal Department As String, _ ByVal Record_Type As Single, _ ByVal record_count As Long, _ ByVal Pdate As Date, _ ByRef PL_Return As Profit_Loss_Return) Dim Wrec As New Profit_Loss_Summary_Record Dim ProfitnLossRec As New Profit_Loss_Summary_Record Dim wSalesRec As New Profit_Loss_Summary_Record Dim TotMonthly_Item As Decimal = 0 Dim TotYearly_Item As Decimal = 0 Dim result As Long = 0 Dim SalesAccountNo As Long = 0 Dim AccountStor As Long = 0 Dim StrSalesAccountNo As String = "" Dim CGAccountNo As Long = 0 Dim Branch_Name As String = "" Dim ReturnValues As New Profit_Loss_Return Dim wdepartment As String = "" Dim sql As String = "" Dim RecordTypeSet As SqlDataReader Dim RecordTypeConnection As New SqlConnection Dim RecordTypeCommand As New SqlCommand On Error GoTo Err_sbBuildRecordTypeProfitnLossTable sql = File.ReadAllText(DirectoryPath & RecordTypeQueryName) RecordTypeConnection = New SqlConnection("context connection = true") RecordTypeConnection.Open() RecordTypeCommand = New SqlCommand(Sql, RecordTypeConnection) RecordTypeSet = RecordTypeCommand.ExecuteReader() While RecordTypeSet.Read() AccountStor = RecordTypeSet!AccountNo If AccountStor = 53410 Then record_count = record_count End If sbBuildSummaryProfitnLossTableRec(RecordTypeSet, Record_Type, Department, Pdate, Profi

[Colin Angus Mackay]

reykentj wrote:

"ABORTED HERE without SQl Attribute. Cannot get here with it!!"

It "aborted" with what error message?!! Do you actually want help? This is the third time you've been asked for the error message. Why are you being so evasive? I'd love to help, but I'm not going to read through the code looking for the cause of an error until I know what error I'm supposed to be looking for.

---

Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website

[reykentj]

Running the code last provided "Error The context connection is already in use. occurred in Module: sbUpdateSummaryProfitnLossTableRecord" "Error 5 occurred in Module: sbUpdateSummaryProfitnLossTableRecord" was obtained from my error handling I captured the following output. A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll A first chance exception of type 'System.IO.FileNotFoundException' occurred in Clrtest I think I am trying to use MARS in this environment. Is this even possible? rkj -- modified at 0:33 Sunday 15th April, 2007