object reference not set to an instance of an object

[mjenkinson05]

Im using a mysql connector to insert a record into a MYSQL Database table

The code i know works, because ive used it in an app before,

The poblem im getting is the error "object reference not set to an instance of an object" but i cant see what ive missed. I know it will be something so obvoius, so im hoping a fresh pair of eyes will find it.

Private Sub SaveNewCustomer()

        'Step 1 - Create SQL Command to INSERT record
        Dim cmd As MySqlCommand
        Dim cn As MySqlConnection
        cmd = New MySqlCommand("INSERT into CLIENTS (Company, Contact, Phone, Mobile, Email, Address, Website) VALUES (?Company, ?Contact, ?Phone, ?Mobile, ?Email, ?Address, ?Website)", cn)

        Try

            With cmd.Parameters
                '.AddWithValue("?ID", txtID.Text)
                .AddWithValue("?Company", txtCompany.Text)
                .AddWithValue("?Contact", txtContact.Text)
                .AddWithValue("?Phone", txtPhone.Text)
                .AddWithValue("?Mobile", txtMobile.Text)
                .AddWithValue("?Email", txtEmail.Text)
                .AddWithValue("?Address", txtAddress.Text)
                .AddWithValue("?Website", txtWWW.Text)

            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        'Step 2 - Connect and Insert
        Try
            cn = New MySqlConnection()
            cn.ConnectionString = "server=" & My.Settings.HostIP & ";" & "user id=" & My.Settings.User & ";" & "password=" & My.Settings.Password & ";" & "database=clients"
            cn.Open()
            cmd.Connection = cn
            cmd.Prepare()
            cmd.ExecuteNonQuery()
            cn.Close()
            MsgBox("Successfully Added")

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try

    End Sub

[Lightning]

On which line does the error occur?

[mjenkinson05]

sorry!

cn.open()

)

[Nitesh]

ok your problem is here:

Dim cmd As MySqlCommand
        Dim cn As MySqlConnection
        cmd = New MySqlCommand("INSERT into CLIENTS (Company, Contact, Phone, Mobile, Email, Address, Website) VALUES (?Company, ?Contact, ?Phone, ?Mobile, ?Email, ?Address, ?Website)", cn)

you assign a connection to the command but you set the connection properties afterwards.

try this:

  Dim cmd As MySqlCommand
        Dim cn As MySqlConnection
        cn = New MySqlConnection()
            cn.ConnectionString = "server=" & My.Settings.HostIP & ";" & "user id=" & My.Settings.User & ";" & "password=" & My.Settings.Password & ";" & "database=clients"

cmd = New MySqlCommand("INSERT into CLIENTS (Company, Contact, Phone, Mobile, Email, Address, Website) VALUES (?Company, ?Contact, ?Phone, ?Mobile, ?Email, ?Address, ?Website)", cn)

            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
            MsgBox("Successfully Added")

[mjenkinson05]

I thought that, but no, that still creates the error.
If I set a breakpoint, and step down through the code, the step after cn.open() is the Exception Catch, so it is deff the cn.open thats causing the issue.

I just cant see whats causing it.

[chris128]

when you step through the code, when it is just about to execute cn.Open() can you see if cn is actually Nothing (by hovering the mouse over cn in the debugger) ?

[mjenkinson05]

ok. i have uploaded a photo here: http://twitpic.com/gu1m4

[mjenkinson05]

ok. I decided to uncomplicate things by creating a new module, and writing the code again by hand.

this is what i have :

Imports MySql.Data.MySqlClient
Module modDBConnect
    Public conn As New MySqlConnection

    Public Sub ConnectDatabase()

        Try
            If conn.State = ConnectionState.Closed Then
                conn.ConnectionString = "DATABASE=" & My.Settings.Database & ";SERVER=" & My.Settings.HostIP & ";user id=" & My.Settings.User & ";password=" & My.Settings.Password ' & ";charset=utf8"
                conn.Open()
            End If

        Catch myerror As Exception

            MessageBox.Show(myerror.Message)
            End
        End Try
    End Sub

    Public Sub DisconnectDatabase()
        Try
            conn.Close()
        Catch myerror As MySql.Data.MySqlClient.MySqlException

        End Try
    End Sub

