Solved

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

Posted on 2010-08-20
6
1,014 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

860 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