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

Posted on 2010-08-20
Medium Priority
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 -

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

            ' 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 & "%' "

            '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)

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

            objDataAdapter2 = Nothing
            objConnection2 = Nothing


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

    End Sub
Question by:MarvinEMarshall
  • 3
  • 3
LVL 42

Expert Comment

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

Author Comment

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

Expert Comment

ID: 33493408
Do the close before assigning Nothing.  The nothing assigment is what uninstantiates the object.
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

LVL 42

Accepted Solution

dqmq earned 2000 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.

Author Comment

ID: 33497246
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.

Author Closing Comment

ID: 33608288
Thanks again.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

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.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard 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.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

587 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