none
Shortcut to repetitive code RRS feed

  • Question

  • Afternoon, the following code is being used across my whole program:   

    con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\Users\BusinessOffice1Room\Documents\Waliur File\A Level\Computer Science\Coursework\Work In progression\State of War League Table.mdb"

    'con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source = N:\Computer Science Coursework\Work In progression\State of War League Table.mdb"

    Is there a shortcut approach to this without messing up the program such as making it a public sub. 

    Saturday, March 4, 2017 3:57 PM

Answers

  • Add this class to your project

    Public Class Connections
        Private Shared _Instance As Connections
        Public Property ConnectionString As String
    
        <DebuggerStepThrough()>
        Protected Sub New()
            Me.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" &
                "Data Source = C:\Users\BusinessOffice1Room\Documents\" &
                "Waliur File\A Level\Computer Science\Coursework\Work In " &
                "progression\State of War League Table.mdb"
        End Sub
        <DebuggerStepThrough()>
        Public Shared Function Instance() As Connections
            If _Instance Is Nothing Then
                _Instance = New Connections
            End If
            Return _Instance
        End Function
    End Class
    

    Use it anywhere in the project

    Using cn As New OleDb.OleDbConnection With {.ConnectionString = Connections.Instance.ConnectionString}
    
    End Using


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Waliur Rahman Saturday, March 4, 2017 11:35 PM
    • Unmarked as answer by Waliur Rahman Saturday, March 4, 2017 11:41 PM
    • Marked as answer by Waliur Rahman Monday, March 6, 2017 1:29 PM
    Saturday, March 4, 2017 5:40 PM
    Moderator

