Solved

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

Posted on 2004-10-25
329 Views
Last Modified: 2010-04-23
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.').

Thanks



---  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


        cnn.Open()

        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.Close()
            rst = Nothing
        Else
            Do
                Me.lstEmailType.Items.IndexOf(rst("idEmailType").Value)
                Me.lstEmailType.Items.Add(rst("Email Type").Value)
                rst.MoveNext()
            Loop Until rst.EOF

            rst.Close()
            rst = Nothing
        End If
    End Sub



0
Question by:GabicusC
    1 Comment
     
    LVL 7

    Accepted Solution

    by:
    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.")
                Me.txtUsername.Focus()
                bFail = True
            Else
                If Me.txtPassword.Text = "" Then
                    MsgBox("Please enter a Password.")
                    Me.txtPassword.Focus()
                    bFail = True
                End If
            End If

            If bFail = False Then 'All is well
                'Connect to SQL server and validate login
                Try
                    '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
                    oComm.Parameters.Add(sParam)

                    '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.")
                        Me.txtUsername.Focus()
                    Else
                        Dim frmMain As frmMain = New frmMain
                        'Set the global variable for user logged in
                        _intUserID = intReturnID

                        'Open Main form and hide Login screen
                        frmMain.Show()
                        Me.Hide()
                    End If

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

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

    End Class
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
    Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
    Want to pick and choose which updates you receive? Feel free to check out this quick video on how to manage your email notifications.
    This video is in connection to the article "The case of a missing mobile phone (https://www.experts-exchange.com/articles/28474/The-Case-of-a-Missing-Mobile-Phone.html)". It will help one to understand clearly the steps to track a lost android phone.

    877 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now