How to make the connection globel

Lost User

Dear Friends, I have a small web application with the below code for the pageload event i would like optimise this in such a way that the code behind page_load event should be minimum i tried to use class but i can not access the variables defined in class Please take a look on my code and give me some nice idea to optimise it. Dim cn As New SqlClient.SqlConnection Dim Cm As New SqlClient.SqlCommand Dim CmTel As New SqlClient.SqlCommand Dim CmPP As New SqlClient.SqlCommand Dim CmOD As New SqlClient.SqlCommand Dim Dr As SqlClient.SqlDataReader Dim DrTel As SqlClient.SqlDataReader Dim DrPP As SqlClient.SqlDataReader Dim DrOD As SqlClient.SqlDataReader Dim SaleManNo As String = Session("SaleManNo") Dim TotalCus As Long Dim TotalBytel As Long Dim TotalByPP As Long Dim TotalByOD As Long Try cn.ConnectionString = ("Initial Catalog=SalesMgt;data source=Murtuza;uid=sa;pwd=sumayya;") cn.Open() Cm.Connection = cn Cm.CommandText = "Select Count(cuscode) From CusDetail where SalesManNo='" _ & SaleManNo & "'" Dr = Cm.ExecuteReader Dr.Read() If Dr.HasRows Then TotalCus = Dr(0) TxtTotalCus.Text = CType(TotalCus, String) End If Dr.Close() CmTel.CommandText = "Select Count(cuscode) From CusDetail where SalesManNo='" _ & SaleManNo & "' and WayOfBooking like '%Telephone%'" CmTel.Connection = cn DrTel = CmTel.ExecuteReader DrTel.Read() If DrTel.HasRows Then TotalBytel = DrTel(0) TxtTotTel.Text = CType(TotalBytel, String) End If DrTel.Close() CmPP.Connection = cn CmPP.CommandText = "Select Count(cuscode) From CusDetail where SalesManNo='" _ & SaleManNo & "' and WayOfBooking like '%Presentation%'" DrPP = CmPP.ExecuteReader DrPP.Read() If DrPP.HasRows Then TotalByPP = DrPP(0) TxtTotalPP.Text = CType(TotalByPP, String) End If DrPP.Close() CmOD.Connection = cn CmOD.CommandText = "Select Count(cuscode) From CusDetail where SalesManNo='" _ & SaleMa

StylezHouse

This is a little cleaner...

Public Sub Page_Load()
TxtTotalCus.Text = GetValue("Select Count(cuscode) From CusDetail where SalesManNo='" & SaleManNo & "'")
TxtTotTel.Text = GetValue("Select Count(cuscode) From CusDetail where SalesManNo='" & SaleManNo & "' and WayOfBooking like '%Telephone%'")
TxtTotalPP.Text = GetValue("Select Count(cuscode) From CusDetail where SalesManNo='" & SaleManNo & "' and WayOfBooking like '%Presentation%'")
TxtTotOutDoor.Text = GetValue("Select Count(cuscode) From CusDetail where SalesManNo='" & SaleManNo & "' and WayOfBooking like '%Out door%'")
End Sub

Private Function GetValue(ByVal Query) As String
    Dim cn As New SqlClient.SqlConnection("Initial Catalog=SalesMgt;data source=Murtuza;uid=sa;pwd=sumayya;")
    Dim Cm As New SqlClient.SqlCommand(Query, cn)
    Dim strReturn As String
    Try
        cn.Open()
        strReturn = Cm.ExecuteScalar
        If strReturn Is Nothing Then
            strReturn = String.Empty
        End If
    Catch ex As Exception
        'Handle Error
    Finally
        Cm.Dispose()
        cn.Dispose()
    End Try
    Return strReturn
End Function

Lost User

Dear StylezHouse Thanks a lot for improving my code Thanks Murtuza.