Error Message - Connection must be valid and open - MySQL

[poolet25]

Ok. I am building a program for a small office and I have a little problem with the code so I thought it's better to post a topic before continiue.. I have search into google and a lot of forums about database connectivity and my problem is simillar to other but a lot of things differs... The error that I get is on executeNoQuery()
and it's called {"Connection must be valid and open."}... Well a lot of things may related to about that... At least now from my re-search I covered all the filds of the problem and still not fixed... So please check my code for (Add new customer) button and let me know where I have mess up...!!! Thank you very much for your understanding..!!!

Imports MySql.Data.MySqlClient

Public Class Form2
    Dim ServerString As String = "Server=localhost;User Id=root;Password=root;Database=customers2"
    Dim SQLConnection As MySqlConnection = New MySqlConnection

 Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SQLConnection.ConnectionString = ServerString
        Try
            If SQLConnection.State = ConnectionState.Closed Then
                SQLConnection.Open()
                MsgBox("Successfully Connected to MySQL Database")
            Else
                SQLConnection.Close()
                MsgBox("Connection is Closed.")
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Public Sub SaveNames(ByRef SQLStatement As String)
        Dim cmd As MySqlCommand = New MySqlCommand

        With cmd
            .CommandText = SQLStatement
            .CommandType = CommandType.Text
            .Connection = SQLConnection
            .ExecuteNonQuery()
        End With

        SQLConnection.Close()
        MsgBox("Successfully Added!")
        SQLConnection.Dispose()

    End Sub

    Private Sub addUser_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles addUser.Click
        Dim SQLStatement As String = "INSERT INTO people(First_Name,Last_Name,Street,State,City,Postal,Country,Phone,Mobile,Fax,Email,Note) VALUES('" & firsttxt.Text & "', '" & lasttxt.Text & "', '" & streettxt.Text & "', '" & statetxt.Text & "', '" & citytxt.Text & "', '" & postaltxt.Text & "', '" & countrytxt.Text & "', '" & phonetxt.Text & "', '" & mobiletxt.Text & "', '" & faxtxt.Text & "', '" & emailtxt.Text & "', '" & notetxt.Text & "')"
        SaveNames(SQLStatement)
    End Sub

    Private Sub products_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles products.Click
        Me.GroupBox2.Visible = True
        Me.customers.Enabled = True
        Me.products.Enabled = False
    End Sub

    Private Sub customers_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles customers.Click
        Me.customers.Enabled = False
        Me.products.Enabled = True
        Me.GroupBox2.Visible = False
    End Sub
End Class

[kingsonprisonic]

Here is the error

Public Sub SaveNames(ByRef SQLStatement As String)
        Dim cmd As MySqlCommand = New MySqlCommand
 'Here you have to open the connection so use--->   SQLConnection.Open()
        With cmd
            .CommandText = SQLStatement
            .CommandType = CommandType.Text
            .Connection = SQLConnection
            .ExecuteNonQuery()
        End With
 
        SQLConnection.Close()
        MsgBox("Successfully Added!")
        SQLConnection.Dispose()
 
    End Sub

[poolet25]

hello kingsonprisonic thank you for your reply
well I have already try that but I get other error

{"Unknown database 'customers2'"}

:/ seems to be phpmyadmin the problem and not into my code... As I mention before
a lot of problem are relative.. Any other ideas appreciate..!!

Thank you again..!!

[kingsonprisonic]

I think you have problem with your connection string...

Please visit this site..

http://www.connectionstrings.com/

[poolet25]

Thank you very much..!!!

[poolet25]

ok.. I finally fixed the problem now, And hoppfully I can add a new Customer into my database from visual basic.... Many Thanks to kingsonprisonic you point me into the correct direction....

There is another problem now and I need to share it with you since I loose my mind.... If try to add 2 customers in a row (without exit the program) the program crached "Connection must be valid and open." Seems like the program when insert the first value close the connection .... So probably I will need to used some kind of loop statement to control the connection... Any ideas about that????

Thank you very much

[poolet25]

:-D seems that I have fixed my problem... I added a new customers at least 10 times successfully (without any ERROR) But a second opinion is always welcome so please
check below my code and thought about it for a while at let me know if there is any kind of problem, at the end I have configure the database and set the records only for Id and name of the customers to be more clear to me.... So please fell free to check my code before set the threat as solved.... Thank you in advance.. Many thanks to kingsonprisonic!!!!!!!! :) Cheerrssss...!!!!

Imports MySql.Data.MySqlClient

