Try adding a new data source to your project by selecting
- Data - Add New Data Source
- Selecting Database and Next
- Clicking on the New Connection... button
- Selecting Data Source: Microsoft ODBC Data Source and clicking Continue
- Then selecting Use user or system data source name: QuickBooks Data
- Then press Test Connection
But you might get an error message from Microsoft Visual Studio that reads:
You can connect to the ODBC data source using the OdbcConnection class (http://msdn2.microsoft.com/en-us/library/system.data.odbc.odbcconnection.aspx).
The following code is the minimal code required to create a DataSet from the SQL statement and attaches the dataset to a DataGrid:
Dim cnQODBC As System.Data.Odbc.OdbcConnection Dim daQODBC As System.Data.Odbc.OdbcDataAdapter Dim dsQODBC As System.Data.DataSet
cnQODBC = New System.Data.Odbc.OdbcConnection("DSN=QuickBooks Data") cnQODBC.Open() daQODBC = New System.Data.Odbc.OdbcDataAdapter("SELECT ListID, FullName, CompanyName FROM Customer", cnQODBC) dsQODBC = New System.Data.DataSet
daQODBC.Fill(dsQODBC) dgDataGrid.DataSource = dsQODBC dgDataGrid.DataBind() 'May or maynot be required depending on where you put this code.
See also: Do you have any sample code for VB.NET? Problem using Visual Studio 2005
See also the msdn .NET Framework Developer Center: http://msdn2.microsoft.com/en-us/library/system.stathreadattribute.aspx
Note: ADODB works, but when you loop through the resultant recordset via Recordset.MoveNext(), an additional query is made through QODBC for each record. These additional queries caused an additional 50 minutes of runtime for a 7000 invoice query.
The first function below demonstrates the ADODB method that took ~1 hour. The second does the same thing with ODBC and takes less than a minute.
[STAThread] static void test1() { ADODB.Connection con = new ADODB.Connection(); con.Open("DSN=SOQB;OLE DB Services=-2", "", "", -1); string invoiceSQL = "SELECT CustomerRefFullName, RefNumber, TxnDate, BalanceRemaining, AppliedAmount, Memo " + "FROM Invoice " + "WHERE TxnDate>{d'2006-04-02'}"; ADODB.Recordset invoiceResult = new ADODB.Recordset(); invoiceResult.Open(invoiceSQL, con, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, 0); while(!invoiceResult.EOF) { Console.WriteLine("Invoice #" + invoiceResult.Fields["RefNumber"].Value.ToString()); invoiceResult.MoveNext(); } con.Close(); }
[STAThread] static void test2() { OdbcConnection con = new OdbcConnection("DSN=SOQB"); con.Open(); OdbcDataAdapter dAdapter = new OdbcDataAdapter( "SELECT CustomerRefFullName, RefNumber, TxnDate, BalanceRemaining, AppliedAmount, Memo " + "FROM Invoice " + "WHERE TxnDate>{d'2006-04-02'}", con); DataTable result = new DataTable(); dAdapter.Fill(result); DataTableReader reader = new DataTableReader(result); while(reader.Read()){ Console.WriteLine("Invoice #: " + reader.GetString(1)); } con.Close(); }
|