Deactivate QODBC Online from your computer

You can deactivate the QODBC Online license from:

Start >> All Programs >> QODBC Driver for QuickBooks Online>> Configure QODBC Data Source

Go To "System DSN" Tab>> Select "QuickBooks Online Data">> click "Configure"

Switch to the Activate/Deactivate tab >> click "Deactivate."

     Enter your 16-digit CD Key (with the dashes) and click on the Deactivate button.

Manual Deactivation

Note: Manual Deactivation can be used for computers that do not have direct Internet access or where the automated deactivation fails.

      Copy the information from your computer screen (not the example shown here) into the image directly below and click on Process to generate a code to deactivate QODBC Online. Click on the Copy Information to the Clipboard button to save your CD Key.

      If you have correctly entered your CD Key, Request Code, and Software ID, your deactivation will now be processed. Click on the Manual button on the computer that has QODBC Online installed and copy and enter your Serial Number, Name, Request Code, and Deactivation Code from the above image, and click on the Deactivate button when done.

     If you have entered the information incorrectly, you will receive a "CD Key not found" message.

     Clicking on the Deactivate button will generate a verification number.

     Copy the information from your computer screen (not the example shown here) into the image directly below and click on Process to confirm your deactivation of QODBC Online.

     Once you process your verification number, your copy of QODBC Online will then be successfully deactivated on your computer so that you can transfer the license to your new computer! See: How to activate QODBC Online to activate the license on your new computer.

     Click Cancel to exit the QODBC Online Setup screen.

Note: Using the latest version of QODBC, credit for deactivation CDKEY is received instantly. In some cases, deactivation may take between 24 and 48 hours, depending on your region and license pack.

How to extract sp_reports using Microsoft Excel

Open Microsoft Query in Excel

Open Microsoft Excel, get external data from Microsoft Query via Data Menu -> "Get Data -> From Other Sources->From Microsoft Query" as below:

Note: In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab.
Please refer to Troubleshooting - How to enable Microsoft Excel 365 - Legacy Microsoft Query.

It will take some time to get the DSN list:

In the "Choose Data Source" Window, Select the QuickBooks Data DSN you set up with the QODBC driver. This is one of our pre-installed DSN names or one that you have created.

Note: Uncheck option "Use Query Wizard to Created/edit Queries" since we do not need to import any tables here.

Note: Youed to Select the QuickBooks Online Data DSN.

C for using QuickBooks Online, reportlose the "Add Tables" window by clicking the "Close" button.

Press the "SQL" button to input your sp_report query as below. Here we take a BalanceSheetDetail report as an example:

sp_report BalanceSheetDetail parameters DateMacro = 'ThisMonthToDate'

Click the "OK" button to close the warning message:

Press Execute button to get results in Microsoft Query:

Return Records to Excel Spreadsheet

Select Menu File -> Return Data to Microsoft Excel to return records to Excel Spreadsheet.

Also, Refer:

How to use the QuickBooks Reporting Engine with QODBC

How to use the QuickBooks Reporting Engine with QODBC Online

Tags: QuickBooks Online, QBO, Excel, MS Query, QODBC Online, Sp_reports, Balance sheet detail

Purchase QODBC Online CDKEY

     When you first install QODBC Online, it automatically activates a "once-off" FREE 30-Day Evaluation... beyond which QODBC Online will stop working, and you will need to purchase a CDKEY. Once you have received your CDKEY, click on Start -> All Programs -> locate the QODBC Driver for QuickBooks Online program group on your desktop. Click on the Configure QODBC Online Data Source icon to launch the setup program.

     Select the Activate/Deactivate tab page in the QODBC Online Setup screen and click on the Activate button.

     Enter your 16-digit CDKEY (with the - dashes) and click on the Activate button.

    Activation Successful.

Manual Activation

     Manual activation can be used for computers that do not have direct Internet access or where the automated activation fails.

     Click on the Manual button on the computer that has QODBC Online installed and copy and enter your Serial Number, Name, Request Code, and Activation Code from the above image and click on the Activate button when done. Copy the information from your computer screen into the image directly below and click on Process to generate a code to activate QODBC Online. Click on the Copy button to save your CDKEY.

     Your activation will now be processed if you have correctly entered your CDKEY, Request Code, and Software ID., If you have entered the information incorrectly or have exceeded your purchased quantity of license seats, you will receive a "CDKEY not found" message.

     Your copy of QODBC Online is now successfully activated! Click OK to continue.

Tags: QuickBooks Online, QBO, QODBC Online, Online Activation

Sample Application:

Please click here to download Sample Code.

Please refer below steps for using an application for Inserting InvoiceLine to existing Invoice using C#.NET.

Run the application & click on the "Connect" button.

The application is connected with QuickBooks Online.

The application has two functionality:

1. Append the existing Invoice with a new Description Line which will add a new Description Line to the existing Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice & description which you want to enter and click on the "Insert New Invoice Line (Description Only)" button.

New Description Line is added to the existing Invoice.

Result in QuickBooks Online.

2. Append the existing Invoice with a new ItemInventory/ItemService Line, adding a new ItemInventory/ItemService Line to the existing Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice, the Item Full Name, Quantity, Rate & Description which you want to enter and click on the "Insert New Invoice Line (Inventory/Service)" button.

New Item Line is added to the existing Invoice.

Result in QuickBooks Online.

