?
Solved

VB.NET System Call Fails - RuntimeCallableWrapper

Posted on 2013-02-05
6
Medium Priority
?
3,025 Views
Last Modified: 2013-02-06
VB.NET program, Local Access Database

My program is generating this error -:

DisconnectedContext was detected
Message: Transition into COM context 0x676010 for this RuntimeCallableWrapper failed with the following error: System call failed. (Exception from HRESULT: 0x80010100 (RPC_E_SYS_CALL_FAILED)). This is typically because the COM context 0x676010 where this RuntimeCallableWrapper was created has been disconnected or it is busy doing something else. Releasing the interfaces from the current COM context (COM context 0x675ea0). This may cause corruption or data loss. To avoid this problem, please ensure that all COM contexts/apartments/threads stay alive and are available for context transition, until the application is completely done with the RuntimeCallableWrappers that represents COM components that live inside them.

The error started occuring after I added a second call to the database inside a loop.

The program I wrote loops through all items in a database.
It sums up the total number of items sold (located in one table) in one query, then in another query it sums up the total number of items on a Credit Memo -
so that the number of items on the credit memo can be subtracted from the total number of items sold.

Below is how it looks -
"iis" = integer (inventory items sold)
"cms" = integer (credit memo sold)

 sql2 = "select sum(InvoiceLineQuantity) as QSold from InvoiceLine where txnDate <= #" & newStart & "# and txnDate >= #" & newEnd & "# and InvoiceLineItemRefFullName = '" & ds.Tables(0).Rows(x).Item("FullName") & "'"
                    da2 = New OleDbDataAdapter(sql2, cn)
                    ds2 = New DataSet()
                    da2.Fill(ds2, "table1")
                    If ds2.Tables(0).Rows(0).Item("QSold").Equals(System.DBNull.Value) Then
                        iis = 0
                    Else
                        iis = ds2.Tables(0).Rows(0).Item("QSold").ToString()
                    End If

'Credit memo section (added to subtract item returns for accurate total sales)
sql2 = "select sum(CreditmemoLineQuantity) as CMSold from CreditmemoLine where txnDate <= #" & newStart & "# and txnDate >= #" & newEnd & "# and CreditMemoLineItemRefFullName = '" & ds.Tables(0).Rows(x).Item("FullName") & "'"
                    da3 = New OleDbDataAdapter(sql2, cn)
                    ds3 = New DataSet()
                    da3.Fill(ds3, "table1")

                    If ds3.Tables(0).Rows(0).Item("CMSold").Equals(System.DBNull.Value) Then
                        cms = 0
                    Else
                        cms = ds3.Tables(0).Rows(0).Item("CMSold")
                    End If

Open in new window


After the code runs, I subtract "cms" from "iis" to get "totalSales"

this code loops for each item from a specific vendor.  Some vendors have 20 items, our biggest vendor has 800+ items.  The error happens when I run the 800+ item vendor.

I'm a self taught programmer, so perhaps i'm going about this the wrong way - but I don't understand how a COM error happens - or how it could be related to the database calls.  But, since the report ran just fine prior to me adding the credit memo section, that's what must be causing the error.

Can anyone provide any insight into what might be causing the error, or what I can do to prevent it?  The compiler did not give me a pin-point spot or line number for the error...

Thanks for your help!
0
Comment
Question by:slightlyoff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 39

Expert Comment

by:appari
ID: 38858267
are you executing the application in debug mode? normally this error is displayed only if you run the application in a debug session. try executing the exe file and see if you are still getting the error.
0
 
LVL 18

Expert Comment

by:dj_alik
ID: 38858311
The problem is that the process takes too long to complete.

try to create new  connection object in second oledbadapter also.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 38858347
You can do it with one SQL statement and two derived tables (sub-selects); and even handle the nulls with NZ() too.
I haven't concatenated all the SQL for you, but this should give you the general idea.
SELECT NZ([t1].[QSold],0)+NZ([t2].[CMSold],0) AS totalSales
FROM 
  (SELECT Sum(CreditmemoLineQuantity) as QSold FROM CreditmemoLine)  AS t1
 ,(SELECT Sum(InvoiceLineQuantity) AS CMSold FROM InvoiceLine)
 AS t2;

