none
Tracking No Or Auto No For Entries With The Same Product Id and Invoice RRS feed

  • Question

  • I am trying to do a simple thing and let me make it clear first. I've a excel file and it's being loaded by an application to store excel data in a database table. It works fine and uploads data. Now I've a requirement in this scenario and the following is the excel sheet:

    ProductId - Invoice No - Invoice Date - Price - Quantity
    101 - Inv-1000 - 7/10/2017 10:00 - 1000 - 10
    101 - Inv-1000 - 7/10/2017 10:30 - 200 - 2
    102 - Inv-1000 - 7/10/2017 10:30 - 400 - 20
    101 - Inv-1001 - 7/11/2017 10:30 - 300 - 5
    102 - Inv-1001 - 7/11/2017 10:30 - 200 - 5

    See in the excel sheet, product id 101 has two entries with the same invoice no with different timing and quantities. What I want is to create a tracking no whenever there are similar invoice no and product id (Repeated invoice no and product id). Suppose, for product id 101, it has already two entries with invoice no 'Inv-1000'. So it should create two different tracking no like 1 and 2 as follows in a database table:

    ProductId - Invoice No - Invoice Date - Price - Quantity - Auto No
    101 - Inv-1000 - 7/10/2017 10:00 - 1000 - 10 - 1
    101 - Inv-1000 - 7/10/2017 10:30 - 200 - 2 - 2
    102 - Inv-1000 - 7/10/2017 10:30 - 400 - 20 - 1
    101 - Inv-1001 - 7/11/2017 10:30 - 300 - 5 - 1
    102 - Inv-1001 - 7/11/2017 10:30 - 200 - 5 - 1

    I tried to use the following code to do the above task but it creates only 1 for all the entries even for the repeated ones:

    Do Until rs3.EOF
      If (rs4.recordCount > 0) Then
         generateId = rs3.Fields.Item("Auto No") + 1
      Else
         generateId = 1
      End If           
    rs3.MoveNext
    Loop

    Seems like I am missing something. Any idea or suggestion would be appreciated in this regard. Thanks.

    Note: I am validating the column names right now means if the excel sheet column doesn't match the table column, then it will not allow to upload data. Similarly, I've tried to validate row data of the excel sheet. In this case, if product id 101, invoice no 'Inv-1000' is in the table already and even with different tracking like 1, 2 already existed, then it shouldn't allow this data to be uploaded further. It looks simple but don't get to work. Struggling! Sample - Existed in the table:

    ProductId - Invoice No - Invoice Date - Price - Quantity - Auto No
    101 - Inv-1000 - 7/10/2017 10:00 - 1000 - 10 - 1
    101 - Inv-1000 - 7/10/2017 10:30 - 200 - 2 - 2
    102 - Inv-1000 - 7/10/2017 10:30 - 400 - 20 - 1
    101 - Inv-1001 - 7/11/2017 10:30 - 300 - 5 - 1
    102 - Inv-1001 - 7/11/2017 10:30 - 200 - 5 - 1

    Finally will not allow in the next upload for the above data. One more thing, keep the file in D directory and in the TextBox, write this to upload the excel file - D:\SampleExcel.xlsx.

    Tried code:

    Dim recordCount As Integer 'Variable to get record count Dim i As Integer Private Sub btnUpload_Click() LoadExcelSheet End Sub '**Method To Upload Excel File - Starts** Public Sub LoadExcelSheet() Dim con As ADODB.Connection Dim conn As ADODB.Connection '**Record Set To Check Table Records - Starts** Dim rs As ADODB.Recordset Dim rs2 As ADODB.Recordset Dim rs3 As ADODB.Recordset Dim rs4 As ADODB.Recordset '**Record Set To Check Table Records - Ends** Dim i As Long Dim strQuery As String Dim strQueryExistData As String Dim strQueryMatchCol As String Dim strQueryExcel As String Dim strFile As String Dim strSheet As String Set con = New ADODB.Connection Set conn = New ADODB.Connection Set rs = New ADODB.Recordset Set rs2 = New ADODB.Recordset Set rs3 = New ADODB.Recordset Set rs4 = New ADODB.Recordset i = 0 strFile = txtFileName.Text strSheet = "Sheet1" con.Provider = "Microsoft.ACE.OLEDB.12.0" con.ConnectionString = "Data Source = " & strFile & ";" & "Extended Properties = Excel 12.0;" conn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=Northwind;Data Source=.;" con.Open strQuery = "SELECT * FROM [" & strSheet & "$]" strQueryMatchCol = "SELECT m.[ProductId], m.[Invoice No], m.[Invoice Date], m.[Price], m.[Quantity] FROM ExcelData m" strQueryExistData = "SELECT m.[ProductId], m.[Invoice No], m.[Auto No] FROM ExcelData m" strQueryExcel = "SELECT [ProductId], [Invoice No] FROM [" & strSheet & "$]" rs.Open strQuery, con, adOpenStatic, adLockOptimistic rs2.Open strQueryMatchCol, conn, adOpenStatic, adLockOptimistic rs3.Open strQueryExistData, conn, adOpenStatic, adLockOptimistic rs4.Open strQueryExcel, con, adOpenStatic, adLockOptimistic strDate = Format(Now, "YYYY-MM-DD") + " 00:00:00" Do Until rs.EOF Dim generateId As Integer generateId = 1 '**Check Excel Column - Validation** If (rs.Fields(0).Name = rs2.Fields(0).Name And rs.Fields(1).Name = rs2.Fields(1).Name And rs.Fields(2).Name = rs2.Fields(2).Name And rs.Fields(3).Name = rs2.Fields(3).Name And rs.Fields(4).Name = rs2.Fields(4).Name And rs.Fields(0).Name <> "") Then '**Trying To Check If Product Has The Same Id and Invoice No, Then Increment By One Or Just One** '**Example - If Product Id 101 and Invoice No Inv-1000 Has Two Entries (Repeated), Then In The [Auto No] Column 'Should Be Included With The Numbers 1 and 2** Do Until rs3.EOF If (rs4.recordCount > 0) Then generateId = rs3.Fields.Item("Auto No") + 1 Else generateId = 1 End If rs3.MoveNext Loop conn.Execute ("INSERT INTO ExcelData ([ProductId], [Invoice No], [Invoice Date], [Price], [Quantity], [Auto No]) VALUES ('" + Trim(rs.Fields(0).Value) + "', '" + Trim(rs.Fields(1).Value) + "', '" + Trim(rs.Fields(2).Value) + "', '" + Trim(rs.Fields(3).Value) + "', '" + Trim(rs.Fields(4).Value) + "', '" + Trim(generateId) + "')") i = 1 Else i = 0 End If rs.MoveNext Loop If (i = 0) Then MsgBox "Column names aren't in correct order! Please check excel sheet 1.", vbInformation, "Info" ElseIf (i = 1) Then MsgBox "Uploaded!", vbInformation, "Info" End If rs.Close Set rs = Nothing con.Close conn.Close Set con = Nothing Set conn = Nothing End Sub '**Method To Upload Excel File - Ends**

    With the above code, right now only getting the below output:

    ProductId - Invoice No - Invoice Date - Price - Quantity - Auto No
    101 - Inv-1000 - 7/10/2017 10:00 - 1000 - 10 - 1
    101 - Inv-1000 - 7/10/2017 10:30 - 200 - 2 - 1
    102 - Inv-1000 - 7/10/2017 10:30 - 400 - 20 - 1
    101 - Inv-1001 - 7/11/2017 10:30 - 300 - 5 - 1
    102 - Inv-1001 - 7/11/2017 10:30 - 200 - 5 - 1


    Sunday, July 23, 2017 6:16 PM

