Using SQL DELETE query with VB.NET

Question

I’ve written a program in VB.NET in conjunction with a SQL server database table to record the assessment marks of students. The SQL table (Marks) details columns, which show Student_ID, Assessment (e.g. Maths, Science, English) and Mark. The subroutine shown below is designed to allow users to delete specific assessments and, by using the DELETE statement nested within a conditional if/elseif clause, I have managed to achieve this, the program runs fine. However, as a relative newcomer to programming I am conscious about adopting good practice and, if possible, avoiding repetitive code which, in my mind, is clearly evident within the if/elseif clause. This problem becomes increasingly relevant when considering the possibility of having to deal with 15 or more student assessments. My question is, can the SQL query statement be modified to incorporate an ‘assessment’ variable rather than explicitly typing in each condition within an if/elseif clause? I tried to do this in the same way as the variable ‘id’ is used to identify the ‘Student_ID’ within the SQL query statement, but this returned an error stating that, for example, Maths is not a column. Hope I have explained this sufficiently and everything makes sense. Any ideas, as always, most appreciated.

Thanks, Mike

   Console.WriteLine("")
    Console.Write("Enter assessment to be deleted : ")
    Dim assessment_name As String = Console.ReadLine

    If assessment_name = "Maths" Then
        query = ("DELETE FROM Marks WHERE Assessment='Maths' AND Student_ID=" & id)
    ElseIf assessment_name = "English" Then
        query = ("DELETE FROM Marks WHERE Assessment='English' AND Student_ID=" & id)
    ElseIf assessment_name = "Science" Then
        query = ("DELETE FROM Marks WHERE Assessment='Science' AND Student_ID=" & id)
    ElseIf assessment_name = "Art" Then
        query = ("DELETE FROM Marks WHERE Assessment='Art' AND Student_ID=" & id)
    End If

    command = New SqlCommand(query, connection)
    command.ExecuteNonQuery()

Side note; look into using parameters rather than String concatenation. Also look into implementing Using — Bugs
– Bugs, Commented Apr 30, 2017 at 13:59
Wither parameters, the WHERE clause can be refactored to WHERE Assessment=@Assessment AND Student_ID=@Id;. That would allow you to use a single query in addition to the other benefits of parameterized queries. — Dan Guzman
– Dan Guzman, Commented Apr 30, 2017 at 14:44
Thanks for your help guys. Had a little problem figuring out how to declare the parameters for assessment and id, but I got there in the end. — Mike
– Mike, Commented May 2, 2017 at 14:07

Mederic · Accepted Answer · 2017-05-01 12:37:12Z

1

I think you could do something like below.

You can use parameters to easily create your query.

You can use a USING block for easy dispose

Public Sub DeleteNote(ByVal Assessement As String, ByVal Studentid As Integer)
    'Using statement for easy dispose
    Using connection As New SqlConnection(connectionString), _
          command As New SqlCommand("DELETE FROM Marks WHERE Assessment=@Assessement AND Student_ID=@Studentid", connection)

        'Add parameters
        command.Parameters.AddWithValue("@Assessement", Assessement)
        command.Parameters.AddWithValue("@Studentid", Studentid)

        'Open the connection
        connection.Open()
        'Execute query
        Using reader As SqlDataReader = command.ExecuteReader()
            While reader.Read()
                'Read result of query
                Console.WriteLine(String.Format("{0}, {1}",
                     reader(0), reader(1)))
            End While
        End Using
    End Using
End Sub

edited May 1, 2017 at 12:37

answered Apr 30, 2017 at 17:01

Mederic

2,0545 gold badges23 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Joel Coehoorn Over a year ago

clearing the connection pool like that is very poor practice. It breaks the connection re-use feature built into the pool. Typically, it's better to let exceptions bubble up to a higher level of abstraction. Return codes add complexity that don't help the application... they tend to be ignored, and hide details about the error. I also cleaned up a few other things.

Scott Chamberlain Over a year ago

@JoelCoehoorn you missed the ' around the Assessment='@Assessement' :)

Mederic Over a year ago

@JoelCoehoorn I think it depends on the program on mine due to extensive sql integrety testing i cant read the bytes of the file etc as long the connection in opened as the pool keeps the file open.

Mederic Over a year ago

@ScottChamberlain thanks I didnt notice since not using IDE haha actually I had them but someone edited them out

Scott Chamberlain Over a year ago

@Mederic you can see who edited them out by clicking on the link to see the revision of the post ;)

|

Collectives™ on Stack Overflow

Using SQL DELETE query with VB.NET

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related