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

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

Sample Application:

Please click here for download Sample Code.

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

Run application & click on "Connect" button.

Application connected with QuickBooks.

The application has two functionality:

1. Append existing Invoice with a new Description Line which will add new Description Line in existing Invoice.

You need to insert RefNumber (i.e. Invoice#) of existing Invoice & description which you want to enter and click on "Insert New Invoice Line (Description Only)" button.

New Description Line is added to existing Invoice.

Result in QuickBooks.

2. Append existing Invoice with a new ItemInventory/ItemService Line which will add new ItemInventory/ItemService Line in existing Invoice.

You need to insert RefNumber (i.e. Invoice#) of existing Invoice, Item Full Name, Quantity, Rate & Description which you want to enter and click on "Insert New Invoice Line (Inventory/Service)" button.

New Item Line is added to existing Invoice.

Result in QuickBooks.

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
        {

        }
    }
      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

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