Is there anything really wrong with this code?

[plenderj]

I'm a VB6 developer myself mostly, and am just starting with .Net
I've no formal training in .Net whatsoever, but have just forged ahead and tried to write an application.

Is there anything really wrong or stupid with this app so far?
i.e. dodgy coding practices that have carreid over from vb6 etc. ?

Form1.vb

Imports System.Data.SqlClient
 
Public Class Form1
 
    Inherits System.Windows.Forms.Form
    Private WithEvents mySqlConnection As sqlConnection
    Private mySQLReader As SqlDataReader
    Private blnHaveCleared As Boolean = False
    Private lngLineCount As Long, lngCurrLine As Long
    Private colHeaders As New Collection()
 
#Region " Windows Form Designer generated code "
   . . . just adds a listbox and a button
#End Region
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        mySqlConnection = New SqlConnection("Integrated Security=SSPI;Persist Security Info=False;User ID=usrShop;Initial Catalog=dbShop;Data Source=JAMIETOSH;")
        mySqlConnection.Open()
    End Sub
 
    Private Sub mySqlConnection_StateChange(ByVal sender As Object, ByVal e As System.Data.StateChangeEventArgs) Handles mySqlConnection.StateChange
        If e.CurrentState = ConnectionState.Open Then
            Dim intFileNumber As Integer = FreeFile(), strFileContent As String
            Dim strArr() As String, strFields() As String
            Dim strHeaders() As String, lngColIndex As Long, i As Long
            FileOpen(intFileNumber, "c:\test.txt", OpenMode.Input)
            strFileContent = InputString(intFileNumber, LOF(intFileNumber))
            strArr = Split(strFileContent, vbCrLf)
            lngLineCount = UBound(strArr)
            For lngCurrLine = 0 To 100 'lngLineCount
                If Not lngCurrLine = 0 Then
                    strFields = Split(strArr(lngCurrLine), vbTab)
                    sqlAddHeaders(strFields(1), strFields(0), mySqlConnection)
                    'sqlAddProductToDB(strArr(lngCurrLine), mySqlConnection)
                End If
            Next
            FileClose(intFileNumber)
            mySqlConnection.Close()
        End If
    End Sub
 
End Class

[plenderj]

modMisc.vb

Imports System.Data.SqlClient
 
Module modMisc
 
#Region "Declarations ..."
    Private blnHaveCleared(1) As Boolean
 
    Private colMasterHeaders() As clsColumnReference
    Private colSecondHeaders() As clsColumnReference
    Private colThirdHeaders() As clsColumnReference
 
    Private Const constMasterHeader As Long = 0
    Private Const constSecondaryHeader As Long = 1
    Private Const constThirdHeader As Long = 2
 
