-
Feb 28th, 2018, 08:05 PM
#1
Thread Starter
Lively Member
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
-
Feb 28th, 2018, 08:44 PM
#2
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.
-
Feb 28th, 2018, 08:59 PM
#3
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:
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
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|