Hi friends, i need to convert the code from ADO to ADO.NET which uses either a datareader or dataset. Please help me
This is the class file....
Public Class DALGetData()
Dim iRetVal = String.Empty
Private _sFlgUnitOwnerCvg As String
Dim SQLStatement = String.Empty
Dim SQLSelect = String.Empty
Dim SQLFrom = String.Empty
Dim SQLWhere = String.Empty
Dim sBVSID
Dim sBVSDate
Dim iITV_ACV
Dim iITV_RC
Dim sBVSMsg
Dim vITVInformation()
Dim Q As Char
Dim strQualifier As Char
Dim sUnitCvgType
Dim lTemp
Dim sUnitOwnerCvgType
Dim iUnitLimit
Dim iUnitOwnerCvgLimit = 0
Dim lNBR_OF_UNITS_TOT
Dim constring As String = "DSN=COSPROD;"
Public Function GetData()
Dim iBldgCount, iPrevLOC
iRetVal = ""
iBldgCount = 0
iPrevLOC = 0
sFlgUnitOwnerCvg = ""
SQLSelect = ""
SQLFrom = ""
SQLWhere = ""
SQLStatement = ""
Dim rsBldgInformation = New ADODB.Recordset
''rsBldgInformation As New ADODB.Recordset()
'' ActiveConnection() = "Dsn=COSPROD;uid=iusr;dbalias=COS_DEV"
Dim con = New OdbcConnection(constring)
con.Open()
'**********************************************************************************
If (HttpContext.Current.Session("sesPDCT_CD") = "PREM" And HttpContext.Current.Session("sesPOLICY_PGM_CD") = "RST") Or _
(Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "ICU" Or Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "IED" Or Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "IRE" Or _
Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "ISS" Or Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "MEE" Or Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "OFF" Or _
Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "RET" Or Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "SER" Or Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "WHL" Or _
Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "TRD" Or Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "CFT") Then
SQLSelect = "SELECT B.LOC_NBR, B.UOR_NBR, A.STREET_ADDR_1, A.CITY_NM, A.ST_CD, A.ZIP_CD, B.COV_PROP, B.ACV_REPLCMNT_BLD , B.NBR_OF_UNITS_TOT "
SQLFrom = "FROM " & "COS.TCOSW090 A, " & "COS.TCOSW110 B "
SQLWhere = "WHERE " _
& "A.POLICY_NBR=B.POLICY_NBR AND A.PART_NBR=B.PART_NBR AND A.ITERATION=B.ITERATION AND " _
& "A.LOC_NBR=B.LOC_NBR AND A.LOC_TYP_CD=B.LOC_TYP_CD AND " _
& "A.PART_NBR = '" & HttpContext.Current.Session("sesPart_Nbr") & "' " _
& "AND A.POLICY_NBR = '" & HttpContext.Current.Session("sesPolicy_Nbr") & "' " _
& "AND A.ITERATION = " & HttpContext.Current.Session("sesIteration") & " " _
& "AND A.LOC_TYP_CD = 'B' " _
& "ORDER BY B.LOC_NBR, B.UOR_NBR"
Else
SQLSelect = "SELECT B.LOC_NBR, B.UOR_NBR, A.STREET_ADDR_1, A.CITY_NM, A.ST_CD, A.ZIP_CD, B.COV_PROP, C.UW_COND_ANSWER, B.NBR_OF_UNITS_TOT "
SQLFrom = "FROM " & "COS.TCOSW090 A, " & "COS.TCOSW110 B, " & "COS.TCOSW380 C "
SQLWhere = "WHERE " _
& "A.POLICY_NBR=B.POLICY_NBR AND A.PART_NBR=B.PART_NBR AND A.ITERATION=B.ITERATION AND " _
& "A.LOC_NBR=B.LOC_NBR AND A.LOC_TYP_CD=B.LOC_TYP_CD AND " _
& "A.POLICY_NBR=C.POLICY_NBR AND A.PART_NBR=C.PART_NBR AND A.ITERATION=C.ITERATION AND " _
& "A.LOC_NBR=C.LOC_NBR AND A.LOC_TYP_CD=C.LOC_TYP_CD AND " _
& "A.PART_NBR = '" & HttpContext.Current.Session("sesPart_Nbr") & "' " _
& "AND A.POLICY_NBR = '" & HttpContext.Current.Session("sesPolicy_Nbr") & "' " _
& "AND A.ITERATION = " & HttpContext.Current.Session("sesIteration") & " " _
& "AND A.LOC_TYP_CD = 'B' " _
& "AND C.UOR_TYP = 'L' " _
& "AND C.UW_COND_ID = 'PLOC06' " _
& "AND C.UOR_NBR = 0 " _
& "ORDER BY B.LOC_NBR, B.UOR_NBR"
End If
On Error Resume Next
Dim err = 0
SQLStatement = SQLSelect & " " & SQLFrom & " " & SQLWhere
rsBldgInformation.Open(SQLStatement, commDec.DataConn, 0, 3)
If err = 0 Then
Do
iITV_ACV = 0
iITV_RC = 0
sBVSID = ""
sBVSDate = ""
sBVSMsg = ""
iRetVal = ""
If IsThereAnyITVData(rsBldgInformation("LOC_NBR"), rsBldgInformation("UOR_NBR")) = "True" Then
Call GetITVData(rsBldgInformation("LOC_NBR"), rsBldgInformation("UOR_NBR"))
If iRetVal = "1" Then
iBldgCount = iBldgCount + 1
Dim vITVInformation(14, iBldgCount)
'Update the Array with Building/Location Information
vITVInformation(1, iBldgCount) = rsBldgInformation("LOC_NBR")
vITVInformation(2, iBldgCount) = Trim(rsBldgInformation("STREET_ADDR_1").ToString())
vITVInformation(3, iBldgCount) = Trim(rsBldgInformation("CITY_NM").ToString())
vITVInformation(4, iBldgCount) = Trim(rsBldgInformation("ST_CD").ToString())
vITVInformation(5, iBldgCount) = Trim(rsBldgInformation("ZIP_CD").ToString())
vITVInformation(6, iBldgCount) = rsBldgInformation("UOR_NBR")
vITVInformation(7, iBldgCount) = FormatNumber(rsBldgInformation("COV_PROP"), 0)
vITVInformation(10, iBldgCount) = sBVSID
vITVInformation(11, iBldgCount) = sBVSDate
vITVInformation(12, iBldgCount) = sBVSMsg
vITVInformation(13, iBldgCount) = ""
vITVInformation(14, iBldgCount) = ""
'SIR 06212010-224331 DM 98061 - BVS/ITV RST Program (USZAVP8) Begin MOD
'**********************************************************************************
'USZAVP8 SIR : 07142010-9426 DM 99515 - BVS/ITV for other Precision programs BEGIN
'**********************************************************************************
'If Session("sesPDCT_CD") = "PREM" And Session("sesPOLICY_PGM_CD") = "RST" Then 'Old Code
If (HttpContext.Current.Session("sesPDCT_CD") = "PREM" And HttpContext.Current.Session("sesPOLICY_PGM_CD") = "RST") Or _
(Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "ICU" Or Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "IED" Or Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "IRE" Or _
Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "ISS" Or Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "MEE" Or Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "OFF" Or _
Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "RET" Or Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "SER" Or Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "WHL" Or _
Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "TRD" Or Trim(HttpContext.Current.Session("sesPOLICY_PGM_CD")) = "CFT") Then
'**********************************************************************************
'USZAVP8 SIR : 07142010-9426 DM 99515 - BVS/ITV for other Precision programs End
'**********************************************************************************
If Trim(rsBldgInformation("ACV_REPLCMNT_BLD").ToString()) = "1" Then
If iITV_ACV > 0 Then
vITVInformation(8, iBldgCount) = FormatNumber(iITV_ACV, 0)
End If
If iITV_RC > 0 Then
vITVInformation(9, iBldgCount) = FormatNumber(iITV_RC, 0)
End If
vITVInformation(13, iBldgCount) = "ACV"
vITVInformation(14, iBldgCount) = "RC"
ElseIf Trim(rsBldgInformation("ACV_REPLCMNT_BLD").ToString()) = "2" Or Trim(rsBldgInformation("ACV_REPLCMNT_BLD").ToString()) = "4" Then
If iITV_RC > 0 Then
vITVInformation(8, iBldgCount) = FormatNumber(iITV_RC, 0)
End If
If iITV_ACV > 0 Then
vITVInformation(9, iBldgCount) = FormatNumber(iITV_ACV, 0)
End If
vITVInformation(13, iBldgCount) = "RC"
vITVInformation(14, iBldgCount) = "ACV"
ElseIf HttpContext.Current.Session("sesPDCT_CD") = "PREM" And Trim(rsBldgInformation("ACV_REPLCMNT_BLD").ToString()) = "5" Then
If iITV_RC > 0 Then
vITVInformation(8, iBldgCount) = FormatNumber(iITV_RC, 0)
End If
If iITV_ACV > 0 Then
vITVInformation(9, iBldgCount) = FormatNumber(iITV_ACV, 0)
End If
vITVInformation(13, iBldgCount) = "RC"
vITVInformation(14, iBldgCount) = "ACV"
End If
ElseIf HttpContext.Current.Session("sesPDCT_CD") = "ABOP" And HttpContext.Current.Session("sesPOLICY_PGM_CD") = "HAB" Then
'SIR 06212010-224331 DM 98061 - BVS/ITV RST Program (USZAVP8) End MOD
If Trim(rsBldgInformation("UW_COND_ANSWER").ToString()) = "1" Then
If iITV_ACV > 0 Then
vITVInformation(8, iBldgCount) = FormatNumber(iITV_ACV, 0)
End If
If iITV_RC > 0 Then
vITVInformation(9, iBldgCount) = FormatNumber(iITV_RC, 0)
End If
vITVInformation(13, iBldgCount) = "ACV"
vITVInformation(14, iBldgCount) = "RC"
ElseIf Trim(rsBldgInformation("UW_COND_ANSWER").ToString()) = "2" Or Trim(rsBldgInformation("UW_COND_ANSWER").ToString()) = "5" Then
If iITV_RC > 0 Then
vITVInformation(8, iBldgCount) = FormatNumber(iITV_RC, 0)
End If
If iITV_ACV > 0 Then
vITVInformation(9, iBldgCount) = FormatNumber(iITV_ACV, 0)
End If
vITVInformation(13, iBldgCount) = "RC"
vITVInformation(14, iBldgCount) = "ACV"
End If
'SIR 06212010-224331 DM 98061 - BVS/ITV RST Program (USZAVP8) Begin MOD
End If
'SIR 06212010-224331 DM 98061 - BVS/ITV RST Program (USZAVP8) End MOD
'Get Unit Owner Coverage Limit
If iPrevLOC <> rsBldgInformation("LOC_NBR").ToString() Then
iPrevLOC = rsBldgInformation("LOC_NBR")
Call GetUnitOwnerCvg(rsBldgInformation("LOC_NBR"))
End If
'Unit Owners Coverage Included (05R400)
If sUnitOwnerCvgType = "05R400" Then
'Get the Unit Limit
sUnitCvgType = "057760"
Call GetUnitLimit(rsBldgInformation("LOC_NBR"), rsBldgInformation("UOR_NBR"), sUnitCvgType)
If iUnitLimit > 0 Then
lTemp = 0
lTemp = lTemp + CLng(rsBldgInformation("COV_PROP").ToString())
lTemp = lTemp + CLng(iUnitLimit)
vITVInformation(7, iBldgCount) = FormatNumber(lTemp, 0) & "*"
sFlgUnitOwnerCvg = "*"
End If
End If
'Unit Owners Coverage Blanket (05R402)
If sUnitOwnerCvgType = "05R402" Then
'Get the Unit Limit
sUnitCvgType = "057761"
Call GetUnitLimit(rsBldgInformation("LOC_NBR"), rsBldgInformation("UOR_NBR"), sUnitCvgType)
If iUnitLimit > 0 Then
lTemp = 0
lTemp = lTemp + CLng(rsBldgInformation("COV_PROP").ToString())
lTemp = lTemp + CLng(iUnitLimit)
vITVInformation(7, iBldgCount) = FormatNumber(lTemp, 0) & "*"
sFlgUnitOwnerCvg = "*"
End If
End If
'Unit Owners Coverage Per Unit (05R401)
If sUnitOwnerCvgType = "05R401" Then
If iUnitOwnerCvgLimit > 0 Then
lNBR_OF_UNITS_TOT = rsBldgInformation("NBR_OF_UNITS_TOT")
lTemp = 0
lTemp = CLng(lNBR_OF_UNITS_TOT)
lTemp = lTemp * CLng(iUnitOwnerCvgLimit)
lTemp = lTemp + CLng(rsBldgInformation("COV_PROP").ToString())
vITVInformation(7, iBldgCount) = FormatNumber(lTemp, 0) & "*"
sFlgUnitOwnerCvg = "*"
End If
End If
End If
End If
rsBldgInformation.MoveNext()
Loop Until rsBldgInformation.EOF
Else
'LogIoError(IOMsg, rsBldgInformation)
GetData = "Error"
If Current.Session("DebugIO") = "Y" Then Stop
End If
rsBldgInformation = Nothing
End Function
Private Function IsThereAnyITVData(ByVal pLOC_NBR, ByVal pUOR_NBR)
SQLSelect = ""
SQLFrom = ""
SQLWhere = ""
SQLStatement = ""
IsThereAnyITVData = "False"
SQLSelect = "SELECT COUNT(*) "
SQLFrom = "FROM " & strQualifier & "TCOSW320 A, " & strQualifier & "TCOSW030 B "
SQLWhere = "WHERE " _
& "A.PART_NBR = '" & HttpContext.Current.Session("sesPart_Nbr") & "' " _
& "AND A.POLICY_NBR = '" & HttpContext.Current.Session("sesPolicy_Nbr") & "' " _
& "AND A.ITERATION = " & HttpContext.Current.Session("sesIteration") & " " _
& "AND A.LOC_NBR = " & pLOC_NBR & " " _
& "AND A.UOR_NBR = " & pUOR_NBR & " " _
& "AND A.LOC_TYP_CD = 'B' " _
& "AND A.UOR_TYP = 'B' " _
& "AND A.TEXT_ID = 'BLDG93' " _
& "AND A.PART_NBR = B.PART_NBR " _
& "AND A.POLICY_NBR = B.POLICY_NBR " _
& "AND A.ITERATION = B.ITERATION " _
& "AND A.TEXT_DATE1 BETWEEN B.POLICY_EFF_DT AND B.POLICY_EXP_DT"
Dim rsIsThereAnyITVData As New ADODB.Recordset()
On Error Resume Next
Dim Err = 0
SQLStatement = SQLSelect & " " & SQLFrom & " " & SQLWhere
rsIsThereAnyITVData.Open(SQLStatement, constring, 0, 3)
If Err = 0 Then
If CInt(rsIsThereAnyITVData(0).ToString()) > 0 Then
IsThereAnyITVData = "True"
End If
End If
rsIsThereAnyITVData = Nothing
End Function
Private Function GetITVData(ByVal pLOC_NBR, ByVal pUOR_NBR)
Dim iCount
SQLSelect = ""
SQLFrom = ""
SQLWhere = ""
SQLStatement = ""
iCount = 0
SQLSelect = "SELECT A.TEXT_ID, A.TEXT_DATA1, A.TEXT_VAL1, A.TEXT_DATE1 "
SQLFrom = "FROM " & strQualifier & "TCOSW320 A "
SQLWhere = "WHERE " _
& "A.PART_NBR = '" & HttpContext.Current.Session("sesPart_Nbr") & "' " _
& "AND A.POLICY_NBR = '" & HttpContext.Current.Session("sesPolicy_Nbr") & "' " _
& "AND A.ITERATION = " & HttpContext.Current.Session("sesIteration") & " " _
& "AND A.LOC_NBR = " & pLOC_NBR & " " _
& "AND A.UOR_NBR = " & pUOR_NBR & " " _
& "AND A.LOC_TYP_CD = 'B' " _
& "AND A.UOR_TYP = 'B' " _
& "AND A.TEXT_ID IN ('BLDG25','BLDG28','BLDG27','BLDG30','BLDG31') "
Dim rsITVData As New ADODB.Recordset()
On Error Resume Next
Dim Err = 0
SQLStatement = SQLSelect & " " & SQLFrom & " " & SQLWhere
rsITVData.Open(SQLStatement, constring, 0, 3)
If Err = 0 Then
Do While Not rsITVData.EOF
'BVSProcessDate
If Trim(rsITVData("TEXT_ID").ToString()) = "BLDG27" Then
sBVSDate = FormatDateTime(Trim(rsITVData("TEXT_DATE1").ToString()), 2)
If sBVSDate = "12/31/9999" Then sBVSDate = ""
iRetVal = "1"
End If
'BVSErrorDescription
If Trim(rsITVData("TEXT_ID").ToString()) = "BLDG25" Then
sBVSMsg = Trim(rsITVData("TEXT_DATA1").ToString())
iRetVal = "1"
End If
'BVSID
If Trim(rsITVData("TEXT_ID").ToString()) = "BLDG28" Then
sBVSID = Trim(rsITVData("TEXT_DATA1").ToString())
iRetVal = "1"
End If
'Actual Cash Value
If Trim(rsITVData("TEXT_ID").ToString()) = "BLDG30" Then
iITV_ACV = rsITVData("TEXT_VAL1")
iRetVal = "1"
End If
'Replacement Cost
If Trim(rsITVData("TEXT_ID").ToString()) = "BLDG31" Then
iITV_RC = rsITVData("TEXT_VAL1")
iRetVal = "1"
End If
rsITVData.MoveNext()
iCount = iCount + 1
Loop
Else
'LogIoError(IOMsg, rsITVData)
If HttpContext.Current.Session("DebugIO") = "Y" Then Stop
End If
rsITVData = Nothing
End Function