Public Class Form2
    Dim ServerString As String = "Server=localhost;User Id=root;Password=root;Database=test"
    Dim SQLConnection As MySqlConnection = New MySqlConnection
    Dim DataAdapter1 As MySqlDataAdapter = New MySqlDataAdapter()
    Dim ds As DataSet = New DataSet()
    Dim sql As MySqlCommand = New MySqlCommand("SELECT * FROM people", SQLConnection)
    Dim cmd As MySqlCommand = New MySqlCommand
    Dim Comb As MySqlCommandBuilder

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SQLConnection.ConnectionString = ServerString
        Try
            If SQLConnection.State = ConnectionState.Closed Then
                SQLConnection.Open()
                MsgBox("Successfully Connected to MySQL Database")
            Else
                SQLConnection.Close()
                MsgBox("Connection is Closed.")
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Public Sub SaveNames(ByRef SQLStatement As String)
        Dim cmd As MySqlCommand = New MySqlCommand

        With cmd
            .CommandText = SQLStatement
            .CommandType = CommandType.Text
            .Connection = SQLConnection
            .ExecuteNonQuery()

        End With
        SQLConnection.Close()
        MsgBox("Successfully Added!")
        SQLConnection.Dispose()

    End Sub

    Private Sub addUser_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles addUser.Click

        Dim SQLStatement As String = "INSERT INTO people(name) VALUES('" & firsttxt.Text & "')"
        Try
            SQLConnection.Open()
            cmd.ExecuteNonQuery()
            SQLConnection.Close()

        Catch ex As Exception

        End Try
        SaveNames(SQLStatement)
    End Sub

    Private Sub products_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles products.Click
        Me.GroupBox2.Visible = True
        Me.customers.Enabled = True
        Me.products.Enabled = False
    End Sub

    Private Sub customers_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles customers.Click
        Me.customers.Enabled = False
        Me.products.Enabled = True
        Me.GroupBox2.Visible = False
    End Sub

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click

        DataAdapter1.SelectCommand = sql
        DataAdapter1.Fill(ds, "people")

        datagrid1.DataSource = ds
        datagrid1.DataMember = "people"

        SQLConnection.Close()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        Comb = New MySqlCommandBuilder(DataAdapter1)
        Dim i As Integer = DataAdapter1.Update(ds.Tables("people"))
        MessageBox.Show("Modify the number user " & i.ToString & " rows")
    End Sub
End Class

[M.Waqas Aslam]

well it is nice that your prob is solved , but i think the way you code is not good , it is better to use modules for connection , insert delete, search , update , because it makes your prog easy to debug and easy to understand .

Regards

[poolet25]

well it is nice that your prob is solved , but i think the way you code is not good , it is better to use modules for connection , insert delete, search , update , because it makes your prog easy to debug and easy to understand .

Regards

Hello waqasaslammmeo and thank you for your reply, Can you please be more specific.. What exactly you mean with modules for connectio??

Thank you for your understating..!!!

[kingsonprisonic]

Ok i can tell you a very nice way of programming with database..

When you use database in your project, i think you need your connection string on each form for accessing the database..

So try to create a class for connection string like...

Imports System.Data.SqlClient
Public Class connection
    Public con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\tammy\documents\visual studio 2010\Projects\dbapp\dbapp\exdb.mdf;Integrated Security=True;User Instance=True")
    Public Sub open()
        con.Open()
    End Sub
    Public Sub close()
        con.Close()
    End Sub
End Class

Now in any form where you need to access database..

Use this

Imports System.Data.SqlClient
Public Class Form1
       Dim db As New connection 'This is the connection Object

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            db.Open() ' Just open the connection

'Code for accessing the database tables...
'Like
'Dim com as New SqlCommand("Insert Into Table1(Field1,Field2) Values ('"+Me.TextBox1.Text+"','"+Me.TextBox2.Text+"')",db.con)
'com.ExecuteNonQuery()


'And always try to use Try-Catch block to handle the run-time errors.... 

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            db.Close()' When needed close the connection
        End Try
    End Sub

End Class

Hope this will help you. If the connection string is changed you just change it to only the connection Class. But in your first choice you have to change it to all the forms....

[poolet25]

Well seems nice.. Thank you very much... I can do that since I used only one form from the entire, I prefer to used hide and show layer since I am connectet into a database. Well, I will tris seems that you open a conectivity directly into the file of database, I am a new with that :) but I will figure out, it's fine...

Thank you very much for your help.!!!!
Cheeerrrrsssss!!!! :)

[kingsonprisonic]

If it helps please give reputation and mark the thread as solved...

[poolet25]

If it helps please give reputation and mark the thread as solved...

Ok... Kingsonprsonic...Thanks anyway... I would also want to ask you something else..(I don't want to open a new threat since has a relation with that) I have finished the Add button, Edit button, and Search for the products of the company and I am now working with, Delete products!!! And I am really stuck, Any that I used from google are old cmd lines for vb 6 and so on so, aren't work for me... Any ideas how to do that?? Please If you are kind and anwser me, let me know as my paste here my command and I will change it after into modules for connection since I am try to figure out how it's works your thought and I don't want to used class only for delete records, I want all my code to be the same for each process..... Thanks!!! I will leave you feedback after that.... :) Regards plouto.!!!

