Vb6连接Oracle的不同方法 + 计算日期在第几周

发布时间：2020-12-16 22:21:18 所属栏目：大数据来源：网络整理

导读：Private Sub showNow_Click() GetNow End Sub Private Sub GetNow() Dim Conn As New ADODB.Connection Dim rs As New ADODB.Recordset Conn.ConnectionString = "Driver={Microsoft ODBC for Oracle};Server=SvrName;UID=UserID;PWD=Password;" Conn.Open Di

Private Sub showNow_Click()
GetNow
End Sub

Private Sub GetNow()
Dim Conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Conn.ConnectionString = "Driver={Microsoft ODBC for Oracle};Server=SvrName;UID=UserID;PWD=Password;"
Conn.Open
Dim strSQL As String
strSQL = "select sysdate from dual"
rs.Open strSQL,Conn,1,1
CurrTime.Text = rs(0)
rs.Close
Conn.Close
End Sub

Private Sub showWeekSeq_Click()
Dim strNow As String
strNow = Trim(CurrTime.Text)
If strNow = "" Then strNow = CStr(Now)
CurrTime.Text = GetWeekSeq(strNow)
End Sub

'计算具体日期在一年中的第几周
Private Function GetWeekSeq(strStartTime As String) As String
Dim adoConn As ADODB.Connection
Dim adoFirstDay As ADODB.Recordset
Dim strSQL As String
Dim dStart As Date
Dim dFirstDay As Date
Dim nWeek As Integer
Dim nYear As Integer
Dim strTempNo As String

OracleConnect adoConn
If adoConn Is Nothing Then Exit Function

dStart = Format(strStartTime,"MM/dd/yyyy")
nYear = Year(DateAdd("d",7,dStart))
strSQL = "SELECT next_day(to_date('01/01/" & nYear & "','mm/dd/yyyy'),1) -7 SUNDAY " _
& " FROM DUAL"
FillRS adoFirstDay,strSQL,adoConn
dFirstDay = Format(adoFirstDay(0),"MM/dd/yyyy")

If DateDiff("d",dFirstDay,dStart) < 0 Then
nYear = Year(dStart)
strSQL = "SELECT next_day(to_date('01/01/" & nYear & "',1) -7 SUNDAY " _
& " FROM DUAL"
FillRS adoFirstDay,adoConn
dFirstDay = Format(adoFirstDay(0),"MM/dd/yyyy")
End If

nWeek = CInt(DateDiff("w",dStart) + 1)
strTempNo = nYear & "_" & IIf(nWeek < 10,"0" & nWeek,nWeek)
Set adoFirstDay = Nothing
Set adoConn = Nothing
GetWeekSeq = strTempNo
End Function

'-----------------------------------------
'连接Oracle数据库的不同方法，传参
'-----------------------------------------
Public Sub OracleConnect(inConn As ADODB.Connection)
On Error GoTo Errlbl
Set inConn = Nothing
Set inConn = New ADODB.Connection

If inConn.State = 1 Then Exit Sub
sConString = "Driver={Microsoft ODBC for Oracle};Server=SvrName;UID=UserID;PWD=Password;"
' sConString = "Provider=MSDAORA.1;Data Source=SvrName;User ID=UserID;Password=Password;"
'使用Oracle驱动的速度要快一点
' sConString = "Provider=OraOLEDB.Oracle;Data Source=SvrName;User ID=UserID;Password=Password;"
' sConString = "Provider=OraOLEDB.Oracle.1;Data Source=SvrName;User ID=UserID;Password=Password;"
With inConn
.Open sConString
.CursorLocation = adUseClient
.CommandTimeout = 30
End With
Exit Sub

Errlbl:
Err.Clear
Set inConn = Nothing
End Sub

Public Sub FillRS(inRs As ADODB.Recordset,inSQL As String,inConn As ADODB.Connection)
On Error GoTo ErrContinue
Set inRs = Nothing
Set inRs = New ADODB.Recordset

inRs.CursorLocation = adUseClient
inRs.Open inSQL,inConn,adOpenDynamic,adLockReadOnly
Exit Sub
ErrContinue:
Err.Clear
Set inRs = Nothing

End Sub

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!