End Module

I then created a fresh new form, with 2 buttons. One calles the Connect method, the other the disconnect. no sql query commands, or anything.

It STILL creates a "not referenced" error on the conn.open

im starting to think this is not my fault any more.

any ideas yet?

[chris128]

Was all that in the same project or a completely new blank project?

[mjenkinson05]

the same project. should i create a new one and try it again?

[chris128]

Yeah just give that a go and see what happens

[Nitesh]

try this:

Using con as New MySqlConnection(strConn)

where strConn is equal to that string that has, Database....... blah blah

if that don't work i assume your connection string is incorrect

[mjenkinson05]

made a new program, wrote the code by hand. same error.
this really doesnt make any sence!

[mjenkinson05]

try this:

Using con as New MySqlConnection(strConn)

where strConn is equal to that string that has, Database....... blah blah

if that don't work i assume your connection string is incorrect

Could you elaborate?

[chris128]

Are you using the latest version of the .NET connector from here: http://www.mysql.com/products/connector/ ?

[chris128]

Could you elaborate?

I think he just means instead of creating a New MySqlConnection and then setting the connection string, try setting the connection string when you create the new instance - thats assuming the MySqlConnection class has a constructor that supports this.

[mjenkinson05]

Yes, i was using 6.1 origionally and it didnt work. i realised it was a beta verison, so i downgraded to 6.0, but still got the problem.

[Nitesh]

strConn="DATABASE=" & My.Settings.Database & ";SERVER=" & My.Settings.HostIP & ";user id=" & My.Settings.User & ";password=" & My.Settings.Password ' & ";charset=utf8"

Using con as New MySqlConnection(strConn)

cmd = New MySqlCommand("INSERT into CLIENTS (Company, Contact, Phone, Mobile, Email, Address, Website) VALUES (?Company, ?Contact, ?Phone, ?Mobile, ?Email, ?Address, ?Website)", con)

            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()

End Using

[chris128]

Ah I see. Have you tried setting the connection string in the constructor as Nitesh suggested? Thats how I always do it when working with SQL Server, never used MySQL but I just checked on their website and the constructor does support this. So you can just do:

Dim conn As New MySqlConnection("connection string here")

[mjenkinson05]

great ideas guys, but still nothing.

anything else?

[chris128]

I just tried to install the MySQL ADO.NET connector to see if I can help you out but it wont install just says "the installation failed due to an error" but doesnt tell me anything other than that! I'll keep trying..

[mjenkinson05]

i really dont know what this could be! Theres nothing on the web about it anywhere.

[chris128]

Well I just rebooted and still cant install the MySQL .NET components so I'm afraid I cant test and help you further. The only thing I can think of is that after you downgraded from the BETA version something got messed up... Have you got another PC you can try running that code on and see if the same thing happens?

[mjenkinson05]

ok so i think im gunna sack off the mysql idea, is there any other way of doing it? im not up on MSSQL server etc.

[chris128]

MS SQL server is pretty much the same as MySql from what I understand - the main difference being MySQL is free and MS SQL is not. Having said that, the express edition of MS SQL server is also free but it does have some limits..

[mjenkinson05]

is there a guide anywhere i can use to walk me thru? i am gunna give the 2008 express verison a try
maybe i might get ms products to work on an ms os. (maybe being the operative word)

[chris128]

A guide to do what? You connect to it and use it through VB in the same way you were doing with MySQL. Instead of Dim xx As New MySQLConnection you just do Dim xx As New SQLConnection (After importing the SqlClient namespace anyway)

[mjenkinson05]

yes i get that, but i dont know how to set up the initial sql server, database, permissions etc.

MYSQL make it easy, microsoft (as usual) do not

[chris128]

There's not really much to it - I've not used 2008 but in 2005 basically you do this:
Install it, enable remote connections on it if necessary using the SQL Server Surface Configuration app that will now be in your Start Menu (think you might have to do it in another area as well in the general Configuration program, also in the Start Menu under Microsoft SQL Server). Then if you need to setup a database etc then install the free SQL Management Studio Express software and tell it to connect to your SQL server machine, its pretty obvious once you get in there how to do it (ie, right click on Databases and go to New Database).
I'm sure there are plenty of guides on the net as well.