• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

Need to set up a Database connection class to be used throughout a project

I am trying to create a Database Class to be used throughout a project.  I have written the following code.  I am more familiar with ASP coding then I am with VB.net and I am having trouble calling the class properly to be used within the from code.   The error that I am getting is (Type 'SqlConn.Conn' is not defined.').


---  Class Code

Public Class SqlConn
    Public Function Conn()
        Dim cnn = New ADODB.Connection
        cnn.ConnectionString = "Provider=SQLOLEDB.1;Password=xxx;Persist Security Info=True;User Id=xxx;Initial Catalog=xxx;Data Source=xxx"
        cnn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
    End Function
End Class

----- Form Code

 Public Sub frmListServEmailer_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim rst As ADODB.Recordset
        Dim strQuery As String
       Dim cnn As SqlConn.conn


        rst = New ADODB.Recordset
        rst.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        rst.LockType = ADODB.LockTypeEnum.adLockReadOnly
        strQuery = "Select idEmailType, fldEmailDescription as [Email Type] from tblEmailType order by idEmailType"
        rst = cnn.execute(strQuery)

        If rst.EOF Then
            rst = Nothing
                Me.lstEmailType.Items.Add(rst("Email Type").Value)
            Loop Until rst.EOF

            rst = Nothing
        End If
    End Sub

Richard Comito
Richard Comito
1 Solution
Here is a sample from one of my projects....see the LOOK HERE labels below

Public Class clsDBConn
    'Returns the connection string being used to access SQL database
    Public Function getStrConn() As String
        Dim strConn As String

               'PRODUCTION EDMAPPS2K1
        'strConn = "User ID=orm;Password=YOURPASSWORD;Initial Catalog=OUTSIDERENTALS;Data Source=EDMAPPS2K1"

        Return strConn
    End Function
End Class

 'Variables and objects
        Dim cDB As clsDBConn = New clsDBConn 'Connection Class <----------LOOK HERE
        Dim strConn As String = cDB.getStrConn 'Get connection string for database <----------LOOK HERE
        Dim oConn As New SqlConnection 'SQL connection object
        Dim intReturnID As Integer 'Return ID from Stored Procedures
        Dim oComm As SqlCommand 'SQL Command object
        Dim bFail As Boolean = False 'Test for failure

        'Validate form has data
        If Me.txtUsername.Text = "" Then
            MsgBox("Please enter a User Name.")
            bFail = True
            If Me.txtPassword.Text = "" Then
                MsgBox("Please enter a Password.")
                bFail = True
            End If
        End If

        If bFail = False Then 'All is well
            'Connect to SQL server and validate login
                'Pass connection string to SqlConnection
                oConn.ConnectionString = strConn <----------LOOK HERE

                'SQL Database statements
                oComm = New SqlCommand
                oComm.CommandType = CommandType.StoredProcedure
                oComm.CommandText = "spValidateLogin"
                oComm.Connection = oConn

                'Send in parameters
                oComm.Parameters.Add(New SqlParameter("@varUserName", SqlDbType.VarChar)).Value = Me.txtUsername.Text
                oComm.Parameters.Add(New SqlParameter("@varPassword", SqlDbType.VarChar)).Value = Me.txtPassword.Text

                'Return value from stored procedure
                Dim sParam As SqlParameter
                sParam = New SqlParameter
                sParam.ParameterName = "@ReturnID"
                sParam.SqlDbType = SqlDbType.Int
                sParam.Direction = ParameterDirection.Output

                'Open connection and execute oComm
                oConn.Open() 'Open connection
                oComm.ExecuteNonQuery() 'Execute stored procedure
                intReturnID = oComm.Parameters("@ReturnID").Value 'Get Return value
                oConn.Close() 'Close connection

                'Analyze Return value for errors
                If intReturnID < 0 Then 'All errors are less than 0
                    MsgBox("Invalid Login, please try again.")
                    Dim frmMain As frmMain = New frmMain
                    'Set the global variable for user logged in
                    _intUserID = intReturnID

                    'Open Main form and hide Login screen
                End If

            Catch ex As Exception
                MsgBox("Error connecting to SQL Server")
                oConn.Close() 'Close connection
            End Try
        End If

        Me.Cursor = System.Windows.Forms.Cursors.Default
    End Sub

End Class

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now