Well, Rex, I didn't really want to wade through your whole complete web page code, but I think I may have found your problem. In your Page_Load there is one point where you have an open connection that you don't close (after a Transaction Rollback). That could *definitely* cause a lot of Open connections. Correct this and see if it solves your problem.
~~Bonnie
>Bonnie,
>
>Here is the code. I think it has something to do with the OracleClient version that ships with VS 2003 and Oracle 8i. THe code just goes to the connection pool and if a connection is available, uses it, otherwise it creates another 100 connection and uses one of those.
>
>TIA
>
>Rex
>
>
>
>Web.Config:
>
>
><configuration>
> <system.web>
> <customErrors mode="Off" />
> <sessionState
> mode="InProc"
> stateConnectionString="tcpip=127.0.0.1:42424"
> sqlConnectionString="data source=127.0.0.1;user id=sa;password="
> cookieless="false"
> timeout="20"
> />
> <compilation debug="true">
> </compilation>
> <httpRuntime executionTimeout="3600"/>
> <trace
> enabled="false"
> requestLimit="100"
> pageOutput="false"
> traceMode="SortByTime"
> localOnly="false"
> />
> </system.web>
> <appSettings>
> <add key="odh1" value="Data Source=odhdev1;User ID=XXXXXXXXXX;Password=XXXXXXXXXX;Max Pool Size=500;Min Pool Size=100"/>
> </appSettings>
></configuration>
>
>----------------------------------------------------------------------------------------------------------------------------------------------------------
>'_______________________________________________________________
>'
>'General Information
>'
>'File Name: edit_child.vb
>'Author: jburneff
>'Project: ET/HMG
>'
>'Description: Code behind file for edit_child.aspx
>'
>'----------------------------------------------------------------
>'Revision History: (One entry per person with date)
>'
>'12/13/2001 jburneff Version 1.0.0
>'01/08/02 VenkG Version 1.0.1
>'01/18/02 VenkG Version 1.0.2+
>'----------------------------------------------------------------
>'Copyright Notice:
>'
>'Copyright © 2001 Rapidigm Corporation
>'
>'Warning: This computer program is protected by copyright law and
>'international treaties. Unauthorized reproduction or distribution
>'of this program, or any portion of it, may result in severe civil
>'and criminal penalties, and will be prosecuted to the maximium
>'extent under the law.
>'
>'Have a nice day.
>'_______________________________________________________________
>
>Public Class edit_child
> Inherits System.Web.UI.Page
> Protected WithEvents Validationsummary1 As System.Web.UI.WebControls.ValidationSummary
> Protected WithEvents txtLastName As Rapidigm.Security.Controls.RapTextBox
> Protected WithEvents txtFirstName As Rapidigm.Security.Controls.RapTextBox
> Protected WithEvents txtMiddleName As Rapidigm.Security.Controls.RapTextBox
> Protected WithEvents txtBirthDate As Rapidigm.Security.Controls.RapTextBox
> Protected WithEvents dlcounty As System.Web.UI.WebControls.DropDownList
> Protected WithEvents dlsex As System.Web.UI.WebControls.DropDownList
> Protected WithEvents dlethnicitycode As System.Web.UI.WebControls.DropDownList
> Protected WithEvents cblRace As System.Web.UI.WebControls.CheckBoxList
> Protected WithEvents lblfullname As Rapidigm.Security.Controls.RapLabel
> Protected WithEvents lblstreet1 As Rapidigm.Security.Controls.RapLabel
> Protected WithEvents lblstreet2 As Rapidigm.Security.Controls.RapLabel
> Protected WithEvents lblcity As Rapidigm.Security.Controls.RapLabel
> Protected WithEvents lblstate As Rapidigm.Security.Controls.RapLabel
> Protected WithEvents lblzip5 As Rapidigm.Security.Controls.RapLabel
> Protected WithEvents lblzip4 As Rapidigm.Security.Controls.RapLabel
> Protected WithEvents dlimmunization As System.Web.UI.WebControls.DropDownList
> Protected WithEvents dlhealthcare As System.Web.UI.WebControls.DropDownList
> Protected WithEvents lblImmunizationExit As Rapidigm.Security.Controls.RapLabel
> Protected WithEvents lblProviderExit As Rapidigm.Security.Controls.RapLabel
> Protected WithEvents dlfamilyfunding As System.Web.UI.WebControls.DropDownList
> Protected WithEvents dlschooldistrict As System.Web.UI.WebControls.DropDownList
> Protected WithEvents dlincomebracket As System.Web.UI.WebControls.DropDownList
> Protected WithEvents myDataGrid As System.Web.UI.WebControls.DataGrid
> Protected WithEvents btnNew As Rapidigm.Security.Controls.RapButton
> Protected WithEvents btnSave As Rapidigm.Security.Controls.RapButton
> Protected WithEvents btnCancel As Rapidigm.Security.Controls.RapButton
> Protected WithEvents rpdfrm As System.Web.UI.HtmlControls.HtmlForm
> Protected WithEvents txtssn As ucTxtSSN
> Protected WithEvents rfvLastName As System.Web.UI.WebControls.RequiredFieldValidator
> Protected WithEvents rfvFamilyFunding As System.Web.UI.WebControls.RequiredFieldValidator
> Protected WithEvents rfvSchoolDistrict As System.Web.UI.WebControls.RequiredFieldValidator
> Protected WithEvents rfvIncomeBracket As System.Web.UI.WebControls.RequiredFieldValidator
> Protected WithEvents revBirthDate As System.Web.UI.WebControls.RegularExpressionValidator
> Protected WithEvents cvCheckDate As System.Web.UI.WebControls.CustomValidator
> Protected WithEvents rfvBirthDate As System.Web.UI.WebControls.RegularExpressionValidator
>
> Dim adapter As OracleDataAdapter
> Dim dsreferral As DataSet
> Dim reccount As Integer
>
> Public ra_rows As Integer
> Public ra_err_msg As String
>
> Dim etid As String
> Dim e_id As Integer
> Dim eg_id As String
> Dim myConnection As OracleConnection
> Dim myCommand As New OracleCommand()
> Dim myCommand1 As New OracleCommand()
> Dim myTrans As OracleTransaction
> Dim county_id As Integer
> Dim old_county As String
> Dim us As Rapidigm.UserSession.UserInfo
>
> Public odh As String = ConfigurationSettings.AppSettings("odh1")
> Public strSecurity As String = Server.MapPath("/fchs/earlytrack/xml/edit_child.xml")
> Public strPages As String = Server.MapPath("/fchs/earlytrack/xml/pages.xml")
>
>#Region " Web Form Designer Generated Code "
>
> 'This call is required by the Web Form Designer.
> <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
>
> End Sub
>
> Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
> 'CODEGEN: This method call is required by the Web Form Designer
> 'Do not modify it using the code editor.
> InitializeComponent()
> If (CType(Session("Loggedin"), String) <> "Y") Then
> Response.Redirect("/")
> End If
>
> us = CType(Session("UserValues"), UserInfo)
> county_id = us.CurrentCountyId
>
> Try
> If Not CheckPage.IsOK(us.RoleName, strPages, "edit_child.aspx") Then
> Response.Redirect("/fchs/earlytrack/not_auth.aspx")
> End If
> Catch ex As Exception
> Response.Redirect("/")
> End Try
> End Sub
>
>#End Region
>
> Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
> 'Put user code to initialize the page here
>
> old_county = CType(Session("old_county"), String)
>
> txtssn.Security = Control_Maker.GetComponentSecurity(us.RoleName, strSecurity, "txtssn")
>
> '*************************************
> '* page component security *
> '*************************************
> Dim frmForm As HtmlForm
> frmForm = CType(MyBase.FindControl("rpdfrm"), HtmlForm)
> Control_Maker.SecureControls(us.RoleName, strSecurity, frmForm)
>
> '*************************************
> '* datagrid security start *
> '*************************************
> Dim dgresult As String = Control_Maker.GetComponentSecurity(us.RoleName, strSecurity, "mydatagrid")
> If dgresult = "N" Then
> Dim elResult As String = "T"
> Dim col As Type
> Dim strCol As String
> Dim i As Integer
>
> For i = 0 To myDataGrid.Columns.Count - 1
> col = myDataGrid.Columns(i).GetType()
> strCol = col.Name
>
> Select Case strCol
> Case "BoundColumn"
> Dim bc As BoundColumn = CType(myDataGrid.Columns(i), BoundColumn)
> elResult = Control_Maker.GetComponentSecurity(us.RoleName, strSecurity, bc.DataField)
> Case "ButtonColumn"
> Dim buc As ButtonColumn = CType(myDataGrid.Columns(i), ButtonColumn)
> elResult = Control_Maker.GetComponentSecurity(us.RoleName, strSecurity, buc.CommandName)
> Case "EditCommandColumn"
> Dim ecc As EditCommandColumn = CType(myDataGrid.Columns(i), EditCommandColumn)
> elResult = Control_Maker.GetComponentSecurity(us.RoleName, strSecurity, ecc.EditText)
> End Select
>
> If (elResult <> "N") Then
> myDataGrid.Columns(i).Visible = False
> End If
> Next i
> Else
> myDataGrid.Visible = False
> End If
> '*************************************
> '* datagrid security end *
> '*************************************
>
> If Not IsPostBack Then
> populate_dropdowns()
> getChildInfo()
> End If
> End Sub
>
> Sub getChildInfo()
> Dim P_COUNTY_ID, P_ET_ID, P_USER_ID, P_COUNTY_NAME, P_FULL_NAME, P_FIRST_NAME, P_MIDDLE_NAME, P_LAST_NAME, P_BIRTH_DATE, P_SEX, P_ETHNICITY_ID As OracleParameter
> Dim P_ETHNICITY_DESC, P_RACE_ID, P_SSN, P_STREET1, P_STREET2, P_CITY, P_STATE, P_ZIP5, P_ZIP4 As OracleParameter
> Dim P_ENTRY_IMMUNIZATION_FLAG, P_EXIT_IMMUNIZATION_FLAG, P_ENTRY_PROVIDER_FLAG, P_EXIT_PROVIDER_FLAG, P_SCHOOL_DIST_ID, P_SCHOOL_DIST_DESC As OracleParameter
> Dim P_INCOME_RANGE_ID, P_INCOME_RANGE_DESC, P_FUND_SRC_ID, P_FUND_SRC_DESC, P_ERROR_ID, P_ERROR_MSG, P_RACE_DESC, P_RA_REC As OracleParameter
> Dim PP_ET_ID, PP_USER_ID, PP_ACCBY, PP_ERROR, PP_ERROR_TXT, P_SERVICE_AGENCY, P_SERVICE_CRDNTR, P_PEDIATRICIAN As OracleParameter
> Dim countyid As Integer
> Dim P_E_I_FLAG, P_E_IM_FLAG, P_ENTRY_P_FLAG, P_EXIT_P_FLAG, P_SCHOOL_DESC, P_INCOME_DESC As OracleParameter
>
> countyid = 1
>
> myConnection = New OracleConnection(odh)
> myConnection.Open()
>
> myCommand1 = myConnection.CreateCommand()
> With myCommand1
> .CommandText = "pkg_child.hold_child"
> .CommandType = CommandType.StoredProcedure
>
> With .Parameters
> PP_ET_ID = .Add("P_ET_ID", OracleType.VarChar, 10)
> PP_ET_ID.Value = CType(Session("ETID"), String)
>
> PP_USER_ID = .Add("P_USER_ID", OracleType.VarChar, 10)
> PP_USER_ID.Value = us.UserName
>
> PP_ACCBY = .Add("P_ACCESSED_BY", OracleType.VarChar, 60)
> PP_ACCBY.Direction = ParameterDirection.Output
>
> PP_ERROR = .Add("P_ERROR_ID", OracleType.Int32)
> PP_ERROR.Direction = ParameterDirection.Output
>
> PP_ERROR_TXT = .Add("P_ERROR_MSG", OracleType.VarChar, 128)
> PP_ERROR_TXT.Direction = ParameterDirection.Output
> End With
> .ExecuteNonQuery()
> End With
>
> If CType(PP_ERROR.Value, Integer) > 0 Then
> ' response.write(PP_ACCBY.value)
> myConnection.Close()
> Response.Redirect("/fchs/earlytrack/cus_error.aspx")
> Return
> End If
>
> myTrans = myConnection.BeginTransaction()
> myCommand = myConnection.CreateCommand()
> With myCommand
> .CommandText = "pkg_child.get_child_info"
> .CommandType = CommandType.StoredProcedure
> .Transaction = myTrans
> etid = CType(Session("ETID"), String)
>
> With .Parameters
> P_ET_ID = .Add("P_ET_ID", OracleType.VarChar, 10)
> P_ET_ID.Value = etid
>
> P_USER_ID = .Add("P_USER_ID", OracleType.VarChar, 10)
> P_USER_ID.Value = us.UserName
>
> P_COUNTY_ID = .Add("P_COUNTY_ID", OracleType.VarChar, 3)
> P_COUNTY_ID.Direction = ParameterDirection.Output
>
> P_COUNTY_NAME = .Add("P_COUNTY_NAME", OracleType.VarChar, 30)
> P_COUNTY_NAME.Direction = ParameterDirection.Output
>
> P_FULL_NAME = .Add("P_FULL_NAME", OracleType.VarChar, 40)
> P_FULL_NAME.Direction = ParameterDirection.Output
>
> P_FIRST_NAME = .Add("P_FIRST_NAME", OracleType.VarChar, 40)
> P_FIRST_NAME.Direction = ParameterDirection.Output
>
> P_MIDDLE_NAME = .Add("P_MIDDLE_NAME", OracleType.VarChar, 40)
> P_MIDDLE_NAME.Direction = ParameterDirection.Output
>
> P_LAST_NAME = .Add("P_LAST_NAME", OracleType.VarChar, 40)
> P_LAST_NAME.Direction = ParameterDirection.Output
>
> P_BIRTH_DATE = .Add("P_BIRTH_DATE", OracleType.VarChar, 10)
> P_BIRTH_DATE.Direction = ParameterDirection.Output
>
> P_SEX = .Add("P_SEX", OracleType.VarChar, 1)
> P_SEX.Direction = ParameterDirection.Output
>
> P_ETHNICITY_ID = .Add("P_ETHNICITY_ID", OracleType.VarChar, 1)
> P_ETHNICITY_ID.Direction = ParameterDirection.Output
>
> P_ETHNICITY_DESC = .Add("P_ETHNICITY_DESC", OracleType.VarChar, 10)
> P_ETHNICITY_DESC.Direction = ParameterDirection.Output
>
> P_RACE_ID = .Add("P_RACE_ID", OracleType.VarChar, 25)
> P_RACE_ID.Direction = ParameterDirection.Output
>
> P_RACE_DESC = .Add("P_RACE_DESC", OracleType.VarChar, 100)
> P_RACE_DESC.Direction = ParameterDirection.Output
>
> P_SSN = .Add("P_SSN", OracleType.VarChar, 12)
> P_SSN.Direction = ParameterDirection.Output
>
> P_STREET1 = .Add("P_STREET1", OracleType.VarChar, 55)
> P_STREET1.Direction = ParameterDirection.Output
>
> P_STREET2 = .Add("P_STREET2", OracleType.VarChar, 55)
> P_STREET2.Direction = ParameterDirection.Output
>
> P_CITY = .Add("P_CITY", OracleType.VarChar, 30)
> P_CITY.Direction = ParameterDirection.Output
>
> P_STATE = .Add("P_STATE", OracleType.VarChar, 2)
> P_STATE.Direction = ParameterDirection.Output
>
> P_ZIP5 = .Add("P_ZIP5", OracleType.VarChar, 5)
> P_ZIP5.Direction = ParameterDirection.Output
>
> P_ZIP4 = .Add("P_ZIP4", OracleType.VarChar, 4)
> P_ZIP4.Direction = ParameterDirection.Output
>
> P_E_I_FLAG = .Add("P_ENTRY_IMMUNIZATION_FLAG", OracleType.Char, 1)
> P_E_I_FLAG.Direction = ParameterDirection.Output
>
> P_E_IM_FLAG = .Add("P_EXIT_IMMUNIZATION_FLAG", OracleType.Char, 1)
> P_E_IM_FLAG.Direction = ParameterDirection.Output
>
> P_ENTRY_P_FLAG = .Add("P_ENTRY_PROVIDER_FLAG", OracleType.Char, 1)
> P_ENTRY_P_FLAG.Direction = ParameterDirection.Output
>
> P_EXIT_P_FLAG = .Add("P_EXIT_PROVIDER_FLAG", OracleType.Char, 1)
> P_EXIT_P_FLAG.Direction = ParameterDirection.Output
>
> P_SCHOOL_DIST_ID = .Add("P_SCHOOL_DIST_ID", OracleType.Int32)
> P_SCHOOL_DIST_ID.Direction = ParameterDirection.Output
>
> P_SCHOOL_DESC = .Add("P_SCHOOL_DIST_DESC", OracleType.VarChar, 60)
> P_SCHOOL_DESC.Direction = ParameterDirection.Output
>
> P_INCOME_RANGE_ID = .Add("P_INCOME_RANGE_ID", OracleType.Int32)
> P_INCOME_RANGE_ID.Direction = ParameterDirection.Output
>
> P_INCOME_DESC = .Add("P_INCOME_RANGE_DESC", OracleType.VarChar, 60)
> P_INCOME_DESC.Direction = ParameterDirection.Output
>
> P_FUND_SRC_ID = .Add("P_FUND_SRC_ID", OracleType.Int32)
> P_FUND_SRC_ID.Direction = ParameterDirection.Output
>
> P_FUND_SRC_DESC = .Add("P_FUND_SRC_DESC", OracleType.VarChar, 60)
> P_FUND_SRC_DESC.Direction = ParameterDirection.Output
>
> P_SERVICE_AGENCY = .Add("P_SERVICE_AGENCY", OracleType.VarChar, 60)
> P_SERVICE_AGENCY.Direction = ParameterDirection.Output
>
> P_SERVICE_CRDNTR = .Add("P_SERVICE_CRDNTR", OracleType.VarChar, 120)
> P_SERVICE_CRDNTR.Direction = ParameterDirection.Output
>
> P_PEDIATRICIAN = .Add("P_PEDIATRICIAN", OracleType.VarChar, 60)
> P_PEDIATRICIAN.Direction = ParameterDirection.Output
>
> P_ERROR_ID = .Add("P_ERROR_ID", OracleType.Int32)
> P_ERROR_ID.Direction = ParameterDirection.Output
>
> P_ERROR_MSG = .Add("P_ERROR_MSG", OracleType.VarChar, 100)
> P_ERROR_MSG.Direction = ParameterDirection.Output
>
> P_RA_REC = .Add("P_RA_REC", OracleType.Cursor)
> P_RA_REC.Direction = ParameterDirection.Output
> End With
> End With
>
> Try
> dsreferral = New DataSet()
> adapter = New OracleDataAdapter(myCommand)
> reccount = adapter.Fill(dsreferral)
> myDataGrid.DataSource = dsreferral
> myDataGrid.DataBind()
> myDataGrid.Columns(6).Visible = (reccount > 1)
>
> If CType(P_ERROR_ID.Value, Integer) = 0 Then
> If Not IsDBNull(P_FIRST_NAME.Value) Then
> txtFirstName.Text = CType(P_FIRST_NAME.Value, String)
> End If
>
> If Not IsDBNull(P_LAST_NAME.Value) Then
> txtLastName.Text = CType(P_LAST_NAME.Value, String)
> End If
>
> If Not IsDBNull(P_MIDDLE_NAME.Value) Then
> txtMiddleName.Text = CType(P_MIDDLE_NAME.Value, String)
> End If
>
> If Not IsDBNull(P_BIRTH_DATE.Value) Then
> txtBirthDate.Text = CType(P_BIRTH_DATE.Value, String)
> End If
>
> If Not IsDBNull(P_SSN.Value) Then
> txtssn.Text = CType(P_SSN.Value, String)
> End If
>
> If Not IsDBNull(P_STREET1.Value) Then
> lblstreet1.Text = CType(P_STREET1.Value, String)
> End If
>
> If Not IsDBNull(P_STREET2.Value) Then
> lblstreet2.Text = CType(P_STREET2.Value, String)
> End If
>
> If Not IsDBNull(P_CITY.Value) Then
> lblcity.Text = CType(P_CITY.Value, String)
> End If
>
> If Not IsDBNull(P_STATE.Value) Then
> lblstate.Text = CType(P_STATE.Value, String)
> End If
>
> 'TODO: AAA Check here
> If Not IsDBNull(P_ZIP5.Value) Then
> lblzip5.Text = CType(P_ZIP5.Value, String)
> End If
>
> If Not IsDBNull(P_ZIP4.Value) Then
> lblzip4.Text = " - " & CType(P_ZIP4.Value, String)
> End If
>
> If Not IsDBNull(P_E_IM_FLAG.Value) Then
> lblImmunizationExit.Text = CType(P_E_IM_FLAG.Value, String)
> End If
>
> If Not IsDBNull(P_EXIT_P_FLAG.Value) Then
> lblProviderExit.Text = CType(P_EXIT_P_FLAG.Value, String)
> End If
>
> If Not IsDBNull(P_SEX.Value) AndAlso CType(P_SEX.Value, String) <> " " Then
> dlsex.Items.FindByValue(CType(P_SEX.Value, String)).Selected = True
> End If
>
> If Not IsDBNull(P_ETHNICITY_ID.Value) Then
> dlethnicitycode.Items.FindByValue(CType(P_ETHNICITY_ID.Value, String)).Selected = True
> End If
>
> dlcounty.Items.FindByValue(CType(P_COUNTY_ID.Value, String)).Selected = True
>
> If old_county = Nothing Then
> old_county = dlcounty.SelectedItem.Text
> Session("old_county") = old_county
> End If
>
> With dlfamilyfunding.Items
> If IsDBNull(P_FUND_SRC_ID.Value) Then
> .FindByValue("").Selected = True
> Else
> .FindByValue(CType(P_FUND_SRC_ID.Value, String)).Selected = True
> End If
> End With
>
> With dlschooldistrict.Items
> If IsDBNull(P_SCHOOL_DIST_ID.Value) Then
> .FindByValue("").Selected = True
> Else
> .FindByValue(CType(P_SCHOOL_DIST_ID.Value, String)).Selected = True
> End If
> End With
>
> With dlincomebracket.Items
> If IsDBNull(P_INCOME_RANGE_ID.Value) Then
> .FindByValue("").Selected = True
> Else
> .FindByValue(CType(P_INCOME_RANGE_ID.Value, String)).Selected = True
> End If
> End With
>
> With dlimmunization.Items
> If IsDBNull(P_E_I_FLAG.Value) Then
> If IsNothing(.FindByValue("")) Then
> .FindByValue("N").Selected = True
> Else
> .FindByValue("").Selected = True
> End If
> Else
> .FindByValue(CType(P_E_I_FLAG.Value, String)).Selected = True
> End If
> End With
>
> With dlhealthcare.Items
> If IsDBNull(P_ENTRY_P_FLAG.Value) Then
> If IsNothing(dlhealthcare.Items.FindByValue("")) Then
> .FindByValue("N").Selected = True
> Else
> .FindByValue("").Selected = True
> End If
> Else
> .FindByValue(CType(P_ENTRY_P_FLAG.Value, String)).Selected = True
> End If
> End With
>
> Dim strrace As String
> strrace = CType(P_RACE_ID.Value, String).Trim()
> Dim strchrace As String
> Dim j As Integer
> j = 0
> Dim k As Integer
> Do While j < strrace.Length - 1
> k = strrace.IndexOf(",", j)
> If (k >= 0) Then
> strchrace = strrace.Substring(k - 1, 1)
> If (strchrace.Trim().Length > 0) Then
> Dim i As Integer
> For i = 0 To cblRace.Items.Count - 1
> If cblRace.Items(i).Value = strchrace Then
> cblRace.Items(i).Selected = True
> End If
> Next
> End If
> If (j < k) Then
> j = k + 1
> End If
> Else
> j = strrace.Length + 1
> End If
> Loop
> End If
>
> myTrans.Commit()
> myConnection.Close()
> Catch eex As Exception
> Response.Write(eex.ToString())
> 'response.write(P_ERROR_ID.value)
> Finally
> 'mytrans.rollback()
> End Try
> End Sub
>
> Sub populate_dropdowns()
> Dim conpubs As OracleConnection
> Dim cmdselect As OracleCommand
> conpubs = New OracleConnection(odh)
>
> conpubs.Open()
> '
> ' PKG_COUNTY.LIST_BY_DESC
> '
>
> cmdselect = New OracleCommand("select county_name,county_id from county order by county_name", conpubs)
> Dim myReader As OracleDataReader
> myReader = cmdselect.ExecuteReader()
>
> With dlcounty
> .DataSource = myReader
> .DataTextField = "County_name"
> .DataValueField = "County_id"
> .DataBind()
> .Items.Insert(0, New ListItem())
> End With
> myReader.Close()
>
> '
> ' PKG_ETHNICITY.LIST_BY_DESC
> '
>
> cmdselect = New OracleCommand("select ethnicity_desc,ethnicity_id from ethnicity order by ethnicity_desc", conpubs)
> Dim myReader1 As OracleDataReader
> myReader = cmdselect.ExecuteReader()
>
> With dlethnicitycode
> .DataSource = myReader
> .DataTextField = "Ethnicity_Desc"
> .DataValueField = "Ethnicity_id"
> .DataBind()
> .Items.Insert(0, New ListItem())
> End With
>
> myReader.Close()
>
> '
> ' PKG_FUND_SOURCE.LIST_BY_DESC
> '
>
> cmdselect = New OracleCommand("select fund_src_desc,fund_src_id from fund_source order by fund_src_desc", conpubs)
> Dim myReader2 As OracleDataReader
> myReader2 = cmdselect.ExecuteReader()
>
> With dlfamilyfunding
> .DataSource = myReader2
> .DataTextField = "fund_src_desc"
> .DataValueField = "fund_src_id"
> .DataBind()
> .Items.Insert(0, New ListItem())
> End With
> myReader2.Close()
>
> Dim sCmd As New StringBuilder()
>
> '
> ' PKG_SCHOOL_DISTRICT.LIST_FOR_USER_COUNTIES
> '
>
> With sCmd
> .Append("select ")
> .Append(" SCHOOL_DIST_DESC,")
> .Append(" school_dist_id ")
> .Append("from ")
> .Append(" school_district ")
> .Append("where ")
> .Append(" COUNTY_ID IN (")
> .Append("select ")
> .Append(" county_id ")
> .Append("from ")
> .Append(" county ")
> .Append("where ")
> .Append(" county.COUNTY_NAME IN (")
> .Append(us.CountyName)
> .Append("))")
> End With
> cmdselect = New OracleCommand(sCmd.ToString(), conpubs)
>
> Dim myReader3 As OracleDataReader
> myReader3 = cmdselect.ExecuteReader()
> With dlschooldistrict
> .DataSource = myReader3
> .DataTextField = "school_dist_desc"
> .DataValueField = "school_dist_id"
> .DataBind()
> .Items.Insert(0, New ListItem())
> End With
> myReader3.Close()
>
> '
> ' PKG_INCOME_RANGE.LIST_BY_DESC
> '
>
> cmdselect = New OracleCommand("select income_range_desc,income_range_id from income_range order by income_range_desc", conpubs)
> Dim myReader4 As OracleDataReader
> myReader4 = cmdselect.ExecuteReader()
>
> With dlincomebracket
> .DataSource = myReader4
> .DataTextField = "income_range_desc"
> .DataValueField = "income_range_id"
> .DataBind()
> .Items.Insert(0, New ListItem())
> End With
> myReader4.Close()
>
> '
> ' PKG_RACE.LIST_BY_DESC
> '
>
> cmdselect = New OracleCommand("select race_id,race_desc from race order by race_desc", conpubs)
> Dim myReader5 As OracleDataReader
> myReader5 = cmdselect.ExecuteReader()
> With cblRace
> .DataSource = myReader5
> .DataTextField = "race_desc"
> .DataValueField = "race_id"
> .DataBind()
> End With
> myReader5.Close()
>
> Dim c_etid As String
> c_etid = CType(Session("ETID"), String)
> eg_id = " "
> '
> ' PKG_SERVICE.LIST_OPEN_ELIG_FOR_CHILD
> '
>
> cmdselect = New OracleCommand("select eligibility_id from service where et_id = " & c_etid & " and exit_Date is Null", conpubs)
> Dim myReader6 As OracleDataReader
> myReader6 = cmdselect.ExecuteReader()
> While myReader6.Read()
> e_id = CType(myReader6.GetDecimal(0), Integer)
> eg_id += e_id.ToString()
> End While
> myReader6.Close()
>
> conpubs.Close()
>
> With dlsex.Items
> .Add(New ListItem("Female", "F"))
> .Add(New ListItem("Male ", "M"))
> .Insert(0, New ListItem())
> End With
>
> dlincomebracket.Items.Add(New ListItem(" ", ""))
> dlschooldistrict.Items.Add(New ListItem(" ", ""))
> dlfamilyfunding.Items.Insert(0, New ListItem(" ", ""))
>
> With dlimmunization.Items
> .Add(New ListItem("No", "N"))
> .Add(New ListItem("Yes", "Y"))
>
> If (eg_id.Trim().Length = 0) Then
> .Insert(0, New ListItem("", ""))
> End If
> End With
>
> With dlhealthcare.Items
> .Add(New ListItem("No", "N"))
> .Add(New ListItem("Yes", "Y"))
> If (eg_id.Trim().Length = 0) Then
> .Insert(0, New ListItem("", ""))
> End If
> End With
> End Sub
>
> Sub MyDataGrid_Edit(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
> Session("resp_adult_id") = e.Item.Cells(3).Text
> ' response.write(e.Item.Cells(5).Text)
> Response.Redirect("/fchs/earlytrack/edit_adult.aspx")
> End Sub
>
> Sub MyDataGrid_Delete(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
> Dim P_ET_ID, P_RA_ID, P_ERR_ID, P_ERR_MSG As OracleParameter
> If (e.Item.Cells(4).Text = "Y") Then
> ra_err_msg = "CareGiver is Marked as Primary. Cannot Delete"
> Return
> End If
>
> Try
> myConnection = New OracleConnection(odh)
> myConnection.Open()
> myTrans = myConnection.BeginTransaction()
> myCommand = myConnection.CreateCommand()
> With myCommand
> .CommandText = "pkg_responsible_adult.DELETE_RA"
> .CommandType = CommandType.StoredProcedure
> .Transaction = myTrans
>
> With .Parameters
> P_ET_ID = .Add("P_ET_ID", OracleType.VarChar, 10)
> P_ET_ID.Value = CType(Session("ETID"), String)
>
> P_RA_ID = .Add("P_RESP_ADULT_ID", OracleType.Int32)
> P_RA_ID.Value = e.Item.Cells(3).Text
>
> P_ERR_ID = .Add("P_ERROR_ID", OracleType.Int32)
> P_ERR_ID.Direction = ParameterDirection.Output
>
> P_ERR_MSG = .Add("P_ERROR_MSG", OracleType.VarChar, 100)
> P_ERR_MSG.Direction = ParameterDirection.Output
> End With
> .ExecuteNonQuery()
> End With
> If CType(P_ERR_ID.Value, Integer) = 0 Then
> myTrans.Commit()
> myConnection.Close()
> populate_dropdowns()
> getChildInfo()
> Else
> Response.Write(P_ERR_MSG.Value)
> myTrans.Rollback()
> myConnection.Close()
> End If
> Catch objException As Exception
> Response.Write(objException.ToString())
> End Try
> End Sub
>
> Function edit_ra(ByVal etid As String) As Boolean
> Response.Redirect("/fchs/earlytrack/edit_adult.aspx?raid=" + etid)
> End Function
>
> Sub update_Child()
> Dim P_COUNTY_ID, P_ET_ID, P_USER_ID, P_COUNTY_NAME, P_FULL_NAME, P_FIRST_NAME, P_MIDDLE_NAME, P_LAST_NAME, P_BIRTH_DATE, P_SEX, P_ETHNICITY_ID As OracleParameter
> Dim P_ETHNICITY_DESC, P_RACE_ID, P_SSN, P_STREET1, P_STREET2, P_CITY, P_STATE, P_ZIP5, P_ZIP4 As OracleParameter
> Dim P_ENTRY_IMMUNIZATION_FLAG, P_EXIT_IMMUNIZATION_FLAG, P_ENTRY_PROVIDER_FLAG, P_EXIT_PROVIDER_FLAG, P_SCHOOL_DIST_ID, P_SCHOOL_DIST_DESC As OracleParameter
> Dim P_INCOME_RANGE_ID, P_INCOME_RANGE_DESC, P_FUND_SRC_ID, P_FUND_SRC_DESC, P_ERROR_ID, P_ERROR_MSG, P_RACE_DESC As OracleParameter
> Dim P_E_I_FLAG, P_E_IM_FLAG, P_ENTRY_P_FLAG, P_EXIT_P_FLAG As OracleParameter
>
> myConnection = New OracleConnection(odh)
> myConnection.Open()
>
> myTrans = myConnection.BeginTransaction()
> myCommand = myConnection.CreateCommand()
> With myCommand
> .CommandText = "pkg_child.UPDATE_CHILD"
> .CommandType = CommandType.StoredProcedure
> .Transaction = myTrans
>
> etid = CType(Session("ETID"), String)
>
> With .Parameters
> P_COUNTY_ID = .Add("P_COUNTY_ID", OracleType.Int32)
> P_COUNTY_ID.Value = dlcounty.SelectedItem.Value
>
> P_ET_ID = .Add("P_ET_ID", OracleType.VarChar, 10)
> P_ET_ID.Value = etid
>
> P_USER_ID = .Add("P_USER_ID", OracleType.VarChar, 10)
> P_USER_ID.Value = us.UserName
>
> P_SSN = .Add("P_SSN", OracleType.VarChar, 12)
> P_SSN.Value = txtssn.Text
>
> P_FIRST_NAME = .Add("P_FIRST_NAME", OracleType.VarChar, 40)
> P_FIRST_NAME.Value = txtFirstName.Text
>
> P_MIDDLE_NAME = .Add("P_MIDDLE_NAME", OracleType.VarChar, 40)
> P_MIDDLE_NAME.Value = txtMiddleName.Text
>
> P_LAST_NAME = .Add("P_LAST_NAME", OracleType.VarChar, 40)
> P_LAST_NAME.Value = txtLastName.Text
>
> P_BIRTH_DATE = .Add("P_BIRTH_DATE", OracleType.VarChar, 10)
> P_BIRTH_DATE.Value = txtBirthDate.Text
>
> P_SEX = .Add("P_SEX", OracleType.VarChar, 1)
> P_SEX.Value = dlsex.SelectedItem.Value
>
> P_ETHNICITY_ID = .Add("P_ETHNICITY_ID", OracleType.VarChar, 1)
> P_ETHNICITY_ID.Value = dlethnicitycode.SelectedItem.Value
>
> Dim raceid As String
> raceid = ""
> Dim i As Integer
>
> For i = 0 To cblRace.Items.Count - 1
> If cblRace.Items(i).Selected = True Then
> raceid += cblRace.Items(i).Value
> raceid += ","
> End If
> Next
>
> P_RACE_ID = .Add("P_RACE_ID", OracleType.VarChar, 25)
> P_RACE_ID.Value = raceid
>
> P_E_I_FLAG = .Add("P_ENTRY_IMMUNIZATION_FLAG", OracleType.Char, 1)
> P_E_I_FLAG.Value = dlimmunization.SelectedItem.Value
>
> P_E_IM_FLAG = .Add("P_EXIT_IMMUNIZATION_FLAG", OracleType.Char, 1)
> P_E_IM_FLAG.Value = lblImmunizationExit.Text
>
> P_ENTRY_P_FLAG = .Add("P_ENTRY_PROVIDER_FLAG", OracleType.Char, 1)
> P_ENTRY_P_FLAG.Value = dlhealthcare.SelectedItem.Value
>
> P_EXIT_P_FLAG = .Add("P_EXIT_PROVIDER_FLAG", OracleType.Char, 1)
> P_EXIT_P_FLAG.Value = lblProviderExit.Text
>
> P_SCHOOL_DIST_ID = .Add("P_SCHOOL_DIST_ID", OracleType.Int32)
> P_SCHOOL_DIST_ID.Value = dlschooldistrict.SelectedItem.Value
>
> P_INCOME_RANGE_ID = .Add("P_INCOME_RANGE_ID", OracleType.Int32)
> P_INCOME_RANGE_ID.Value = dlincomebracket.SelectedItem.Value
>
> P_FUND_SRC_ID = .Add("P_FUND_SRC_ID", OracleType.Int32)
> P_FUND_SRC_ID.Value = dlfamilyfunding.SelectedItem.Value
>
> P_ERROR_ID = .Add("P_ERROR_ID", OracleType.Int32)
> P_ERROR_ID.Direction = ParameterDirection.Output
>
> P_ERROR_MSG = .Add("P_ERROR_MSG", OracleType.VarChar, 100)
> P_ERROR_MSG.Direction = ParameterDirection.Output
> End With
> End With
>
> Try
> myCommand.ExecuteNonQuery()
>
> If CType(P_ERROR_ID.Value, Integer) = 0 Then
> myTrans.Commit()
> myConnection.Close()
> If CType(Session("old_county"), String) <> dlcounty.SelectedItem.Text AndAlso InStr(us.CountyName, dlcounty.SelectedItem.Text) = 0 Then
> Response.Redirect("/fchs/earlytrack/child_search_results.aspx")
> Else
> Response.Redirect("/fchs/earlytrack/display_child_info.aspx?id=" + CType(Session("ETID"), String))
> End If
> Else
> myTrans.Rollback()
> myConnection.Close()
> Response.Write(P_ERROR_ID.Value)
> Response.Write(P_ERROR_MSG.Value)
> End If
> Catch eex As Exception
> Response.Write(eex.ToString())
> Finally
> Session.Remove("old_county")
> End Try
> End Sub
>
> Sub Button_New(ByVal s As Object, ByVal e As EventArgs)
> Response.Redirect("/fchs/earlytrack/add_adult.aspx")
> End Sub
>
> Sub Button_Save(ByVal s As Object, ByVal e As EventArgs)
> If (MyBase.IsValid) Then
> Try
> update_Child()
> ' Response.Redirect("/fchs/earlytrack/display_child_info.aspx?id="+Session("ETID"))
> Catch eex As Exception
> Response.Write(eex.ToString())
> Finally
> End Try
> ' Response.Redirect("/fchs/earlytrack/display_child_info.aspx?id="+Session("ETID"))
> End If
> End Sub
>
> Sub remove_lock()
> Dim P_ET_ID, P_USER_ID As OracleParameter
> Dim P_ERROR_ID, P_ERROR_MSG As OracleParameter
>
> myConnection = New OracleConnection(odh)
> myConnection = New OracleConnection(odh)
> myConnection.Open()
>
> myTrans = myConnection.BeginTransaction()
> myCommand = myConnection.CreateCommand()
> With myCommand
> .CommandText = "pkg_child.release_child"
> .CommandType = CommandType.StoredProcedure
> .Transaction = myTrans
> etid = CType(Session("ETID"), String)
>
> With .Parameters
> P_ET_ID = .Add("P_ET_ID", OracleType.VarChar, 10)
> P_ET_ID.Value = etid
>
> P_USER_ID = .Add("P_USER_ID", OracleType.VarChar, 10)
> P_USER_ID.Value = us.UserName
>
> P_ERROR_ID = .Add("P_ERROR_ID", OracleType.Int32)
> P_ERROR_ID.Direction = ParameterDirection.Output
>
> P_ERROR_MSG = .Add("P_ERROR_MSG", OracleType.VarChar, 100)
> P_ERROR_MSG.Direction = ParameterDirection.Output
> End With
> End With
>
> Try
> myCommand.ExecuteNonQuery()
> If CType(P_ERROR_ID.Value, Integer) = 0 Then
> myTrans.Commit()
> myConnection.Close()
> Else
> ' response.write("Coming in to the else part of the if")
> myTrans.Rollback()
> myConnection.Close()
> Response.Write(P_ERROR_ID.Value)
> Response.Write(P_ERROR_MSG.Value)
> End If
> Catch eex As Exception
> Response.Write(eex.ToString())
> Finally
> End Try
> End Sub
>
> Sub Button_Cancel(ByVal s As Object, ByVal e As EventArgs)
> Try
> remove_lock()
> Response.Redirect("/fchs/earlytrack/display_child_info.aspx?id=" + CType(Session("ETID"), String))
> Catch eex As Exception
> Response.Write(eex.ToString())
> End Try
> End Sub
>
> Sub Check_Date(ByVal sender As Object, ByVal args As ServerValidateEventArgs)
>
> If (IsDate(txtBirthDate.Text)) Then
> 'get the date range
> Dim pastTime As DateTime
> Dim currentTime As DateTime
> Dim myTime As DateTime
>
> pastTime = DateTime.Now
> currentTime = DateTime.Now
> myTime = DateTime.Parse(txtBirthDate.Text)
> '
> ' TODO: Changed the next line to allow Cuyahoga county to all older children
> '
> ' pastTime = pastTime.AddMonths(-48)
> pastTime = pastTime.AddMonths(-60)
> currentTime = currentTime.AddMonths(10)
>
> If (myTime > pastTime) Then
> If (myTime <= currentTime) Then
> args.IsValid = True
> Else
> args.IsValid = False
> cvCheckDate.ErrorMessage = "Date must not be later than 10 Months from the current date."
> End If
> Else
> args.IsValid = False
> cvCheckDate.ErrorMessage = "Date must be no more than 48 months prior to the current date."
> End If
> Else
> If Len(txtBirthDate.Text) > 0 Then
> args.IsValid = False
> cvCheckDate.ErrorMessage = "Not a valid date."
> End If
> End If
> End Sub
>End Class
>
>----------------------------------------------------------------------------------------------------------------------------------------------------------
>
>>
>>Rex,
>>
>>I use SQL Server instead of Oracle, so some things may be different. Can you give us an idea of how your app is accessing the data? Some sample code of a typical access method?
>>
>>~~Bonnie
>>
>>
>>>All,
>>>
>>>I am using the MS OracleClient and connection pooling in my 2 application. I have the min pool size set to 100 and the max pool size set to 500. The connection strings are identical. This is a test server and I am the only user, but the Oracle DBA is telling me there are 201 open connection for my user name. The 1 is from my TOAD connection, but the connection count is doubled.
>>>
>>>I am using the OracleClient from Visual Studio 2003.
>>>
>>>Has anyone had/solved this problem?
>>>
>>>TIA
>>>
>>>Rex
