Jump to content


Photo

Problems with MySQL and VB.NET


  • Please log in to reply
8 replies to this topic

#1 smakme7757

smakme7757

    Éclair

  • Atomican
  • 4,068 posts
  • Location:Europe

Posted 01 December 2011 - 08:04 AM

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
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

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
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 :).

Edited by smakme7757, 01 December 2011 - 08:13 AM.


#2 kikz

kikz

    Canelé

  • Hero
  • 19,299 posts

Posted 01 December 2011 - 08:25 AM

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.

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.

Edited by kikz, 01 December 2011 - 08:42 AM.


#3 smakme7757

smakme7757

    Éclair

  • Atomican
  • 4,068 posts
  • Location:Europe

Posted 01 December 2011 - 08:27 AM

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
Public Class LoggInn
Private query As New Tool

Then right before i run the SQL command i added in Query.Connect().
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.

Edited by smakme7757, 01 December 2011 - 08:36 AM.


#4 kikz

kikz

    Canelé

  • Hero
  • 19,299 posts

Posted 01 December 2011 - 08:43 AM

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.

Edited by kikz, 01 December 2011 - 08:46 AM.


#5 smakme7757

smakme7757

    Éclair

  • Atomican
  • 4,068 posts
  • Location:Europe

Posted 01 December 2011 - 08:49 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!

Edited by smakme7757, 01 December 2011 - 08:51 AM.


#6 kikz

kikz

    Canelé

  • Hero
  • 19,299 posts

Posted 01 December 2011 - 09: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 :)

#7 smakme7757

smakme7757

    Éclair

  • Atomican
  • 4,068 posts
  • Location:Europe

Posted 01 December 2011 - 09:33 AM

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

Posted Image

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

Edited by smakme7757, 01 December 2011 - 09:34 AM.


#8 kikz

kikz

    Canelé

  • Hero
  • 19,299 posts

Posted 01 December 2011 - 09: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 :)

Edited by kikz, 01 December 2011 - 09:40 AM.


#9 smakme7757

smakme7757

    Éclair

  • Atomican
  • 4,068 posts
  • Location:Europe

Posted 01 December 2011 - 09:48 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:

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.

Edited by smakme7757, 01 December 2011 - 09:49 AM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users