Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
Problems with MySQL and VB.NET, Function not working
smakme7757
post Dec 1 2011, 08:04 AM
Post #1
Atomican
Overlord




Ok i've spent a few hours trying to figure this out so hopefully one of you people can give me a hand

Ok here is the main program form:

LoggInn.vb
CODE
Imports MySql.Data.MySqlClient
Public Class LoggInn
    Private Sub Form1Load(sender As System.Object, e As EventArgs) Handles MyBase.Load
        'Connect to database
        Dim initiateConn As New Tool
        initiateConn.Connect()

        'Password field obscured
        Txt_Passwd.PasswordChar = "*"
        End Sub

    Private Sub BtnLogginnClick(sender As System.Object, e As System.EventArgs) Handles Btn_Logginn.Click
        Dim username As String
        Dim password As String

        Dim logincheck As New DataTable
        Dim query As New Tool

        'Dummy data
        logincheck = query.Runquery("Select username from accounts where user_id = 1")

        Dim temprad As DataRow

        For Each temprad In logincheck.Rows
            username = temprad("username")
            testbox.Items.Add(username)
        Next

    End Sub

End Class


And here is the Class "Tool" which is basically one Subrutine (Connect) to log into the database at startup and the 2nd part is a function (Runquery) which will retreive the desired information from the database. The subrutine "Connect" works fine when i start the project (It's in form load). No errors and it connects to the database without a problem.

To make it easier on the eyes i've put the subrutine and function into thier own code boxes, but both are in the same file in Visual Studio

CODE
Imports MySql.Data.MySqlClient

Public Class Tool
    Private _conn As New MySqlConnection

    Public Sub Connect()

        Dim server As String
        Dim database As String
        Dim username As String
        Dim password As String

        server = "192.168.1.60"
        database = "software"
        username = "Jack"
        password = "1234"

        _conn.ConnectionString = "Server=" & server & ";" _
            & "Database=" & database & ";" _
            & "Uid=" & username & ";" _
            & "Pwd=" & password & ";"

        Try
            _conn.Open()
            _conn.Close()

        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to database: " & _
                            myerror.Message)
        Finally
            _conn.Dispose()
        End Try

    End Sub

CODE
Public Function Runquery(ByVal sql As String) As DataTable
        Dim myData As New DataTable
        
        Try
'*****************************************************************

          _conn.Open()

'*****************************************************************

            Dim mycommand As New MySqlCommand
            Dim myadapter As New MySqlDataAdapter

            mycommand.Connection = _conn
            mycommand.CommandText = sql

            myadapter.SelectCommand = mycommand
            myadapter.Fill(myData)

            _conn.Close()
        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to database " & myerror.Message)
        Finally
            _conn.Dispose()
        End Try

        Return myData

    End Function

End Class


My problem it that i'm given the message:

"Error connecting to the database Unable to connect to any of the specific MySQL hosts" - Where the program hangs - I've marked it in the code with lots of stars :).

It works fine if the subrutine and function are in the main form, but i really want to understand how to get it to work this way

Thanks for the help :).

This post has been edited by smakme7757: Dec 1 2011, 08:13 AM


--------------------
Currently running Ubuntu 13.10
Go to the top of the page
 
+Quote Post
kikz
post Dec 1 2011, 08:25 AM
Post #2
Hero
Titan




You either need to:

a) need to call connect every time do any db operations, or
b) declare an object of type Tool in the MainForm, at class level.

Basically what's happening is your call to initiateConn.Connect() is setting up the connection string but initiateConn is going out of scope when the Load event completes. You then call query.Runquery in the Button click event without first setting the connection string by calling query.Connect().

Have a look at the repository pattern. It's similar to what you are trying to do.

e: Maybe you're calling .Connect in the Load event to prewarm the connection pool? I'd avoid doing that, if the connection pool takes a while to open, because it'll slow the opening of the form, which will annoy users.

