<![CDATA[FLEXquarters.com Limited]]> <![CDATA[[QODBC-POS] Sample C#.NET Code for Creating SalesReceipt]]> Sample C#.NET Code for Creating Sales Receipt

Sample Application:

Please click here to download the Sample Code.

Please refer to the steps below for using an application for creating a SalesReceipt using C#.NET.

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

The application is connected with QuickBooks POS.

You need to insert CustomerListId, Comments, Cashier, SalesReceiptType, Item ListID, Quantity, Rate & Amount, which you want to enter, and click on the "Insert New SalesReceiptItem" button.

New SalesReceipt created.

Result in QuickBooks POS.

Note: Modify/Append existing SalesReceipt feature is not supported by QuickBooks SDK, so it is unavailable through QODBC POS.

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_SalesReceipt_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 DisplaySalesReceiptInGrid(string customerListID)
        {
            string query = string.Format("SELECT CustomerListId, Comments, Cashier, SalesReceiptType, SalesReceiptItemListId,  SalesReceiptItemQty as ItemQuantity, SalesReceiptItemPrice As ItemPrice, TenderCash01TenderAmount As TotalTenderAmount FROM SalesReceiptItem where TxnID='{0}'", customerListID);
            ProcessQuery(query);
        }

     


        private void InsertSalesReceiptLineItem(string customerName, string comments, string cashierName, string salesReceiptType, string itemName, int quanity, int rate, int tenderAmount)
        {            
            string query =
                    string.Format("Insert Into SalesReceiptItem (CustomerListId, Comments, Cashier, SalesReceiptType, SalesReceiptItemListId,  SalesReceiptItemQty, SalesReceiptItemPrice, TenderCash01TenderAmount) Values ('{0}','{1}','{2}','{3}','{4}',{5},{6},{7})",customerName,comments,cashierName,salesReceiptType,itemName,quanity,rate,tenderAmount);
                //query = string.Format("Insert into invoiceline(txnid,InvoiceLineItemRefFullName, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineDesc) values('{0}','{1}',{2},{3},'{4}') ", txnID, itemFullName, quanity, rate, description);
                //MessageBox.Show(query);
                using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
                {
                    //MessageBox.Show("" + QBEmployeecmd.Connection.State.ToString());
                    QBEmployeecmd.CommandType = CommandType.Text;
                    //MessageBox.Show("" + QBEmployeecmd.CommandType.ToString());
                    QBEmployeecmd.ExecuteNonQuery();
                    //MessageBox.Show("Execute Success");

                }
        }

        private void ProcessQuery(string query)
        {
            var cmd = new OdbcCommand(query, _cn);            
            DataSet dataSet = new DataSet();            
            OdbcDataReader reader = cmd.ExecuteReader();            
            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();
        }

        private void button2_Click(object sender, EventArgs e)
        {
           
           
        }

        private void btnSalesReceiptItem_Click(object sender, EventArgs e)
        {
            try
            {
                if (_cn == null || _cn.State == ConnectionState.Closed)
                {
                    btnConnect_Click(null, null);
                }
                if (string.IsNullOrEmpty(txtcustomerName.Text))
                {
                    MessageBox.Show("Customer Name is Required.");
                    return;
                }
                if (string.IsNullOrEmpty(txtComments.Text))
                {
                    MessageBox.Show("Comments is required.");
                    return;
                }
                if (string.IsNullOrEmpty(txtCashierName.Text))
                {
                    MessageBox.Show("Cashier Name is required.");
                    return;
                }
                if (string.IsNullOrEmpty(txtSalesReceiptType.Text))
                {
                    MessageBox.Show("Sales Receipt Type is required.");
                    return;
                }
                if (string.IsNullOrEmpty(txtItemName.Text))
                {
                    MessageBox.Show("Item Name is required.");
                    return;
                }
                if (string.IsNullOrEmpty(txtQuantity.Text))
                {
                    MessageBox.Show("Quantity is required.");
                    return;
                }
                if (string.IsNullOrEmpty(txtItemPrice.Text))
                {
                    MessageBox.Show("Item Price is required.");
                    return;
                }
                if (string.IsNullOrEmpty(txtTenderAmount.Text))
                {
                    MessageBox.Show("Tender Amount is required.");
                    return;
                }
                InsertSalesReceiptLineItem(txtcustomerName.Text, txtComments.Text, txtCashierName.Text, txtSalesReceiptType.Text, txtItemName.Text, int.Parse(txtQuantity.Text), int.Parse(txtItemPrice.Text), int.Parse(txtTenderAmount.Text));
                string lastInsertedId = GetLastInsertedId("sp_lastInsertID SalesReceiptItem");
                DisplaySalesReceiptInGrid(lastInsertedId);
            }
            catch (Exception ex)
            {

                MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));
            }
        }
        private string GetLastInsertedId(string query)
        {
            string lastInsertedId = "";
            using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
            {
                QBEmployeecmd.CommandType = CommandType.Text;
                var executedResult = QBEmployeecmd.ExecuteScalar();
                lastInsertedId = executedResult.ToString();               
            }
            return lastInsertedId;
        }

    }
}
]]> <![CDATA[[QODBC-POS] Sample C#.NET Web Application for Creating SalesReceipt]]> Sample C#.NET Web Application for Creating Sales Receipt

Sample Application:

Please click here to download the Sample Code.

Please refer to the steps below for using an application for creating a SalesReceipt using C#.NET.

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

The application is connected with QuickBooks POS.

You need to insert CustomerListId, Comments, Cashier, SalesReceiptType, Item ListID, Quantity, Rate & Amount, which you want to enter, and click on the "Insert New SalesReceiptItem" button.

New SalesReceipt created.

Result in QuickBooks POS.

Note: Modify/Append existing SalesReceipt feature is not supported by QuickBooks SDK, so it is unavailable through QODBC POS.

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
        {           
            lblErrorMessage.Visible = false;
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {

        }
    }
    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";
            lblConnectionStatus.Text = "Errorred";
            lblErrorMessage.Text = string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace);
            lblErrorMessage.Visible = true;            
        }
    }  
    private void InsertSalesReceiptLineItem(string customerName, string comments, string cashierName, string salesReceiptType, string itemName, int quanity, int rate, int tenderAmount)
        {
            string lastInsrtedId = "";
            string query = string.Format("Insert Into SalesReceiptItem (CustomerListId, Comments, Cashier, SalesReceiptType, SalesReceiptItemListId,  SalesReceiptItemQty, SalesReceiptItemPrice, TenderCash01TenderAmount) Values ('{0}','{1}','{2}','{3}','{4}',{5},{6},{7})", customerName, comments, cashierName, salesReceiptType, itemName, quanity, rate, tenderAmount);
                using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
                {
                    QBEmployeecmd.CommandType = CommandType.Text;
                    QBEmployeecmd.ExecuteNonQuery();                    
                    lblErrorMessage.Text = "SalesReceiptItem Insert Successfully";
                    lblErrorMessage.Visible = true;
                }                
        }


        private void ProcessQuery(string query)
        {
            var cmd = new OdbcCommand(query, _cn);            
            DataSet dataSet = new DataSet();            
            OdbcDataReader reader = cmd.ExecuteReader();
            DataTable myTable = new DataTable();
            myTable.Load(reader);
            grvData.AutoGenerateColumns = true;
            grvData.DataSource = myTable;
            grvData.BorderStyle = BorderStyle.Double;
            grvData.DataBind();
        }

        private void DisplaySalesReceiptInGrid(string customerListID)
        {
            string query = string.Format("SELECT CustomerListId, Comments, Cashier, SalesReceiptType, SalesReceiptItemListId, SalesReceiptItemDesc1,  SalesReceiptItemQty As ItemQuantity , SalesReceiptItemPrice As ItemPrice, TenderCash01TenderAmount as TenderTotalAmount FROM SalesReceiptItem where TxnID='{0}'", customerListID);
            ProcessQuery(query);
        }
        protected void btnSalesReceiptItem_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(txtcustomerName.Text))
                {
                    lblErrorMessage.Text = "Customer Name is Required.";
                    lblErrorMessage.Visible = true;
                    return;
                }
                if (string.IsNullOrEmpty(txtComments.Text))
                {
                    lblErrorMessage.Text = "Comments is required.";
                    lblErrorMessage.Visible = true;
                    return;
                }
                if (string.IsNullOrEmpty(txtCashierName.Text))
                {
                    lblErrorMessage.Text = "Cashier Name is required.";
                    lblErrorMessage.Visible = true;
                    return;
                }
                if (string.IsNullOrEmpty(txtSalesReceiptType.Text))
                {
                    lblErrorMessage.Text = "Sales Receipt Type is required.";
                    lblErrorMessage.Visible = true;
                    return;
                }
                if (string.IsNullOrEmpty(txtItemName.Text))
                {
                    lblErrorMessage.Text = "Item Name is required.";
                    lblErrorMessage.Visible = true;
                    return;
                }
                if (string.IsNullOrEmpty(txtQuantity.Text))
                {
                    lblErrorMessage.Text = "Quantity is required.";
                    lblErrorMessage.Visible = true;
                    return;
                }
                if (string.IsNullOrEmpty(txtItemPrice.Text))
                {
                    lblErrorMessage.Text = "Item Price is required.";
                    lblErrorMessage.Visible = true;
                    return;
                }
                if (string.IsNullOrEmpty(txtTenderAmount.Text))
                {
                    lblErrorMessage.Text = "Tender Amount is required.";
                    lblErrorMessage.Visible = true;
                    return;
                }
                InsertSalesReceiptLineItem(txtcustomerName.Text, txtComments.Text, txtCashierName.Text, txtSalesReceiptType.Text, txtItemName.Text, int.Parse(txtQuantity.Text), int.Parse(txtItemPrice.Text), int.Parse(txtTenderAmount.Text));
                string lastInsertedId = GetLastInsertedId("sp_lastInsertID SalesReceiptItem");
                DisplaySalesReceiptInGrid(lastInsertedId);
            }
            catch (Exception ex)
            {
                lblErrorMessage.Text = string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace);
                lblErrorMessage.Visible = true;
            }
        }

        private string GetLastInsertedId(string query)
        {
            string lastInsertedId = "";
            using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
            {
                QBEmployeecmd.CommandType = CommandType.Text;
                var executedResult = QBEmployeecmd.ExecuteScalar();
                lastInsertedId = executedResult.ToString();
                lblErrorMessage.Text = "SalesReceiptItem Insert Successfully";
                lblErrorMessage.Visible = true;
            }
            return lastInsertedId;
        }
}
]]> <![CDATA[[QODBC-POS] Troubleshooting - Unable to connect to QuickBooks POS 2015 (12.0) using Office 365 / 2016]]> Troubleshooting - Unable to connect to QuickBooks POS 2015 (12.0) using Office 365 / 2016 