[kingsonprisonic]

I am now working with, Delete products!!! And I am really stuck,

Where you stuck. Are you getting any error. Then post the code please...

[poolet25]

Where you stuck. Are you getting any error. Then post the code please...

Well as I was search on google I find the following code.. I have try it but isn't make any sence since isn't works for me.. I get error that I am trying to convert into string values and thats correct, I don't know where to start even If I check on msdn is helps since there are a lot of unsused code and even if I try to debug it aren't works

If productIDtxt.Text = " " Then
            MessageBox.Show("Error.Please select product ID that you want to delete.", "Delete Product", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            Try

                ServerString = New SqlCommand("Delete from product where id= productIDtxt.TXT")

                MessageBox.Show("Product with id: ", "Successfully delete", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

        End If

[kingsonprisonic]

Yes yo go well..

but some modification needed..

If productIDtxt.Text = " " Then
            MessageBox.Show("Error.Please select product ID that you want to delete.", "Delete Product", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            Try
DatabaseOpenConnection.Open()
            Dim ServerString As New SqlCommand("Delete from product where id="+ productIDtxt.Text,DatabaseOpenConnection)
ServerString .ExecuteNonQuery()

                MessageBox.Show("Product with id: ", "Successfully delete", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Catch ex As Exception
                MsgBox(ex.ToString)
Finally
DatabaseOpenConnection.Close()
            End Try

        End If

Here DatabaseOpenConnection is a SqlConnection Object

First declare it like

Dim DatabaseOpenConnection As New SqlConnection("Your connection string..")

You need to Import a namespace for this.

Imports System.Data.SqlClient

[poolet25]

Yes yo go well..

but some modification needed..

If productIDtxt.Text = " " Then
            MessageBox.Show("Error.Please select product ID that you want to delete.", "Delete Product", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            Try
DatabaseOpenConnection.Open()
            Dim ServerString As New SqlCommand("Delete from product where id="+ productIDtxt.Text,DatabaseOpenConnection)
ServerString .ExecuteNonQuery()

                MessageBox.Show("Product with id: ", "Successfully delete", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Catch ex As Exception
                MsgBox(ex.ToString)
Finally
DatabaseOpenConnection.Close()
            End Try

        End If

Here DatabaseOpenConnection is a SqlConnection Object

First declare it like

Dim DatabaseOpenConnection As New SqlConnection("Your connection string..")

You need to Import a namespace for this.

Imports System.Data.SqlClient

Thank you....

1 problem with the code::

Dim DatabaseOpenConnection As New SqlConnection("Your connection string..")

what excatly is connection string ??

I thought was something like that:::

Dim DatabaseOpenConnection As New SqlConnection("Server=localhost;User Id=root;Password=root;Database=test")

OR

Dim DatabaseOpenConnection As New SqlConnection("SELECT * FROM products")

but isn't work for me... Any ideas??

I appreciate your help...!!! Thanks a lot.!!!
Regards

[kingsonprisonic]

yes

Dim DatabaseOpenConnection As New SqlConnection("Server=localhost;User Id=root;Password=root;Database=test")

is a connection string....

Please visit this link : http://www.connectionstrings.com

[poolet25]

yes

Dim DatabaseOpenConnection As New SqlConnection("Server=localhost;User Id=root;Password=root;Database=test")

is a connection string....

Please visit this link : http://www.connectionstrings.com

When I try the following code I get the follow error when I am trying
to Delete a record...!!! Amy Ideas???

Thank you...!!!!

[IMG]http://i39.tinypic.com/2zqsrjb.jpg[/IMG]

[kingsonprisonic]

Then you choose the wrong connection string.

Ok now tell me where is your database? In your own computer or remote??

[poolet25]

Then you choose the wrong connection string.

Ok now tell me where is your database? In your own computer or remote??

Well at the beginning I was using my database into my Debian server but I had a lots of problems to connect with VB.net so I change and create my database into my computer...

NOTE::
As localhost read the database no .mdf files or .accdb files the changes are made directly into the database if I want to create a connection with .mdf file the code of my project will change dramatically. So I fixed the problem with that way probably I will export an .mdf file and used the database from file since it's better and easy to backup..

[M.Waqas Aslam]

this thread is now going very long , even when you got solution of your first prob. please always use next thread for your next prob. so that others can also participate in giving solution.

Regards

[poolet25]

this thread is now going very long , even when you got solution of your first prob. please always use next thread for your next prob. so that others can also participate in giving solution.

Regards

Thank you for your reply but I think it's better to keep the specific threat since the problem has nothing to do with something else... If any moderator have an idea glad to hear, and better to use other thread I will post another one...!!

Regards

[M.Waqas Aslam]

in single thread you have to just discus your single issue ,but if you just make a single thread open through out your prob you will face in your project then it is not a good practice , as i am not a moderator , but it is the way to discus your prob in any online community .

Regards