CODE
Imports MySql.Data.MySqlClient
Public Class LoggInn

    Private _mysqlTool as Tool
    
    Private Sub Form1Load(sender As System.Object, e As EventArgs) Handles MyBase.Load
        'Connect to database - not sure why you're doing this ???
        _mysqlTool = New Tool
         ' Calling Connect here doesn't really serve any purpose.
                 ' _mysqlTool.Connect()

        'Password field obscured
        Txt_Passwd.PasswordChar = "*"
        End Sub

    Private Sub BtnLogginnClick(sender As System.Object, e As System.EventArgs) Handles Btn_Logginn.Click
        
        'Dummy data
        Dim logincheck = mysqlTool.Runquery("Select username from accounts where user_id = 1")

        ' You'll only get 1 username at most back...
        For Each Dim temprad In logincheck.Rows
            username = temprad("username")            
            testbox.Items.Add(username)
        Next

    End Sub

End Class

Imports MySql.Data.MySqlClient

Public Class Tool
        
    Private Function GetConnection() as MySqlConnection
        Dim conn as New MySqlConnection
        
        Dim server As String
        Dim database As String
        Dim username As String
        Dim password As String

        server = "192.168.1.60"
        database = "software"
        username = "Jack"
        password = "1234"

        conn.ConnectionString = "Server=" & server & ";" _
            & "Database=" & database & ";" _
            & "Uid=" & username & ";" _
            & "Pwd=" & password & ";"
        
        Return conn
        
    End Function
    
    Public Function Runquery(ByVal sql As String) As DataTable
        
        Try
            Using (Dim conn = GetConnection())
                conn.Open()
        
                Dim myData as New DataTable
                
                Dim myCommand = conn.CreateCommand()
                myCommand.CommandText = sql
                
                Dim myAdapter As New MySqlDataAdapter
                myAdapter.SelectCommand = myCommand
                myAdapter.Fill(myData)
                
                Return myData
                
            End Using

        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to database " & myerror.Message)
        Finally
            _conn.Dispose()
        End Try

        Return New DataTable

    End Function

End Class


I've created a Function to return a Connection, because Connections should be short lived, per call to the database. Connection Pooling will keep the time to create down, and leaving connections around will use extra memory unnecessarily.

This post has been edited by kikz: Dec 1 2011, 08:42 AM
Go to the top of the page
 
+Quote Post
smakme7757
post Dec 1 2011, 08:27 AM
Post #3
Atomican
Overlord




I appreciate you taking the time to have a look at it.

I wouldn't mind hearing your method as well, but i'll give it another go and see if i can get my method working :).


I got it working :).

At the top of the main form
CODE
Public Class LoggInn
Private query As New Tool


Then right before i run the SQL command i added in Query.Connect().
CODE
query.Connect()
logincheck = query.Runquery("Select username from accounts where user_id = 1")


I'm not sure if that's 100% what you meant, but it works now.

I guess using query.Connect() each time isn't so bad. If i had the function in the main form i wouldn't have to write it, but it looks a lot cleaner.

This post has been edited by smakme7757: Dec 1 2011, 08:36 AM


--------------------
Currently running Ubuntu 13.10
Go to the top of the page
 
+Quote Post
kikz
post Dec 1 2011, 08:43 AM
Post #4
Hero
Titan




have a look at my edit :)

Using Connect each time is a waste of time because all it does is open then close, then dispose (which is the same as close :)) the connection. All you're doing is validating that the connection can be opened, which you'll know about as soon as you call runquery. It'd make more sense to call Connect, CanConnect and have it return a boolean. Even then it's a waste.



This post has been edited by kikz: Dec 1 2011, 08:46 AM
Go to the top of the page
 
+Quote Post
smakme7757
post Dec 1 2011, 08:49 AM
Post #5
Atomican
Overlord




QUOTE (kikz @ Dec 1 2011, 09:25 AM) *
e: Maybe you're calling .Connect in the Load event to prewarm the connection pool? I'd avoid doing that, if the connection pool takes a while to open, because it'll slow the opening of the form, which will annoy users.

' You'll only get 1 username at most back...
For Each Dim temprad In logincheck.Rows
username = temprad("username")
testbox.Items.Add(username)


Actually it's a little side project for myself. I'm just having a play really (The goal was to just get back a value from the database so i know the database is working). The reason i had the program connect to the database at startup so i knew it was working, but like you said pretty pointless :)

