Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-10-25
1
Medium Priority
?
347 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
Comment
Question by:Richard Comito
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 7

Accepted Solution

by:
natloz earned 1350 total points
ID: 12403889
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

618 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