Problem Description:

I have QuickBooks POS 2015 (12.0) & I want to connect to QuickBooks POS using MS Access/Excel. I am unable to connect to QBPOS with Access and Excel 2016.

I am getting the following error:

Also, I am getting the error "Internal error 160000" in MS Access.

I can connect to QBPOS with the QODBC POS Test Tool.

Is there an issue with the most recent version of MS Office?

Solution:

This is a known issue with QuickBooks POS SDK & Office 2016, so Office 2016 cannot connect to QuickBooks POS using QuickBooks POS Data DSN.

You can use QRemote DSN "QuickBooks POS Data QRemote" in MS Access/ Excel, which does not have this issue.

Please select QRemote DSN "QuickBooks POS Data QRemote" at the time of connection & you will be able to connect to QuickBooks POS 2015 (12.0) using Office 365 / 2016.

Also, Refer:

Using QuickBooks POS Data with Access 2016 / 365 / 2013

Using QuickBooks POS Data with Excel 2016 / 365 / 2013

]]> <![CDATA[[QODBC-POS] Using QuickBooks POS Data with Excel 2016 / 365 / 2013]]> How to Use QODBC POS with MS Excel 2016 / 365 / 2013

Set up QODBC to use with MS Excel

Note: QODBC allows you to quickly merge live QuickBooks 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 MS Office Component named "Microsoft Query." About MS Query, please refer to Microsoft Office with MS Query and QODBC for more.

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

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.

