Results 1 to 3 of 3

Thread: Connection String

  1. #1

    Thread Starter
    Lively Member Carmell's Avatar
    Join Date
    Dec 2015
    Posts
    105

    Connection String

    Hi I am starting to learn VB.NET and I am little bit confused right now.
    I want to create a connection string dynamically at run time.

    I made a module for it and I want to call it everytime I need it.

    Code:
    Imports System.Data.SqlClient
    Module Module1
        Public connection As SqlConnection
        Public cmd As SqlCommand
        Public rd As SqlDataReader
        Sub con()
            connection = New SqlConnection
            cmd = New SqlCommand
            connection.ConnectionString = "Server='" & My.Settings.SName & "';user='" & My.Settings.UName & "';password='" & My.Settings.Pword & "';database='" & My.Settings.DBName & "'"
    
            'Dim connection As New SqlConnection("Server='" & My.Settings.SName & "';user='" & My.Settings.UName & "';password='" & My.Settings.Pword & "';database='" & My.Settings.DBName & "'")
    
        End Sub
    End Module
    And now I'm on my login page but I don't know how to call my module or it's not possible.
    Here's my login code:
    Code:
    Imports System.Data.SqlClient
    Public Class LoginForm1
        Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
            con()
            connection.Open()
            cmd.Connection = connection
            cmd.CommandText = "SELECT * from tbl_login where username='" & txtusername.Text & " and password='" & txtpassword.Text & "'"
    
            If rd.HasRows Then
                Mainform.Show()
            Else
                MsgBox("Invalid Login credentials", MsgBoxStyle.Exclamation, "Failed!")
            End If
        End Sub
    By the way I'm using sql server 2008.
    Thank you so much

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Connection String

    First things first, please don't just post code and say that it doesn't work. I that case, we have to trawl through the whole lot and look for anything that might cause any issue. That's a waste of our time. When you execute that code, something happens. Don't keep that a secret from those whom you want to help you. Most likely, an exception is thrown and that exception has an error message to help diagnose the problem. If you want us to help diagnose the problem, tell us what the error message is and where it happens.

    Presumably, there's a NullReeferenceException thrown when you try to get the HasRows property from that 'rd' field that you never actually assign an object to. If you want to use a data reader then you have to actually create one, which you do by calling ExecuteReader on a command. I suggest that you follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data for some ADO.NET code examples, including using a data reader.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Connection String

    There are some other issues with that code. Really, the most that module should contain is a method to construct the connection string and return it. The ADO.NET objects should be created and uses where they're needed. You'll see examples of that in my CodeBank thread.

    Also, at the very least, you should use String.Format to build a String from multiple parts like that. Using '&' repeatedly is obviously legal but it makes code harder to read and thus more error prone. This:
    vb.net Code:
    1. connection.ConnectionString = "Server='" & My.Settings.SName & "';user='" & My.Settings.UName & "';password='" & My.Settings.Pword & "';database='" & My.Settings.DBName & "'"
    would better written like this:
    vb.net Code:
    1. connection.ConnectionString = String.Format("Server='{0}';user='{1}';password='{2}';database='{3}'",
    2.                                             My.Settings.SName,
    3.                                             My.Settings.UName,
    4.                                             My.Settings.Pword,
    5.                                             My.Settings.DBName)
    Better still though, if you're building a connection string, is to use a connection string builder, e.g.
    vb.net Code:
    1. Dim builder As New ConnectionStringBuilder
    2.  
    3. builder.DataSource = My.Settings.SName
    4. 'Etc.
    5.  
    6. connection.ConnectionString = builder.ConnectionString

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width