Knowledgebase: QODBC Online
[QODBC-Online] Sample C#.NET Code for Inserting InvoiceLine to existing Invoice
Posted by Jack - QODBC Support on 24 March 2017 11:12 AM

Sample Code for Inserting InvoiceLine to an existing Invoice using C#.NET

Sample Application:

Please click here to download the Sample Code.

Please refer to the steps below for using an application for inserting InvoiceLine to an 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 is connected with QuickBooks Online.

The application has two functionalities:

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.

A 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

(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).