Application Source Code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Drawing;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : Page
{
    private static OdbcConnection _cn;
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            //ListOutDSN();
            lblErrorMessage.Visible = false;
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {

        }
    }
  /*  private void ListOutDSN()
    {
        cboListOfDSN.Items.Clear();

        var myODBCKeys = Microsoft.Win32.Registry.LocalMachine.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources", false).GetValueNames();

        foreach (var item in myODBCKeys)
        {
            cboListOfDSN.Items.Add(item);
        }
        myODBCKeys = Microsoft.Win32.Registry.CurrentUser.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources", false).GetValueNames();
        foreach (var item in myODBCKeys)
        {
            cboListOfDSN.Items.Add(item);
        }
        //end of adding DSN to list

        if (cboListOfDSN.Items.Count > 0)
        {
            foreach (ListItem item in cboListOfDSN.Items)
            {
                if (item.Text == "QuickBooks Data")
                {
                    cboListOfDSN.Text = item.Text;
                    return;
                }
                if (item.Text == "QuickBooks Data POS")
                {
                    cboListOfDSN.Text = item.Text;
                    return;
                }
                if (item.Text == "QuickBooks Data Online")
                {
                    cboListOfDSN.Text = item.Text;
                    return;
                }
            }
            cboListOfDSN.SelectedIndex = 0;
        }
    }*/

    protected void btnConnet_Click(object sender, EventArgs e)
    {
        try
        {
            if (btnConnect.Text == "Disconnect")
            {
                if (_cn != null)
                {
                    lblConnectionStatus.Text = "Disconnecting....";
                    _cn.Close();
                    _cn.Dispose();
                    _cn = null;
                    btnConnect.Text = "Connect";
                    lblConnectionStatus.Text = "Not Connected";
                    lblConnectionStatus.ForeColor = Color.DarkRed;
                }
            }
            else
            {
                if (_cn == null || _cn.State == ConnectionState.Closed)
                {
                    lblConnectionStatus.Text = "Connecting....";
                    _cn = new OdbcConnection(string.Format("DSN={0}", cboListOfDSN.Text));
                    _cn.ConnectionTimeout = 60;
                    _cn.Open();
                    btnConnect.Text = "Disconnect";
                    lblConnectionStatus.Text = "Connected";
                    lblConnectionStatus.ForeColor = Color.Green;
                }

            }

        }
        catch (Exception ex)
        {
            btnConnect.Text = "Errorred";
            lblErrorMessage.Text = string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace);
            lblErrorMessage.Visible = true;
            //MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));            
        }
    }
    protected void btnInsertInvoiceDescription_Click(object sender, EventArgs e)
    {
        try
        {
            if (_cn == null || _cn.State == ConnectionState.Closed)
            {
                btnConnet_Click(null, null);
                btnConnect.Text = "Connect";
                lblConnectionStatus.Text = "NotConnected";
                lblConnectionStatus.ForeColor = Color.DarkRed;
            }
            if (string.IsNullOrEmpty(txtDInvoiceNo.Text))
            {
                lblErrorMessage.Text = "Invoice Refer Number is required";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("Invoice Refer Number is required");
                return;
            }
            if (string.IsNullOrEmpty(txtDDescription.Text))
            {
                lblErrorMessage.Text = "New Line Description is required";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("New Line Description is required");
                return;
            }
            InsertInvoiceLineDescriptionOnly(txtDInvoiceNo.Text, txtDDescription.Text);
            DisplayInvoiceInGrid(txtDInvoiceNo.Text);
        }
        catch (Exception ex)
        {
            lblErrorMessage.Text = string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace);
            lblErrorMessage.Visible = true;
            //MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));
        }
    }
    protected void btnInvoiceInsertItem_Click(object sender, EventArgs e)
    {
        try
        {
            if (_cn == null || _cn.State == ConnectionState.Closed)
            {
                btnConnet_Click(null, null);
                btnConnect.Text = "Connect";
                lblConnectionStatus.Text = "NotConnected";
                lblConnectionStatus.ForeColor = Color.DarkRed;
            }
            if (string.IsNullOrEmpty(txtIInvoiceNo.Text))
            {
                lblErrorMessage.Text = "Invoice Refer Number is required";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("Invoice Refer Number is required");
                return;
            }
            if (string.IsNullOrEmpty(txtIItemFullName.Text))
            {
                lblErrorMessage.Text = "New Line Item Full Name is required";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("New Line Item Full Name is required");
                return;
            }
            if (string.IsNullOrEmpty(txtIQuantity.Text))
            {
                lblErrorMessage.Text = "Invoice Refer Quantiy is required";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("Invoice Refer Quantiy is required");
                return;
            }
            if (string.IsNullOrEmpty(txtIRate.Text))
            {
                lblErrorMessage.Text = "New Line Rate is required";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("New Line Rate is required");
                return;
            }
            InsertInvoiceLineItem(txtIInvoiceNo.Text, txtIItemFullName.Text, int.Parse(txtIQuantity.Text), int.Parse(txtIRate.Text), txtIDescription.Text);
            DisplayInvoiceInGrid(txtIInvoiceNo.Text);
        }
        catch (Exception ex)
        {
            lblErrorMessage.Text = string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace);
            lblErrorMessage.Visible = true;
            //MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));
        }
    }

        private void InsertInvoiceLineDescriptionOnly(string invoiceRefNumber, string newItemDescription)
        {
            OdbcDataReader QBdrdr;
            string txnID = string.Empty;
            string query = string.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber);
            //select the invoice and get the txnid from the existing Invoice.
            using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
            {
                QBEmployeecmd.CommandType = CommandType.Text;

                QBdrdr = QBEmployeecmd.ExecuteReader();


                if (QBdrdr.HasRows == true)
                {
                    txnID = QBdrdr["txnid"].ToString();

                }
                else
                {
                    txnID = string.Empty;
                }
                QBdrdr.Close();

            }
            if (!string.IsNullOrEmpty(txnID))
            {
                query = string.Format("Insert into invoiceline(txnid,InvoiceLineDesc) values('{0}','{1}') ", txnID, newItemDescription);
                using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
                {
                    QBEmployeecmd.CommandType = CommandType.Text;
                    QBEmployeecmd.ExecuteNonQuery();

                    lblErrorMessage.Text = "Insert Successfully(Description Only)";
                    lblErrorMessage.Visible = true;
                }
            }
            else
            {
                lblErrorMessage.Text = "No Invoice Found";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("No Invoice Found");
            }

        }


        private void InsertInvoiceLineItem(string invoiceRefNumber, string itemFullName, int quanity, int rate, string description)
        {
            OdbcDataReader QBdrdr;
            string txnID = string.Empty;
            string query = string.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber);
            //select the invoice and get the txnid from the existing Invoice.
            using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
            {
                QBEmployeecmd.CommandType = CommandType.Text;

                QBdrdr = QBEmployeecmd.ExecuteReader();


                if (QBdrdr.HasRows == true)
                {
                    txnID = QBdrdr["txnid"].ToString();

                }
                else
                {
                    txnID = string.Empty;
                }
                QBdrdr.Close();

            }
            if (!string.IsNullOrEmpty(txnID))
            {
                query = string.Format("Insert into invoiceline(txnid,InvoiceLineItemRefFullName, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineDesc) values('{0}','{1}',{2},{3},'{4}') ", txnID, itemFullName, quanity, rate, description);
                using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
                {
                    QBEmployeecmd.CommandType = CommandType.Text;
                    QBEmployeecmd.ExecuteNonQuery();
                    lblErrorMessage.Text = "Insert Successfully (Inventory/Service)";
                    lblErrorMessage.Visible = true;
                }
            }
            else
            {
                lblErrorMessage.Text = "No Invoice Found";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("No Invoice Found");
            }
        }


        private void ProcessQuery(string query)
        {
            var cmd = new OdbcCommand(query, _cn);
            //OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            //adapter.Fill(dataSet);
            OdbcDataReader reader = cmd.ExecuteReader();
            //if (reader.HasRows)
            {
                DataTable myTable = new DataTable();
                myTable.Load(reader);
                grvData.AutoGenerateColumns = true;
                grvData.DataSource = myTable;
                grvData.DataBind();

            }

        }

        private void DisplayInvoiceInGrid(string invoiceRefNumber)
        {

            string query = string.Format("select RefNumber,CustomerRefFullName,InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount  from InvoiceLine where RefNumber='{0}'", invoiceRefNumber);
            ProcessQuery(query);
        }
    }

Keywords: sample .net, sample, .net, Dot Net, C#,C#.Net, QuickBooks Online, QBO, QODBC Online, Invoice line

Sample Application:

Please click here to download Sample Code.

Please refer below steps for using an application for Inserting InvoiceLine to existing Invoice using C#.NET.

, You can change DSN as per your configuration. I am connecting to the default DSN QuickBooks Online Data in this example. Run the application, select DSN & click on the "Connect" button. Run the application, select DSN & click on the "Connect" button.

The application connected with QuickBooks Online.

The application has two functionality:

1. Append the existing Invoice with a new Description Line which will add a new Description Line to the existing Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice & description which you want to enter and click on the "Insert New Invoice Line (Description Only)" button.

New Description Line is added to the existing Invoice.

Result in QuickBooks Online.

2. Append the existing Invoice with a new ItemInventory/ItemService Line, adding a new ItemInventory/ItemService Line to the existing  Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice, the Item Full Name, Quantity, Rate & Description which you want to enter and click on the "Insert New Invoice Line (Inventory/Service)" button.

New Item Line is added to the existing Invoice.

Result in QuickBooks Online.

