3 Replies - 3454 Views - Last Post: 28 February 2011 - 04:06 AM Rate Topic: -----

#1 BooMGiRL  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 27-February 11

Help connecting to MySQL database w/Update in VB.NET

Posted 27 February 2011 - 09:12 PM

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:

Imports System
Imports System.Data
Imports MySql.Data.MySqlClient


To the General_Declarations

Also I declared a global variable containing a MySQL Connection:

Public Class Form1

    Dim conn As New MySqlConnection


Here is my function that connects to MySQL:

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



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


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:

 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



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



Here is the entire code of the project all together:


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


Is This A Good Question/Topic? 0
  • +

Replies To: Help connecting to MySQL database w/Update in VB.NET

#2 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: Help connecting to MySQL database w/Update in VB.NET

Posted 28 February 2011 - 12:50 AM

Here is something to look at:

You are mixing datareaders with data adapter etc. only use one at a time for your connection

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 myData As MySqlDataReader
        Dim SQL As String

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

        myCommand.Connection = conn
        myCommand.CommandText = SQL

        Try
            myData = myCommand.ExecuteReader()
            'dont do this yet as you read the first line and will not be able to go
            'a record back
            'myData.Read()
            If myData.HasRows = False Then
                Label1.Text = "CLOSED"
                'close the reader here as you want to execute more stuff..
                myData.Close()
                SQL = "UPDATE boomtable SET Tab1 = 'CLOSED' WHERE Tab1 = 'OPEN'"
                myCommand.CommandText = SQL
                myCommand.ExecuteNonQuery()
             Else
                Label1.Text = "OPEN"
                'close the reader here as you want to execute more stuff..
                myData.Close()                
                SQL = "UPDATE boomtable SET Tab1 = 'OPEN' WHERE Tab1 = 'CLOSED'"
                myCommand.CommandText = SQL
                myCommand.ExecuteNonQuery()
            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



Was This Post Helpful? 1
  • +
  • -

#3 BooMGiRL  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 27-February 11

Re: Help connecting to MySQL database w/Update in VB.NET

Posted 28 February 2011 - 03:40 AM

It works... Finally.. You are a god
Was This Post Helpful? 0
  • +
  • -

#4 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: Help connecting to MySQL database w/Update in VB.NET

Posted 28 February 2011 - 04:06 AM

Yeah, just check what i did with the data reader and when to close it

:)
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1