vb6连接oracle 查询,Vb6连接Oracle的不同方法 + 计算日期在第几周

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 & "','mm/dd/yyyy'), 1) -7 SUNDAY " _

& " FROM DUAL"

FillRS adoFirstDay, strSQL, adoConn

dFirstDay = Format(adoFirstDay(0), "MM/dd/yyyy")

End If

nWeek = CInt(DateDiff("w", dFirstDay, 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