Application Source Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Odbc;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace QODBC_Invoice_Insert_Sample
{
    public partial class Form1 : Form
    {
        private OdbcConnection _cn;
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                ListOutDSN();
                Application.DoEvents();
            }
            catch (Exception ex)
            {

                MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));
            }
            
        }

        private void ListOutDSN()
        {
            cboListOfDSN.Items.Clear();

            var myODBCKeys = Microsoft.Win32.Registry.LocalMachine.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources", false).GetValueNames();

            foreach (var item in myODBCKeys)
            {
                cboListOfDSN.Items.Add(item);
            }
            myODBCKeys = Microsoft.Win32.Registry.CurrentUser.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources", false).GetValueNames();
            foreach (var item in myODBCKeys)
            {
                cboListOfDSN.Items.Add(item);
            }
            //end of adding DSN to list

            if (cboListOfDSN.Items.Count > 0)
            {
                foreach (string item in cboListOfDSN.Items)
                {
                    if (item == "QuickBooks Data")
                    {
                        cboListOfDSN.Text = item;
                        return;
                    }
                    if (item == "QuickBooks Data POS")
                    {
                        cboListOfDSN.Text = item;
                        return;
                    }
                    if (item == "QuickBooks Data Online")
                    {
                        cboListOfDSN.Text = item;
                        return;
                    }
                }
                cboListOfDSN.SelectedIndex = 0;
            }



        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            try
            {

                Application.DoEvents();
            if (btnConnect.Text == "Disconnect")
            {
                if (_cn != null)
                {
                    lblConnectionStatus.Text = "Disconnecting....";
                    _cn.Close();
                    _cn.Dispose();
                    _cn = null;
                    btnConnect.Text = "Connect";
                    lblConnectionStatus.Text = "Not Connected";
                    lblConnectionStatus.ForeColor = Color.DarkRed;
                }
            }
            else
            {
                if (_cn == null || _cn.State == ConnectionState.Closed)
                {
                    lblConnectionStatus.Text = "Connecting....";
                    _cn = new OdbcConnection(string.Format("DSN={0}", cboListOfDSN.Text));
                    _cn.ConnectionTimeout = 60;
                    _cn.Open();
                    btnConnect.Text = "Disconnect";
                    lblConnectionStatus.Text = "Connected";
                    lblConnectionStatus.ForeColor = Color.Green;
                }

            }
            Application.DoEvents();
            }
            catch (Exception ex)
            {
                Application.DoEvents();
                btnConnect.Text = "Errorred";
                MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));
            }

        }

        private void btnInsertInvoiceDescription_Click(object sender, EventArgs e)
        {
            try
            {
                if (_cn == null || _cn.State == ConnectionState.Closed)
                {
                    btnConnect_Click(null, null);
                }
                if (string.IsNullOrEmpty(txtDInvoiceNo.Text))
                {
                    MessageBox.Show("Invoice Refer Number is required");
                    return;
                }
                if (string.IsNullOrEmpty(txtDDescription.Text))
                {
                    MessageBox.Show("New Line Description is required");
                    return;
                }
                InsertInvoiceLineDescriptionOnly(txtDInvoiceNo.Text, txtDDescription.Text);
                DisplayInvoiceInGrid(txtDInvoiceNo.Text);
            }
            catch (Exception ex)
            {

                MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));
            }
           
           
        }

        private void DisplayInvoiceInGrid(string invoiceRefNumber)
        {

            string query = string.Format("select RefNumber,CustomerRefFullName,InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount  from InvoiceLine where RefNumber='{0}'", invoiceRefNumber);
            ProcessQuery(query);
        }

        private void InsertInvoiceLineDescriptionOnly(string invoiceRefNumber, string newItemDescription)
        {
            OdbcDataReader QBdrdr;
            string txnID = string.Empty;
            string query = string.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber);
            //select the invoice and get the txnid from the existing Invoice.
            using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
            {
                QBEmployeecmd.CommandType = CommandType.Text;

                QBdrdr = QBEmployeecmd.ExecuteReader();


                if (QBdrdr.HasRows == true)
                {
                    txnID = QBdrdr["txnid"].ToString();

                }
                else
                {
                    txnID = string.Empty;
                }
                QBdrdr.Close();

            }
            if (!string.IsNullOrEmpty(txnID))
            {
                query = string.Format("Insert into invoiceline(txnid,InvoiceLineDesc) values('{0}','{1}') ", txnID, newItemDescription);
                using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
                {
                    QBEmployeecmd.CommandType = CommandType.Text;
                    QBEmployeecmd.ExecuteNonQuery();

                }
            }
            else
            {
                MessageBox.Show("No Invoice Found");
            }

        }

        private void btnInvoiceInsertItem_Click(object sender, EventArgs e)
        {
            try
            {
                if (_cn == null || _cn.State == ConnectionState.Closed)
                {
                    btnConnect_Click(null, null);
                }
                if (string.IsNullOrEmpty(txtIInvoiceNo.Text))
                {
                    MessageBox.Show("Invoice Refer Number is required");
                    return;
                }
                if (string.IsNullOrEmpty(txtIItemFullName.Text))
                {
                    MessageBox.Show("New Line Item Full Name is required");
                    return;
                }
                if (string.IsNullOrEmpty(txtIQuantity.Text))
                {
                    MessageBox.Show("Invoice Refer Quantiy is required");
                    return;
                }
                if (string.IsNullOrEmpty(txtIRate.Text))
                {
                    MessageBox.Show("New Line Rate is required");
                    return;
                }
                InsertInvoiceLineItem(txtIInvoiceNo.Text, txtIItemFullName.Text, int.Parse(txtIQuantity.Text), int.Parse(txtIRate.Text), txtIDescription.Text);
                DisplayInvoiceInGrid(txtIInvoiceNo.Text);
            }
            catch (Exception ex)
            {

                MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));
            }
           
        }

        private void InsertInvoiceLineItem(string invoiceRefNumber, string itemFullName, int quanity, int rate, string description)
        {
            OdbcDataReader QBdrdr;
            string txnID = string.Empty;
            string query = string.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber);
            //select the invoice and get the txnid from the existing Invoice.
            using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
            {
                QBEmployeecmd.CommandType = CommandType.Text;

                QBdrdr = QBEmployeecmd.ExecuteReader();


                if (QBdrdr.HasRows == true)
                {
                    txnID = QBdrdr["txnid"].ToString();

                }
                else
                {
                    txnID = string.Empty;
                }
                QBdrdr.Close();

            }
            if (!string.IsNullOrEmpty(txnID))
            {
                query = string.Format("Insert into invoiceline(txnid,InvoiceLineItemRefFullName, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineDesc) values('{0}','{1}',{2},{3},'{4}') ", txnID, itemFullName, quanity, rate, description);
                using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
                {
                    QBEmployeecmd.CommandType = CommandType.Text;
                    QBEmployeecmd.ExecuteNonQuery();

                }
            }
            else
            {
                MessageBox.Show("No Invoice Found");
            }
        }

        private void ProcessQuery(string query)
        {
            var cmd = new OdbcCommand(query, _cn);
            //OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            //adapter.Fill(dataSet);
            OdbcDataReader reader = cmd.ExecuteReader();
            //if (reader.HasRows)
            {
                DataTable myTable = new DataTable();
                myTable.Load(reader);
                grvData.AutoGenerateColumns = true;
                grvData.DataSource = myTable;
                
            }

        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (_cn != null && _cn.State != ConnectionState.Closed)
            {
                _cn.Close();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            this.Close();
        }

    }
}

Keywords: sample .net, sample, .net, Dot Net, C#,C#.Net

Sample Application:

Please click here to download Sample Code.

Please refer below steps for using an application for Inserting InvoiceLine to existing Invoice using PHP.

Run application.

The application has two functionality:

1. Append the existing Invoice with a new Description Line which will add a new Description Line to the existing Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice & description which you want to enter and click on the "Insert New Invoice Line (Description Only)" button.

New Description Line is added to the existing Invoice.

Result in QuickBooks Online.

2. Append the existing Invoice with a new ItemInventory/ItemService Line, adding a new ItemInventory/ItemService Line to the existing Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice, the Item Full Name, Quantity, Rate & Description which you want to enter and click on the "Insert New Invoice Line (Inventory/Service)" button.

New Item Line is added to the existing Invoice.

Result in QuickBooks Online.

Application Source Code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
    <title>QODBC PHP Script To Display SQL Results</title>

</head>
<body topmargin="3" leftmargin="3" marginheight="0" marginwidth="0" bgcolor="#ffffff" link="#000066" vlink="#000000" alink="#0000ff" text="#000000">


<table>
	<tr>
		<td>
			Append existing Invoice with a new Description line
			<form name="frmDescriptionLine" id="frmDescriptionLine" method="post">
			<table>
				<tr>
					<td>Enter Invoice No. (RefNumber)</td>
					<td><input type="text" name="txtInvoiceNo" id="txtInvoiceNo" style="width:250px;"/></td>
				</tr>
				<tr>
					<td>Enter New Item Description</td>
					<td><input type="text" name="txtDescription" id="txtDescription"style="width:250px;"/></td>
				</tr>
				<tr>
					<td colspan="2"><input type="submit" name="btnDescriptionLine" id="btnDescriptionLine" value="Insert New Invoice Line (Description Only)" onclick="InsertRecord()"/></td>
				</tr>
				</table>
			</form>
		</td>		
		<td>
			Append existing Invoice with a new  ItemInventory/ItemService Line
			<form name="frmItemLine" id="frmItemLine" method="post">
			<table>
				<tr>
					<td>Enter Invoice No. (RefNumber)</td>
					<td><input type="text" name="txtItemInvoiceNo" id="txtItemInvoiceNo" style="width:250px;"/></td>
				</tr>
				<tr>
					<td>Enter Item FullName</td>
					<td><input type="text" name="txtItemRefDescription" id="txtItemRefDescription" style="width:250px;"/></td>
				</tr>
				<tr>
					<td>Quantity</td>
					<td><input type="text" name="txtItemQuantity" id="txtItemQuantity" value="1"/></td>					
				</tr>
				<tr>
				<td>Rate</td>
				<td> <input type="text" name="txtItemRate" id="txtItemRate" value="1"/></td>
				</tr>
				<tr>
					<td>Enter Description</td>
					<td><input type="text" name="txtItemDescription" id="txtItemDescription" style="width:250px;"/></td>
				</tr>
				<tr>
					<td colspan="2"><input type="submit" name="btnItemLine" id="btnItemLine" value="Insert Invoice Line (Inventory/Service)" /></td>
				</tr>	
				</table>
			</form>
		</td>
	</tr>
