Solved

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

Posted on 2010-08-20
6
1,015 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:MarvinEMarshall
  • 3
  • 3
6 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 33490563
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
 

Author Comment

by:MarvinEMarshall
ID: 33493223
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
 
LVL 42

Expert Comment

by:dqmq
ID: 33493408
Do the close before assigning Nothing.  The nothing assigment is what uninstantiates the object.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 33493444
>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
 

Author Comment

by:MarvinEMarshall
ID: 33497246
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
 

Author Closing Comment

by:MarvinEMarshall
ID: 33608288
Thanks again.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question