Imports System.Data.SqlClient
Module modMisc
#Region "Declarations ..."
Private blnHaveCleared As Boolean
Private colMasterHeaders() As clsColumnReference
Private colSecondHeaders() As clsColumnReference
Private colThirdHeaders() As clsColumnReference
#End Region
#Region "clsColumnReference()"
Class clsColumnReference
Public strColumnText As String
Public lngParentIndex As Long
Public blnIsMasterParent As Boolean
Public strChildSAPCodes As String
End Class
#End Region
#Region "sqlAddProductToDB()"
Public Sub sqlAddProductToDB(ByVal strData As String, ByVal mySqlConnection As SqlConnection)
Dim strSQLStatement As String
If Not blnHaveCleared Then
strSQLStatement = "DELETE FROM tblProducts WHERE strProductReference = strProductReference;"
Else
strData = Replace$(strData, "'", vbNullString)
strData = Replace$(strData, vbTab, "','")
strSQLStatement = _
"INSERT INTO tblProducts " & _
"(strProductReference, strSectionText, strShortDescription, strDetailURL, strTradePrice, strPrice, strDetailLink, strFullDescription, strRRP, strStock) VALUES " & _
"(" & "'" & strData & "'" & ");"
End If
Dim mySQLCommand As New SqlCommand(strSQLStatement, mySqlConnection)
'ListBox1.Items.Add(strPrepare(strSQLStatement))
Try
mySQLCommand.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message & vbCrLf & ex.StackTrace & vbCrLf & vbCrLf & strSQLStatement)
End Try
If Not blnHaveCleared Then
blnHaveCleared = True
sqlAddProductToDB(strData, mySqlConnection)
End If
End Sub
#End Region
#Region "doInitHeaders()"
Private Sub doInitHeaders()
If colMasterHeaders Is Nothing Then ReDim colMasterHeaders(0) : colMasterHeaders(0) = New clsColumnReference()
If colSecondHeaders Is Nothing Then ReDim colSecondHeaders(0) : colSecondHeaders(0) = New clsColumnReference()
If colThirdHeaders Is Nothing Then ReDim colThirdHeaders(0) : colThirdHeaders(0) = New clsColumnReference()
End Sub
#End Region
#Region "sqlAddHeaders()"
Public Sub sqlAddHeaders(ByVal strData As String, ByVal strChildSAPCode As String)
Dim i As Long, strHeaders() As String = Split(strData, "|"), lngParentIndex As Long
Dim blnFoundText As Boolean = False : doInitHeaders()
'' Master Headers
''
For i = 0 To UBound(colMasterHeaders)
With colMasterHeaders(i)
If strHeaders(0) = .strColumnText Then
blnFoundText = True
[b] lngParentIndex = i[/b]
Exit For
End If
End With
Next
If Not blnFoundText Then
ReDim Preserve colMasterHeaders(UBound(colMasterHeaders) + 1)
colMasterHeaders(UBound(colMasterHeaders)) = New clsColumnReference()
With colMasterHeaders(UBound(colMasterHeaders))
.blnIsMasterParent = True
.lngParentIndex = -1
.strColumnText = strHeaders(0)
lngParentIndex = UBound(colMasterHeaders)
End With
End If
blnFoundText = False
'' Secondary Headers
''
For i = 0 To UBound(colSecondHeaders)
With colSecondHeaders(i)
If strHeaders(1) = .strColumnText Then
blnFoundText = True
[b] lngParentIndex = i[/b]
Exit For
End If
End With
Next
If Not blnFoundText Then
ReDim Preserve colSecondHeaders(UBound(colSecondHeaders) + 1)
colSecondHeaders(UBound(colSecondHeaders)) = New clsColumnReference()
With colSecondHeaders(UBound(colSecondHeaders))
.blnIsMasterParent = False
.lngParentIndex = lngParentIndex
.strColumnText = strHeaders(1)
lngParentIndex = i
End With
End If
blnFoundText = False
'' Third Headers
''
For i = 0 To UBound(colThirdHeaders)
If strHeaders(2) = colThirdHeaders(i).strColumnText Then
colThirdHeaders(i).strChildSAPCodes += strChildSAPCode
blnFoundText = True
Exit For
End If
Next
If Not blnFoundText Then
ReDim Preserve colThirdHeaders(UBound(colThirdHeaders) + 1)
colThirdHeaders(UBound(colThirdHeaders)) = New clsColumnReference()
With colThirdHeaders(UBound(colThirdHeaders))
.blnIsMasterParent = False
.lngParentIndex = lngParentIndex
.strColumnText = strHeaders(2)
.strChildSAPCodes = strChildSAPCode
End With
End If
Dim colThirdLevel As New clsColumnReference()
colThirdLevel = colThirdHeaders(UBound(colThirdHeaders))
Dim colSecondLevel As New clsColumnReference()
colSecondLevel = colSecondHeaders(colThirdLevel.lngParentIndex)
Dim colMasterLevel As New clsColumnReference()
colMasterLevel = colMasterHeaders(colSecondLevel.lngParentIndex)
doLogFile(colMasterLevel.strColumnText & ":" & _
colSecondLevel.strColumnText & ":" & _
colThirdLevel.strColumnText & ":" & _
colThirdLevel.strChildSAPCodes)
End Sub
Private Sub doLogFile(ByVal strString As String)
Dim lngFileHandle As Long = FreeFile()
FileOpen(lngFileHandle, "c:\mylog.txt", OpenMode.Append)
Print(lngFileHandle, strString & vbCrLf)
FileClose(lngFileHandle)
End Sub
#End Region
End Module
