mysql issue using .net - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

mysql issue using .net

BooMGiRLBooMGiRL Posts: 3Member
Hi.. I am trying to make a very simple program that connects to a MySQL database using VB.NET. My program only has one form and one label. I setup my MySQL database to have a table and one field (a

VARCHAR)called "Tab1". I manually inserted the value "CLOSED" into the Tab1 field using PHPMyAdmin. I want my program to change the value of the field to OPEN/CLOSED and I also want the label text on my form

to change too when it's clicked.

So far, I've added the MySQL.data reference to my project and added:

[code]Imports System
Imports System.Data
Imports MySql.Data.MySqlClient[/code]

To the General_Declarations

Also I declared a global variable containing a MySQL Connection:

[code]Public Class Form1

Dim conn As New MySqlConnection[/code]

Here is my function that connects to MySQL:

[code]Private Function Connect(ByVal server As String, ByRef user As String, ByRef password As String, ByRef database As String)
' Connection string with MySQL Info
conn.ConnectionString = "server=" + server + ";" _
& "user id=" + user + ";" _
& "password=" + password + ";" _
& "database=" + database + ";"
Try
conn.Open()
Return True
Catch ex As MySqlException
Return MsgBox(ex.Message)
End Try
End Function[/code]


I made the program connect to MySQL on Form_Load like this:
[code]Connect("db4free.net", "boomun", "*******", "boomdb")[/code]

And it connects fine but here is where I have the trouble.... I want the field to change from CLOSED to OPEN when I click Label1. It does change to OPEN on the first click, but I get a mysql Error Msgbox

saying "There is already an open DataReader associated with this connection that must be closed first".... How do I close the DataReader?

Here is what I have so far:

[code] Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label1.Click
Dim myCommand As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter
Dim myData As MySqlDataReader
Dim SQL As String

SQL = "SELECT * FROM `boomtable` WHERE `Tab1` = 'CLOSED'"

myCommand.Connection = conn
myCommand.CommandText = SQL
myAdapter.SelectCommand = myCommand

Try
myData = myCommand.ExecuteReader()
myData.Read()
If myData.HasRows = 0 Then
Label1.Text = "CLOSED"
SQL = "UPDATE boomtable SET Tab1 = 'CLOSED' WHERE Tab1 = 'OPEN'"
myCommand.CommandText = SQL
myCommand.ExecuteNonQuery()
myData.Close()

Else
Label1.Text = "OPEN"
SQL = "UPDATE boomtable SET Tab1 = 'OPEN' WHERE Tab1 = 'CLOSED'"
myCommand.CommandText = SQL
myCommand.ExecuteNonQuery()
myData.Close()

End If
Catch ex As MySqlException
MsgBox(ex.Message)
End Try

End Sub
[/code]

I have [code]myData.close()[/code] there but I still get that error! What gives?



Here is the entire code of the project all together:


[code]Imports System
Imports System.Data
Imports MySql.Data.MySqlClient

Public Class Form1

Dim conn As New MySqlConnection

Private Function Connect(ByVal server As String, ByRef user As String, ByRef password As String, ByRef database As String)
' Connection string with MySQL Info
conn.ConnectionString = "server=" + server + ";" _
& "user id=" + user + ";" _
& "password=" + password + ";" _
& "database=" + database + ";"
Try
conn.Open()
Return True
Catch ex As MySqlException
Return MsgBox(ex.Message)
End Try
End Function

Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label1.Click
Dim myCommand As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter
Dim myData As MySqlDataReader
Dim SQL As String

SQL = "SELECT * FROM `boomtable` WHERE `Tab1` = 'CLOSED'"

myCommand.Connection = conn
myCommand.CommandText = SQL
myAdapter.SelectCommand = myCommand

Try
myData = myCommand.ExecuteReader()
myData.Read()
If myData.HasRows = 0 Then
Label1.Text = "CLOSED"
SQL = "UPDATE boomtable SET Tab1 = 'CLOSED' WHERE Tab1 = 'OPEN'"
myCommand.CommandText = SQL
myCommand.ExecuteNonQuery()
myData.Close()

Else
Label1.Text = "OPEN"
SQL = "UPDATE boomtable SET Tab1 = 'OPEN' WHERE Tab1 = 'CLOSED'"
myCommand.CommandText = SQL
myCommand.ExecuteNonQuery()
myData.Close()

End If
Catch ex As MySqlException
MsgBox(ex.Message)
End Try

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Connect("db4free.net", "boomun", "boom123", "boomdb")
End Sub

End Class[/code]

Sign In or Register to comment.