Connection Error using mysql in Visual Basic

Question

I'm trying to setup connection to a database within my program, and I'm running into a problem when adding data to the Database. I know I've done something wrong but I can't for the life of me figure it out!

Basically the program throws an error at the ExecuteNonQuery() line, saying the connection is not open. But I've opened the connection just a couple of lines above? It might be because I'm trying to import the connection from a seperate form?

The Error is : An unhandled exception of type 'System.InvalidOperationException' occurred in MySql.Data.dll Additional information: Connection must be valid and open.

I know some parts are commeneted out, thats just what I do whem I'm troubleshooting.

Imports MySql.Data.MySqlClient

Public Class frmCompanyAdd


Public Sub Button1_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
    'Try
    frmMain.conn.Open()
    ' Catch ex As Exception
    'End Try
    Dim cmd As New MySqlCommand(String.Format("INSERT INTO `Company` (`companyname` , `location` , `contactphone` , `numberpc`) VALUES (" & txtcompanyname.Text & "," & txtPst.Text & " , " & txtPhone.Text & "," & txtPcNo.Text & ")"))

    cmd.ExecuteNonQuery()
    'frmMain.conn.Close()


End Sub

I'll say it because someone else is bound to; parameterised queries are your friend. — Jonathon Cowley-Thom
– Jonathon Cowley-Thom, Commented Mar 30, 2015 at 13:10
Program is only a small internal thing to be used by me and my boss to make our lives easier...the db is fresh just for this project and doesn't contain any valuable or personal information. You are right though, I know it's not exactly secure. — Jamiman
– Jamiman, Commented Mar 30, 2015 at 13:18
Fine, security is not a concern, but now try to insert a company with a single quote in its name. Trust me, use parameters always. — Steve
– Steve, Commented Mar 30, 2015 at 13:33
You make a good point. Seems like its worth taking the time to learn the proper way, probably good for the future as well :) Thanks for the advice :) — Jamiman
– Jamiman, Commented Mar 30, 2015 at 13:58

rory.ap · Accepted Answer · 2015-03-30 13:25:37Z

1

It looks like you never set the connection on the cmd command after you construct it. You either need to provide the connection in the constructor or set it afterwards.

As usual, I must also point out that your INSERT statement is open to a possible malicious SQL injection attack. You should use a parameterized query instead of concatenating values together to form your query.

I can't get the library installed, but it's probably just a constructor overload, e.g. Dim cmd As New MySqlCommand(yourProperlyParameterizedQuery, frmMain.conn)

edited Mar 30, 2015 at 13:25

answered Mar 30, 2015 at 13:08

rory.ap

35.7k15 gold badges104 silver badges199 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jamiman Over a year ago

Could you give me an example of what you mean by this? Do I need to open the connection after constructing it or?

rory.ap Over a year ago

I'm trying, but I don't have the MySql libraries. Where did you get them?

Jonathon Cowley-Thom Over a year ago

What roryap is getting at is that you haven't actually told cmd which connection its supposed to be using.

rory.ap Over a year ago

@Jamiman -- Got it installed, but can't get the reference to work. Says it's invalid.

Jamiman Over a year ago

Might it be because I've got the actual connection info on a seperate form?

|

Collectives™ on Stack Overflow

Connection Error using mysql in Visual Basic

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related