</table>


    
</body>
</html>



<?php
if(isset($_POST['btnDescriptionLine']))
{

	$invoiceNo= $_POST['txtInvoiceNo'];
	$invoiceDescr = $_POST['txtDescription'];	
	if($invoiceNo == "" || $invoiceDescr == "" )
	{
		echo '<script language="javascript">';
		echo 'alert("Enter Invoice No. and Invoice Line Description both.")';
		echo '</script>';
	}
	else
	{
		set_time_limit(120);
		$oConnect = odbc_connect("QuickBooks Online Data QRemote", "", "");
		$sSQL = "select txnid from InvoiceLine where RefNumber='$invoiceNo'";
		$oResult = odbc_exec($oConnect, $sSQL);
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);
		while ($lFldCnt < $lFieldCount) {
		$lFldCnt++;
        $sFieldName = odbc_field_name($oResult, $lFldCnt);
		//print("                        $sFieldName\n");
		}		
		$lRecCnt = 0;
		while(odbc_fetch_row($oResult)) {
		$lRecCnt++;		
		//print("$lRecCnt");
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);		
		while ($lFldCnt < $lFieldCount) {
			$lFldCnt++;
			$sFieldValue = trim(odbc_result($oResult, $lFldCnt));
			If ($sFieldValue == "") {
				print("\n");
			}
			else {
				//print("$sFieldValue\n");
			}
		}
		//print("\n");
		}				
		$sSQL = "Insert into invoiceline(txnid,InvoiceLineDesc) values('$sFieldValue','$invoiceDescr')";
		$oResult = odbc_exec($oConnect, $sSQL);
		//$sSQL = "SELECT * FROM InvoiceLine Where txnid='$sFieldValue'";
		$sSQL = "SELECT RefNumber,CustomerRefFullName,InvoiceLineItemRefFullName,InvoiceLineDesc,InvoiceLineRate,InvoiceLineQuantity,InvoiceLineAmount  FROM InvoiceLine Where txnid='$sFieldValue'";
		#Perform the query
		$oResult = odbc_exec($oConnect, $sSQL);
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);
		//print("$lFieldCount");
		print("<table border=\"1\">");
		print("<th>Line No.</th>\n");
		while ($lFldCnt < $lFieldCount) {
			$lFldCnt++;
				$sFieldName = odbc_field_name($oResult, $lFldCnt);
			print("<th>$sFieldName</th>\n");
		}
		
		
				$lRecCnt = 0;
		#Fetch the data from the database
		while(odbc_fetch_row($oResult)) {
			$lRecCnt++;
			print("                    <tr>\n");
			print("                        <td>$lRecCnt</td>\n");
			$lFldCnt = 0;
			$lFieldCount = odbc_num_fields($oResult);
			while ($lFldCnt < $lFieldCount) {
				$lFldCnt++;
				$sFieldValue = trim(odbc_result($oResult, $lFldCnt));
				If ($sFieldValue == "") {
					print("<td> </td>\n");
				}
				else {
					print("<td valign=\"Top\">$sFieldValue</td>\n");
				}
			}
			print("</tr>\n");
		}
		print("</table>");
		
		odbc_close($oConnect);
				//echo("Invoice No: " .  $invoiceNo. "<br />\n");
				//echo("Invoice Desc: " . $invoiceDescr. "<br />\n");	
			}
}


if(isset($_POST['btnItemLine']))
{
	$invoiceItemNo= $_POST['txtItemInvoiceNo'];
	$invoiceItemRef = $_POST['txtItemRefDescription'];
	$invoiceItemQuantity = $_POST['txtItemQuantity'];
	$invoiceItemRate = $_POST['txtItemRate'];
	$invoiceItemDescr = $_POST['txtItemDescription'];	
	if($invoiceItemNo == "" || $invoiceItemDescr == "" || $invoiceItemRef == "" || $invoiceItemQuantity =="" || $invoiceItemRate =="" )
	{
		echo '<script language="javascript">';
		echo 'alert("Fill the Details properly")';
		echo '</script>';
	}
	else
	{		
		set_time_limit(120);
		$oConnect = odbc_connect("QuickBooks Online Data QRemote", "", "");
		$sSQL = "select txnid from InvoiceLine where RefNumber='$invoiceItemNo'";
		$oResult = odbc_exec($oConnect, $sSQL);
		//echo $oResult;
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);
		while ($lFldCnt < $lFieldCount) {
		$lFldCnt++;
        $sFieldName = odbc_field_name($oResult, $lFldCnt);		
		}		
		$lRecCnt = 0;
		while(odbc_fetch_row($oResult)) {
		$lRecCnt++;		
		//print("$lRecCnt");
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);		
		while ($lFldCnt < $lFieldCount) {
			$lFldCnt++;
			$sFieldValue = trim(odbc_result($oResult, $lFldCnt));
			If ($sFieldValue == "") {
				print("\n");
			}
			else {
				//print("$sFieldValue\n");
			}
		}
		print("\n");
		}		
		
		$sSQL = "Insert into invoiceline(txnid,InvoiceLineItemRefFullName, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineDesc) values('$sFieldValue','$invoiceItemRef',$invoiceItemQuantity,$invoiceItemRate,'$invoiceItemDescr')";
		/*if($oResult = odbc_exec($oConnect, $sSQL)){
			echo '<script language="javascript">';
			echo 'alert("Success")';
			echo '</script>';
		}
		else
		{
			echo $oResult; exit();
		}*/		
		//print($oResult);
		$oResult = odbc_exec($oConnect, $sSQL);
		
		$sSQL = "SELECT RefNumber,CustomerRefFullName,InvoiceLineItemRefFullName,InvoiceLineDesc,InvoiceLineRate,InvoiceLineQuantity,InvoiceLineAmount FROM InvoiceLine Where txnid='$sFieldValue'";
		//$sSQL = "SELECT * FROM InvoiceLine Where txnid='$sFieldValue'";
		#Perform the query
		$oResult = odbc_exec($oConnect, $sSQL);
		
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);
		//print("$lFieldCount");
		print("<table border=\"1\">");
		print("<th>Line No.</th>\n");
		while ($lFldCnt < $lFieldCount) {
			$lFldCnt= $lFldCnt+1;
				$sFieldName = odbc_field_name($oResult, $lFldCnt);
			print("<th>$sFieldName</th>\n");
		}
				$lRecCnt = 0;
		#Fetch the data from the database
		while(odbc_fetch_row($oResult)) {
			$lRecCnt++;
			print("<tr>\n");
			print("<td>$lRecCnt</td>\n");
			$lFldCnt = 0;
			$lFieldCount = odbc_num_fields($oResult);
			while ($lFldCnt < $lFieldCount) {
				$lFldCnt++;
				$sFieldValue = trim(odbc_result($oResult, $lFldCnt));
				If ($sFieldValue == "") {
					print("<td> </td>\n");
				}
				else {
					print("<td valign=\"Top\">$sFieldValue</td>\n");
				}
			}
			print("</tr>\n");
		}
		print("</table>");
		
		odbc_close($oConnect);
				//echo("Invoice No: " .  $invoiceNo. "<br />\n");
				//echo("Invoice Desc: " . $invoiceDescr. "<br />\n");	
			}
}


?>	

Tags: QuickBooks Online, QBO, QODBC Online, PHP

Installation Steps for Windows 10

After you have downloaded QODBC Online Installer, navigate to the “Downloads” Folder or the location where you have saved QODBC Online Installer. Run the installer as administrator by clicking on the installer and selecting "Run As Administrator."  Don't forget to read the "Readme" file during installation for the latest information on QODBC Online setup, new features, and other driver information.

Please close all the applications. It is better to reboot the machine before starting the Installation.  Select the destination directory to install the QODBC support files. Click "OK" to continue.

The FLEXquarters QODBC Driver for QuickBooks Online^® has been successfully installed on your computer. You can begin to use it immediately because it will run as a free 30-day evaluation edition, serving as all editions of QODBC Online, depending on how it is configured.

QODBC Online installed on the Machine                        

To install a full purchase QODBC Online registration code to an evaluation copy from a previous download, see the Icons that have been added to your Start Menu on your Windows 10. i.e., “Purchase QODBC Online Now.”

The first step in configuring the driver is to click on the QODBC Online Setup Screen icon, and the setup screen will become available.

Also, refer to the following:

Connecting QODBC Online Driver to QuickBooks Online for the First Time (Video)

How to use QODBC Online with QuickBooks Online Edition

Tags: QuickBooks Online, QBO, Online installation, QODBC Online, Windows 10

