Knowledgebase
[QODBC-Desktop] Sample C#.NET Code for Inserting InvoiceLine to existing Invoice
Posted by Jack - QODBC Support on 02 March 2017 02:37 PM

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

Sample Application:

Please click here to download Sample Code.

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

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

An application connected with QuickBooks.

The application has two functionality:

1. Append the existing Invoice with a new Description Line, adding a new Description Line to the current 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.

2. Append the existing Invoice with a new ItemInventory/ItemService Line, adding a new ItemInventory/ItemService Line to the current 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.

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

(3 vote(s))
Helpful
Not helpful

Comments (3)
Dan
14 November 2018 12:47 AM
I downloaded and run your source code from this article. Select DSN combobox is empty. What is the reason?
Rajendra Dewani
18 April 2019 03:11 PM
@Dan, Please change the Target Platform to X86 and try again.
jamesh kumar
13 December 2018 08:54 AM
good
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).