#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(0) 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(0) Then
            blnHaveCleared(0) = 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, ByVal mySqlConnection As SqlConnection)
        Dim i As Long, strHeaders() As String = Split(strData, "|"), lngParentIndex As Long
        Dim lngHeaderIndex(2) As Long
        Dim blnFoundText As Boolean = False : doInitHeaders()
 
        Dim blnAddHeader(2) As Boolean
        Dim strHeaderTable(2) As String
        strHeaderTable(0) = "tblMasterHeaders"
        strHeaderTable(1) = "tblSecondaryHeaders"
        strHeaderTable(2) = "tblThirdHeaders"
        For i = 0 To 2
            blnAddHeader(i) = False
        Next
 
 
        '''''''''''''''''''''''''''''
        '' Split up column headers ''
        '''''''''''''''''''''''''''''
 
        '' Master Headers
        ''
        For i = 0 To UBound(colMasterHeaders)
            With colMasterHeaders(i)
                If strHeaders(0) = .strColumnText Then
                    blnFoundText = True
                    lngParentIndex = i
                    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)
                blnAddHeader(constMasterHeader) = True                
            End With
        End If
        blnFoundText = False
        lngHeaderIndex(0) = lngParentIndex
 
 
        '' Secondary Headers
        ''
        For i = 0 To UBound(colSecondHeaders)
            With colSecondHeaders(i)
                If strHeaders(1) = .strColumnText Then
                    blnFoundText = True
                    lngParentIndex = i
                    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
                blnAddHeader(constSecondaryHeader) = True
            End With
        End If
        blnFoundText = False
        lngHeaderIndex(1) = lngParentIndex
 
        '' 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
                blnAddHeader(constThirdHeader) = True
            End With
        End If
        lngHeaderIndex(2) = lngParentIndex
 
        '''''''''''''''''''''''''''''
        '' Insert into SQL DB      ''
        '''''''''''''''''''''''''''''
        Dim strSQLStatement As String, mySQLCommand As SqlCommand
 
        If Not blnHaveCleared(1) Then
            For i = 0 To 2
                strSQLStatement = "DELETE FROM " & strHeaderTable(i) & " WHERE lngParentIndex = lngParentIndex"
                mySQLCommand = New SqlCommand(strSQLStatement, mySqlConnection)
                mySQLCommand.ExecuteNonQuery()
            Next
            mySQLCommand = Nothing
            blnHaveCleared(1) = True
        End If
        If blnAddHeader(0) Then
            strSQLStatement = "INSERT INTO " & strHeaderTable(0) & vbCrLf & _
                                "VALUES ( "
            strSQLStatement += lngHeaderIndex(0) & "," & -1 & "," & "'" & colMasterHeaders(lngHeaderIndex(0)).strColumnText & "'"
            strSQLStatement += ");"
            mySQLCommand = New SqlCommand(strSQLStatement, mySqlConnection)
            mySQLCommand.ExecuteNonQuery()
            mySQLCommand = Nothing
        End If
        If blnAddHeader(1) Then
            strSQLStatement = "INSERT INTO " & strHeaderTable(1) & vbCrLf & _
                                "VALUES ( "
            strSQLStatement += lngHeaderIndex(1) & "," & colSecondHeaders(lngHeaderIndex(1)).lngParentIndex & ",'" & colSecondHeaders(lngHeaderIndex(1)).strColumnText & "'"
            strSQLStatement += ");"
            mySQLCommand = New SqlCommand(strSQLStatement, mySqlConnection)
            mySQLCommand.ExecuteNonQuery()
            mySQLCommand = Nothing
        End If
        If blnAddHeader(2) Then
            strSQLStatement = "INSERT INTO " & strHeaderTable(2) & vbCrLf & _
                                "VALUES ( "
            strSQLStatement += colThirdHeaders(lngHeaderIndex(2)).lngParentIndex & ",'" & colThirdHeaders(lngHeaderIndex(2)).strColumnText & "','" & colThirdHeaders(lngHeaderIndex(2)).strChildSAPCodes & "'"
            strSQLStatement += ");"
            mySQLCommand = New SqlCommand(strSQLStatement, mySqlConnection)
            mySQLCommand.ExecuteNonQuery()
            mySQLCommand = Nothing
        End If
    End Sub
#End Region
End Module

[plenderj]

Right. Here's my update and finished and working modMisc.
See my post here to see why I'm going this : http://www.vbforums.com/showthread.p...hreadid=283087

Imports System.Data.SqlClient
 
Module modMisc
 
#Region "Declarations ..."
    Private blnHaveCleared(1) As Boolean
 
    Private colMasterHeaders() As clsColumnReference
    Private colSecondHeaders() As clsColumnReference
    Private colThirdHeaders() As clsColumnReference
 
    Private Const constMasterHeader As Long = 0
    Private Const constSecondaryHeader As Long = 1
    Private Const constThirdHeader As Long = 2
 