Problem Description:

The QODBC Driver for QuickBooks Online runs differently under Microsoft Vista/W7 than other versions of Windows because of its new strict security settings.

If the Test connection is unsuccessful and shows Connecting to QuickBooks Online failed because of a Windows security issue, you have forced Administrator rights. The setup must be run without "Run As Administrator," and UAC must be turned on to Test the connection.

Solutions:

When you run the QODBC Online Setup screen from ODBC Manager, the Setup Screen runs as "elevated permissions," and QODBC Online runs as "elevated permissions."

Follow the below steps to resolve the issue:

Close QODBC Online Setup Screen, Close Data Source Administrator ( if Open).
Click Start -> All Programs -> QODBC Driver for use with QuickBooks Online -> QODBC Online Setup Screen.

Perform Test Connection using the "Connect to QuickBooks" button from QODBC Online Setup Screen

Refer: How to use QODBC Online with QuickBooks Online Edition

Data Source Administrator ( which by default runs as "Run as Administrator" ) If the above does not resolve the "Test Connection" Security issue, please follow the below steps.

Alternative

Use QODBC Online Test Tool or any other application like MS Access, Excel, or your custom application and connect to the DSN; it should get connected ( Even though  "Test Connection to QuickBooks" is not enabled or is having a Windows security issue. )

Refer: Connecting QODBC Online Driver to QuickBooks Online for the First Time (Video)

If QODBC Online Test Tool can connect, you should be able to use QODBC with your application(s).
You can find QODBC Online Test Tool at Windows Start->All Programs->QODBC Driver for use with QuickBooks Online->QODBC Online Test Tool.

If QODBC Online Test Tool can connect, you should be able to use QODBC with your application(s). There is no need to test the connection via QODBC Online Setup Screen once it has passed connect via QODBC Online Test Tool. There is no need to test the connection via QODBC Online Setup Screen once it has been connected via QODBC Online Test Tool. You may ignore the Test Connection windows security message as Test Connection may fail if executed in a different context than a normal user.

Tags: QuickBooks Online, QBO, QODBC Online, Windows Vista, 7, QODBC Online Test Tool

QODBC Online General Setup

     The first step in configuring the driver is to click on the QODBC Online Setup Screen, and the following general setup screen will become available.

Server: Indicates where the QODBC's Connector/SocketReceiver should be running. The default is localhost (i.e., on the same machine)

Port: Indicates which port QODBC and the QODBC's Connector/SocketReceiver should be running. Default is 8383

Data Source Name: This is a display name for your QuickBooks data, such as QuickBooks Data, QuickBooksPRO, My QuickBooks Data, etc. (Default Setting is QuickBooks Online Data).

Import/Export Connection Key: If you want to connect the same company file from another, you can export the connection key from one machine & import it to another.

Sandbox Company (for testing only): used If you want to work with QuickBooks Online sandbox company.

Connect to QuickBooks: used to authorize QODBC Online at QuickBooks Online to access QuickBooks Online company data through QODBC Online.

Test Connection to QuickBooks Online will determine if you have a proper installation and settings with QuickBooks Online.

QODBC Online Messages Setup

     Messages allow you to review message logs from QODBC Online regarding errors and issues communicating with QuickBooks Online, clear these messages, and review messages produced by the QuickBooks Online API interface.

Detail Tracing (Slow) creates a trace log of exactly what QODBC Online is doing in the background with the application and is used for debugging purposes. Ensure the log is turned off when not needed, as these can use up large amounts of disk space.

Display Optimizer Status Panel will display the progress of optimization on the data. This is especially helpful on large data files being reloaded in their entirety.

When selected, the Display Driver Status Panel shows precisely what QODBC Online is doing when accessing the QuickBooks Online data and provides a good assessment of speed using optimized and non-optimized data.

Verbose Optimizer Status Panel causes the Optimizer panel to come up whenever any optimizer happens. The normal checkbox only pops up the optimizer when doing the full update or reloading all.

QODBC Online Optimizer Setup

      Recommended settings for using QODBC Online are shown here; read below for a summary explanation of all available options.

The optimizer brings some data to a local cache to increase query retrieval performance. Check to activate the optimizer.

Optimizer Database Folder specifies where the local data store will be saved on disk. The default location %AppData%\QODBC Driver for QuickBooks Online\Optimizer is under your Windows login account name under your Documents and Settings folder. The data will be stored in a file in this folder with a name similar to the company file name with a .opt extension. If you store that data somewhere other than the default, click the browse button and select the destination folder. With very large company files, we suggest running synchronization after hours.

Keep my optimized data up to date as of when you choose the optimizer to synchronize itself with your Company data. Depending on how static your data need is and the size of the file, you can opt for various time frames of synchronization. The farther down the list you go, the less synchronization will occur, and your queries will run much faster but will be using less current data than the company files.

The start of every query is the default setting using the most current data. Use this default setting if your question requires the latest up-to-the-minute data and speed is not the primary focus. Please remember that the QODBC Optimizer is local to your computer only, so when other users change things in QuickBooks, how does QODBC know? The optimizer will default update new and revised entries in a table from QuickBooks and then execute the query against the locally optimized table. This is faster than reading everything out of QuickBooks every time, especially the more data you have.

The start of every new connection (with "Load Updated Data" first) is used if you don't need to update the optimized data while you are, for example, running queries or importing or exporting data. The optimized information is updated upon a new connection and left updated until the next new connection occurs again.

The start of every new connection (with "Reload All Data" first) is used if you need to rebuild optimized data from scratch before running important queries (like Sales Commission) or importing or exporting data. The optimized data is reconstructed upon a new connection and updated until the next new connection occurs again.

The end of yesterday was used to balance speed and data updating better and would be the best option for most users. It only has to synchronize each table once daily, and the data will be current at the end of the previous day.

The end-of-last-month option is a significant speed boost for running last month's or last year's queries. It does not need to synchronize again to get the data.

The last time I pressed one of the load data button options allowing you to control the synchronized timing. Synchronizing only occurs when you manually run it by pressing the Load Updated Data in the QODBC setup screen.

For balances and stock quantities: the option Nulls - will return NULLs for all fields that cannot be kept in sync, Dirty Reads - means return the most current value that was loaded into the optimizer cache, and Real-Time uses the slower method to get the data directly out of QuickBooks making sure you get the most current value for the requested fields. (Real Time is the previous method used).

Optimizing data after an Insert or Update causes QODBC to do Load Updated Data after an insert or update is executed on a table. Useful for programmers who want to test that their data has successfully been written to QuickBooks.

The multi-Table Sync option is unchecked by default. If checked, the QODBC Online optimizer will use other tables to re-sync tables. However, this will slow down performance. When off, all Multi-Table Sync fields become Real-Time fields.

Load Updated Data: This button is used to Synchronize your optimized data with your company file. This will only load the data that has changed since the last automatic or manual synchronization.

Reload All Data: This button is used to rebuild the optimized data from scratch completely. It is useful when starting a new company if you do not want to synchronize the tables as you first encounter them and ensure that your data is 100% current.

QODBC Online Advanced Setup

     The selected defaults in the Advanced settings should apply to most users and usually don't need to be changed.

Maximum Field Name Length: is set to the QuickBooks Online limitation, but specific applications like Delphi cannot handle this limit and must be set accordingly.

Use Case Sensitive RefNumber Searches, when checked, means a RefNumber of 'ABC' and 'abc' will be different but means QuickBooks can use an index internally for a search instead of a complete table scan. (Note: this feature applies to versions of QuickBooks 2005 and higher)

Include Raw Files, if checked, will display both filtered and raw data formats. QODBC formats data into SQL field types more easily used in programming languages for math and comparison operations. The Raw tables return the data as it is returned to QODBC without modification.

ODBC Compatibility "Default" is 2.1. ODBC compatibility is for specific applications that require ODBC 2.1 Compatibility to use QODBC. Anything that uses the Java ODBC library, like ColdFusion, requires this option turned on, or recordsets will be returned with no row sets or only 1-row sets.

If you use Microsoft Excel Power Query or Microsoft Power BI, change the ODBC Compatibility to 3.8. 

SQL Server Support will turn transaction support on so that SQL Server will allow updates when using QODBC as a linked server. QODBC Online still does not support transactions but simulates the turning on and off of transactions.

The Advanced tab in QODBC Setup has new defaults for Use Iterators and Iterator or Chunk Size fields. This allows you to turn off iterators. This may be needed on systems like MS-SQL Server and Web Servers that use multiple threads. Iterator or Chunk Size determines the size of the row sets that are retrieved from QuickBooks. Set this value high if you are exporting records from QuickBooks. The maximum Iterator or Chunk size that can be set is 100000 (more elevated than that, and it resets itself back to the default of 500).