Office 2016:

Office 2013:

 

Office 2016:

Office 2013:

Create a blank workbook or 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 "From Other Sources-> From Microsoft Query."

It will take some time to get the DSN list:

 

And then, in the "Choose Data Source" Window, please select a DSN to continue. (Here, I choose the default data source with a sample company file open. And check to use the Query Wizard to create/edit queries.
For 64-bit MS Office, changing QRemote DSN to "QuickBooks POS Data 64-bit" is the default DSN created when installing QODBC POS.

The first time you use MS Excel to access QuickBooks, you will be asked to grant access permission for QODBC:

Select the "Yes, always" option to finish authorizing.

Import QuickBooks data into MS Excel via MS Query

After you grant permission for QODBC, you will be guided by the Query Wizard. In the Choose Columns window, please select a table and the columns you want to input. Multiple selections are available. And once a table is set, it is the default that all its columns are set. 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. If you don't want to set any query, 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:

Return Data to Microsoft Excel:

This method will directly export the data to Excel, where you can view and edit these records, but the data is static. The modification will not be uploaded to QuickBooks.

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

In the Workbook Connection Window, 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, and you can view and edit data via SQL Statement there, and 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 the records with a Name having a value equaling "Residential." Select the field header name and click on it, followed by selecting Criteria -> Add Criteria. In the popped-out Add Criteria window, specify the criteria Field as "Name," 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 recordset:

 

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

 

 

 


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

Keywords: create odbc connection, Office 2013, Office 365, Excel 365, Access 365, Office 2016, Access 2016, Excel 2016

]]> <![CDATA[[QODBC-POS] Using QuickBooks POS Data with Access 2016 / 365 / 2013]]> How to Use QODBC POS with MS Access 2016 / 365 / 2013

Creating a Table in Microsoft Access:

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

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

Office 2016:

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 on the External Data menu:

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

 

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 a linked table in MS Access.

Build a Linked Table in MS Access

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

Select the QuickBooks-related 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 an example.)
For 64-bit MS Office, changing QRemote DSN to "QuickBooks POS Data 64-bit" is the default DSN created when installing QODBC POS.

The first time you use MS Access to access QuickBooks, it will ask you to grant permission for this application:

Select the "Yes, Always" option, and then click "Done" in the next window:

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 being created in the table list of MS Access:

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

The linked table is always in sync with QuickBooks 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 across an application that is not fully ODBC compatible, they often support getting data from Access tables. To make this application work with QuickBooks 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 tables. This middleware approach allows us to support nearly any application, even if not fully ODBC compliant.

 

Suppose you want to import a table into MS Access. In that case, you have to select Import the source data into a new table in the current database option instead of linking to the data source by creating a linked table & repeat the above steps: this option will directly extract tables into Access Database, you can view and edit these tables, but all modification will not be uploaded unless you build another query to do so.

 

Keywords: read/write with MS Access, Office 2013, Office 365, Excel 365, Access 365, Office 2016, Access 2016, Excel 2016

]]>