|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| Update SQL Error when using VB.NET 2005 |
| Author |
Message |
|
|
| Posted : 2007-12-12 11:01:02 |
|
I'm currently evaluating the QODBC trial version with Quick Books Enterprise V8. I'm trying to develop code using VB.NET 2005 with .NET version 2.0. I'm using the Microsoft.Practices.EnterpriseLibrary.Data best practices objects to send SQL commands to the database. This is a setup I've used for several projects using MS Access and MS SQL Server databases.
I'm having good luck with my query sql commands, but a simple update query bombs with a 'Driver Not Capable' error.
The sql statement is this:
Update Customer Set CompanyName='Boland Consulting' Where FullName='Catherine Boland'
My connection string looks like this:
<add name="QB_Data" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;DSN=QuickBooks"
providerName="System.Data.Odbc" />
My code looks like this:
Public Function ExecuteSQL(ByVal strSQL As String, ByRef strError As String) As Boolean
Dim result As Boolean = False
' Two operations, one to credit an account, and one to debit another
' account.
Dim dbCommand As DbCommand = m_db.GetSqlStringCommand(strSQL)
Using connection As DbConnection = m_db.CreateConnection()
connection.Open()
Dim transaction As DbTransaction = connection.BeginTransaction()
Try
' execute command
m_db.ExecuteNonQuery(dbCommand, transaction)
' Commit the transaction
transaction.Commit()
result = True
Catch ex As Exception
' Rollback transaction
transaction.Rollback()
strError = ex.Message
End Try
connection.Close()
Return result
End Using
End Function
This update query works fine if I import the QB data into MS Access using the QuickBooks DSN I set up with QODBC. So what gives?
|
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-12-12 12:05:51 |
Check that you are not running QODBC still in 2.1 ODBC Compatibility Mode. This must be unchecked in the QODBC Advanced Setup screen.
Check the Simulate Transaction Support for SQL Server option if you must use transaction.Commit().
Also, the Provider Properties should be for MSDASQL (Microsoft OLE DB Provider for ODBC Drivers), not Microsoft.Jet.OLEDB. |
|
|
|
|
|
| Posted : 2007-12-12 12:26:53 |
Ok, 'Run 2.1 ODBC Compatibility' was checked and I changed my connection string to:
<add name="QB_Data" connectionString="Provider=MSDASQL;DSN=QuickBooks" providerName="System.Data.Odbc" />
I'm still getting this exception:
ERROR [IM001] [QODBC] Driver not capable
|
|
|
|
|
|
| Posted : 2007-12-12 12:36:46 |
| for some reason QODBC doesn't like the command being executed within a transaction. If I comment out the transaction code and send out a straight
m_db.ExecuteNonQuery(dbCommand) command, it is happy.
Why would that be?
|
|
|
|
|
|
| Posted : 2007-12-12 12:40:05 |
ok, I suppose it would help if I actually read your entire post where you said: "Check the Simulate Transaction Support for SQL Server option if you must use transaction.Commit()."
Duh. It works fine now, thanks. |
|
|
|
|