The aim of this project is to create a nice local database for all my software that i own with serial keys usernames and passwords and so on. And then a GUI in VB.net to be able to search through the whole lot. I've just finished up a uni project where we had to create a DBMS for a firm selling sports equipment and also rental equipment. It worked out well, it's our first assignment and i didn't know any Mysql or VB.net before i started (3 months ago) and the system works extremely well. You can hire, buy, return, order a package deal and all sorts of stuff. The Mysql SELECT sentences got pretty advanced, but it was awesome fun!

We didn't use much Object oreintated programming in our creation so i'm trying to have a play with it at home to really understand how it all fits together.

I'm reading through your example now by the way. I'll have to try it tomorrow as it's 23:47 here and i've got an exam on monday, but again i really appreciate you taking the time to look through my terrible code =D

I might also add that i saw in another thread a piece of software you recomended called ReSharper which looked interesting so i'm giving the trial a go and it's pretty nifty!

This post has been edited by smakme7757: Dec 1 2011, 08:51 AM


--------------------
Currently running Ubuntu 13.10
Go to the top of the page
 
+Quote Post
kikz
post Dec 1 2011, 09:26 AM
Post #6
Hero
Titan




Cool. It's a good start then (your uni DBMS). Strange the uni would choose VB.NET and not make use of OO techniques, which I assume you're learning at uni too?

Needs more Nhibernate :)
Go to the top of the page
 
+Quote Post
smakme7757
post Dec 1 2011, 09:33 AM
Post #7
Atomican
Overlord




Ok i had a quick play. I can't seem to get this bit working.



QUOTE (kikz @ Dec 1 2011, 10:26 AM) *
Cool. It's a good start then (your uni DBMS). Strange the uni would choose VB.NET and not make use of OO techniques, which I assume you're learning at uni too?

Needs more Nhibernate :)


It's the first semester so i'm presuming it's a crash course to get everyone up to speed. It wasn't a requirement to include OO and i was unfortunate to get into a group that just didn't want to try it out. That's why i'm making my own little programs to give it a go :). Apart from the exam next week i think we're done with VB.net and are moving onto Java next semester.

The actual uni course isn't 100% programming focussed. I don't really know how to translate the name into something meaningful in english, but would it make sense to say it's a degree in Computer administration? We get heavily into linux, server enviroments, windows powershell, database theory, mysql, entrepreneurship ect..

I really should have asked about this about a month ago, we could have saved like 1000 lines of code by using some of the methods you have written up here :P

This post has been edited by smakme7757: Dec 1 2011, 09:34 AM


--------------------
Currently running Ubuntu 13.10
Go to the top of the page
 
+Quote Post
kikz
post Dec 1 2011, 09:39 AM
Post #8
Hero
Titan




I don't programme in VB.NET so I'm not suprised it doesn't compile :) I just took my C# and converted to what I thought VB.NET would be. anyhoo, the main problem is the _conn.Dispose() at the bottom. needs to be conn.Dispose().

Certainly by using inheritence you could have saved heaps of lines of code. But if you're only in 1st year, you probably don't 'get' that stuff yet :)

This post has been edited by kikz: Dec 1 2011, 09:40 AM
Go to the top of the page
 
+Quote Post
smakme7757
post Dec 1 2011, 09:48 AM
Post #9
Atomican
Overlord




QUOTE (kikz @ Dec 1 2011, 10:39 AM) *
I don't programme in VB.NET so I'm not suprised it doesn't compile :) I just took my C# and converted to what I thought VB.NET would be. anyhoo, the main problem is the _conn.Dispose() at the bottom. needs to be conn.Dispose().

Certainly by using inheritence you could have saved heaps of lines of code. But if you're only in 1st year, you probably don't 'get' that stuff yet :)


No worries. I'll get it working tomorrow. Now i see the pattern it shouldn't be too hard to figure it out when i'm actually awake :).

Yea the coding was terrible, but for not knowing a single thing, i mean not even something as simple as:

CODE
Dim name As String = "Jack"
Label1.Text = name


It's pretty neat to actually have made something combining a live database and a GUI that you make yourself (Yes i can imagine it would have been a piece of cake for a veteran such as yourself :))

Anyhow i'm falling asleep, again thank you for the tips.

Have a nice brunch.

This post has been edited by smakme7757: Dec 1 2011, 09:49 AM


--------------------
Currently running Ubuntu 13.10
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 



Lo-Fi Version Time is now: 2nd August 2014 - 04:31 PM