#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(0) 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(0) Then
            blnHaveCleared(0) = 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, ByVal mySqlConnection As SqlConnection)
        Dim i As Long, strHeaders() As String = Split(strData, "|"), lngParentIndex As Long
        Dim lngHeaderIndex(2) As Long
        Dim blnFoundText As Boolean = False : doInitHeaders()
 
        Dim blnAddHeader(2) As Boolean
        Dim strHeaderTable(2) As String
        strHeaderTable(0) = "tblMasterHeaders"
        strHeaderTable(1) = "tblSecondaryHeaders"
        strHeaderTable(2) = "tblThirdHeaders"
        For i = 0 To 2
            blnAddHeader(i) = False
        Next
 
 
        '''''''''''''''''''''''''''''
        '' Split up column headers ''
        '''''''''''''''''''''''''''''
 
        '' Master Headers
        ''
        For i = 0 To UBound(colMasterHeaders)
            With colMasterHeaders(i)
                If strHeaders(0) = .strColumnText Then
                    blnFoundText = True
                    lngParentIndex = i
                    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)
                blnAddHeader(constMasterHeader) = True                
            End With
        End If
        blnFoundText = False
        lngHeaderIndex(0) = lngParentIndex
 
 
        '' Secondary Headers
        ''
        For i = 0 To UBound(colSecondHeaders)
            With colSecondHeaders(i)
                If strHeaders(1) = .strColumnText Then
                    blnFoundText = True
                    lngParentIndex = i
                    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
                blnAddHeader(constSecondaryHeader) = True
            End With
        End If
        blnFoundText = False
        lngHeaderIndex(1) = lngParentIndex
 
        '' 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
                blnAddHeader(constThirdHeader) = True
            End With
        End If
        lngHeaderIndex(2) = lngParentIndex
 
        '''''''''''''''''''''''''''''
        '' Insert into SQL DB      ''
        '''''''''''''''''''''''''''''
        Dim strSQLStatement As String, mySQLCommand As SqlCommand
 
        If Not blnHaveCleared(1) Then
            For i = 0 To 2
                strSQLStatement = "DELETE FROM " & strHeaderTable(i) & " WHERE lngParentIndex = lngParentIndex"
                mySQLCommand = New SqlCommand(strSQLStatement, mySqlConnection)
                mySQLCommand.ExecuteNonQuery()
            Next
            mySQLCommand = Nothing
            blnHaveCleared(1) = True
        End If
        If blnAddHeader(0) Then
            strSQLStatement = "INSERT INTO " & strHeaderTable(0) & vbCrLf & _
                                "VALUES ( "
            strSQLStatement += lngHeaderIndex(0) & "," & -1 & "," & "'" & colMasterHeaders(lngHeaderIndex(0)).strColumnText & "'"
            strSQLStatement += ");"
            mySQLCommand = New SqlCommand(strSQLStatement, mySqlConnection)
            mySQLCommand.ExecuteNonQuery()
            mySQLCommand = Nothing
        End If
        If blnAddHeader(1) Then
            strSQLStatement = "INSERT INTO " & strHeaderTable(1) & vbCrLf & _
                                "VALUES ( "
            strSQLStatement += lngHeaderIndex(1) & "," & colSecondHeaders(lngHeaderIndex(1)).lngParentIndex & ",'" & colSecondHeaders(lngHeaderIndex(1)).strColumnText & "'"
            strSQLStatement += ");"
            mySQLCommand = New SqlCommand(strSQLStatement, mySqlConnection)
            mySQLCommand.ExecuteNonQuery()
            mySQLCommand = Nothing
        End If
        If blnAddHeader(2) Then
            strSQLStatement = "SELECT * FROM " & strHeaderTable(2) & " WHERE strColumnText = '" & colThirdHeaders(lngHeaderIndex(2)).strColumnText & "';"
            mySQLCommand = New SqlCommand(strSQLStatement, mySqlConnection)
            Dim mySQLReader As SqlDataReader
            mySQLReader = mySQLCommand.ExecuteReader()
            If Not mySQLReader.Read Then
                mySQLReader.Close()
                strSQLStatement = "INSERT INTO " & strHeaderTable(2) & vbCrLf & _
                                    "VALUES ( "
                strSQLStatement += colThirdHeaders(lngHeaderIndex(2)).lngParentIndex & ",'" & colThirdHeaders(lngHeaderIndex(2)).strColumnText & "','" & colThirdHeaders(lngHeaderIndex(2)).strChildSAPCodes & "'"
                strSQLStatement += ");"
                mySQLCommand = New SqlCommand(strSQLStatement, mySqlConnection)
                mySQLCommand.ExecuteNonQuery()
            Else
                mySQLReader.Close()
                strSQLStatement = "UPDATE " & strHeaderTable(2) & vbCrLf & _
                                    "SET strChildSAPCodes = '" & colThirdHeaders(lngHeaderIndex(2)).strChildSAPCodes & "' "
                strSQLStatement += " WHERE strColumnText = '" & colThirdHeaders(lngHeaderIndex(2)).strColumnText & "';"
                mySQLCommand = New SqlCommand(strSQLStatement, mySqlConnection)
                mySQLCommand.ExecuteNonQuery()
            End If
            mySQLCommand = Nothing
        End If
    End Sub
#End Region
End Module

[taxes]

Hi,

