Error - Object reference not set to an instance of an object - Visual Studio 2010 - SQL

Windows App Project.  All the code works fine in Opening the SQL database, Select Command, Fill etc and displayed in a Datagridview.  However, from a recursive directory routine, I've filled four list boxes, that I will be inserting into a new table in my much larger SQL database.  The data from the listboxes is being pulled and setup for INSERT INTO commands, and all goes fine until I hit the line -
 
nonqueryCommand.ExecuteQuery()

where I get the exception -Object reference not set to an instance of an object.

Below is the complete code that Opens the SQL database and displays data to a DataGridview and then reads the listboxes for the INSERT INTO, etc.   Thanks so much for anyone who shows me what I'm doing wrong to get the exception.

Private Sub btnTest2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTest2.Click

        Dim SearchStr As String
        SearchStr = "20"

        Dim objConnection2 As New SqlConnection("Data Source=localhost\SQLEXPRESS;Initial Catalog=sql_song1;User ID=sa;Password=xxxxxxxx")
        Dim objDataAdapter2 As New SqlDataAdapter
        Dim objDataSet2 As New DataSet

        Try
            ' Set the Select Coommand - New SqlCommand()
            objDataAdapter2.SelectCommand = New SqlCommand()
            objDataAdapter2.SelectCommand.Connection = objConnection2
            objDataAdapter2.SelectCommand.CommandText = "SELECT * FROM tblWave1 WHERE tblwave1.WavArtistName LIKE '%" & SearchStr & "%' "
            objConnection2.Open()

            'Fill the dataset object with data
            'objDataAdapter2.Fill(objDataSet2, "WaveFile")
            objDataAdapter2.Fill(objDataSet2, "D")

            ' objConnection2.Close()

            'Set the DataGridView properties to bind it to our data...
            DataGridView2.AutoGenerateColumns = True

            'DataGridView2.DataSource = objDataSet
            'DataGridView2.DataMember = "WaveFile"

            DataGridView2.DataSource = objDataSet2
            DataGridView2.DataMember = "D"

            'Create Command object
            Dim nonqueryCommand As SqlCommand = objConnection2.CreateCommand()

            nonqueryCommand.CommandText = "INSERT INTO tblWave1 (tblWave1.WavIdno, tblWave1.WavName, tblWave1.WavPath, tblWave1.WavArtistName, tblWave1.WavAlbumName) VALUES (@WavIdno, @WavName, @WavPath, @WavArtistName,@WavAlbumName)"

            ' Add Parameters to Command Parameters collection
            nonqueryCommand.Parameters.Add("@WavIdno", SqlDbType.SmallInt)
            nonqueryCommand.Parameters.Add("@WavName", SqlDbType.NVarChar, 50)
            nonqueryCommand.Parameters.Add("@WavPath", SqlDbType.NVarChar, 50)
            nonqueryCommand.Parameters.Add("@WavArtistName", SqlDbType.NVarChar, 50)
            nonqueryCommand.Parameters.Add("@WavAlbumName", SqlDbType.NVarChar, 50)

            Dim w As Integer = 0

            For w = 9 To 10
                nonqueryCommand.Parameters("@WavIdno").Value = w
                nonqueryCommand.Parameters("@WavName").Value = ListPart3.Items(w)
                nonqueryCommand.Parameters("@WavPath").Value = lstArtAlb.Items(w)
                nonqueryCommand.Parameters("@WavArtistName").Value = ListPart1.Items(w)
                nonqueryCommand.Parameters("@WavAlbumName").Value = ListPart2.Items(w)
            Next

            nonqueryCommand.ExecuteNonQuery() <<<<<< Exception thrown here <<<<<<<<<<

            'Cleanup
            objDataAdapter2 = Nothing
            objConnection2 = Nothing

            objConnection2.Close()

        Catch ex As Exception
            error1.Text = ex.Message
        End Try

 
    End Sub
MarvinEMarshallAsked:
Who is Participating?
 
dqmqConnect With a Mentor Commented:
>nonqueryCommand.Prepare() needs to preceed the nonqueryCommand.Parameters portion

Not a bad idea, considering you are executing the command in a loop.  But I don't think that is what caused your object reference not set error.
0
 
dqmqCommented:
Can't see your problem.   However, don't you intend to have it inside the immediately preceding  "For 9 To 10... Next" loop?

You might try instantiating nonQueryCommand the same way you instantiated SelectCommand
0
 
MarvinEMarshallAuthor Commented:
dqmg
Thanks for your suggestions.  You are correct the NonqueryCommand needed to be inside the for next loop.  I did move it there, but same error.  I did some digging and the statement -  nonqueryCommand.Prepare() needs to preceed the nonqueryCommand.Parameters portion.  I do have it partially working, but as I have the Sql connection open, the end still throws an error -

            objDataAdapter2 = Nothing
            objConnection2 = Nothing
            objConnection2.Close()  <<<<<< Object reference not set to an instance of an object.

I of course want to award you the full points, but this is the last line and I'm home if this is solved.

Marvin E Marshall
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dqmqCommented:
Do the close before assigning Nothing.  The nothing assigment is what uninstantiates the object.
0
 
MarvinEMarshallAuthor Commented:
dqmq
Yes, you are right, after your other suggestions, I went F8 down through and saw that 'nothing' line before the close.  I commented out the nothing and rerun and bingo, it all worked.  So your follow-up is right on and is the final solution to a mulit-part problem.  Thanks so much for the help.
0
 
MarvinEMarshallAuthor Commented:
Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.