All replies

  • This forum is for vb.net while your question is more suited to the Excel for developer forum which I'm moving your question to that forum.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, July 23, 2017 11:38 PM
  • Hi TechView-2017,

    You could iterate through the data range and set Auto No directly.

    Here is the example.

    Private Sub CommandButton1_Click()

    Dim rng As Range

    Set rng = ActiveSheet.Range("A2:F16")

    Application.ScreenUpdating = False

    'clear auton information

    rng.Columns(6).ClearContents

    For i = 1 To rng.Rows.Count

    'if auto no is nothing, this is a record with new combination of ProductID

    If IsEmpty(rng.Cells(i, 6).Value) Then

    'get current product id and invoice no

    PID = rng.Cells(i, 1).Value

    INO = rng.Cells(i, 2).Value

    Count = 0

          'loop from current row to the end

           'if a row has the same product id and invoice no, set its auto no

          For j = i To rng.Rows.Count

                If rng.Cells(j, 1).Value = PID And rng.Cells(j, 2).Value = INO Then

                Count = Count + 1

                rng.Cells(j, 6).Value = Count

                End If

          Next j

    End If

    Next i

    Application.ScreenUpdating = True

    End Sub

    ->I am validating the column names right now means if the excel sheet column doesn't match the table column, then it will not allow to upload data.

    It seems about another problem about updating data to a database? I suggest you post another thread and provide more details about this, such as which database will you upload the data...

    Best Regard,

    Terry

    Wednesday, July 26, 2017 5:13 AM
  • Hi TechView-2017,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply or provide your solution and then mark it as answer to close this thread. 
    If not, please feel free to let us know your current issue.

    Best Regards,

    Terry

    Thursday, August 3, 2017 8:15 AM