HI BELOW IS A CODE I HAVE WRITTEN W

[pamo]

HI BELOW IS A CODE I HAVE WRITTEN WHICH DOES NOT SEEM TO BE WORKING. I CANNOT IDENTIFY ANY PROBLEMS WITH IT, CAN U !!!! CHEERS FOR ANY 1 WHO CAN

sorry i put in the whole code as i aint got a clue where the error is coz the thing is no error comes up when i run it but it doesnt update the table!!!!

PAMO

HERE IS THE CODE COPY AND PASTE IT INTO NOTEPAD OR SOMETHING:

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;2002&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;




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;

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, start_date, end_date, call_source, call_destination, queue_duration, ring_duration, call_duration, clerical_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_ddmmyyyy, start_hhmmss, end_ddmmyyyy, end_hhmmss)&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;


'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;




'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


'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;



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

 

[mwolf00]

You say &quot;create ms access db filename&quot;. Are you actually trying to create blank databases with this code? Or do the databases already exist? Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048
Tek-Tips Best Practices: FAQ183-3179
It's sad that some Americans proudly turn their backs on the very Flag the gives them the opportunity to do just that... - Mike

 

[pamo]

no what i am doing is i have a access database which stores the data of the calls made to a switchboard. This then needs to be transferred to a SQL database which is where i am moving the data too. SO to answer ur question the databases do already exist

thing is it runs fine and says succesfull but no updates occur!!

 

[mwolf00]

This code is pretty lengthy (the insert statement) - I recommend the you response.write it each time the loop iterates to see if the sql is correct. You can also tell how many times the loop executes (vs the EOF loop).

response.write &quot;<hr>&quot; & SQLinsert
ConnWrite.Execute(&quot;Insert into CallRecordData &quot; & SQLinsert)

I can't really debug w/o having the databases...
Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048
Tek-Tips Best Practices: FAQ183-3179
It's sad that some Americans proudly turn their backs on the very Flag the gives them the opportunity to do just that... - Mike

 

[BG12424]

Pamo, please visit FAQ FAQ333-3255 to learn how to debug your application. There is some useful information that might help you in this case and future ones. ________________________________________________________________________
Are you trying to debug your ASP applications? See faq333-3255 for more details

regards,
Brian