PDA

Click to See Complete Forum and Search --> : conversion to ADO.Net

airforce1
June 27th, 2011, 11:18 PM
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
airforce1
June 27th, 2011, 11:23 PM
code cont...


Private Function GetUnitOwnerCvg(ByVal pLOC_NBR)

SQLSelect = ""
SQLFrom = ""
SQLWhere = ""
SQLStatement = ""
iUnitOwnerCvgLimit = 0
sUnitOwnerCvgType = ""

Dim rsUnitOwnerCvg As New ADODB.Recordset()


SQLSelect = "SELECT COV_CD, COV_VAR_3 "
SQLFrom = "FROM " & strQualifier & "TCOSW340 "
SQLWhere = "WHERE " _
& "PART_NBR = '" & HttpContext.Current.Session("sesPart_Nbr") & "' " _
& "AND POLICY_NBR = '" & HttpContext.Current.Session("sesPolicy_Nbr") & "' " _
& "AND ITERATION = " & HttpContext.Current.Session("sesIteration") & " " _
& "AND LOC_NBR = " & pLOC_NBR & " " _
& "AND UOR_NBR = 0 " _
& "AND LOC_TYP_CD = 'B' " _
& "AND UOR_TYP = 'L' " _
& "AND COV_CD in ('05R400', '05R401', '05R402')"

On Error Resume Next

Dim Err = 0
SQLStatement = SQLSelect & " " & SQLFrom & " " & SQLWhere
rsUnitOwnerCvg.Open(SQLStatement, constring, 0, 3)

If Err = 0 Then
If Not rsUnitOwnerCvg.EOF Then
iUnitOwnerCvgLimit = rsUnitOwnerCvg("COV_VAR_3")
sUnitOwnerCvgType = rsUnitOwnerCvg("COV_CD")
End If
Else
'LogIoError(IOMsg, rsUnitOwnerCvg)
If HttpContext.Current.Session("DebugIO") = "Y" Then Stop
End If

rsUnitOwnerCvg = Nothing

End Function

Private Sub GetUnitLimit(ByVal pLOC_NBR, ByVal pUOR_NBR, ByVal psUnitCvgType)

SQLSelect = ""
SQLFrom = ""
SQLWhere = ""
SQLStatement = ""
iUnitLimit = 0

Dim rsUnitLimit As New ADODB.Recordset()

SQLSelect = "SELECT COV_VAR_3 "
SQLFrom = "FROM " & strQualifier & "TCOSW340 "
SQLWhere = "WHERE " _
& "PART_NBR = '" & HttpContext.Current.Session("sesPart_Nbr") & "' " _
& "AND POLICY_NBR = '" & HttpContext.Current.Session("sesPolicy_Nbr") & "' " _
& "AND ITERATION = " & HttpContext.Current.Session("sesIteration") & " " _
& "AND LOC_NBR = " & pLOC_NBR & " " _
& "AND UOR_NBR = " & pUOR_NBR & " " _
& "AND LOC_TYP_CD = 'B' " _
& "AND UOR_TYP = 'B' " _
& "AND COV_CD = '" & psUnitCvgType & "'"

On Error Resume Next
Dim err = 0

SQLStatement = SQLSelect & " " & SQLFrom & " " & SQLWhere
rsUnitLimit.Open(SQLStatement, constring, 0, 3)

If err = 0 Then
If Not rsUnitLimit.EOF Then
iUnitLimit = rsUnitLimit("COV_VAR_3")
End If
Else
'LogIoError(IOMsg, rsUnitLimit)
If HttpContext.Current.Session("DebugIO") = "Y" Then Stop
End If

rsUnitLimit = Nothing

End Sub

End Class
HanneSThEGreaT
July 26th, 2011, 03:13 PM
Please use CODE tags when posting code!