Iterators and Iterator or Chunk Size fields allow you to turn off iterators. This is needed on MS SQL and Web Servers that use multiple threads.

Text Field Handling has been added to QODBC v7.00.00.204 and later. Convert Fields > 255 to LONG VARCHAR and Limit length to 4096 characters (default): These fields help some development environments get data from extended areas that would not be able to otherwise. Connection string options include ConvertToLongVARCHAR=Yes|No, and MaxLONGVARCHAR=4096.

Convert All Numerics to VARCHARS is helpful for loading tables in Access when Access chooses the wrong size numeric field sizes. Access will give an error "data too large for field DECIMAL." When you get this error, switching this option on will allow all the data to be loaded into Access as strings. The data can be converted as needed by your programs.

Adding XML to the trace log is an advanced option to put more information in the trace log to help.

The support team diagnoses a problem. It should not be used unless directed by the support staff, as this will generate large amounts of data in the trace log.

Rewise XML Response

Activate/Deactivate Tab

This tab allows you to purchase a CDKey, activate a license with the CDKey, or deactivate a currently active license and move that license to a new machine.

Activate is the online option for entering the CDKey issued to you upon purchasing the product to fully activate QODBC Online from the evaluation version to Standard Edition that has been purchased. Suppose you do not have internet access to activate the product. In that case, you can use the phone option to supply your reseller or distributor with the information to activate the product. 

Deactivate is the online method of deactivating a currently registered version of QODBC Online to transfer the product to another machine. Suppose internet access is not available from the registered machine. In that case, you can use the phone option to supply your reseller or distributor with the information to deactivate the product CDKey for you. Using the latest version of QODBC, credit for deactivation CDKEY is received instantly. In some cases, this process will take 24 to 48 hours before your CDKey account is credited with the deactivated license, and you can reactivate the license on a different machine.

About Tab

 
About screen shows the QODBC Online version currently installed, whether it is an evaluation copy or fully registered. The first letter in the serial number denotes what license type is installed, and the 2nd & 3rd digits of the QODBC version. (The serial number may be required for upgrades, rebate offers, technical installation support, or the Help Desk system.)

Check Website for Updates will launch your internet browser to our website and see if you have the latest version of QODBC installed on your machine. You can view the Readme file for the list of all the latest features in the current version of QODBC Online and download the newest updater should you be using an older version of the driver.

Keywords: setup for online, QuickBooks Online, QBO, QODBC Online, setup

Download: QODBC Online Quick Start Guide (PDF) 

Report Displayed in QODBC Online

Stored Procedure Command

      With QODBC, the same report can be generated using stored procedure reports like this:

      sp_report BalanceSheetDetail show RowData, Amount_1 as Amount
      parameters DateMacro = 'ThisYearToDate'

Predefined DateMacro Options

     All the predefined DateMacro options available to you are:

|All|Today|Yesterday|ThisWeek|ThisWeekToDate|LastWeek|LastWeekToDate|NextWeek|NextFourWeeks
|ThisMonth|ThisMonthToDate|LastMonth|LastMonthToDate|NextMonth|ThisQuarter|ThisQuarterToDate
|LastQuarter|LastQuarterToDate|NextQuarter|ThisYear|ThisYearToDate|LastYear|LastYearToDate|NextYear|

Display in QODBC

      sp_report BalanceSheetDetail show RowData, Amount_1 as Jan16,Amount_2 as Feb16, Amount_3 as March16      parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'Month' where RowType='DataRow'

Display in QODBC

Also, Refer

How to use the QuickBooks Reporting Engine with QODBC Online

List of Reports available in QODBC Online

List of Parameters available in BalanceSheetDetail report

Tags: QuickBooks Online, QBO, QODBC Online, report, Balance sheet

Report Displayed in QODBC

Stored Procedure Command

       With QODBC Online, the same report can be generated using stored procedure reports like this:

       sp_report ProfitAndLossStandard show RowData, Amount_1 as Amount
       parameters DateMacro = 'ThisYearToDate'

Predefined DateMacro Options

      All the predefined DateMacro options available to you are:

|All|Today|Yesterday|ThisWeek|ThisWeekToDate|LastWeek|LastWeekToDate|NextWeek|NextFourWeeks
|ThisMonth|ThisMonthToDate|LastMonth|LastMonthToDate|NextMonth|ThisQuarter|ThisQuarterToDate|LastQuarter
|LastQuarterToDate|NextQuarter|ThisYear|ThisYearToDate|LastYear|LastYearToDate|NextYear|

Display in QODBC

Change report to use "Cash" reporting

Stored Procedure Command

      To change these reports to use "Cash" reporting, add the ReportBasis parameter:

      sp_report ProfitAndLossStandard show RowData, Amount_1 as Amount
      parameters DateMacro = 'ThisYearToDate', ReportBasis='Cash'

ReportBasis Options

      The options are: |Cash|Accrual|None|

Typical Profit and Loss Detail Report in QuickBooks Online

Report Displayed in QODBC

Stored Procedure Command

       With QODBC Online, the same report can be generated using stored procedure reports like this:

       sp_report ProfitAndLossDetail show RowData, TransactionType, DocumentNumber, Name, Memo, Split, Amount, Balance
       parameters DateMacro = 'ThisYearToDate'

Predefined DateMacro Options

      All the predefined DateMacro options available to you are:

|All|Today|Yesterday|ThisWeek|ThisWeekToDate|LastWeek|LastWeekToDate|NextWeek|NextFourWeeks
|ThisMonth|ThisMonthToDate|LastMonth|LastMonthToDate|NextMonth|ThisQuarter|ThisQuarterToDate|LastQuarter
|LastQuarterToDate|NextQuarter|ThisYear|ThisYearToDate|LastYear|LastYearToDate|NextYear|

Display in QODBC

Change report to use "Cash" reporting

Stored Procedure Command

      To change these reports to use "Cash" reporting, add the ReportBasis parameter:

      sp_report ProfitAndLossDetail show RowData, TransactionType, DocumentNumber, Name, Memo, Split, Amount, Balance
      parameters DateMacro = 'ThisYearToDate', ReportBasis='Cash'

ReportBasis Options

      The options are: |Cash|Accrual|None| 

Also, Refer

How to use the QuickBooks Reporting Engine with QODBC Online

List of Reports available in QODBC Online

List of Parameters available in ProfitAndLossStandard report

List of Parameters available in ProfitAndLossDetail report

Tags: QuickBooks Online, QBO, QODBC Online, online report, Profit and loss report

     QODBC Online accepts SQL commands through the ODBC Interface, then converts those calls to qbXML navigational orders within the QuickBooks Online API and returns recordsets that qualify for the query results. This driver is not a Client/Server product; it navigates the QuickBooks API directly.

     QODBC also supports extracting all the QuickBooks Online Reports through the ODBC Interface. To know the list of Reports available, use the command SP_REPORTS. 

SP_REPORTS Related

SP_REPORTS

     You can use the new stored procedure SP_REPORTS to list all the available sp_reports in QuickBooks.

SP_REPORTCOLUMNS

     You can also see all the columns available in a given SP_REPORT by using the sp_reportcolumns report name

SP_PARAMETERS

     Use SP_PARAMETERS to list all the parameters available for a given SP_REPORT. The VALUES column lists available values that can be used with the parameter.

SP_REPORTS Used with MS Query

     Stored Procedure reports (sp_reports) can also be used by Microsoft Excel (or other MS Office products) to bring any number of QuickBooks Online reports directly into a spreadsheet by using Microsoft Query (shown in this example doing a Balance Sheet Detail report).

Note: In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab.
Please refer to Troubleshooting - How to enable Microsoft Excel 365 - Legacy Microsoft Query.

Stored Procedure SQL Scripts

      With your QuickBooks Online company file open, click Start and Programs and locate QODBC Driver for the QuickBooks Online program group. Click on the QODBC Online Test Tool icon to launch the program supplied with QODBC. To establish a connection, select the QuickBooks Online Data DSN, Click Connect and copy (Ctrl-C), and paste (Ctrl-V) into QODBC Online Test Tool any of the following QuickBooks Report SQL Scripts: This sample Stored Procedure SQL Scripts can be copied (Ctrl-C) and pasted (Ctrl-V) into QODBC Online Test Tool and queried.

       sp_report APAgingDetail show RowData, Date, TransactionType, DocumentNumber, Vendor, Location, DueDate, PastDue, Amount, OpenBalance
