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

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.

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:

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:

connection.ConnectionString = String.Format("Server='{0}';user='{1}';password='{2}';database='{3}'",
                                            My.Settings.SName,
                                            My.Settings.UName,
                                            My.Settings.Pword,
                                            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:

Dim builder As New ConnectionStringBuilder
 
builder.DataSource = My.Settings.SName
'Etc.
 
connection.ConnectionString = builder.ConnectionString