locked
Opening/Closing SQL connections RRS feed

  • Question

  • On my module I declare the following.

    Imports System.Data
    Imports System.Data.SqlClient

    Public Const app_db As String = "Data Source=192.168.0.8;Initial Catalog=DB;User ID=sa;Password=pass"
    Public app_con As New SqlConnection
    Public app_cmd As New SqlCommand
    Public app_dr As SqlDataReader

    On my start up form I initialize the connections.

    app_con.ConnectionString = app_db
    app_cmd.Connection = app_con
    With app_cmd
     .CommandType = CommandType.Text
     .Connection = app_con
    End With

    When Ever I need to use them I have something like :

    Public Sub app_exe(the_sql As String)
    Try
     app_con.Open()
     app_cmd.CommandText = the_sql
     app_cmd.ExecuteNonQuery()

    Catch ex As Exception
     MsgBox(Err.Description)
    Finally
    app_con.Close()
    End Try
    End Sub

    How come when I populate a DataGridView I don't have to open a connection? it just works.
    and do I have to close it? I must be doing something wrong here.
    Please advice...

    Dim app_adpt As New System.Data.SqlClient.SqlDataAdapter("select  from po_lines where po_num=" & curr_po & ";", app_con)
    Dim app_ds As New DataSet()
    app_adpt.Fill(app_ds)
    Me.dg_sub.DataSource = app_ds.Tables(0)
    app_con.Close()

    Thank you all,

    John



    • Edited by Bre-x Thursday, October 5, 2017 3:36 PM
    Thursday, October 5, 2017 3:34 PM

Answers

  • Your connection (app_con) is declared globally (Public) within your module. It will persist throughout the life of the application. You closed that connection after filling the DataGridView, but the SqlDataAdapter will implicitly re-open and close that connection the next time data is requested since app_con still contains the connection information. You don't have to explicitly open and close the connection when requesting data in this instance.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Bre-x Thursday, October 5, 2017 4:54 PM
    Thursday, October 5, 2017 4:45 PM

All replies

  • Your connection (app_con) is declared globally (Public) within your module. It will persist throughout the life of the application. You closed that connection after filling the DataGridView, but the SqlDataAdapter will implicitly re-open and close that connection the next time data is requested since app_con still contains the connection information. You don't have to explicitly open and close the connection when requesting data in this instance.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Bre-x Thursday, October 5, 2017 4:54 PM
    Thursday, October 5, 2017 4:45 PM
  • Thank Paul

    Thursday, October 5, 2017 4:54 PM