All replies

  • The only way I know of is to declare "con" at form level and put the assignment in a sub. If "con" is declared at form level, once its connection string is set, you should not need to set it again unless you are changing it.
    • Marked as answer by Waliur Rahman Saturday, March 4, 2017 11:35 PM
    • Unmarked as answer by Waliur Rahman Saturday, March 4, 2017 11:35 PM
    Saturday, March 4, 2017 4:29 PM
  • I see, thanks for the help bro I find the help is better than nothing. Im glad these forums are about because Im to some extent equivalent to noob level programming and help from people can help me progress along the way 
    Saturday, March 4, 2017 4:57 PM
  • Add this class to your project

    Public Class Connections
        Private Shared _Instance As Connections
        Public Property ConnectionString As String
    
        <DebuggerStepThrough()>
        Protected Sub New()
            Me.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" &
                "Data Source = C:\Users\BusinessOffice1Room\Documents\" &
                "Waliur File\A Level\Computer Science\Coursework\Work In " &
                "progression\State of War League Table.mdb"
        End Sub
        <DebuggerStepThrough()>
        Public Shared Function Instance() As Connections
            If _Instance Is Nothing Then
                _Instance = New Connections
            End If
            Return _Instance
        End Function
    End Class
    

    Use it anywhere in the project

    Using cn As New OleDb.OleDbConnection With {.ConnectionString = Connections.Instance.ConnectionString}
    
    End Using


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Waliur Rahman Saturday, March 4, 2017 11:35 PM
    • Unmarked as answer by Waliur Rahman Saturday, March 4, 2017 11:41 PM
    • Marked as answer by Waliur Rahman Monday, March 6, 2017 1:29 PM
    Saturday, March 4, 2017 5:40 PM
    Moderator
  • Add this class to your project...

    Interesting!

    When I read this a minute ago, my first thought was to use a Singleton class for it.


    "One who has no vices also has no virtues..."

    • Marked as answer by Waliur Rahman Saturday, March 4, 2017 10:49 PM
    • Unmarked as answer by Waliur Rahman Saturday, March 4, 2017 11:35 PM
    Saturday, March 4, 2017 5:53 PM
  • Waliur,

    I think you got confused on your marking of the answerer(s) here; I didn't answer anything.

    Please modify your selection.

    Thanks :-)


    "One who has no vices also has no virtues..."


    Saturday, March 4, 2017 10:58 PM
  • Oh apologies I'm new to this entire forum started today as well
    • Marked as answer by Waliur Rahman Saturday, March 4, 2017 11:35 PM
    • Unmarked as answer by Waliur Rahman Saturday, March 4, 2017 11:35 PM
    Saturday, March 4, 2017 11:34 PM
  • I tried that code but shows an error of unhandled exception. I got a code that connects to the database which is basically a login system which checks if the username has already signed in and verifies if the username is already taken
    Imports System.Data.OleDb
    Public Class Sign_In_Player_1
        Dim con As New OleDb.OleDbConnection
        Dim dbProvider As String
        Dim dbSource As String
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim maxRows As Integer
        Public UserOne As String
        Public UserOneMedals As String
    
        Private Sub Sign1_Click(sender As Object, e As EventArgs) Handles Sign1.Click
            If UsernameB.Text = Nothing Or PasswordB.Text = Nothing Then
                MessageBox.Show("empty fields", "Log in Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
    
            'con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\Users\BusinessOffice1Room\Documents\Waliur File\A Level\Computer Science\Coursework\Work In progression\State of War League Table.mdb"
            ''con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source = N:\Computer Science Coursework\Work In progression\State of War League Table.mdb"
            'con.Open()
            sql = "SELECT * From UserStorage WHERE Username= " & "'" & UsernameB.Text & "'"
            da = New OleDb.OleDbDataAdapter(sql, con)
            da.Fill(ds, "UserStorageDS1")
            con.Close()
    
            If ds.Tables("UserStorageDS1").Rows.Count > 0 Then
                If ds.Tables("UserStorageDS1").Rows(0).Item("Password").ToString = PasswordB.Text And Not Sign_In_player_2.userTwo = UsernameB.Text Then
                    MessageBox.Show("Welcome " & UsernameB.Text)
                    UserOne = UsernameB.Text
                    UserOneMedals = ds.Tables("UserStorageDS1").Rows(0).Item("Medals")
                    Me.Hide()
                    Sign_In_player_2.Show()
                Else
                    If ds.Tables("UserStorageDS1").Rows(0).Item("Password").ToString = PasswordB.Text And Sign_In_player_2.userTwo = UsernameB.Text Then
                        MessageBox.Show("account in use", "error", MessageBoxButtons.OK)
                    Else
                        MessageBox.Show("The Username and Password don't match", "error", MessageBoxButtons.OK)
                    End If
                End If
            End If
        End Sub

    Saturday, March 4, 2017 11:43 PM
  • Waliur,

    Your particular use aside, let's briefly talk about the title of your thread here.

    I mentioned that Karen's class used the Singleton pattern so just what is that and how is it useful?

    The concept is that by taking advantage of a private (or Protected) constructor and a single shared "instance" of the class, only one single instance can ever exist.

    This is a great way to set up something where you might want to disallow any but one instance (I use it for user settings, but of course don't forget about Application Settings). For example:

    Option Strict On Option Explicit On Option Infer Off Public Class Form1 Private _settings As UserSettings Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load _settings = UserSettings.GetInstance Stop End Sub End Class Public Class UserSettings Private Sub New() End Sub Private Shared _instance As UserSettings Private _colorPreference As Color = Color.Azure Public Shared Function GetInstance() As UserSettings If _instance Is Nothing Then _instance = New UserSettings End If Return _instance End Function Public Property ColorPreference As Color Get Return _colorPreference End Get Set(ByVal value As Color) _colorPreference = value End Set End Property End Class


    Please note that in the form's .Load event handler, I create a new instance using the way that I set it up to work: With the shared function that I call "GetInstance".

    The exact way it's done isn't important; it's the concept that matters. There can only ever be one single instance because of the use of a shared member of the class.

    Does that make sense?


    "One who has no vices also has no virtues..."


    Sunday, March 5, 2017 12:16 AM
  • Hello,

    This is a pattern to consider. Most logins will ask to register or login. The following would be for a login for an existing user and also a method to determine if the user name selected is available.

    Normally the IsNullOrWhiteSpace would be handled by the caller so keep that in mind. Connections is in my first reply.

    Imports System.Data.OleDb
    Public Class Login
        Public Function UserLogin(ByVal UserName As String, ByVal UserPassword As String) As Boolean
            If Not String.IsNullOrWhiteSpace(UserName) AndAlso Not String.IsNullOrWhiteSpace(UserPassword) Then
                Using cn As New OleDbConnection With {.ConnectionString = Connections.Instance.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "SELECT * From UserStorage WHERE Username = @UserName AND Password = @UserPassword"
                        cmd.Parameters.AddWithValue("@UserName", UserName)
                        cmd.Parameters.AddWithValue("@UserPassword", UserPassword)
                        cn.Open()
                        Dim Reader = cmd.ExecuteScalar
                        If Reader IsNot Nothing Then
                            ' name and password match
                            Return True
                        Else
                            ' bad login
                            Return False
                        End If
                    End Using
                End Using
            Else
                Return False
            End If
        End Function
        Public Function UserNameExists(ByVal UserName As String) As Boolean
            If Not String.IsNullOrWhiteSpace(UserName) Then
                Using cn As New OleDbConnection With {.ConnectionString = Connections.Instance.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "SELECT * From UserStorage WHERE Username = @UserName"
                        cmd.Parameters.AddWithValue("@UserName", UserName)
                        cn.Open()
                        Dim Reader = cmd.ExecuteScalar
                        If Reader IsNot Nothing Then
                            ' user name taken
                            Return True
                        Else
                            ' user name available
                            Return False
                        End If
                    End Using
                End Using
            Else
                Return False
            End If
        End Function
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, March 5, 2017 12:24 AM
    Moderator
  • I see so from this code, whenever I want to use it again, do i just write name of form inherits connection or along those lines
    Sunday, March 5, 2017 11:14 AM
  • Frank what does the quote mean its quite curious i saw it again somewhere else
    Sunday, March 5, 2017 11:15 AM
  • I just wanna mention just to make sure this is a wondows form application, I should've mention that before hand. Im just new to the MSDN forums and I hope to use this to help me become a better coder even if things get difficult.
    Sunday, March 5, 2017 11:18 AM
  • In an hour or so Im goig to start a the other feature a discussions page so that i can stay online and see what people have to say plus it's simultaneously like a group chat
    Sunday, March 5, 2017 11:19 AM
  • I just wanna mention just to make sure this is a wondows form application, I should've mention that before hand. Im just new to the MSDN forums and I hope to use this to help me become a better coder even if things get difficult.

    Hello,

    We assumed you were writing a Windows Forms application :-)

    In regards to a former reply you posted, you indicated there was a error which does no good to us unless you tell us what the error message was.



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, March 5, 2017 11:27 AM
    Moderator
  • The method seen as correct for this is to use the Config.Sys

    It is an XML file, you can use it in your program by Settings

    https://msdn.microsoft.com/en-us/library/ms254494(v=vs.110).aspx


    Success
    Cor

    Sunday, March 5, 2017 12:03 PM
  • Alright its a good idea I make the error code specific and make sure I take note of them. Im gonna make a chat so I can keep an eye on updated comments
    Sunday, March 5, 2017 12:55 PM
  • Quick question I should have mentioned this before hand darn it, I have sql codes such as the following:

    sql = "SELECT * From UserStorage WHERE Username= " & "'" & UsernameB.Text & "'"
            da = New OleDb.OleDbDataAdapter(sql, con)
            da.Fill(ds, "UserStorageDS1")
            con.Close()

    This is the function for the sign in for the first player and the next one is an if statement comparing two login systems to make sure the same person hasnt signed in. 

    If ds.Tables("UserStorageDS1").Rows.Count > 0 Then
                If ds.Tables("UserStorageDS1").Rows(0).Item("Password").ToString = PasswordB.Text And Not Sign_In_player_2.userTwo = UsernameB.Text Then
                    MessageBox.Show("Welcome " & UsernameB.Text)
                    UserOne = UsernameB.Text
                    UserOneMedals = ds.Tables("UserStorageDS1").Rows(0).Item("Medals")
                    Me.Hide()
                    Sign_In_player_2.Show()
                Else
                    If ds.Tables("UserStorageDS1").Rows(0).Item("Password").ToString = PasswordB.Text And Sign_In_player_2.userTwo = UsernameB.Text Then
                        MessageBox.Show("account in use", "error", MessageBoxButtons.OK)
                    Else
                        MessageBox.Show("The Username and Password don't match", "error", MessageBoxButtons.OK)
                    End If
                End If
            End If
        End Sub

    Should I just keep that exactly the same? they are all unique in my opinion or from what I have seen in my code.

    Also could you give me a step by step guide to making this class and pointers when referring this class in my other forms. This will really help when carrying things out. 

    Sunday, March 5, 2017 7:54 PM