Knowledgebase: Data & Table
[QODBC-POS] Sample C#.NET Code for Creating SalesReceipt
Posted by Jack - QODBC Support on 29 August 2017 07:37 AM

Sample C#.NET Code for Creating SalesReceipt

Sample Application:

Please click here for downloading Sample Code.

Please refer below steps for using application for Creating to SalesReceipt using C#.NET.

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

Application 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 "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 not available 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;
        }

    }
}
(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).