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

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

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.

Run the application & 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.

A 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

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