parameters DateFrom={d'2015-01-01'},DateTo={d'2015-11-24'}

       sp_report APAgingDetail parameters DateFrom={d'2015-01-01'},DateTo={d'2015-11-24'}

       sp_report APAgingSummary show Current_Title, Amount_Title, Vendor, Current, Amount parameters DateMacro = 'Today'

       sp_report ARAgingDetail show RowData, Date, TransactionType, DocumentNumber, Client, Location, DueDate, Amount, OpenBalance
parameters DateFrom={d'2015-01-01'},DateTo={d'2015-11-24'}

       sp_report ARAgingSummary show Current_Title, Amount_Title, RowData,Text, Current, Amount parameters DateMacro = 'Today'

       sp_report ARAgingSummary parameters DateMacro = 'Today'

       sp_report BalanceSheetDetail shows RowData, Label, Amount parameters DateMacro = 'ThisMonthToDate'

       sp_report CashFlow show RowData, Label, Time, Amount parameters DateMacro = 'ThisMonthToDate'

       sp_report GeneralLedger shows RowData, Date, TransactionType, DocumentNumber, Name, Memo, Split, Amount, Balance
parameters DateMacro = 'ThisMonthToDate'

       sp_report ProfitAndLossDetail show RowData, TransactionType, DocumentNumber, Name, Memo, Split, Amount, Balance
parameters DateMacro = 'ThisMonthToDate'

       sp_report VendorBalanceDetail show RowData, Date, TransactionType, DocumentNumber, Vendor, Location, DueDate, Amount, OpenBalance, Balance
parameters DateMacro = 'ALL'

       sp_report CustomerBalanceDetail show RowData, Date, TransactionType, DocumentNumber, Location, DueDate, Amount, OpenBalance, Balance
parameters DateFrom={d'2015-01-01'},DateTo={d'2015-11-24'}

       sp_report AccountList show RowData, Account, AccountNumber, Type, Description, Balance parameters ModifiedDateMacro = 'this month'

       sp_report SalesByCustomerSummary show Amount_Title, RowData, Amount parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'Total'

       sp_report VendorBalanceSummary show RowData, Vendor, Amount parameters DateMacro = 'ALL'

       sp_report CustomerBalanceSummary show Customer, Amount parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'Total'

       sp_report ExpenseByVendorSummary show RowData, Vendor, Amount parameters DateMacro = 'ThisYearToDate'

       sp_report IncomeByCustomerSummary show Amount_Title, RowData, Amount parameters DateMacro = 'ThisYearToDate'

       sp_report InventoryValuationSummary show Text, RowData, ProductsAndService, Amount parameters DateMacro = 'ThisYearToDate'

       sp_report ProfitAndLossStandard show RowData,Amount parameters DateMacro = 'ThisYearToDate', ReportBasis='Cash'

       sp_report SalesByItemSummary show RowData,ProductsAndService,Amount parameters DateMacro = 'ThisMonthToDate',SummarizeColumnsBy = 'Total'

       sp_report SalesByDepartmentSummary show Amount_Title, RowData,Department, Amount parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'Total'

       sp_report SalesByClassSummary show Amount_Title, RowData,Class, Amount parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'Total'

       sp_report TrialBalance show RowData,Amount parameters DateMacro = 'ThisYearToDate', ReportBasis='Accrual'

Also, Refer

List of Reports available in QODBC Online

How to get information on Tables and Columns

QODBC Stored Procedures Command-List

How to use the VB Demo for testing

VB Demo is missing

Keywords: Report Engine, Sp_reports, QuickBooks Online, QBO, QODBC Online, reports

Creating a Form in Microsoft Access:

Note: Microsoft Access has many uses and will allow you to use your QuickBooks Online Data files similarly to Access databases.

Open MS Access. You can find it via Windows Start->All Programs->Microsoft Office :

A default database file, name Database1, appears in the box. You can edit this to a more relevant name, such as QuickBooks Link. accdb. Save the file as type "Microsoft Office Access Databases".

Download Sample

Then Click "Create" to create a blank database:

After  the new database is created, Create a New Form via the "Form Design" link in Create Tab:

After  the new form is created, Insert a button in the form:

Rename the button as per your requirement (In this example button name is "Select Customer"):

Now go to the button property window and set the property for the "On Click" event. Select "Event Procedure" from the dropdown list, then click on the "..." button:

The new window is open, which contains the VBA code (In this example, it has a code button click event):

Now Insert the module in the form by Right-clicking on the form:

Write code in the module (In this example, I am writing code for the selection of customer name):

*Note: use "QuickBooks Online Data" instead of "QuickBooks Data."

Link newly created module with button click event:

Now click on the "Select Customer" button, and you will get the Name of all customers:

Repeating the above steps can add functionality to your VBA code as per your requirement. In this example, I have added functionality for inserting, Update & Delete of customer records:

By clicking on the "Insert Customer" button, the New customer is added with the name "Testing VB":

By clicking on the "Update Customer" button, Newly added customer name is updated with the name "Updated Testing VB":

By clicking on the "Delete Customer" button, Newly added customer is deleted:

Please refer below code which is used in this example:

Select Customer:

Public Sub example select()
Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "DSN=QuickBooks Online Data;OLE DB Services=-2;"
sSQL = "SELECT Name FROM customer"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")

oConnection.Open sConnectString
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic
sMsg = "**********************" & Chr(100)
Do While (Not oRecordset.EOF)
sMsg = sMsg & oRecordset.Fields("Name") & Chr(100)
oRecordset.MoveNext
Loop
sMsg = sMsg & "**********************"
MsgBox sMsg

oRecordset.Close
Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
End Sub

Insert Customer:

Public Sub exampleInsert()
Const adOpenStatic = 3
Const adLockOptimistic = 3

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "DSN=QuickBooks Online Data;OLE DB Services=-2;"
sSQL = "Insert into customer (Name) values ('Testing VB')"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oConnection.Execute (sSQL)
sMsg = sMsg & "Record Added!!!"
MsgBox sMsg

Set oRecordset = Nothing
Set oConnection = Nothing
End Sub

Update Customer:

Public Sub exampleUpdate()
Const adOpenStatic = 3
Const adLockOptimistic = 3

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "DSN=QuickBooks Online Data;OLE DB Services=-2;"
sSQL = "Update customer set Name='Updated Testing VB' where name='Testing VB'"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oConnection.Execute (sSQL)

sMsg = sMsg & "Record Updated!!!"
MsgBox sMsg

Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
End Sub

Delete Customer:

Public Sub exampleDelete()
Const adOpenStatic = 3
Const adLockOptimistic = 3

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL
sConnectString = "DSN=QuickBooks Online Data;OLE DB Services=-2;"
sSQL = "delete from customer where name='Updated Testing VB'"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oConnection.Execute (sSQL)
sMsg = sMsg & "Record Deleted!!!"
MsgBox sMsg

Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
End Sub

Download Sample

Tags: QuickBooks Online, QBO, Access, MS Access, QODBC Online VBA, VBA script, sample VBA online

Setup QODBC to use with MS Excel

Note: QODBC allows you to quickly merge live QuickBooks Online data into the cells of your Microsoft Excel worksheets for quick calculations and graphs. The data will retain a live link to QuickBooks, and one click will update the worksheet to the most current data in the linked QuickBooks table.

Before using the MS Excel application, please ensure you have installed the Microsoft Office Component named "Microsoft Query." Regarding MS Query, please refer to Microsoft Office with MS Query and QODBC for more information.

Please start Microsoft Excel 2016/2013 from Windows Start->All Programs->Microsoft Office 2016 or Microsoft Office 2013:

Office 2016:

Office 2013:

Office 2016:

Office 2013:

Create a blank workbook, or you can open an existing one. In this example, we are creating a new workbook:

An Empty sheet will open, and you shall be on the "Home" tab:

Please select the "Data" tab and click "Get Data -> From Other Sources->From Microsoft Query."

Note: In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab.
Please refer to Troubleshooting - How to enable Microsoft Excel 365 - Legacy Microsoft Query.

Using Microsoft Query

It will take some time to get the DSN list:

Then, in the "Choose Data Source" Window, Select the QuickBooks Online Data DSN you set up with the QODBC driver. This can be one of our pre-installed DSN names or one that you have created. (Here, I choose the default DSN as the example.) Check to Use the Query Wizard to create/edit queries.)

For 64-bit MS Office, change the QRemote DSN to QuickBooks Online Data 64-bit, the default DSN created when installing QODBC Online.

