Knowledgebase
[QODBC-Desktop] Speeding up Adding and Editing Invoices with ASP.NET
Posted by Juliet (QODBC Support) on 30 April 2010 06:40 AM

Instructions about AspCompat

     Using QODBC on ASP.NET is slow when retrieving field values. "SELECT * FROM Vendor" took ASP.NET to retrieve a 3 to 5-second avg per record or row. But switching to AspCompat="true" will yield 45 to 60 records per second. Retrieval using classic ASP is fine too.

   ASPCompat is a configuration setting at the start of your code.

Example of ASPCompat

<%@ Page AspCompat="true" Language="VB" %>

<script runat="server">
' The components is created at construction time.
Dim comObj As MyComObject = New MyComObject()

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
' The object is first used here.
comObj.DoSomething()
End Sub
</script>

<html >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>

</div>
</form>
</body>
</html>

Special Notes for Insert and Update Query

WHERE Related

     INSERTs go directly into the table, while UPDATEs run the WHERE query first and then perform the updates. So it's important that the WHERE corresponds with a QODBC jump-in (as these act as indexes). Run:

     sp_columns tablename

to see the jump-ins of the table you are using.

Batches Related

     You can also use batches to submit your daily invoices instead.

QODBC Batch Stored Procedures Instructions

     These allow you to start a batch for a given table, and all insert/updates will be queued until the sp_batchupdate command is issued. This allows for fewer round trips to QuickBooks, which increases performance when doing large transfers or recording from external systems (like FileMaker Pro) to QuickBooks.

     The sp_lastinsertid stored procedure will return the ListID/TxnID plus an error message column for every row added to the batch.

Note: Each batch is limited to 500 transactions.

QODBC Batch Stored Procedures Detail

sp_batchclear tablename – clears the current batch started with sp_batchstart.

sp_batchstart tablename – starts a new batch. All inserts/updates issued on this table will be batched until sp_batchupdate is issued.

sp_batchupdate tablename – sends the batched transactions to QuickBooks. ListID/TxnIDs and error messages are available through the sp_lastinsertid tablename.

Possible Solutions Provided by Customers

       I was able to do a workaround for the speed issue by getting the assigned ListID's for the class and customer list and inserting those into a field in my SQL Server tables in the ASP.NET application to the corresponding customers and classes. This way, only one call to QB is run (the actual insert or update of an invoice) instead of 6 or 7 I had before trying to find the right ListID to insert into the invoice.

(151 vote(s))
Helpful
Not helpful
Comments (1)
Emmanuella
20 September 2012 07:32 AM
I'm so glad I found my soultion online.
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).