CStr function problemo

[pamo]

I have a table which has imported data thru the use of a vbscript. I have a time field for logon_time and logoff_time. The problem is that a time for eg: 07:09:50 being 7am 9 min and 50 sec is being truncated to 7:9:50. so all leading zeros are removed. I HAVE BEEN TOLD THAT CStr function will resolve this but i am not sure how

 

[BDC2]

For display purposes you will need to convert to a string, break down the units and add a leading zero where appropriate.

logon_time = CStr(logon_time)
hrs = Left(logon_time,InStr(logon_time,":&quot-1)
mins = Left(logon_time,InStrRev(logon_time,":&quot-1)
mins = Right(mins,len(mins)-InStr(mins,":&quot)
secs = Right(logon_time,len(logon_time)-InStrRev(logon_time,":&quot)
If len(hrs)<2 then hrs = &quot;0&quot; & hrs
If len(mins)<2 then mins = &quot;0&quot; & mins
If len(secs)<2 then secs = &quot;0&quot; & secs
logon_time = hrs&&quot;:&quot;&mins&&quot;:&quot;&secs

your variable &quot;logon_time&quot; will now be a string and will not be available for time calculations.

BDC.

 

[pamo]

will this work within my vbscript as i need this to happen for every record set inserted and bearing in mind i have 2 million records to pull across i dont want 2 have to do this 2million times!! so will i be able to put it into my script and allow it to loop like the script is already looping ???

 

[BDC2]

This should work, but I don't know what your vbscript is so I can't guarantee anything.

What exactly are you trying to do? You have 2M records in a database (what type?) and your trying to &quot;pull them across&quot;?

The script converts a time to a string, If your putting the string back into a database it might be better to keep it as a time.

BDC.

 

[pamo]

i am copying data from an access table into a sql table. i am using a vbscript to transfer the data across, here is a copy of the script i am using

************************************************************************************************************************
dim varLOOP
dim Sqlstring
dim SqlInsert
dim Conn
dim Rs
dim ConnWrite
dim objcmd
dim strconn
dim LpCount
dim var_day
dim var_month
dim var_year
dim var_logon_date
dim var_logoff_date

on error resume next

'**********************************************************************************************************************
'set variable
varLOOP = 1

'start loop for month value
Do until varLOOP =13
'**********************************************************************************************************************
'msgbox varLOOP

'build connection string to required DB
'work out month from loop counter
DBMonth = varLOOP

'if month is less than 10 then add a zero
if DBMonth < 10 then
DBMonth = &quot;0&quot; & DBMonth
end if

DBYear = &quot;2003&quot;

'create ms access db filename
accessDB = &quot;histcrec&quot; & DBMonth & DBYear & &quot;.mdb&quot;

strconn=&quot;PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=&quot;
strconn=strconn & &quot;Q:\rtarchdata\&quot; & accessDB & &quot;;&quot;

'msgbox accessDB

'Build SQLstring

sqlstring = &quot;select * from callrecorddata&quot;

'inputbox &quot;test&quot;, &quot;test&quot;, &quot;select from callrecorddata complete&quot;


set Conn = createobject(&quot;adodb.connection&quot
set Rs = createobject(&quot;adodb.recordset&quot
Conn.open = strconn
Rs.open SqlString, Conn
LpCount = 0

err.clear

if Rs.eof then

set ConnWrite = createobject(&quot;adodb.connection&quot

ConnWrite.open = &quot;DSN=CallScan&quot;

Sqlinsert = SqlInsert & &quot;insert into callrecorddataerr (agent_id)&quot;
Sqlinsert = SqlInsert & &quot;values ('0')&quot;
'inputbox &quot;test&quot;, &quot;test&quot;, &quot;insert into callrecorddataerr&quot;
ConnWrite.Execute(SQLinsert)

else:

set ConnWrite = createobject(&quot;adodb.connection&quot
ConnWrite.open = &quot;DSN=CallScan&quot;


do until Rs.eof

'Build a SQL insert string
SqlInsert = &quot;&quot;
Sqlinsert = SqlInsert & &quot;(call_id, V_start_date, V_end_date, call_source, call_destination, queue_duration, ring_duration, call_duration, Writeup_duration, enquiry_duration, source_hold_duration, dest_hold_duration, park_duration, call_type, call_origin_type, call_termination_type, agent_id, agent_group, src_agent_id, queue_id, call_info, tli_oli_digits, &quot;

Sqlinsert = SqlInsert & &quot;start_date, start_time, end_date, end_time)&quot;

Sqlinsert = SqlInsert & &quot; values ('&quot; & rs(&quot;call_id&quot & &quot;', '&quot; & rs(&quot;start_date&quot & &quot;', '&quot;
Sqlinsert = SqlInsert & rs(&quot;end_date&quot & &quot;', '&quot; & rs(&quot;call_source&quot & &quot;', '&quot; & rs(&quot;call_destination&quot & &quot;', '&quot;
Sqlinsert = SqlInsert & rs(&quot;queue_duration&quot & &quot;', '&quot; & rs(&quot;ring_duration&quot & &quot;', '&quot; & rs(&quot;call_duration&quot & &quot;', '&quot;
Sqlinsert = SqlInsert & rs(&quot;clerical_duration&quot & &quot;', '&quot; & rs(&quot;enquiry_duration&quot & &quot;', '&quot; & rs(&quot;source_hold_duration&quot & &quot;', '&quot;
Sqlinsert = SqlInsert & rs(&quot;dest_hold_duration&quot & &quot;', '&quot; & rs(&quot;park_duration&quot & &quot;', '&quot; & rs(&quot;call_type&quot & &quot;', '&quot;
Sqlinsert = SqlInsert & rs(&quot;call_origin_type&quot & &quot;', '&quot; & rs(&quot;call_termination_type&quot & &quot;', '&quot; & rs(&quot;agent_id&quot & &quot;', '&quot;
Sqlinsert = SqlInsert & rs(&quot;agent_group&quot & &quot;', '&quot; & rs(&quot;src_agent_id&quot & &quot;', '&quot; & rs(&quot;queue_id&quot & &quot;', '&quot;
Sqlinsert = SqlInsert & rs(&quot;call_info&quot & &quot;', '&quot; & rs(&quot;tli_oli_digits&quot & &quot;', &quot;
'inputbox &quot;test&quot;, &quot;test&quot;, &quot;insert complete&quot;

'build logon date
var_day = datepart (&quot;d&quot;, rs(&quot;start_date&quot)
var_month = datepart (&quot;m&quot;, rs(&quot;start_date&quot)
var_year = datepart (&quot;yyyy&quot;, rs(&quot;start_date&quot)

Var_month = monthname(var_month)

var_start_date = var_day & &quot; &quot; & var_month & &quot; &quot; & var_year


'logon date

sqlinsert = sqlinsert & &quot;'&quot; & var_start_date & &quot;', &quot;

'logon date
sqlinsert = sqlinsert & &quot;'&quot; & datepart(&quot;h&quot;, rs(&quot;start_date&quot) & &quot;:&quot; & datepart(&quot;n&quot;, rs(&quot;start_date&quot) & &quot;:&quot; & datepart(&quot;s&quot;, rs(&quot;start_date&quot) & &quot;', &quot;

'inputbox &quot;test&quot;, &quot;test&quot;, &quot;logon date built&quot;


'build logoff date
var_day = datepart (&quot;d&quot;, rs(&quot;end_date&quot)
var_month = datepart (&quot;m&quot;, rs(&quot;end_date&quot)
var_year = datepart (&quot;yyyy&quot;, rs(&quot;end_date&quot)

var_month = monthname(var_month)


var_end_date = var_day & &quot; &quot; & var_month & &quot; &quot; & var_year
'inputbox &quot;test&quot;, &quot;test&quot;, &quot;logon date built&quot;

'end date
Sqlinsert = SqlInsert & &quot;'&quot; & var_end_date & &quot;', &quot;
'end time
Sqlinsert = SqlInsert & &quot;'&quot; & datepart(&quot;h&quot;, rs(&quot;end_date&quot) & &quot;:&quot; & datepart(&quot;n&quot;, rs(&quot;end_date&quot) & &quot;:&quot; & datepart(&quot;s&quot;, rs(&quot;end_date&quot)& &quot;')&quot;

'inputbox &quot;test&quot;, &quot;test&quot;, &quot;Insert into CallRecordData &quot; & SQLinsert

ConnWrite.Execute(&quot;Insert into CallRecordData &quot; & SQLinsert)

if err.number <> 0 then

ConnWrite.Execute(&quot;Insert into CallRecordDataErr &quot; & SQLinsert)
err.clear
end if
LPCount = LPCount + 1
Rs.movenext
loop
ConnWrite.close
set ConnWrite = nothing
end if
Rs.close
Conn.close
set Rs = nothing
set Conn = nothing


'**********************************************************************************************************************
'increment by one
varLOOP = varLOOP + 1

'end main loop
Loop
'**********************************************************************************************************************
'destroy connections
set Rs = nothing
set Conn = nothing

 

[BDC2]

The script I provided should be ok as long as the SQL fields for logon_time and logoff_time are strings. From your code, it looks like your doing a similar kind of thing already for the dates by creating strings from the DatePart.

BDC.