-- returns:
totalSales
20

Open in new window

Then you populate your OleDbDataAdapter and DataSet using the concatenated SQL and pull the totalSales from your DataSet in one bite.
  ...
totalSales= ds.Tables(0).Rows(0).Item("totalSales")

I suspect your connection is going out of scope, most likely because you are not closing it in the loop, essentially leaving 800 open connections, some of which will be cleaned up the garbage collector, but eventually you will exceed your max open connections limit.


Alan ";0)
0
Quick Start: DOCKER

Sometimes you just need a Quick Start on a topic in order to begin using it.. this is just what you need to know to get up and running with Docker!

 
LVL 1

Author Comment

by:slightlyoff
ID: 38861092
Thanks for the replies!

Alan, I tried your SQL String - finishing it with the where clause:
SELECT NZ([t1].[QSold],0)-NZ([t2].[CMSold],0) AS totalSales FROM (SELECT Sum(CreditmemoLineQuantity) as CMSold FROM CreditmemoLine where CreditMemoLine.txnDate <= #11/1/2012# And CreditmemoLine.txnDate >= #8/3/2012# And CreditmemoLine.CreditMemoLineItemRefFullName = 'HA:BRGMP01AS') AS t2 , (SELECT Sum(InvoiceLineQuantity) AS QSold FROM InvoiceLine where InvoiceLine.txnDate <= #11/1/2012# And InvoiceLine.txnDate >= #8/3/2012# And InvoiceLine.InvoiceLineItemRefFullName = 'HA:BRGMP01AS') AS t1

Open in new window


When I execute it in access, it works as advertised :)  - However, when I run it in VB.NET, I get :  Undefined function 'NZ' in expression.

I have changed my code to be this:

cn2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\quickbooks2.mdb;Persist Security Info=True"
                    cn2.Open()
                    da2 = New OleDbDataAdapter(sql2, cn2)
                    ds2 = New DataSet()
                    da2.Fill(ds2, "table1")
                    TotalSales = ds2.Tables(0).Rows(0).Item("totalSales")
                    cn2.Close()

Open in new window


VB.NET doesn't like the NZ function, which is too bad, cause that looks handy.  I'm not sure if there is away around it - but I'm checking for nulls before setting the variable, so I think that will work.

Thank you so much for your help!
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 2000 total points
ID: 38861297
Maybe try the Microsoft.ACE.OLEDB.12.0 provider, depends which version the Access DB is, 12.0 is for an Access 2007 catalog. Alternatively you could try using the Jet immediate if function in the SQL:
SELECT IIF([t1].[QSold]=NULL,0,[t1].[QSold])+IIF([t2].[CMSold]=NULL,0,[t2].[CMSold]) AS totalSales

Open in new window

I know the VB IIF function had a glitch (not sure if it still does) where it would execute both sides of the IIF, which was a big problem if you were calling some insert or update procedure on each side of the IIF; but the JET IIF has no such limitiation.

Also checking for nulls can be a bit pedantic, =Null, Is Null can return unexpected results, I have sometimes resorted to wrapping the value in Len() function to remove any ambiguity, IIF(Len([t1].[QSold])=0,0,[t1].[QSold]) ...

Anyhow there's a few options.

Alan ";0)
1
 
LVL 1

Author Comment

by:slightlyoff
ID: 38861482
Thanks, I used

SELECT IIF(ISNULL([t1].[QSold])=TRUE,0,[t1].[QSold])- IIF(ISNULL([t2].[CMSold])=TRUE,0,[t2].[CMSold]) as totalSales FROM (SELECT Sum(CreditmemoLineQuantity) AS CMSold FROM CreditmemoLine where CreditMemoLine.txnDate <= #11/1/2012# And CreditmemoLine.txnDate >= #8/3/2012# And CreditmemoLine.CreditMemoLineItemRefFullName = 'SB:SB044') AS t2 , (SELECT Sum(InvoiceLineQuantity) AS QSold FROM InvoiceLine where InvoiceLine.txnDate <= #11/1/2012# And InvoiceLine.txnDate >= #8/3/2012# And InvoiceLine.InvoiceLineItemRefFullName = 'SB:SB044') AS t1

Open in new window


And it works as its supposed to.  Thank you very much for your help!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question