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
Full 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 poNo As String
Dim itemCode As String
Dim grnNo As String
Dim matDes As String
Dim transName As String
Dim goDown As String
Dim vendorName As String
Dim process As String
Dim vendorDnNo As String
Dim rcvQty As String
Dim unit As String
Dim totalAmt As String
Dim invoiceNo As String
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
You should refresh the content of your rs3 recordset, to get the current data, and therefore the updated number of records, otherwise you will have always the data at the time you opened it.
As you are using a module-level variable to store the number of records, then inside your loop, after the conn.Execute ("INSERT INTO.... you could simply use: rs3.requery. This is in someway equivalent as you would close and reopen the rs3 recordset before your INSERT INTO statement.
EDIT:
Moreover, your snippet Do Until rs3.EOF... seems to me it should be replaced by a query like this: SELECT ISNULL(MAX([Auto No]),0) FROM ExcelData WHERE [Invoice No]= 'Inv-1000' AND YEAR([Invoice Date]) = 2017. You should increment that result. Please, pay attention how you compare the date, and for this selection query, strip off the time part. You will find the result inside rs3.fields("Auto No"). No need here to loop over the records. You should requery this recordset after every insert.
EDIT2: Here an untested pseudo-code snippet, you should get the idea:
fmtString = "YYYY-MM-DD hh:mm:ss" ' you should adapt this to your need '
rs.Open "SELECT * FROM [" & strSheet & "$]", con, adOpenForwardOnly
Do Until rs.EOF
InvNo = Trim(rs.Fields("InvoiceNo").Value)
InvDateTime = CDate(rs.Fields("Invoice Date").Value)
' separate year from full datetime, depending how the cell format is '
InvYear = Format(InvDateTime, "YYYY")
' compose the query to get the Auto No '
sql = "SELECT ISNULL(MAX([Auto No]),0) AS MaxNo FROM ExcelData"
sql += " WHERE [Invoice No] = '" + InvNo + "' "
sql += " AND YEAR([Invoice Date]) = '" + InvYear + "'"
Set rsCheck = conn.Execute(sql) ' get the Auto No '
AutoNo = rsCheck.Fields("MaxNo").Value + 1
' compose the query to store the invoices '
sql = "INSERT INTO ExcelData ("
sql += "ProductId, "
sql += "[Invoice No], "
sql += "[Invoice Date], "
sql += "Price, "
sql += "Quantity, "
sql += "[Auto No]"
sql += ") VALUES ("
sql += "'" + Trim(rs.Fields("ProductId").Value) + "',"
sql += "'" + InvNo + "',"
sql += "'" + Format(InvDateTime, fmtString) + "',"
sql += CCur(Trim(rs.Fields("Price").Value)) + ","
sql += CSng(Trim(rs.Fields("Quantity").Value)) + ","
sql += AutoNo
sql += ")"
conn.Execute(sql) ' store the invoice '
rs.MoveNext
Loop
IMHO it would be better if you store native data types in your table instead of all text values, then you may need here also to check the data-type of the Excel cell and provide the correct conversion against the column type of your destination table. Sorry, but I believe the topic of the datatype conversion is too broad and it won't fit here - you may better post some new questions for that.
If you prefer to store all values as text to avoid all this boring data-type conversions, i would suggest you to store dates in the format YYYY-MM-DD HH:MM:SS (24, no AM/PM) and AutoNo in a fixed length format 001..002..003 so they are at least correctly sorted.
EDIT3:
If your import based on the above snippet is tested and working, you can rewrite your check for already imported lines, by using the exact date time of the Excel file, as follows:
fmtString = "YYYY-MM-DD hh:mm:ss" ' you should adapt this to your need '
rs.Open "SELECT * FROM [" & strSheet & "$]", con, adOpenForwardOnly
Do Until rs.EOF
InvNo = Trim(rs.Fields("InvoiceNo").Value)
InvDateTime = CDate(rs.Fields("Invoice Date").Value)
ProdId = Trim(rs.Fields("ProductId").Value)
' compose the query to get the same invoice line '
sql = "SELECT COUNT(*) AS ThisLine FROM ExcelData"
sql += " WHERE [Invoice No] = '" + InvNo + "' "
sql += " AND [Invoice Date] = '" + Format(InvDateTime, fmtString) + "'"
sql += " AND [ProductId] = '" + ProdId + "' "
Set rsCheck = conn.Execute(sql) ' get the current line '
If rsCheck.Fields("ThisLine").Value = 0 Then
' compose the query to get the Auto No '
...
...
conn.Execute(sql) ' store the invoice '
End If
rs.MoveNext
Loop
If you have enough time resolution in your data, i believe you can skip the check for the same quantity, up to you, but anyway, you should investigate the content of the Invoice Date field in your Excel file).