Creating a general SQL module in VB.NET

Question

Hey everyone. I'm fairly new to VB.NET and I'm looking to create a module that will contain all general SQL functionality like connect, disconnect and execute a sql query etc.

I think I'm almost there but the code keeps bombing in one place.

Can anyone see what is wrong with the following code?

It bombs here, setting the command object to a connection object. The opening and closing of the connection works fine.

cmdSystem.Connection = cnSystem

Or maybe I'm just thinking old VB and am going about this all wrong.

Public Module modGeneral

Private cnSystem As New SqlClient.SqlConnection
Private cmdSystem As SqlClient.SqlCommand

Public Sub ConnectToSQL()

    Dim sConnectionString As String = "Data Source=SQL;Initial Catalog=XXXX;User ID=XXXX;Password=XXXX"

    Try
        cnSystem.ConnectionString = sConnectionString
        cnSystem.Open()
    Catch ex As Exception

    End Try

End Sub
Public Sub DisconnectFromSQL()

    Try
        cnSystem.Close()
        cnSystem.Dispose()
    Catch ex As Exception

    End Try

End Sub
Public Function lExecuteSQL(ByVal sSQL As String) As Long

    Dim lRecordsAffected As Long = 0

    Try
        cmdSystem.Connection = cnSystem
        cmdSystem.CommandText = sSQL
        lRecordsAffected = cmdSystem.ExecuteNonQuery()
        cmdSystem.Dispose()
    Catch ex As Exception

    End Try

    Return lRecordsAffected

End Function

End Module

Thanks in advance.

Answer 1

at some point, you need to instantiate your command object like you did the connection.

have you considered having these functions in a class instead of a module?