10 Replies - 27227 Views - Last Post: 13 January 2014 - 01:15 PM Rate Topic: -----

#1 boulde4rmash   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 65
  • Joined: 29-September 11

mysql connection module vb.net

Posted 13 January 2014 - 08:30 AM

Imports MySql.Data.MySqlClient

Public Class LogIn
    Dim count As Integer

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        count = count + 1
        Dim x As New MySqlConnection
        Dim admin As New MySqlCommand
        Dim dr1 As MySqlDataReader
        ConnectDatabase()
        admin.CommandText = "SELECT user.username, user.password,user.type,building.building, building.bid FROM user INNER JOIN building on user.building = building.bid WHERE user.username = '" & TextBox1.Text & "' and user.password = '" & TextBox2.Text & "'"
        dr1 = admin.ExecuteReader



        If dr1.HasRows Then
            'Read the data
            dr1.Read()
            'Checks & shows the appropriate user form

            Dim bid As String
            Dim build As String
            bid = dr1(4).ToString()
            build = dr1(3).ToString()
            Select Case dr1(2).ToString()
                Case Is = "admin"
                    Form3.Show()
                Case Is = "guard"
                    Form13.Show()
                Case Is = "verifier"
                    Form1.bid = bid
                    Form1.builder = build
                    Form1.Show()

            End Select
            Me.Visible = False
        Else
            MsgBox("Invalid User or Password " & count)

            If count >= 3 Then
                MsgBox("you cannot log in now", MsgBoxStyle.Exclamation)
                TextBox1.Enabled = False
                TextBox2.Enabled = False
            End If
        End If

    End Sub


  
End Class




This is my code on a log in form. I dont know whats wrong when I try to module the mysql connection. It seems that its not connecting.

This is my code from module
Imports MySql.Data.MySqlClient

Module Connect
    Public conn As New MySqlConnection

    Public Sub ConnectDatabase()

        Try
            If conn.State = ConnectionState.Closed Then
                conn.ConnectionString = "server=localhost;userid=root;password=;database=tracking"

                conn.Open()
            End If

        Catch myerror As Exception
            MsgBox("Connection Error")
            End
        End Try
    End Sub

    Public Sub DisconnectDatabase()
        Try
            conn.Close()
        Catch myerror As MySql.Data.MySqlClient.MySqlException

        End Try
    End Sub
End Module



Is This A Good Question/Topic? 0
  • +

Replies To: mysql connection module vb.net

#2 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: mysql connection module vb.net

Posted 13 January 2014 - 08:38 AM

Did you make sure your Connection String was formatted correctly? Are you receiving and error? If so what is the error text?
Was This Post Helpful? 0
  • +
  • -

#3 boulde4rmash   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 65
  • Joined: 29-September 11

Re: mysql connection module vb.net

Posted 13 January 2014 - 08:44 AM

View Postybadragon, on 13 January 2014 - 08:38 AM, said:

Did you make sure your Connection String was formatted correctly? Are you receiving and error? If so what is the error text?



Connection must be valid and open. <-- this is the error.
Was This Post Helpful? 0
  • +
  • -

#4 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: mysql connection module vb.net

Posted 13 January 2014 - 08:47 AM

do you have conn.Open() and conn.Close() when you are trying to execute your queries?
Was This Post Helpful? 0
  • +
  • -

#5 boulde4rmash   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 65
  • Joined: 29-September 11

Re: mysql connection module vb.net

Posted 13 January 2014 - 09:02 AM

I think the mysql module has it?
Was This Post Helpful? 0
  • +
  • -

#6 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: mysql connection module vb.net

Posted 13 January 2014 - 09:20 AM

yes, but what you call your MySQLConnection in the first code you posted doesnt. If you want to open the correct connection you would need to pass the Connection object to the ConnectDatabase method in your module like so

Public Sub ConnectDatabase(byRef con As MySQLConnection)
' do connection stuff here
End Sub

This post has been edited by ybadragon: 13 January 2014 - 09:20 AM

Was This Post Helpful? 0
  • +
  • -

#7 boulde4rmash   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 65
  • Joined: 29-September 11

Re: mysql connection module vb.net

Posted 13 January 2014 - 09:33 AM

View Postybadragon, on 13 January 2014 - 09:20 AM, said:

yes, but what you call your MySQLConnection in the first code you posted doesnt. If you want to open the correct connection you would need to pass the Connection object to the ConnectDatabase method in your module like so

Public Sub ConnectDatabase(byRef con As MySQLConnection)
' do connection stuff here
End Sub



I dont think the module has errors? Its from a tutorial.
Was This Post Helpful? 0
  • +
  • -

#8 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: mysql connection module vb.net

