Solved

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

Posted on 2010-08-20
6
1,020 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
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 …
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

724 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