S
staspe
vb.net 2003
access 2003
excel 2003
If 150 records exist in tblData, there will 150 results in Excel...
It will loop starting at row(record) 1 in tblData and loop to record
150...
So actually the sql for tblData could just read:
dbCommand = New Data.OleDb.OleDbCommand("SELECT fldWwg FROM tblData",
fldWwg (150 records exist in tblData)
2YY34A
1A324
1A667
1ASD3
2YY35A
2ARR5
2YY56
etc...
When a match is found in tblCoreSkuinformation...The following is
returned to excel.
"fldWwg" from tblData and
"ITEM", "WWGDESC" FROM tblCoreSkuInformation(Lets say there are 200
records here)
"ITEM" is the primary key here SO ONLY ONE RECORD WILL EXIST(ONE TO
ONE)
2YY34 HITS ON 2YY34A Stops loop at record 143 ,,, results
returned : Loop Ends move to NEXT record in tblData
1a324
'==================================================
fldWwg Item WWGDESC
2YY34A 2YY34 WIDGET, ALL KINDS
1A324 1A324 WIDGET BASKETBALL
1A667 NOT FOUND <---- THIS DID NOT RETURN DATA FROM
tblCoreSkuinformation so post "NOT FOUND"
1ASD3 1ASD3 GRAPES, SUNNY VALLEY
2YY35A 2YY35 SCOOTER, 2 WHEELS
2ARR5 NO FOUND <---- THIS DID NOT RETURN DATA FROM
tblCoreSkuinformation "NOT FOUND"
2YY56 2YY56 TENNIS BALL, DIRT COURT
etc...for 150 returns
Question: when "2YY34A" is taken to match "2YY34". Are we saving the
former, or the latter?
Answer :"Both"
Question: And are we saving any of the other fields from whichever of
the tables that version comes?
Answer: Yes as stated above...
Question: And, having found a 'match' for "2YY34A" once, do we then
have to continue looking through all the rest of the records in the
other table to see if there are any more matches?
Stuck on the looping part?
CAN'T PROPERLY LOOP THROUGH TBLDATA TABLE?
Private Sub FirstSearch()
Dim dbConnection As Data.OleDb.OleDbConnection
Dim dbCommand As Data.OleDb.OleDbCommand
Dim dbDataAdapter As Data.OleDb.OleDbDataAdapter
Dim dbCommandBuilder As Data.OleDb.OleDbCommandBuilder
Dim dbDataSet As Data.DataSet
Dim dbCommand2 As Data.OleDb.OleDbCommand
Dim dbDataAdapter2 As Data.OleDb.OleDbDataAdapter
Dim dbCommandBuilder2 As Data.OleDb.OleDbCommandBuilder
Dim dbDataSet2 As Data.DataSet
dbConnection = New
Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\DM2007\SkuCat.mdb;User Id=admin;Password=;")
dbConnection.Open()
dbCommand = New Data.OleDb.OleDbCommand("SELECT fldWwg,
fldMfgname, fldMfrnum, fldDescription FROM tblData", dbConnection)
dbCommand2 = New Data.OleDb.OleDbCommand("SELECT ITEM,
WWGDESC FROM tblCoreSkuInformation", dbConnection)
dbDataAdapter = New Data.OleDb.OleDbDataAdapter(dbCommand)
dbCommandBuilder = New
Data.OleDb.OleDbCommandBuilder(dbDataAdapter)
dbDataSet = New Data.DataSet
dbDataAdapter2 = New Data.OleDb.OleDbDataAdapter(dbCommand2)
dbCommandBuilder2 = New
Data.OleDb.OleDbCommandBuilder(dbDataAdapter2)
dbDataSet2 = New Data.DataSet
dbDataAdapter.Fill(dbDataSet, "tblData")
dbDataAdapter2.Fill(dbDataSet2, "tblCoreSkuInformation")
With dbDataSet.Tables("tblData")
For Each dbRow As Data.DataRow In
dbDataSet.Tables("tblData").Rows
Dim input As String = dbRow.Item("fldWwg")
Dim pattern As String = "(.*?)(\d[a-zA-Z]{1}\w\w\d[a-
zA-Z]?)(.*)"
Dim mc As MatchCollection = Regex.Matches(input,
pattern)
Dim output As String = ""
Dim trimStr() As Char = {" "}
' at some point in the code below, I need to capture
the array of Match/nonmatch items
'then present these items in an excel sheet.
' The looping below is not correct..it appears to
searching and finding ok.. but is
' not looping the tblData table correctly
For Each m As Match In mc
output = m.Groups(2).Value
For Each dbRow2 As Data.DataRow In
dbDataSet2.Tables("tblCoreSkuInformation").Rows
Dim strItem As String
strItem = dbRow2.Item(0)
'dbRow2.Item("ITEM") = output.TrimEnd(trimStr)
If dbRow2.Item("ITEM") =
output.TrimEnd(trimStr) Then
MsgBox("SUCCESS")
Else '
MessageBox.Show(dbRow.Item("fldwwg"))
' MsgBox("NOMATCH")
End If
Next
Next
Next
' dbDataAdapter.Update(dbDataSet, "tblData")
End With
dbDataAdapter.Update(dbDataSet, "tblData")
access 2003
excel 2003
If 150 records exist in tblData, there will 150 results in Excel...
It will loop starting at row(record) 1 in tblData and loop to record
150...
So actually the sql for tblData could just read:
dbCommand = New Data.OleDb.OleDbCommand("SELECT fldWwg FROM tblData",
fldWwg (150 records exist in tblData)
2YY34A
1A324
1A667
1ASD3
2YY35A
2ARR5
2YY56
etc...
When a match is found in tblCoreSkuinformation...The following is
returned to excel.
"fldWwg" from tblData and
"ITEM", "WWGDESC" FROM tblCoreSkuInformation(Lets say there are 200
records here)
"ITEM" is the primary key here SO ONLY ONE RECORD WILL EXIST(ONE TO
ONE)
2YY34 HITS ON 2YY34A Stops loop at record 143 ,,, results
returned : Loop Ends move to NEXT record in tblData
1a324
'==================================================
fldWwg Item WWGDESC
2YY34A 2YY34 WIDGET, ALL KINDS
1A324 1A324 WIDGET BASKETBALL
1A667 NOT FOUND <---- THIS DID NOT RETURN DATA FROM
tblCoreSkuinformation so post "NOT FOUND"
1ASD3 1ASD3 GRAPES, SUNNY VALLEY
2YY35A 2YY35 SCOOTER, 2 WHEELS
2ARR5 NO FOUND <---- THIS DID NOT RETURN DATA FROM
tblCoreSkuinformation "NOT FOUND"
2YY56 2YY56 TENNIS BALL, DIRT COURT
etc...for 150 returns
Question: when "2YY34A" is taken to match "2YY34". Are we saving the
former, or the latter?
Answer :"Both"
Question: And are we saving any of the other fields from whichever of
the tables that version comes?
Answer: Yes as stated above...
Question: And, having found a 'match' for "2YY34A" once, do we then
have to continue looking through all the rest of the records in the
other table to see if there are any more matches?
Stuck on the looping part?
CAN'T PROPERLY LOOP THROUGH TBLDATA TABLE?
Private Sub FirstSearch()
Dim dbConnection As Data.OleDb.OleDbConnection
Dim dbCommand As Data.OleDb.OleDbCommand
Dim dbDataAdapter As Data.OleDb.OleDbDataAdapter
Dim dbCommandBuilder As Data.OleDb.OleDbCommandBuilder
Dim dbDataSet As Data.DataSet
Dim dbCommand2 As Data.OleDb.OleDbCommand
Dim dbDataAdapter2 As Data.OleDb.OleDbDataAdapter
Dim dbCommandBuilder2 As Data.OleDb.OleDbCommandBuilder
Dim dbDataSet2 As Data.DataSet
dbConnection = New
Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\DM2007\SkuCat.mdb;User Id=admin;Password=;")
dbConnection.Open()
dbCommand = New Data.OleDb.OleDbCommand("SELECT fldWwg,
fldMfgname, fldMfrnum, fldDescription FROM tblData", dbConnection)
dbCommand2 = New Data.OleDb.OleDbCommand("SELECT ITEM,
WWGDESC FROM tblCoreSkuInformation", dbConnection)
dbDataAdapter = New Data.OleDb.OleDbDataAdapter(dbCommand)
dbCommandBuilder = New
Data.OleDb.OleDbCommandBuilder(dbDataAdapter)
dbDataSet = New Data.DataSet
dbDataAdapter2 = New Data.OleDb.OleDbDataAdapter(dbCommand2)
dbCommandBuilder2 = New
Data.OleDb.OleDbCommandBuilder(dbDataAdapter2)
dbDataSet2 = New Data.DataSet
dbDataAdapter.Fill(dbDataSet, "tblData")
dbDataAdapter2.Fill(dbDataSet2, "tblCoreSkuInformation")
With dbDataSet.Tables("tblData")
For Each dbRow As Data.DataRow In
dbDataSet.Tables("tblData").Rows
Dim input As String = dbRow.Item("fldWwg")
Dim pattern As String = "(.*?)(\d[a-zA-Z]{1}\w\w\d[a-
zA-Z]?)(.*)"
Dim mc As MatchCollection = Regex.Matches(input,
pattern)
Dim output As String = ""
Dim trimStr() As Char = {" "}
' at some point in the code below, I need to capture
the array of Match/nonmatch items
'then present these items in an excel sheet.
' The looping below is not correct..it appears to
searching and finding ok.. but is
' not looping the tblData table correctly
For Each m As Match In mc
output = m.Groups(2).Value
For Each dbRow2 As Data.DataRow In
dbDataSet2.Tables("tblCoreSkuInformation").Rows
Dim strItem As String
strItem = dbRow2.Item(0)
'dbRow2.Item("ITEM") = output.TrimEnd(trimStr)
If dbRow2.Item("ITEM") =
output.TrimEnd(trimStr) Then
MsgBox("SUCCESS")
Else '
MessageBox.Show(dbRow.Item("fldwwg"))
' MsgBox("NOMATCH")
End If
Next
Next
Next
' dbDataAdapter.Update(dbDataSet, "tblData")
End With
dbDataAdapter.Update(dbDataSet, "tblData")