asked on

vb: date & time

I have two pattern that I created to parse but I can't store these strings in the database to do some comparison.
I have to create a date object using VB and I don't know how. I have an idea:
*******************************
Dim vbdatetime

vbdatetime = #dd-mm-yyyy hh:mi:ss#
***********************************
But I don't know how to use it.
Heres the pattern I use.
***************************************
Pattern4 = "The current time is: (\d+\:\d+:\d+)"
Pattern5 = "The current date is: (\w+\s\d+/\d+/\d+)"
****************************************
the output of this is:
time Date
10:30.40 Tues 3/28/2004

I know I have to get rid of the "Tues".
I don't know, can someone help me in the right direction to get going to create a date object on VB and send to my database? I don't use vb really.

appari

is it is in VB or VB.net?

can you post your existing code?

ASKER CERTIFIED SOLUTION

Pi7

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ryan Chong

Also, try apply the Format function to format your date value in the way you wanted, example:

msgbox format$(Now(), "dd/mm/yyyy")
msgbox format$(Now(), "dd mmm yyyy")
msgbox format$(Now(), "mm/ddyyyy")
msgbox format$(Now(), "yyy mmm dd")

cheers

mquiroz

try something like this:

dim yourdate as date
yourdate = dateserial (2004, 04, 15) 'This way you can't go wrong with date conflicts due to regional settings
msgbox format(year(yourdate),"0000") & "/" & format(month(yourdate),"00") & "/" & format(day(yourdate),"00") 'or whatever format you like...

imaw

ASKER

APPARI,
Here is the code and what I have so far with the date trying to load for the second table but I have error" 'loop' without a 'do' ".
************************************************************************************
'******************************************
'* Dim and const declarations *
'******************************************
Dim dict, key
Dim homedir, conn, rs, connstr, rsstr , vbdatetime
Dim rs2, rsstr2, total, strtotal'for the second table "license".
Dim fso, filelist, fileObj, tStream, tLine
Dim addrec, strtimestamp, strapplication, strmodule, strusers, strcomputername, strsequence, strcomments
Dim timestamp, application, module, users, computername, sequence, comments
Dim batchtime, batchdate
Dim WSHShell
Const adLockOptimistic =3, adOpenKeyset =1

Public Function AddSequence (users, computername, comments, timestamp, strapplication, strmodule, batchtime, batchdate, dict)
key = users + "||" + computername + "||" + comments + "||" + timestamp + "||" + strapplication + "||" + strmodule + "||" + batchtime + "||" + batchdate
'msgbox(key)
if dict.Exists(key) then
dict.Item(key) = dict.Item(key) + 1
else
dict.add key, 1
end if

'msgbox("For Key: " & key & vbcrlf & "Seq No: " & dict.Item(key))
AddSequence = dict.Item(key)
End Function

strtimestamp = ""
strapplication = ""
strmodule = ""

' ****************************
' ** Settings *
' ****************************
homedir = "F:\Program Files\enviflexlm\"
logfile = "envi.log"
connstr = "flexlm2"
rsstr = "Select * from flextable"
rsstr2 = "Select * from license"
folderspec =homedir & "\SOURCEDIR"

' *****************************************
' ** Declare VB Objects that will be used *
' *****************************************
Set fso = CreateObject("Scripting.FileSystemObject")
Set fileList = fso.GetFolder(homedir)
Set oRE = CreateObject("VBScript.RegExp")
' =========================================
' Instantiate Dictionary Object
' =========================================
set dict = CreateObject("Scripting.Dictionary")

'Open connection to database
set conn = createobject("adodb.connection")
conn.open connstr

'Open recordset to add data
set rs = createobject("adodb.recordset")
rs.Open rsstr, conn, adOpenKeyset, adLockOptimistic

'Open recordset to the license table to add data
set rs2 = createobject("adodb.recordset")
rs2.Open rsstr2, conn, adOpenKeyset, adLockOptimistic

'**********************************************
'* script to execute the dump3.bat file *
'**********************************************
'testing mode

set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.run "enviusage.bat"

' ***************************************
' * Search through the directory and
' * find the "flexlm2.log" file. Read the "flexlm2.log"
' * and parse the information we want.
' ***************************************
For Each fileObj In fileList.files

