Cant Delete Row from Database

[djsuperz]

I am trying to delete a row with the patient name Mike.
When click on the command the error says: Object Reference not set to an instance of an object

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim sqlconnectionstring As String = "Data Source=Server;User ID=admin;Password=lalala;Persist Security Info=False;Initial Catalog=Oasis"
Dim SqlConn As New System.Data.SqlClient.SqlConnection(sqlconnectionstring)
Dim strTrackingSheet As String = "Select * from Trackingsheet"
Dim datrackingsheet As New System.Data.SqlClient.SqlDataAdapter(strTrackingSheet, sqlconnectionstring)
Dim dsPatient As New System.Data.DataSet
Dim strSQL1 As String = "DELETE FROM Trackingsheet WHERE First = Mike"
Try
SqlConn.Open()
datrackingsheet.DeleteCommand.CommandText = strSQL1
datrackingsheet.DeleteCommand.ExecuteNonQuery()
Catch exceptionObject As Exception
MessageBox.Show(exceptionObject.Message)
Finally
SqlConn.Close()
End Try
End Sub

Any suggestions?

 

[jetar]

I am fairly new to vb.net and cant tell right off where it is happening, but from the error it sounds like you are trying to access either a property or method without having properly instantiated the object. Unfortunately I am not seeing where that is happening in your code.

So my suggestion would be to set breakpoints everywhere you are using objects and step through each one until you get the error again and then you will have at least narrowed it down to one line of code and probably one object.

Dont know if that helps at all

 

[RiverGuy]

That is right. You have no DeleteCommand object for your DataAdapter. Create a Command object. Set the Command Text. Assign your Command Object to your Adapter's Delete command.

 

[johnCronin]

Are you just trying to delete some rows from the DB. Then you don't need a dataset to do that. Datasets are used for working with disconnected data. The delete command, and update commands associated with the dataAdapter are used to synch the changes of the data in the dataSet back to the DB.

 

[PankajBanga]

First of all your strSQL1 is incorrect. Mike is a string and should be in single quotes (‘) as follows

Dim strSQL1 As String = "DELETE Trackingsheet WHERE First = " & "'" & "Mike" & "'"

Secondly try the following code snippet to delete record from your database

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Create SqlConnection Object.
        Dim myConn As New SqlConnection("server=YourServer;database=YourDataBase;uid=YourUserID;pwd=YourPassword;")

        'Variable to hold the SQL statement.
        Dim mySQL As String
        'Create an SqlCommand object.
        Dim myCommand As SqlCommand
        
        Try
            'Open connection.
            myConn.Open()

            'Initialize SQL string.
            mySQL = "DELETE Trackingsheet WHERE First = " & "'" & "Mike" & "'"

            'Initialize SqlCommand object.
            myCommand = New SqlCommand(mySQL, myConn)

            'Execute the Command.
            myCommand.ExecuteNonQuery()

        Catch Excep As SqlClient.SqlException
            MessageBox.Show(Excep.Message & Excep.Number, MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally
            'Close connection.
            myConn.Close()
        End Try

End Sub

Email: pankajmsm@yahoo.com