Knowledgebase: QODBC
[QODBC-Desktop] Sample VB.NET Web Application for Inserting InvoiceLine into existing Invoice
Posted by Jack - QODBC Support on 25 April 2017 10:11 AM

Sample VB.NET Web Application for Inserting InvoiceLine into existing Invoice

Sample Application:

Please click here for downloading Sample Code.

Please refer below steps for using an application for Inserting InvoiceLine to existing Invoice using VB.NET.

Run the application & click on the "Connect" button.

The application is connected with QuickBooks.

The application has two functionality:

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.

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

Application Source Code:

Imports System.Data.Odbc
Imports System.Drawing

Public Class _Default
    Inherits Page
     Private Shared _cn As OdbcConnection

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        lblErrorMessage.Visible = False
    End Sub
    Protected Sub btnConnect_Click(ByVal sender As Object, ByVal e As EventArgs)
        Try
            If (btnConnect.Text.Equals("Disconnect")) Then
                If (_cn IsNot Nothing) Then
                    lblConnectionStatus.Text = "Disconnecting...."
                    _cn.Close()
                    _cn.Dispose()
                    btnConnect.Text = "Connect"
                    lblConnectionStatus.Text = "Not Connected"
                    lblConnectionStatus.ForeColor = Color.DarkRed
                End If
            Else
                If (_cn Is Nothing OrElse _cn.State = ConnectionState.Closed) Then
                    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
                End If
            End If
        Catch ex As Exception
            btnConnect.Text = "Errorred"
            lblErrorMessage.Text = String.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace)
            lblErrorMessage.Visible = True
        End Try
        
    End Sub

    Protected Sub btnInsertInvoiceDescription_Click(ByVal sender As Object, ByVal e As EventArgs)

        If (_cn Is Nothing OrElse _cn.State = ConnectionState.Closed) Then
            btnConnect_Click(Nothing, Nothing)
            btnConnect.Text = "Connect"
            lblConnectionStatus.Text = "NotConnected"
            lblConnectionStatus.ForeColor = Color.DarkRed
        End If
        If (String.IsNullOrEmpty(txtDInvoiceNo.Text)) Then
            lblErrorMessage.Text = "Invoice Refer Number is required"
            lblErrorMessage.Visible = True
            'MessageBox.Show("Invoice Refer Number is required")
            Return
        End If
        If (String.IsNullOrEmpty(txtDDescription.Text)) Then
            lblErrorMessage.Text = "New Line Description is required"
            lblErrorMessage.Visible = True
            'MessageBox.Show("New Line Description is required")
            Return
        End If

        InsertInvoiceLineDescriptionOnly(txtDInvoiceNo.Text, txtDDescription.Text)
        DisplayInvoiceInGrid(txtDInvoiceNo.Text)
    End Sub

    Protected Sub btnInvoiceInsertItem_Click(ByVal sender As Object, ByVal e As EventArgs)
        Try

            If (_cn Is Nothing OrElse _cn.State = ConnectionState.Closed) Then
                btnConnect_Click(Nothing, Nothing)
                btnConnect.Text = "Connect"
                lblConnectionStatus.Text = "NotConnected"
                lblConnectionStatus.ForeColor = Color.DarkRed
            End If
            If (String.IsNullOrEmpty(txtIInvoiceNo.Text)) Then
                'MessageBox.Show("Invoice Refer Number is required")
                lblErrorMessage.Text = "Invoice Refer Number is required"
                lblErrorMessage.Visible = True
                Return
            End If
            If (String.IsNullOrEmpty(txtIItemFullName.Text)) Then

                'MessageBox.Show("New Line Item Full Name is required")
                lblErrorMessage.Text = "New Line Item Full Name is required"
                lblErrorMessage.Visible = True
                Return
            End If

            If (String.IsNullOrEmpty(txtIQuantity.Text)) Then

                'MessageBox.Show("Invoice Refer Quantiy is required")

                lblErrorMessage.Text = "Invoice Refer Quantiy is required"
                lblErrorMessage.Visible = True
                Return
            End If

            If (String.IsNullOrEmpty(txtIRate.Text)) Then

                'MessageBox.Show("New Line Rate is required")
                lblErrorMessage.Text = "New Line Rate is required"
                lblErrorMessage.Visible = True
                Return
            End If
            InsertInvoiceLineItem(txtIInvoiceNo.Text, txtIItemFullName.Text, Integer.Parse(txtIQuantity.Text), Integer.Parse(txtIRate.Text), txtIDescription.Text)
            DisplayInvoiceInGrid(txtIInvoiceNo.Text)

        Catch ex As Exception
            'MessageBox.Show(String.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace))
            lblErrorMessage.Text = String.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace)
            lblErrorMessage.Visible = True
        End Try
    End Sub
    Private Sub InsertInvoiceLineItem(invoiceRefNumber As String, itemFullName As String, quanity As Int16, rate As Int16, description As String)
        Dim QBdrdr As OdbcDataReader
        Dim txnID As String
        Dim query As String = String.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber)

        Dim QBEmployeecmd As OdbcCommand = New OdbcCommand(query, _cn)

        QBEmployeecmd.CommandType = CommandType.Text

        QBdrdr = QBEmployeecmd.ExecuteReader()


        If (QBdrdr.HasRows = True) Then

            txnID = QBdrdr("txnid").ToString()
        Else
            txnID = String.Empty
        End If
        QBdrdr.Close()


        If (String.IsNullOrEmpty(txnID)) Then
            'MessageBox.Show("No Invoice Found")
            lblErrorMessage.Text = "No Invoice Found"
            lblErrorMessage.Visible = True
        Else
            query = String.Format("Insert into invoiceline(txnid,InvoiceLineItemRefFullName, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineDesc) values('{0}','{1}',{2},{3},'{4}') ", txnID, itemFullName, quanity, rate, description)
            Dim QBEmployeecmd2 As OdbcCommand = New OdbcCommand(query, _cn)

            QBEmployeecmd2.CommandType = CommandType.Text
            QBEmployeecmd2.ExecuteNonQuery()

        End If
    End Sub

    Private Sub InsertInvoiceLineDescriptionOnly(invoiceRefNumber As String, newItemDescription As String)
        Dim QBdrdr As OdbcDataReader
        Dim txnID As String
        Dim query As String
        query = String.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber)

        Dim QBEmployeecmd As OdbcCommand = New OdbcCommand(query, _cn)

        QBEmployeecmd.CommandType = CommandType.Text

        QBdrdr = QBEmployeecmd.ExecuteReader()


        If (QBdrdr.HasRows = True) Then
            txnID = QBdrdr("txnid").ToString()
        Else
            txnID = String.Empty

        End If
        QBdrdr.Close()

        If (String.IsNullOrEmpty(txnID)) Then
            'MessageBox.Show("No Invoice Found")
            lblErrorMessage.Text = "No Invoice Found"
            lblErrorMessage.Visible = True
        Else
            query = String.Format("Insert into invoiceline(txnid,InvoiceLineDesc) values('{0}','{1}') ", txnID, newItemDescription)
            Dim QBEmployeecmd2 As OdbcCommand = New OdbcCommand(query, _cn)

            QBEmployeecmd2.CommandType = CommandType.Text
            QBEmployeecmd2.ExecuteNonQuery()

        End If
    End Sub

    Private Sub DisplayInvoiceInGrid(invoiceRefNumber As String)
        Dim query As String = String.Format("select RefNumber,CustomerRefFullName,InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount  from InvoiceLine where RefNumber='{0}'", invoiceRefNumber)
        ProcessQuery(query)
    End Sub
    Private Sub ProcessQuery(query As String)
        Dim cmd = New OdbcCommand(query, _cn)
        Dim reader As OdbcDataReader = cmd.ExecuteReader()
        Dim myTable As DataTable = New DataTable()
        myTable.Load(reader)
        grvData.AutoGenerateColumns = True
        grvData.DataSource = myTable
        grvData.DataBind()
    End Sub


End Class

Keywords: sample .net, sample, .net, Dot Net, VB.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).