' ****************************
' * Find the flexlm2.log file
' ****************************
If StrComp(fileObj.name, logfile, 1) = 0 Then

'*****************************************************
'* Now that test.log file has been found *
'* Open the file so that we can read the contents. *
'*****************************************************
set tStream = fileObj.OpenAsTextStream(1,0)
addrec = FALSE
timestamp = "ERR"
application = "ERR"
module = "ERR"
users = "ERR"
computername = "ERR"
sequence = "ERR"
comments = "unimplemented"
batchtime = "unimplemented"

Do

'********************************************
'* Read a single line of the test.log file *
'********************************************
tLine = tStream.ReadLine
' msgbox(tLine)
sStr = ""
' *********************************************
' * Set the parameters to our RegExp object *
' *********************************************
oRE.Global = False
oRE.IgnoreCase = False

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Example line that needs to be parsed out is below: '
' '
' PATTERN1 '
' ESRI: UP v7.0 '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' PATTERN2 '
' "Grid" v8.01, vendor: ESRI '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' PATTERN3 '
' dchittenden STI-000659 (FW?F=HN[d1oG#g_9x_/{a (v8.01) (molokini/27005 207), start Mon 2/9 8:38 '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' PATTERN4 '
' The current time is: 10:25:51.38 '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' PATTERN5 '
' The current date is: Tue 03/23/2004 '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Pattern1 = "^\s+(\w+\:)\s(\w+)\s(\w\d\.\d)"
Pattern2 = "^\s\s\""(\w+)\""\s([\w\d\.]+)\,\svendor:\s(\w+)"
Pattern3 = "\s\s\s\s(\w+)\s([\w-]+)\s(.*), start\s(\w+\s\d+\/\d+\s\d+\:\d+)"
'Pattern4 = "The current time is: (\d+\:\d+:\d+.\d+)"
Pattern4 = "The current time is: (\d+\:\d+:\d+)"
Pattern5 = "The current date is: (\w+\s\d+/\d+/\d+)"

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' PATTERN_A for second table "license" ' '
' Users of envi: (Total of 4 licenses available) '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Pattern_A = "Users of (\w+): $\w+\s\w+\s(\d)\s\w+\s\w+$"

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Pattern_combo '
' The current date is: Wed 04/14/2004 '
' The current time is: 14:10:28.96 '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Pattern_combo = "The current date is:\s\w+\s(\d+)/(\d+)/(\d+) The current time is:\s(\d+):(\d+):(\d+).\d+"

' ******************************************
' * Parse the current line that we read in *
' ******************************************
'Set oMatches = oRE.execute(tLine)

' **************************************************
' * If we get a hit (the line matches our RegExp) *
' * Then find all the matches and display them *
' **************************************************

oRE.Pattern=Pattern1
if oRE.test(tLine) Then
Set oMatches = oRE.execute(tLine)
For each oMatch in oMatches
sStr = "Application: " & oMatch.SubMatches(0) & vbcrlf
sStr = sStr & "Type: " & oMatch.SubMatches(1) & vbcrlf
sStr = sStr & "Version: " & oMatch.SubMatches(2) & vbcrlf

'Display the message box
'1' msgbox("Line Read:" & tLine & vbcrlf & "Parsed fields:" & vbcrlf & sStr)
strapplication = oMatch.SubMatches(0) + ","+ oMatch.SubMatches(1) + "," + oMatch.Submatches(2)

Next

Else
oRE.Pattern=Pattern2
if oRE.test(tLine) Then
Set oMatches = oRE.execute(tLine)
For each oMatch in oMatches
sStr = "Module: " & oMatch.SubMatches(0) & vbcrlf
sStr = sStr & "Version: " & oMatch.SubMatches(1) & vbcrlf
sStr = sStr & "Vendor: " & oMatch.SubMatches(2) & vbcrlf

'Display the message box
'1' msgbox("Line Read:" & tLine & vbcrlf & "Parsed fields:" & vbcrlf & sStr)
msgbox("strmodule before: " & strmodule)
strmodule = oMatch.SubMatches(0) + ","+ oMatch.SubMatches(1) + "," + oMatch.Submatches(2)
msgbox("strmodule after: " & strmodule)
Next