Import QuickBooks Online Data into MS Excel via MS Query

After you grant permission for QODBC, you will be guided by Query Wizard. Please select a table and the columns you want to input in the Choose Columns window. Multiple selections are available. Once a table is set, it is the default that all its columns are selected. You can add or remove columns from the correct list "Columns in your query."

You can set a filter for your query in this window. Please click the "Next" button:

This window allows you to sort your data. If you don't want to change the order, please click the "Next" button:

After the setup is finished, MS Excel will ask you to choose an edit method for the records:

Using Microsoft ODBC (Office 365)

Using "From ODBC"

From the list of DSNs, select "QuickBooks Online Data"

If prompted for credentials, enter the username "admin" and the Password "admin."

Ideally, no credentials are required. There is no need to input QuickBooks or QODBC credentials.

You can enter any username and password.

Import QuickBooks Online Data into MS Excel via Microsoft ODBC

Choose the table you want to import/link to Microsoft Excel from the list of tables.

Click the "Load" button to bring data to the Excel file.

Return Data to Microsoft Excel:

This method directly exports the data to Excel. You can view and edit these records, but the data is static, and modifications will not be uploaded into QuickBooks Online.

You can view the detailed connection properties by clicking the "Queries & Connections" button in the Data menu: 

In the Workbook Connection Window, Right-click on the connection name & click the "Properties" button to view detailed information: 

Now you can view the connection string and its SQL statement in the definition window:

View Data or Edit Query in MS Query:

This method will lead you to the MS Query window, where you can view and edit data via SQL Statement. All modifications will be uploaded to QuickBooks.

Click the "SQL" button to view the SQL Statement of this query. The SQL window will show the SQL Statement, and the user can also modify the statement to extract other records:

The desired record set contains only records with a Name equal to "Residential." Select the field header name and click on it, following the selection of Criteria -> Add Criteria. In the popped-out Add Criteria window, the button specifies the criteria Field as "Name " and Operator as "equals. " Then click the Value button, and from the list of values, select "Residential" -> OK -> Add.

The following screen reflects the applied criteria to the query and the resulting QuickBooks record:

When you finish the query, choose to save it so that you can use it directly next time. Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:

     Clicking Data -> Refresh Data or the External Data toolbar will update the data in the worksheet with the latest information from the linked QuickBooks Online tables, as shown above. Changes to the QuickBooks Online files will not affect this worksheet until you refresh the data with the Refresh option.

Tags: QuickBooks Online, QBO, Excel 365, MS Excel, Excel

Creating a Table in Microsoft Access:

Note: Microsoft Access has many uses and will allow you to use your QuickBooks Online data files similarly to Access databases.

Open MS Access 2013; you can find it via Windows Start->All Programs->Microsoft Office 2013:

A default database filename, Database1, appears in the box. You can edit this to a more relevant name, such as QuickBooks Link. accdb. Save the file as type "Microsoft Office Access Databases."

Then Click "Create" to create a blank database:

After  the new database is created, Navigate to "External Data":

Build a connection via External Data->More->ODBC Database, or directly click the ODBC database button at the External Data menu:

Under the Get External Data window, you will get two options:

Import the source data into a new table in the current database. This option will directly extract tables into Access Database, you can view and edit these tables, but all modifications will not be uploaded unless you build another query.

Link to the data source by creating a linked table: this option will create a table just like the first option but will keep its connection to QuickBooks, which means all your operations to this table will be uploaded to QuickBooks, and the data in the table is also in full sync with QuickBooks.

This article will show you how to build the linked table in MS Access.

Build a Linked Table in MS Access

After you select the access method and click OK, then you will get a Select Data Source window:

Select the QuickBooks Online Data DSN you set up with the QODBC driver. This can be one of our pre-installed DSN names or one that you have created. (Here, I choose the default DSN as the example.)

For 64-bit MS Office, change QRemote DSN to QuickBooks Online Data 64-bit is the default DSN created when installing QODBC Online.

Once the connection is built, Access will return a list of available tables in the Select Tables window. Please, select the tables you want to extract data from. Multiple Selection is available.

If the Import/Linking works without any problems, you will see linked tables been created in the table list of MS Access:

If you want to view QuickBooks records in this linked table, double-click the linked server, then you will see the records:

The linked table is always in sync with QuickBooks Online Data, so you cannot add or delete any columns in this table. But you can directly modify the data in the linked table, and QODBC will automatically update the modification to QuickBooks.

Note: When you run an application that is not entirely ODBC compatible, they often support getting data from Access tables. To make this application work with QuickBooks Online tables, you can run the Microsoft Access setup as shown above. Then have this application use the MS Access ODBC driver to talk to the Access database you created, which was linked to QuickBooks Online tables. This middleware approach allows us to support nearly any application, even if not fully ODBC compliant.

Keywords: read/write with ms access, QuickBooks Online, QBO, Access, Linked tables, Access 2013/365

Setup of QODBC Online:

The first step is to download and install QODBC Online. QODBC Online requires .NET Framework 4.6.1 or a later version. So you need to install .NET Framework 4.6.1 to work with QODBC Online.

For system requirement details of QODBC Online, click here.

To purchase QODBC Online - Click here.

Please refer to the steps below to install QODBC Online:

1). Download the QODBC Online installer from the link below:

QODBC Driver for QuickBooks Online Download

2). Install QODBC Online:

Now we have installed QODBC Online. 

Configure QODBC Online:

Now we need to authorize & configure QODBC Online to work with QuickBooks Online.

Please follow the steps below to configure QODBC Online:

1) Open QODBC Online Setup Screen & click on "Connect to QuickBooks," & refer to steps by step guide:

Start >> All Programs >> QODBC Driver for use with QuickBooks Online >> Configure QODBC Online Data Source. 

Go To "System DSN" Tab>> select QuickBooks Online Data>> click "configure"

QODBC Online Setup Screen is opened; please switch to the General tab & click on the "Connect to QuickBooks" button.

2) By clicking "Connect to QuickBooks," open the authorization screen. You need to authorize your company file to work with QODBC Online.

3) You need to enable a popup.

4) Enter your QuickBooks User ID & Password, then click "Sign In."

5) Select the company file you want to use with QODBC Online if you have multiple company files.

6) Click on "Authorize."

7) After successful authorization, it will return to QODBC Online page as connected status. You can close this window.

8) QODBC Online is successfully connected to your QuickBooks Online company.

9) We will test the connection using QODBC Online Test Tool. Please open QODBC Online Test Tool from:
Start>>All Programs>> QODBC Driver for use with QuickBooks Online >>QODBC Online Test Tool

10) Please select the "QuickBooks Online Data" DSN from the dropdown list & click on "Connect."

11) We can connect to DSN from QODBC Online Test Tool. Please write your SQL statement & click on "Run."

12) We can fetch data from QODBC Online Test Tool. 

Disconnect QODBC Online from your QuickBooks Online Company:

1) Disconnect from your QuickBooks Online company by clicking the "Disconnect from QuickBooks" button.

2) By clicking on the "Disconnect from QuickBooks," a message appears for Disconnect connection to QuickBooks Online. Please click Yes to Disconnect.

3) QODBC Online is successfully Disconnected from your QuickBooks Online company.

Import / Export connection key:

If you want to connect the same company file from another machine, you can export the connection key from one device & import it to another.

You can share the connection key from:

Start>>All Programs>> QODBC Driver for use with QuickBooks Online>> Configure QODBC Online Data Source>> Go To "System DSN" Tab>> select QuickBooks Online Data>> click "configure">> switch to General tab >> click on "Export Connection key" button & share key.

Enter the name of the connection key & choose the location where you want to save the connection key:

Now the connection key is exported successfully. You can copy this connection key & paste it into another machine to import the key to that machine.

You can import the connection key from the following:

Start>>All Programs>> QODBC Driver for use with QuickBooks Online>> Configure QODBC Online Data Source>> Go To "System DSN" Tab>> select QuickBooks Online Data>> click "configure">> switch to General tab click on "Import Connection key" button & import key.

Open the connection key from the location where you keep the connection key:

Now the connection key is imported successfully. You can connect to QuickBooks Online using this connection key.

For system requirement details of QODBC Online, click here.

Question:

 I tried to disconnect, but I got the message QODBC Online is not disconnected from your QuickBooks Online company

Answer:

If you are unable to disconnect QODBC Online from your QuickBooks Online, you may delete the QODBC Online DSN and recreate the QODBC Online DSN.

Tags: QuickBooks Online, QBO, QODBC Online, Connect