Update Page

Imports System.Data.SqlClient

Public Class Update
    Inherits System.Web.UI.Page

    Private conn As SqlConnection
    Private currentProgrammeID As Integer
    Private currentCourseID As Integer
    Private currentTable As String
    Private currentMode As String

    Protected Sub UpdatePage_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        currentProgrammeID = Session("programmeId")
        currentTable = Session("table")
        currentMode = Session("mode")

        If currentTable != "Programme" Then
            currentCourseID = Session("courseId")

        End If

        conn = New SqlConnection("(INSERT CONNECTION STRING)")
        conn.Open()

        ' UpdatePage_Load is the reentry-point whenever the 'Update' button is clicked.
        ' The following will check whether if Page_Load was called because of the button click (i.e. Page.IsPostBack will return TRUE)
        If (Not Page.IsPostBack) Then
            If currentTable = "Programme" Then
                If currentMode = "Edit" OrElse currentMode = "Delete" Then
                    PopulateProgrammeForm()

                ElseIf currentMode = "Create" Then
                    ClearForm()
                End If

                PriceLabel.Text = "Price :"

            ElseIf currentTable = "Course" Then
                If currentMode = "Edit" OrElse currentMode = "Delete" Then
                    PopulateCourseForm()

                ElseIf currentMode = "Create" Then
                    ClearForm()
                End If

                PriceLabel.Text = "Day :"

            End If

            ModeLabel.Text = currentMode
            TableNameLabel.Text = currentTable

        End If
    End Sub

    Private Sub UpdatePage_Unload(sender As Object, e As EventArgs) Handles Me.Unload
        conn.Close()
    End Sub

    Protected Sub ConfirmButton_Click(sender As Object, e As EventArgs) Handles UpdateButton.Click
        Dim sql As String
        Dim cmd As SqlCommand

        If currentTable = "Programme" Then
            If currentMode = "Edit" Then
                sql = "UPDATE Programme SET code=@c name=@n, price=@prda WHERE id=@id"
                cmd = New SqlCommand(sql, conn)
                cmd.Parameters.Clear()


            ElseIf currentMode = "Create" Then
                sql = "INSERT INTO Programme (code, name, price) VALUES (@c, @n, @prda)"
                cmd = New SqlCommand(sql, conn)
                cmd.Parameters.Clear()

            ElseIf currentMode = "Delete" Then
                sql = "DELETE FROM Programme WHERE id=@id"
                cmd = New SqlCommand(sql, conn)
                cmd.Parameters.Clear()

            End If

            cmd.Parameters.AddWithValue("@id", currentProgrammeID)

        ElseIf currentTable = "Course" Then
            If currentMode = "Edit" Then
                sql = "UPDATE Course SET code=@c name=@n, day=@prda WHERE id=@id"
                cmd = New SqlCommand(sql, conn)
                cmd.Parameters.Clear()

            ElseIf currentMode = "Create" Then
                sql = "INSERT INTO Course (code, name, day) VALUES (@c, @n, @prda)"
                cmd = New SqlCommand(sql, conn)
                cmd.Parameters.Clear()

            ElseIf currentMode = "Delete" Then
                sql = "DELETE FROM Course WHERE id=@id"
                cmd = New SqlCommand(sql, conn)
                cmd.Parameters.Clear()

            End If

            cmd.Parameters.AddWithValue("@id", currentCourseID)

        End If

        cmd.Parameters.AddWithValue("@c", IIf(CodeTextBox.Text.Length >0, CodeTextBox.Text, DBNull.Value))
        cmd.Parameters.AddWithValue("@n", NameTextBox.Text)
        cmd.Parameters.AddWithValue("@prda", IIf(PriceTextBox.Text.Length >0, PriceTextBox.Text, DBNull.Value))

        Try
            cmd.ExecuteNonQuery()

        Catch exError As SqlException
            MsgBox(exError.Message, MsgBoxStyle.Critical, "Error")

        End Try

        Response.Redirect("Default.aspx")

    End Sub

    Sub PopulateProgrammeForm()
        'load the required data from the database
        Dim getProgrammeSQL As String = "SELECT Programme.id, Programme.code, Programme.name, Programme.price FROM Programme WHERE Programme.id=@id"
        Dim getProgrammeCmd As SqlCommand = New SqlCommand(getProgrammeSQL, conn)
        getProgrammeCmd.Parameters.AddWithValue("@id", currentProgrammeID)
        Dim reader As SqlDataReader = getProgrammeCmd.ExecuteReader()
        Do While reader.Read()
            If reader.IsDBNull(1) Then
                CodeTextBox.Text = ""
            Else
                CodeTextBox.Text = reader.GetString(1)
            End If

            NameTextBox.Text = reader.GetString(2)

            If reader.IsDBNull(3) Then
                PriceTextBox.Text = ""
            Else
                PriceTextBox.Text = reader.GetString(3)
            End If
            Exit Do
        Loop
        reader.Close()

    End Sub

    Sub PopulateCourseForm()
        'load the required data from the database
        Dim getCourseSQL As String = "SELECT Course.id, Course.code, Course.name, Course.day FROM Course WHERE Course.id=@id"
        Dim getCourseCmd As SqlCommand = New SqlCommand(getCourseSQL, conn)
        getCourseCmd.Parameters.AddWithValue("@id", currentCourseID)
        Dim reader As SqlDataReader = getCourseCmd.ExecuteReader()
        Do While reader.Read()
            If reader.IsDBNull(1) Then
                CodeTextBox.Text = ""
            Else
                CodeTextBox.Text = reader.GetString(1)
            End If

            NameTextBox.Text = reader.GetString(2)

            If reader.IsDBNull(3) Then
                PriceTextBox.Text = ""
            Else
                PriceTextBox.Text = reader.GetString(3)
            End If
            Exit Do
        Loop
        reader.Close()

    End Sub

    Sub ClearForm()
        CodeTextBox.Text = ""
        NameTextBox.Text = ""
        PriceTextBox.Text = ""

    End Sub

End Class