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

Sample VB.NET source code 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.

, You can change DSN as per your configuration. In this example, I am connecting to default DSN QuickBooks Data. Run the application, select DSN & 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 Full Item 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
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data.Odbc
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks
Imports System.Windows.Forms
Imports System.Windows.Forms.VisualStyles

Public Class Form1

    Dim _cn As OdbcConnection
    Private Sub btnConnect_Click(sender As Object, e As EventArgs) Handles btnConnect.Click
        Try
            Application.DoEvents()
            If (btnConnect.Text.Equals("Disconnect")) Then
                If (_cn IsNot Nothing OrElse IsDBNull(_cn)) 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
            Application.DoEvents()
        Catch ex As Exception
            Application.DoEvents()
            btnConnect.Text = "Errorred"
            MessageBox.Show(String.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace))
        End Try
    End Sub

    Private Sub button1_Click(sender As Object, e As EventArgs) Handles button1.Click
        Me.Close()
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ListOutDSN()
    End Sub

    Private Sub ListOutDSN()

        cboListOfDSN.Items.Clear()
        Dim myOdbcKeys = Microsoft.Win32.Registry.LocalMachine.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources", False).GetValueNames()

        For Each item As Object In myOdbcKeys
            cboListOfDSN.Items.Add(item)
        Next

        myOdbcKeys = Microsoft.Win32.Registry.CurrentUser.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources", False).GetValueNames()

        For Each item As Object In myOdbcKeys
            cboListOfDSN.Items.Add(item)
        Next


        If (cboListOfDSN.Items.Count > 0) Then
            For Each item As String In cboListOfDSN.Items
                If (item.Equals("QuickBooks Data")) Then
                    cboListOfDSN.Text = item
                    Return
                End If
                If (item.Equals("QuickBooks Data POS")) Then
                    cboListOfDSN.Text = item
                    Return
                End If
                If (item.Equals("QuickBooks Data Online")) Then
                    cboListOfDSN.Text = item
                    Return
                End If
                cboListOfDSN.SelectedIndex = 0
            Next
        End If
    End Sub



    Private Sub btnInsertInvoiceDescription_Click(sender As Object, e As EventArgs) Handles btnInsertInvoiceDescription.Click
        'If (IsNothing(_cn) Or _cn.State = ConnectionState.Closed) Then
        'btnConnect_Click(Nothing, Nothing)
        'End If
        If (_cn Is Nothing OrElse _cn.State = ConnectionState.Closed) Then
            btnConnect_Click(Nothing, Nothing)
        End If
        If (String.IsNullOrEmpty(txtDInvoiceNo.Text)) Then
            MessageBox.Show("Invoice Refer Number is required")
            Return
        End If
        If (String.IsNullOrEmpty(txtDDescription.Text)) Then
            MessageBox.Show("New Line Description is required")
            Return
        End If

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

    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")
        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
    End Sub


    Private Sub btnInvoiceInsertItem_Click(sender As Object, e As EventArgs) Handles btnInvoiceInsertItem.Click

        Try

            If (_cn Is Nothing OrElse _cn.State = ConnectionState.Closed) Then
                btnConnect_Click(Nothing, Nothing)
            End If
            If (String.IsNullOrEmpty(txtIInvoiceNo.Text)) Then
                MessageBox.Show("Invoice Refer Number is required")
                Return
            End If
            If (String.IsNullOrEmpty(txtIItemFullName.Text)) Then

                MessageBox.Show("New Line Item Full Name is required")
                Return
            End If

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

                MessageBox.Show("Invoice Refer Quantiy is required")
                Return
            End If

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

                MessageBox.Show("New Line Rate is required")
                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))
        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")
        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

End Class

Keywords: sample .net, sample, .net, Dot Net, VB.Net

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