If my eyesight is working properly, you have listed code for the "Regions" section of a Module. As far as I have been taught, the code in "Regions" relates to instantiation of objects on forms and has no existence in a Module.

How did it get there? Did you write it physically?

[taxes]

Hi,


By the way, if you are very knowledgible in VB6 etc, then you will find Inheritance, Classes and Database Access the hardest to assimilate. I suggest you check out these headings in MSDN Help and do searches on this forum. Also you will find the following sites helpful (although some articles MAY be too elementary for you!!)

www.edneeis.com

www.learnvisualstudio.net

Also search this Forum for threads on migrating to VB.NET

Best of luck.

[plenderj]

Thanks for that. I also do Java development, so I know my OOP
I'm also used to using ASP w/MySQL, so DB access isn't hard.
But, getting used to ADO.Net took me a little while!

And re; the regions, I typed them in manually so I could make all the code disappear nicely

[taxes]

Hi,

"Thanks for that. I also do Java development, so I know my OOP "

Your'e fortunate. I came from dBase III Plus and VB6, so my brain went AWOL for a while (Still out there sometimes) as I found it difficult to get away from my Recordset mentality

None of the code in Regions should be typed, even on a form. It should only be created by the Windows Forms Designer as you place the controls in the GUI. The exception to this is that you can amend the code e.g. when you want to replace "Friend" with "Protected" to allow derived amendments, but even that is frowned upon and the Modifiers property of the object should be used.

Don't forget to mark the thread as "Resolved" when you have finished.

[nswan]

regions are just so you can organise you code better. If you don't need to see a section of code eg coz you know it works, you can surround it by 'regions' and hide it.

I'm not sure about using replace$...

what's the difference between replace and replace$? you can also call the function from the actual variable name now
eg

myString.replace(.....)

Don't know if one way is faster than the other?

Hope this helps
Nick

[taxes]

HI,

I just noticed something else.

Where did you put

"Imports System.Data.SqlClient"

In your posting it seems to be outside of the module.

[plenderj]

Yeah its outside the module declaration.... so what....

Regarding replace$(), in VB6, string manipulation functions returned a variant instead of a string, unless one appended '$' to the function name

[alex_read]

1) Imports must be placed above the class declaration & it's contained code - this is right.

2) To clear up the #Regions part - regions are a way of organising your code - nothing more, using them in this way is fine.

3) General notes at the moment which I can see from the top one Jamie (haven't properly looked through all of it)...

(a) If you're dealing with single lists it's more efficient to use Arraylists (system.Collections.ArrayList) - this is only for single element lists but more effective than using the vb6 array.

(b) The vb.net way of dealing with files is to use streams (like java does?) - shove a "Imports System.IO" at the top of the class & call on the File.OpenRead, File.OpenWrite methods etc.

[alex_read]

Just read through the rest of it. In VB.Net your meant to stay away from the public & constant variables more & have classes exposing these values as properties (like the properties you had in vb6 activex development)...

Apart from that, looks generally ok - well for you anyways

[plenderj]

Originally posted by alex_read
Apart from that, looks generally ok - well for you anyways

Hang on just a minute. Remember when you were over last weekend? Well I hadn't started any VB.Net, ASP.Net or ADO.Net then - and here I am, end of the week, and I'm doing all this mad crap.

So poo on you

[alex_read]

Good point - so where'd you get it all from vbcode? planetsourcecode? hahah

I'm only messing!

[Memnoch1207]

My 2 cents...

1) Use stored procedures to speed up data processing.
2) Store the ConnectionString in a single location, such as the App.Config file. You can use an application variable to hold the connectionstring.

Imports System.Configuration
 
Application("ConnectionString") = Configuration.AppSettings("ConnectionString")
 
'Or just do this
 
Dim Conn As New SqlConnection(Configuration.AppSettings("ConnectionString"))

3) Create functions to do the work for you.
Instead of doing this

strData = Replace$(strData, "'", vbNullString)
strData = Replace$(strData, vbTab, "','")
 
'you can do this
 
strData = ReplaceStuff(strData)
 
Private Function ReplaceStuff(strValue As String)
   strValue = Replace$(strValue, "'", vbNullString)
   strValue = Replace$(strValue, vbTab, ''',''')
 
   return strValue
End Function

I am sure you are aware of the small OOP since you do Java, but these little things can save alot of time in the end.