Else
oRE.Pattern=Pattern3
if oRE.test(tLine) Then
Set oMatches = oRE.execute(tLine)
For each oMatch in oMatches
sStr = "Username: " & oMatch.SubMatches(0) & vbcrlf
sStr = sStr & "Computername: " & oMatch.SubMatches(1) & vbcrlf
sStr = sStr & "Comments: " & oMatch.SubMatches(2) & vbcrlf
sStr = sStr & "Time start: " & oMatch.SubMatches(3) & vbcrlf

'Display the message box
'1' msgbox("Line Read:" & tLine & vbcrlf & "Parsed fields:" & vbcrlf & sStr)

      users = oMatch.SubMatches(0)
      computername = oMatch.SubMatches(1)
comments = oMatch.SubMatches(2)
timestamp = oMatch.SubMatches(3)

      Next

'msgbox("LOOK HERE!: " & batchtime)
'if necssary data found, add record to database
'if addrec Then
rs. Addnew
rs("timestamp") = timestamp
rs("application") = strapplication
rs("module") = strmodule
rs("users") = users
rs("computername") = computername
rs("sequence") = seq
rs("comments") = comments
rs("batchtime") = batchtime
rs("batchdate") = batchdate
rs.Update
addrec = FALSE

Else
oRE.Pattern=Pattern4
if oRE.test(tLine) Then
Set oMatches = oRE.execute(tLine)
For each oMatch in oMatches
       batchtime = oMatch.SubMatches(0)
       Next

Else
oRE.Pattern=Pattern5
if oRE.test(tLine) Then
Set oMatches = oRE.execute(tLine)
For each oMatch in oMatches
sStr = "batchdate: " & oMatch.SubMatches(0) & vbcrlf

'Display the message box
'1' msgbox("Line Read:" & tLine & vbcrlf & "Parsed fields:" & vbcrlf & sStr)

      batchdate = oMatch.SubMatches(0)

      Next

      Else
oRE.Pattern=Pattern_A
if oRE.test(tLine) Then
Set oMatches = oRE.execute(tLine)
' msgbox("pattern_a=:" & Pattern_A)
' msgbox("tLine=: " & tLine)
For each oMatch in oMatches

      aModules = oMatch.SubMatches(0)
      total = oMatch.SubMatches(1)

       msgbox("PATTERN A:" & strapplication & "      " & aModules & "      " & total & "      " & batchtime & "      " & batchdate)
      Next



      Else
oRE.Pattern=Pattern_combo
if oRE.test(tLine) Then
Set oMatches = oRE.execute(tLine)

For each oMatch in oMatches
                        lvMonth = oMatch.SubMatches(0)
                        lvDay = oMatch.SubMatches(1)
                        lvYear = oMatch.SubMatches(2)
                        lvHour = oMatch.SubMatches(3)
                        lvMinute = oMatch.SubMatches(4)
                        lvSecond = oMatch.SubMatches(5)

                        lvDatestr = lvMonth & "-" & lvDay & "-" & lvYear & " " & lvHour & ":" & lvMinute & ":" & lvSecond
                        msgbox(lvDatestr)
                   Next

                   'msgbox("LOOK HERE 4 data into license!: " & batchtime)
                   'if necssary data found, add record to database'
                   if addrec Then
                        rs2. Addnew
                        rs2("application") = strapplication
                        rs2("module") = aModules
                        rs2("total_free") = total
                        rs2("batchtime") = batchtime
                        rs2("batchdate") = batchdate
                        rs2("dt") = now
                        rs2("batchdate") = batchdate
                        rs2.Update
                        addrec = FALSE

                    seq= AddSequence (users, computername, comments, timestamp, strapplication, strmodule, batchtime, batchdate, dict)

       End if

              End If
'Next
End if
End if
End if
End if
End if

Loop While Not (tStream.AtEndOfStream = TRUE)
tStream.close
End if
Next

rs.close
rs2.close
conn.close

set rs = nothing
set rs2 = nothing
set conn= nothing
set fso = nothing
set fileObj = nothing
set tStream = nothing
************************************************************************************
thanks.
-t

Sachin Parab

You have a nice code written.....But If you are getting error 'loop' without a 'do' ".

You better close your Loop at the end of your program.

appari

one End If is missing

before your "Loop While Not (tStream.AtEndOfStream = TRUE)" add one line "End if"

Pi7

Thanx imaw

vb: date &amp; time

vb: date & time