How I solve this error 'object reference not set to an instance of an object.'

Question

1.00/5 (1 vote)

See more:

VB

Imports System.Data.SqlClient

Public Class Form2
    Public myConn As SqlConnection
    Public myCmd As SqlCommand
    Public myReader As SqlDataReader
    Public results As String

    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'Create a Connection object.
        myConn = New SqlConnection("Data Source=RADIANT-PC\SQLEXPRESS;Initial Catalog=crop;Integrated Security=True")
    End Sub
    'To set up the Command object, which contains the SQL query, add the following code to the Form1_Load event procedure
    'Create a Command object.

    Private Sub btnAddState_Click(sender As Object, e As EventArgs) Handles btnAddState.Click

        Try
            
            myCmd.CommandText = "INSERT INTO [dbo].[tblState] ([statename]) VALUES ('" & txtAddState.Text & "')"
            myCmd.Connection = myConn
            myConn.Open()
            myCmd.ExecuteNonQuery()
            MsgBox("Succesfully Added", MsgBoxStyle.Information, "add")
            myCmd.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        
    End Sub
   
End Class

What I have tried:

VB

'Try
            '    Get
            '        myCmd.CommandType = System.Data.CommandType.Text
            '    End Get
            '    Set(ByVal value As Integer)
            '        myCmd.CommandText = ""

            '    End Set
            'Catch ex As Exception
            '    MessageBox.Show(ex.Message)
            'End Try

Posted 6-Jun-20 2:52am

Member 10669301

Updated 6-Jun-20 3:33am

MadMyche

v2

Add a Solution

Comments

Richard MacCutchan 6-Jun-20 9:14am

Where does the error occur?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Answer 1 · 2020-06-06T03:34:00

There is something definitely missing here; and also a large SQL Injection Vulnerability
NEVER EVER should an SQL query be created from piecing together SQL commands and variables

VB

'To set up the Command object, which contains the SQL query, add the following code to the Form1_Load event procedure
'Create a Command object.

Private Sub btnAddState_Click(sender As Object, e As EventArgs) Handles btnAddState.Click
	Try
        	myCmd.CommandText = "INSERT INTO [dbo].[tblState] ([statename]) VALUES ('" & txtAddState.Text & "')"
		myCmd.Connection = myConn
		myConn.Open()

The priority item is to add the value stored in the variable to your query correctly. This would be done via an SqlParameter. The two lines for this are going to consist of re-defining the query text as well as adding the parameter and value to the command.

VB

Dim query As String = "INSERT INTO [dbo].[tblState] ([statename]) VALUES (@sn)"
' other lines of code... later
myCmd.Parameters.AddWithValue("@sn", txtAddState.Text)

Now onto what is missing... and that would be myCmd. You never define it as an SqlCommand, you just try to start adjusting the properties.
You can also use the overloads of this object so that the connection and commandtext are done when the variable is declared.
I believe your problem is with myCmd. While it is declared at the top of the page; it really is never defined in your code, and until it is defined you cannot get/set any of it's properties. And while you are defining it, you can set the CommandText and Connection properties via an overloaded constructor.
Coupled with the vulnerability fix above, we are now looking at this block of code

VB

Try
	Dim query As String = "INSERT INTO [dbo].[tblState] ([statename]) VALUES (@sn)"
	myCmd = New SqlCommand(query, myConn) 
	myCmd.Parameters.AddWithValue("@sn", txtAddState.Text)

	myConn.Open()

Sql Paramater reference:
SqlParameter Class (System.Data.SqlClient) | Microsoft Docs[^]