How to close all the opened Sqlite Connections?

Question

   If Trim(tbHdName.Text) = "" Then
        Label2.Text = "Enter Valid HardDisk Name!"
        Label2.Visible = True
        btnAddHD.Visible = False
        btnAddHDD.Visible = False
        Table2.Visible = True
        Exit Sub
    End If

    Dim cd1 As String = "select hddName from T_HDD"
    Dim conn1 As New SqliteConnection(SqlDataSource1.ConnectionString)
    Dim cmd1 As New SqliteCommand(cd1, conn1)
    conn1.Open()
    Dim rdr As SqliteDataReader
    rdr = cmd1.ExecuteReader
    While rdr.Read
        If tbHdName.Text.ToUpper = rdr.Item(0).ToUpper Then
            Label2.Text = "HardDisk Already Added!"
            Label2.Visible = True
            Table2.Visible = True
            btnAddHD.Visible = False
            tbHdName.Text = ""
            conn1.Close()
            Exit Sub
        End If
    End While
    conn1.Close()

    Dim osId As Integer
    Dim cdr As String = "select id from T_OS where osName=@osName and osVersion=@osVersion and osBit=@osBit and osLanguage=@osLanguage "
    Dim connr As New SqliteConnection(SqlDataSource1.ConnectionString)
    Dim cmdr As New SqliteCommand(cdr, connr)
    cmdr.Parameters.AddWithValue("@osName", DropDownList7.SelectedValue)
    cmdr.Parameters.AddWithValue("@osVersion", DropDownList8.SelectedValue)
    cmdr.Parameters.AddWithValue("@osBit", DropDownList9.SelectedValue)
    cmdr.Parameters.AddWithValue("@osLanguage", DropDownList10.SelectedValue)
    connr.Open()
    Dim rdrr As SqliteDataReader
    rdrr = cmdr.ExecuteReader
    If rdrr.Read Then
        osId = rdrr.Item(0)
    Else
        Label2.Text = "Check OS details!"
        Label2.Visible = True
        Table2.Visible = True
        btnAddHD.Visible = False
        tbHdName.Text = ""
        connr.Close()
        Exit Sub
    End If
    connr.Close()

    Dim id As Integer
    Dim cd11 As String = "select cbtId from T_Cbt where cbtName=@cbtName"
    Dim conn11 As New SqliteConnection(SqlDataSource1.ConnectionString)
    Dim cmd11 As New SqliteCommand(cd11, conn11)
    cmd11.Parameters.AddWithValue("@cbtName", ddlCbtList.SelectedValue.ToString)
    conn11.Open()
    Dim rdr11 As SqliteDataReader
    rdr11 = cmd11.ExecuteReader
    If rdr11.Read Then
        id = rdr11.Item(0)
    End If
    conn11.Close()

    If TextBox10.Text = "" Then
        Label2.Text = "Invalid Computer Name"
        Label2.Visible = True
        Table2.Visible = True
        btnAddHD.Visible = False
        tbHdName.Text = ""
        Exit Sub
    End If

    Dim cdc As String = "select compName from T_HDD where compName=@compName"
    Dim connc As New SqliteConnection(SqlDataSource1.ConnectionString)
    Dim cmdc As New SqliteCommand(cdc, connc)
    cmdc.Parameters.AddWithValue("@compName", TextBox10.Text)
    connc.Open()
    Dim rdrc As SqliteDataReader
    rdrc = cmdc.ExecuteReader
    If rdrc.Read Then
        Label2.Text = "Computer Name is Already Exists"
        Label2.Visible = True
        Table2.Visible = True
        btnAddHD.Visible = False
        tbHdName.Text = ""
        connc.Close()
        Exit Sub
    End If
    connc.Close()

    If TextBox11.Text = "" Then
        Label2.Text = "Invalid IP Address"
        Label2.Visible = True
        Table2.Visible = True
        btnAddHD.Visible = False
        tbHdName.Text = ""
        Exit Sub
    End If

    Dim cdi As String = "select ip from T_HDD where ip=@ip"
    Dim conni As New SqliteConnection(SqlDataSource1.ConnectionString)
    Dim cmdi As New SqliteCommand(cdi, conni)
    cmdi.Parameters.AddWithValue("@ip", TextBox11.Text)
    conni.Open()
    Dim rdri As SqliteDataReader
    rdri = cmdi.ExecuteReader
    If rdri.Read Then
        Label2.Text = "IP Address is Already Exists"
        Label2.Visible = True
        Table2.Visible = True
        btnAddHD.Visible = False
        conni.Close()
        Exit Sub
    End If
    conni.Close()

    Dim divid As Integer
    Dim cdd As String = "select divId from T_Division where divName = @divname"
    Dim connd As New SqliteConnection(SqlDataSource1.ConnectionString)
    Dim cmdd As New SqliteCommand(cdd, connd)
    cmdd.Parameters.AddWithValue("@divname", DropDownList11.SelectedValue)
    connd.Open()
    Dim rdrd As SqliteDataReader
    rdrd = cmdd.ExecuteReader
    If rdrd.Read Then
        divid = rdrd.Item(0)
        connd.Close()
    Else
        Label2.Text = "Check Division details"
        Label2.Visible = True
        Table2.Visible = True
        btnAddHD.Visible = False
        connd.Close()
        Exit Sub
    End If

    If txtPdate2.Text.Length <> 10 Then
        Label2.Text = "Purchasing Date is Invalid"
        Label2.Visible = True
        Table2.Visible = True
        btnAddHD.Visible = False
        Exit Sub
    End If

    If Trim(tbHdName.Text) = "" Or Trim(TextBox5.Text) = "" Or Trim(TextBox6.Text) = "" Or _
        Trim(TextBox10.Text) = "" Or Trim(TextBox11.Text) = "" Then
        Label2.Text = "Fields Not to be empty"
        Label2.Visible = True
        Table2.Visible = True
        btnAddHD.Visible = False
        Exit Sub
    End If

    Dim cdsr As String = "Insert Into T_HDD(cbtId,hddName,isPrimary,size,manufacture, " &
           "id,ip,compName,osId,division,pdate) values " & 
           "(@cbtid,@hddname,@isprimary,@size,@manufacture,@id,@ip,@compName," & 
           "@osId,@division,@pdate)"

    Dim connsr As New SQLiteConnection(SqlDataSource1.ConnectionString)
    Dim cmdsr As New SQLiteCommand(cdsr, connsr)
    cmdsr.Parameters.AddWithValue("@cbtid", id)
    cmdsr.Parameters.AddWithValue("@hddname", Trim(tbHdName.Text))
    cmdsr.Parameters.AddWithValue("@isprimary", ddlIsPmry.SelectedValue.ToString)
    cmdsr.Parameters.AddWithValue("@size", Trim(TextBox5.Text))
    cmdsr.Parameters.AddWithValue("@manufacture", Trim(TextBox6.Text))
    cmdsr.Parameters.AddWithValue("@id", Trim(TextBox7.Text))
    cmdsr.Parameters.AddWithValue("@ip", Trim(TextBox11.Text))
    cmdsr.Parameters.AddWithValue("@compName", Trim(TextBox10.Text))
    cmdsr.Parameters.AddWithValue("@osId", osId)
    cmdsr.Parameters.AddWithValue("@division", divid)
    cmdsr.Parameters.AddWithValue("@pdate", txtPdate2.Text)
    added = True

    connsr.Open()

    cmdsr.ExecuteNonQuery()


    connsr.Close()

This is my code when i press Submit button.. It shows database locked exception..

But i've closed all connections which i am opend...

Answer 1

I show how I would have coded one of your SELECT commands as an example because there are too many repetitive patterns in your code above

Dim cd1 As String = "select hddName from T_HDD"
Using conn1 = New SqliteConnection(SqlDataSource1.ConnectionString)
    Using  cmd1 = New SqliteCommand(cd1, conn1)
        conn1.Open()
        Using rdr = cmd1.ExecuteReader
            While rdr.Read
                If tbHdName.Text.ToUpper = rdr.Item(0).ToUpper Then
                    Label2.Text = "HardDisk Already Added!"
                    Label2.Visible = True
                    Table2.Visible = True
                    btnAddHD.Visible = False
                    tbHdName.Text = ""
                    Exit Sub
                End If
            End While
       End Using
   End Using
End Using

The Using Statement ensures that every disposable object captured by the Using is correctly CLOSED and disposed when you exit from the Using block also in case of exceptions or via a simple Exit Sub as you do numerous time