Posted 13 January 2014 - 09:37 AM

The module doesn't, but the connection you named x is never actually opened which is why you get that error. If you pass x to the ConnectDatabase function and open it through that it will correctly open the database if your connection string is correct.
Was This Post Helpful? 0
  • +
  • -

#9 thecoat   User is offline

  • D.I.C Addict

Reputation: 153
  • View blog
  • Posts: 537
  • Joined: 07-December 13

Re: mysql connection module vb.net

Posted 13 January 2014 - 09:51 AM

You have at least a couple of problems here. First your module is indeed going to create a connection and open it, but your login class that is calling it doesn't access the same connection.


Dim x As New MySqlConnection



and

Public conn As New MySqlConnection


are two different objects. Calling ConnectDatabase may create and open a connection but the Login form doesn't use it, hence your error.

The second issue somewhat related to the first is that at the point where you execute the following line:

dr1 = admin.ExecuteReader



You've never told the "Admin" object what connection to use. As it stands it looks like you created a connection in the class to use (but it's not really being used at all).


Try this:

ConnectDatabase()
admin.Connection = conn
admin.CommandText = "SELECT user.username, ....



While that will likely work, I'd recommend that you make your module a class and turn it into a data access layer.
Was This Post Helpful? 1
  • +
  • -

#10 boulde4rmash   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 65
  • Joined: 29-September 11

Re: mysql connection module vb.net

Posted 13 January 2014 - 09:59 AM

View Postthecoat, on 13 January 2014 - 09:51 AM, said:

You have at least a couple of problems here. First your module is indeed going to create a connection and open it, but your login class that is calling it doesn't access the same connection.


Dim x As New MySqlConnection



and

Public conn As New MySqlConnection


are two different objects. Calling ConnectDatabase may create and open a connection but the Login form doesn't use it, hence your error.

The second issue somewhat related to the first is that at the point where you execute the following line:

dr1 = admin.ExecuteReader



You've never told the "Admin" object what connection to use. As it stands it looks like you created a connection in the class to use (but it's not really being used at all).


Try this:

ConnectDatabase()
admin.Connection = conn
admin.CommandText = "SELECT user.username, ....



While that will likely work, I'd recommend that you make your module a class and turn it into a data access layer.


Thanks That work! What the advantage of making it a class? and what is a data access layer? Sorry for many questions. Im a beginner
Was This Post Helpful? 0
  • +
  • -

#11 thecoat   User is offline

  • D.I.C Addict

Reputation: 153
  • View blog
  • Posts: 537
  • Joined: 07-December 13

Re: mysql connection module vb.net

Posted 13 January 2014 - 01:15 PM

http://msdn.microsoft.com/en-us/library/7825002w(v=vs.90).aspx


That's a pretty quick basic (and lacking) discussion of the differences. But the basics are that really behind the scenes a Module is a class with less flexibility and only a single instance. A common factor with database applications is a configurable connection at run time. With what you have at the moment your connection string is hard coded in the module. If you decide to switch your target database you have to change your code and create a new version of the application. The usual way to handle this is to create a method to pass the connection string into your module(class) after reading it from a application setting source. This would likely be done with your login form. Where you start to run into a problem with the use of a module is it's instance is created behind the scenes for you, and you don't have the opportunity to dictate that when it is initialized you have to give it a connection string. With a class you define "Constructors" which is done with "Sub New()" methods.

Sub New()
'Here you could use a default hard coded string, or simply disable the sub new() with no parameters
End Sub



Sub New(byval strConn as string)
conn.ConnectionString = strConn
End Sub



The above constructor forces anyone using this New method to hand it a connection string. You could somewhat accomplish this with the module by assigning the connection string to the conn property before calling ConnectDatabase(), but there would be nothing preventing someone calling the method before setting the connection string, so then you have to defensively code for that possibility.

The other facet of this is that with the Module you get one and only one instance, you can't reuse it to connect to a 2nd database without changing the connection, which might break other areas of your app trying to work with the 1st database. With a Class you can create multiple separate instances with different connections.

As far as a DAL (Data Access Layer) it's basically an abstraction of your database code. Essentially a separate code file (or in more complex projects, a separate application layer) to handle database interaction.
It has several benefits, among other things keeping your UI code focused on UI operations instead of cluttering it with database interaction. It also makes an application much easier to maintain in the future, especially if you decide to switch or extend to different data storage types. Since you are just learning I wouldn't try and do a huge robust DAL with every feature and safe guard you will likely come across if you Google the concept. I certainly wouldn't try to implement n-tier (a concept you'll likely come across) in your first light weight project, but it's worth studying and implementing over time as you gain more familiarity with the concepts and they start to "click".
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1