locked
ExecuteNonQuery: Connection property has not been initialized RRS feed

  • Question

  • Hi guys, 

    I've read upon a few topics related to my problem and have had a go at changing my code slightly to what others have found to work. Unfortunately I haven't been very successful so I have finally turned to seek help from you as the community. If someone could point out where I am going wrong then this would be a great help, thanks. 

    And here's the code: 

        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

            
            Try
                provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                Datafile = "E:\Try Again\Bibby trail.accdb"
                myConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Kurtis\Downloads\Version 0.89\Version 0.89\Base 0.2\Base 0.2\bin\Debug\Bibby trail.accdb"
                myConnection.Open()


                Dim cmd As New OleDb.OleDbCommand
                cmd.CommandText = "Insert into ([ID],[Depot Name],[Make],[Model],[Reg],[Current Location]) VALUES (@ID, @Depot, @Make, @Model, @Reg, @Current)"
                cmd.Parameters.AddWithValue("@ID", TextBox1.Text)
                cmd.Parameters.AddWithValue("@Depot", TextBox2.Text)
                cmd.Parameters.AddWithValue("@Make", TextBox3.Text)
                cmd.Parameters.AddWithValue("@Model", TextBox4.Text)
                cmd.Parameters.AddWithValue("@Reg", TextBox5.Text)
                cmd.Parameters.AddWithValue("@Current", TextBox6.Text)
                cmd.ExecuteNonQuery()

                myConnection.Close()
                cmd.Dispose()
                TextBox1.Clear()
                TextBox2.Clear()
                TextBox3.Clear()
                TextBox4.Clear()
                TextBox5.Clear()
                TextBox6.Clear()
                TextBox7.Clear()


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


        End Sub

    Tuesday, December 29, 2015 1:03 PM

Answers

  • Your OleDbCommand is not associated with an OleDbConnection:

    Dim cmd As New OleDb.OleDbCommand

    cmd.Connection = MyConnection



    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Proposed as answer by David M. Nichols Tuesday, December 29, 2015 1:31 PM
    • Marked as answer by kurtisga Tuesday, December 29, 2015 4:53 PM
    Tuesday, December 29, 2015 1:09 PM
  • Thank you for such a quick reply,

    I've added your suggestion and now I get the INSERT INTO syntax error message ? 

    I will have a mess to see if I can change it however if you have any suggestions I am open. 

    Thanks, 

    Kurtis

    You are missing table name
    INSERT INTO table_name
     VALUES (value1,value2,value3,...);


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter or Facebook via my MSDN profile but will not answer coding question on either.

    • Proposed as answer by Cor Ligthert Tuesday, December 29, 2015 4:16 PM
    • Marked as answer by kurtisga Tuesday, December 29, 2015 4:53 PM
    Tuesday, December 29, 2015 2:19 PM
  • Thank you ! The code finally works. Both of the suggestions are very useful. 

    Here is the working code: 

      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

            
            Try
                provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                Datafile = "E:\Try Again\Bibby trail.accdb"
                myConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Kurtis\Downloads\Version 0.89\Version 0.89\Base 0.2\Base 0.2\Bibby trail.accdb"
                myConnection.Open()


                Dim cmd As New OleDb.OleDbCommand
                cmd.Connection = myConnection
                cmd.CommandText = "Insert into Trucks([ID],[Depot Name],[Make],[Model],[Reg],[Current Location]) VALUES (@ID, @Depot, @Make, @Model, @Reg, @Current)"
                cmd.Parameters.AddWithValue("@ID", TextBox1.Text)
                cmd.Parameters.AddWithValue("@Depot", TextBox2.Text)
                cmd.Parameters.AddWithValue("@Make", TextBox3.Text)
                cmd.Parameters.AddWithValue("@Model", TextBox4.Text)
                cmd.Parameters.AddWithValue("@Reg", TextBox5.Text)
                cmd.Parameters.AddWithValue("@Current", TextBox6.Text)
                cmd.ExecuteNonQuery()

                myConnection.Close()
                cmd.Dispose()
                TextBox1.Clear()
                TextBox2.Clear()
                TextBox3.Clear()
                TextBox4.Clear()
                TextBox5.Clear()
                TextBox6.Clear()
                TextBox7.Clear()


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


        End Sub

    • Marked as answer by kurtisga Tuesday, December 29, 2015 4:55 PM
    Tuesday, December 29, 2015 4:55 PM

All replies

  • Your OleDbCommand is not associated with an OleDbConnection:

    Dim cmd As New OleDb.OleDbCommand

    cmd.Connection = MyConnection



    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Proposed as answer by David M. Nichols Tuesday, December 29, 2015 1:31 PM
    • Marked as answer by kurtisga Tuesday, December 29, 2015 4:53 PM
    Tuesday, December 29, 2015 1:09 PM
  • Thank you for such a quick reply,

    I've added your suggestion and now I get the INSERT INTO syntax error message ? 

    I will have a mess to see if I can change it however if you have any suggestions I am open. 

    Thanks, 

    Kurtis

    Tuesday, December 29, 2015 2:17 PM
  • Thank you for such a quick reply,

    I've added your suggestion and now I get the INSERT INTO syntax error message ? 

    I will have a mess to see if I can change it however if you have any suggestions I am open. 

    Thanks, 

    Kurtis

    You are missing table name
    INSERT INTO table_name
     VALUES (value1,value2,value3,...);


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter or Facebook via my MSDN profile but will not answer coding question on either.

    • Proposed as answer by Cor Ligthert Tuesday, December 29, 2015 4:16 PM
    • Marked as answer by kurtisga Tuesday, December 29, 2015 4:53 PM
    Tuesday, December 29, 2015 2:19 PM
  • Thank you ! The code finally works. Both of the suggestions are very useful. 

    Here is the working code: 

      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

            
            Try
                provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                Datafile = "E:\Try Again\Bibby trail.accdb"
                myConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Kurtis\Downloads\Version 0.89\Version 0.89\Base 0.2\Base 0.2\Bibby trail.accdb"
                myConnection.Open()


                Dim cmd As New OleDb.OleDbCommand
                cmd.Connection = myConnection
                cmd.CommandText = "Insert into Trucks([ID],[Depot Name],[Make],[Model],[Reg],[Current Location]) VALUES (@ID, @Depot, @Make, @Model, @Reg, @Current)"
                cmd.Parameters.AddWithValue("@ID", TextBox1.Text)
                cmd.Parameters.AddWithValue("@Depot", TextBox2.Text)
                cmd.Parameters.AddWithValue("@Make", TextBox3.Text)
                cmd.Parameters.AddWithValue("@Model", TextBox4.Text)
                cmd.Parameters.AddWithValue("@Reg", TextBox5.Text)
                cmd.Parameters.AddWithValue("@Current", TextBox6.Text)
                cmd.ExecuteNonQuery()

                myConnection.Close()
                cmd.Dispose()
                TextBox1.Clear()
                TextBox2.Clear()
                TextBox3.Clear()
                TextBox4.Clear()
                TextBox5.Clear()
                TextBox6.Clear()
                TextBox7.Clear()


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


        End Sub

    • Marked as answer by kurtisga Tuesday, December 29, 2015 4:55 PM
    Tuesday, December 29, 2015 4:55 PM