I have the following
vb script which gives me error:
"command text was not set for the command object". Does anybody know what is wrong?
Dim OdbcDSN, connect, sql, resultSet
Dim OdbcDSN_target, connect_target, sql_target, sql_init, resultSet_target
Dim fso
Dim FileExcel
Dim ExcelPathFileName
OdbcDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= c:\test.xml;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
OdbcDSN_target = "DSN=test;UID=test;PWD=test"
On Error Resume Next
Set connect = CreateObject("ADODB.Connection")
Set connect_target = CreateObject("ADODB.Connection")
connect.Open OdbcDSN
connect_target.Open OdbcDSN_target
sql_init="delete from TBL_CHANGES_ORGANISATIES"
Call ErrorHandling()
sql="Select * From [CHANGE_MANAGEMENT$]"
'msgBox sql
connect_target.execute sql_init
Set resultSet = connect.Execute(sql)
resultSet.MoveFirst
Do While Not resultSet.eof
sql_target = "INSERT INTO TBL_CHANGES_ORGANISATIES(CHANGE_MANAGEMENT_ID, SUBMITTER, CREATE_DATE, RESPONSIBLE_PERSON, LAST_MODIFIED_BY, STATUS, CATEGORY, ROW_LEVEL_ACCESS, START_TIME, SLA_END_TIME, GROUP_NOTES, STATUS_CHAR, SERVICE, TIME_CALL, ACTUAL_CHANGE_TIME, PARENT_TASK_ID, TASK_ID, TASK_DESCRIPTION, TASK_STATUS, TASK_TYPE, IMPACT, PRIORITY, COMMITMENT, URGENCY, SLA_EINDTIJD_KLANT, CHANGE_ID, CI, ACTUAL_START_TIME, SHOW_CI, SHOW_IMPACT, SHOW_PRIORITY, SHOW_COMMITMENT, SHOW_SERVICE, SHOW_SERVICE_PROCESS, SHOW_URGENCY, CONTRACT_SETUP_ID, ACTIVE, MASTER_TASK_ID, PROGRESS_NOTES_CHANGE, HOLIDAY_TAG, WORKDAY_TAG, RESOLVE_TIME__MIN_, RESPONSIBLE_GROUP, CALL_ID, OWNER_GROUP, KLANT_REFERENTIE_NUMMER2, PROCESS_CODE, SERVICE_PROCESS, EXTERN_REFERENCE_NUMMER, ACTUAL_END_TIME, CLOSURE_DESCRIPTION, HNC_CLOSURE_CATEGORY, CUSTOMER_SATISFACTION, CHANGE_DESCRIPTION, PERSON_ID, CATEGORYID, HNC_CLOSURE_CATEGORY_ID, ORGANISATION_ID, LOCATION_ID, SPENT_TIME, CLOSURE_CATEGORY, TASKCLOSURECATEGORYID, MAIN_AFFECTED_CI_NAME, MAIN_AFFECTED_CI_TYPE, MAIN_AFFECTED_CI_ITEM_ID, UNITS, PHASE_CODE, PHASE, STATUS_CODE, TASK_PROCESS_CODE, CLOSURE_CATEGORY_LEVEL_1, CHANGE_REASON, CHANGE_TYPE, PAUSE_TOTAL, PRIORITY2, BEGIN_PLAN_DATE, END_PLAN_DATE, EVALUATION, MAIN_AFFECTED_CI_ITEM_TYPE_ID, PERSON_, ORGANISATION, CONTACT_METHOD, LOCATION, CONTACT_DETAILS, AR_SUPRESS_, AR_ESC_SUPRESS_, TASK_COLOR_LIST, MATRIX, CIGROUP, OPEN_TASKS, MOD_DATE, EXTERN_NUMMER, KLANT_REFERENTIE_NUMMER, PARAMETER, PERSONNEL_NO_, MELDER, METHODE, KVICURRENTROW, KVICOUNTROWS, CLOSURE_CATEGORY_LEVEL_2, CLOSURE_CATEGORY_LEVEL_3, CLOSURE_CATEGORY_LEVEL_4, CATEGORIE_LEVEL_1, CATEGORIE_LEVEL_2, CATEGORIE_LEVEL_3, CATEGORIE_LEVEL_4, ORGANIZATION_PARENT, ONTVANGER_ID_NEW_PARENT, NETTO_TEKST, WENSDATUM, EXTERNE_ORGANISATIE, ONTVANGERID_NEW, EXTERN_ERROR_STATUS, TASK_UPDATE, EXTERN_ERROR, RESPONSE_END_TIME, TEMP_DESCRIPTION, TEMP_TASK_DESCRIPTION, BEGIN_PLAN_DATE2, END_PLAN_DATE2, KVI_KORTE_OMSCHRIJVING, KVI_DATUM_TIJD, B2B_REFERENCE_NR, B2B_REQUEST_ID, B2B_REQUEST_, B2B_ORIGINATION_DOMAIN, PAUSE_TEXT, PAUSE_REASON, CKRNUMMER, KLANT_TEVREDENHEID_CODE, REDEN_ONTEVREDEN, ORGANISATION_ID_1, SUBMITTER_1, CREATE_DATE_1, ASSIGNED_TO, LAST_MODIFIED_BY_1, MODIFIED_DATE, STATUS_1, SHORT_DESCRIPTION, ROW_LEVEL_ACCESS_1, ORGANISATION_PARENT, ORGANISATION_PARENT_ID, ORGANISATION_TYPE, BUSINESS_GROUP_ID_INT_, ORGANISATION_NAME, CONTACT_PERSON, TYPE_ID, DELETED__, ANALIST_CODE, REFERENCE_NO_, LABEL, PREFERRED, MOBILE_PHONE, E_MAIL, TELEPHONE, FAX, OTHER, CREATE_WEBCALL, MAINORGANISATIONADRESS, MAINORGANISATIONZIPCODE, MAINORGANISATIONCITY, MAINORGANISATIONSTATE, MAINORGANISATIONCOUNTRY, MM_ONDERZOEK_AFDELING, MM_ONDERZOEK_NR, MAIN_ORGANISATION_ADDRESS_ID, BIJZONDERHEDEN, AANSTUREN_VIA_INTERFACE, CHILD_IS_LOCATIE, ORG_NAAM, AFMELD_TEKST, DELETETOOL_OVERRIDE, TELEFOON, FAX2, E_MAIL2, ORGANISATIENAAM, WEB_ACCOUNT_ID_USER_REQUESTID_, PINCODE_PLAATS, SIMPLIFIED_FORMS, SOURCE, OLD_NAME, HUISNUMMER, TOEVOEGING, PINCODE, VT_KL_ID, VT_ID, VT_SPECIAL_INSTR, VT_TOEGANG_BIJZONDERHEDEN, VT_TMS_BIJZONDERHEDEN, VT_NETWERKOVERZICHT, VT_CKRNR, ORGANISATIE_MENU, VT_CBS_BIJZONDERHEDEN, VT_IGO_BIJZONDERHEDEN, BIJLAGE1, BIJLAGE2, VT_TMS_JN, VT_TMS_PINCODE, VT_OPENINGSTIJDEN_WD_BEGIN, VT_OPENINGSTIJDEN_WD_EIND, VT_OPENINGSTIJDEN_ZA_BEGIN, VERANTWOORDELIJKE_GROEP, VT_OPENINGSTIJDEN_ZA_EIND, VT_OPENINGSTIJDEN_ZO_BEGIN, VT_OPENINGSTIJDEN_ZO_EIND, VT_CITYRING_JN, VT_GRIP_JN, VT_GRIPNET_JN, VT_IGO_VERZENDING, VT_LOCALCONNECT_JN, LEVEL_1, LEVEL_2, LEVEL_3, LEVEL_1_ID, LEVEL_2_ID, LEVEL_3_ID, LEVEL_4, LEVEL_4_ID, LEVEL_1_ORG, LEVEL_2_ORG, LEVEL_3_ORG, LEVEL_4_ORG, SQL_UPDATE_NAME, SQL_UPDATE__OLD__NAME, SQL_UPDATE_MENU_NAME, SQL_UPDATE_ORG_NAME, VERANTWOORDELIJK, VERANTWOORDELIJK_ID, TOON_OP_MELDING_, USE_ROW_LEVEL_ACCESS_, GROUP_ID__CHAR_, BOX_GROUP_ID__CHAR_, BOX, BIJLAGE3, BIJLAGE4, BIJLAGE5, BIJLAGE6) VALUES ('" & resultSet("CHANGE_MANAGEMENT_ID") & "', '" & resultSet("SUBMITTER") & "', '" & resultSet("CREATE_DATE") & "', '" & resultSet("RESPONSIBLE_PERSON") & "', '" & resultSet("LAST_MODIFIED_BY") & "', '" & resultSet("STATUS") & "', '" & resultSet("CATEGORY") & "', '" & resultSet("ROW_LEVEL_ACCESS") & "', '" & resultSet("START_TIME") & "', '" & resultSet("SLA_END_TIME") & "', '" & resultSet("GROUP_NOTES") & "', '" & resultSet("STATUS_CHAR") & "', '" & resultSet("SERVICE") & "' , '" & resultSet("TIME_CALL") & "', '" & resultSet("ACTUAL_CHANGE_TIME") & "', '" & resultSet("PARENT_TASK_ID") & "', '" & resultSet("TASK_ID") & "', '" & resultSet("TASK_DESCRIPTION") & "', '" & resultSet("TASK_STATUS") & "', '" & resultSet("TASK_TYPE") & "', '" & resultSet("IMPACT") & "', '" & resultSet("PRIORITY") & "','" & resultSet("COMMITMENT") & "', '" & resultSet("URGENCY") & "', '" & resultSet("SLA_EINDTIJD_KLANT") & "', '" & resultSet("CHANGE_ID") & "', '" & resultSet("CI") & "', '" & resultSet("ACTUAL_START_TIME") & "', '" & resultSet("SHOW_CI") & "', '" & resultSet("SHOW_IMPACT") & "', '" & resultSet("SHOW_PRIORITY") & "', '" & resultSet("SHOW_COMMITMENT") & "','" & resultSet("SHOW_SERVICE") & "','" & resultSet("SHOW_SERVICE_PROCESS") & "','" & resultSet("SHOW_URGENCY") & "','" & resultSet("CONTRACT_SETUP_ID") & "','" & resultSet("ACTIVE") & "','" & resultSet("MASTER_TASK_ID") & "','" & resultSet("PROGRESS_NOTES_CHANGE") & "','" & resultSet("HOLIDAY_TAG") & "','" & resultSet("WORKDAY_TAG") & "','" & resultSet("RESOLVE_TIME__MIN_") & "','" & resultSet("RESPONSIBLE_GROUP") & "','" & resultSet("CALL_ID") & "','" & resultSet("OWNER_GROUP") & "','" & resultSet("KLANT_REFERENTIE_NUMMER2") & "','" & resultSet("PROCESS_CODE") & "','" & resultSet("SERVICE_PROCESS") & "','" & resultSet("EXTERN_REFERENCE_NUMMER") & "','" & resultSet("ACTUAL_END_TIME") & "','" & resultSet("CLOSURE_DESCRIPTION") & "','" & resultSet("HNC_CLOSURE_CATEGORY") & "','" & resultSet("CUSTOMER_SATISFACTION") & "', '" & resultSet("CHANGE_DESCRIPTION") & "','" & resultSet("PERSON_ID") & "','" & resultSet("CATEGORYID") & "','" & resultSet("HNC_CLOSURE_CATEGORY_ID") & "','" & resultSet("ORGANISATION_ID") & "','" & resultSet("LOCATION_ID") & "','" & resultSet("SPENT_TIME") & "','" & resultSet("CLOSURE_CATEGORY") & "','" & resultSet("TASKCLOSURECATEGORYID") & "','" & resultSet("MAIN_AFFECTED_CI_NAME") & "','" & resultSet("MAIN_AFFECTED_CI_TYPE") & "','" & resultSet("MAIN_AFFECTED_CI_ITEM_ID") & "','" & resultSet("UNITS") & "','" & resultSet("PHASE_CODE") & "','" & resultSet("PHASE") & "','" & resultSet("STATUS_CODE") & "','" & resultSet("TASK_PROCESS_CODE") & "','" & resultSet("CLOSURE_CATEGORY_LEVEL_1") & "','" & resultSet("CHANGE_REASON") & "','" & resultSet("CHANGE_TYPE") & "','" & resultSet("PAUSE_TOTAL") & "','" & resultSet("PRIORITY2") & "','" & resultSet("BEGIN_PLAN_DATE") & "','" & resultSet("END_PLAN_DATE") & "', '" & resultSet("EVALUATION") & "','" & resultSet("MAIN_AFFECTED_CI_ITEM_TYPE_ID") & "','" & resultSet("PERSON_") & "','" & resultSet("ORGANISATION") & "','" & resultSet("CONTACT_METHOD") & "','" & resultSet("LOCATION") & "','" & resultSet("CONTACT_DETAILS") & "','" & resultSet("AR_SUPRESS_") & "', '" & resultSet("AR_ESC_SUPRESS_") & "', '" & resultSet("TASK_COLOR_LIST") & "', '" & resultSet("MATRIX") & "','" & resultSet("CIGROUP") & "', '" & resultSet("OPEN_TASKS") & "', '" & resultSet("MOD_DATE") & "', '" & resultSet("EXTERN_NUMMER") & "', '" & resultSet("KLANT_REFERENTIE_NUMMER") & "', '" & resultSet("PARAMETER") & "', '" & resultSet("PERSONNEL_NO_") & "', '" & resultSet("MELDER") & "', '" & resultSet("METHODE") & "', '" & resultSet("KVICURRENTROW") & "', '" & resultSet("KVICOUNTROWS") & "', '" & resultSet("CLOSURE_CATEGORY_LEVEL_2") & "', '" & resultSet("CLOSURE_CATEGORY_LEVEL_3") & "', '" & resultSet("CLOSURE_CATEGORY_LEVEL_4") & "', '" & resultSet("CATEGORIE_LEVEL_1") & "', '" & resultSet("CATEGORIE_LEVEL_2") & "', '" & resultSet("CATEGORIE_LEVEL_3") & "', '" & resultSet("CATEGORIE_LEVEL_4") & "', '" & resultSet("ORGANIZATION_PARENT") & "', '" & resultSet("ONTVANGER_ID_NEW_PARENT") & "', '" & resultSet("NETTO_TEKST") & "','" & resultSet("WENSDATUM") & "', '" & resultSet("EXTERNE_ORGANISATIE") & "', '" & resultSet("ONTVANGERID_NEW") & "', '" & resultSet("EXTERN_ERROR_STATUS") & "', '" & resultSet("TASK_UPDATE") & "', '" & resultSet("EXTERN_ERROR") & "', '" & resultSet("RESPONSE_END_TIME") & "', '" & resultSet("TEMP_DESCRIPTION") & "', '" & resultSet("TEMP_TASK_DESCRIPTION") & "', '" & resultSet("BEGIN_PLAN_DATE2") & "', '" & resultSet("END_PLAN_DATE2") & "', '" & resultSet("KVI_KORTE_OMSCHRIJVING") & "', '" & resultSet("KVI_DATUM_TIJD") & "', '" & resultSet("B2B_REFERENCE_NR") & "', '" & resultSet("B2B_REQUEST_ID") & "', '" & resultSet("B2B_REQUEST_") & "', '" & resultSet("B2B_ORIGINATION_DOMAIN") & "', '" & resultSet("PAUSE_TEXT") & "', '" & resultSet("PAUSE_REASON") & "', '" & resultSet("CKRNUMMER") & "', '" & resultSet("KLANT_TEVREDENHEID_CODE") & "', '" & resultSet("REDEN_ONTEVREDEN") & "', '" & resultSet("ORGANISATION_ID_1") & "', '" & resultSet("SUBMITTER_1") & "', '" & resultSet("CREATE_DATE_1") & "', '" & resultSet("ASSIGNED_TO") & "', '" & resultSet("LAST_MODIFIED_BY_1") & "', '" & resultSet("MODIFIED_DATE") & "', '" & resultSet("STATUS_1") & "', '" & resultSet("SHORT_DESCRIPTION") & "', '" & resultSet("ROW_LEVEL_ACCESS_1") & "', '" & resultSet("ORGANISATION_PARENT") & "', '" & resultSet("ORGANISATION_PARENT_ID") & "', '" & resultSet("ORGANISATION_TYPE") & "', '" & resultSet("BUSINESS_GROUP_ID_INT_") & "', '" & resultSet("ORGANISATION_NAME") & "', '" & resultSet("CONTACT_PERSON") & "', '" & resultSet("TYPE_ID") & "', '" & resultSet("DELETED__") & "', '" & resultSet("ANALIST_CODE") & "', '" & resultSet("REFERENCE_NO_") & "', '" & resultSet("LABEL") & "', '" & resultSet("PREFERRED") & "', '" & resultSet("MOBILE_PHONE") & "', '" & resultSet("E_MAIL") & "', '" & resultSet("TELEPHONE") & "', '" & resultSet("FAX") & "', '" & resultSet("OTHER") & "', '" & resultSet("CREATE_WEBCALL") & "', '" & resultSet("MAINORGANISATIONADRESS") & "', '" & resultSet("MAINORGANISATIONZIPCODE") & "', '" & resultSet("MAINORGANISATIONCITY") & "', '" & resultSet("MAINORGANISATIONSTATE") & "', '" & resultSet("MAINORGANISATIONCOUNTRY") & "', '" & resultSet("MM_ONDERZOEK_AFDELING") & "', '" & resultSet("MM_ONDERZOEK_NR") & "', '" & resultSet("MAIN_ORGANISATION_ADDRESS_ID") & "', '" & resultSet("BIJZONDERHEDEN") & "', '" & resultSet("AANSTUREN_VIA_INTERFACE") & "', '" & resultSet("CHILD_IS_LOCATIE") & "', '" & resultSet("ORG_NAAM") & "', '" & resultSet("AFMELD_TEKST") & "', '" & resultSet("DELETETOOL_OVERRIDE") & "', '" & resultSet("TELEFOON") & "', '" & resultSet("FAX2") & "', '" & resultSet("E_MAIL2") & "', '" & resultSet("ORGANISATIENAAM") & "', '" & resultSet("WEB_ACCOUNT_ID_USER_REQUESTID_") & "', '" & resultSet("PINCODE_PLAATS") & "', '" & resultSet("SIMPLIFIED_FORMS") & "', '" & resultSet("SOURCE") & "', '" & resultSet("OLD_NAME") & "', '" & resultSet("HUISNUMMER") & "', '" & resultSet("TOEVOEGING") & "', '" & resultSet("PINCODE") & "', '" & resultSet("VT_KL_ID") & "', '" & resultSet("VT_ID") & "', '" & resultSet("VT_SPECIAL_INSTR") & "', '" & resultSet("VT_TOEGANG_BIJZONDERHEDEN") & "', '" & resultSet("VT_TMS_BIJZONDERHEDEN") & "', '" & resultSet("VT_NETWERKOVERZICHT") & "', '" & resultSet("VT_CKRNR") & "', '" & resultSet("ORGANISATIE_MENU") & "', '" & resultSet("VT_CBS_BIJZONDERHEDEN") & "', '" & resultSet("VT_IGO_BIJZONDERHEDEN") & "', '" & resultSet("BIJLAGE1") & "', '" & resultSet("BIJLAGE2") & "', '" & resultSet("VT_TMS_JN") & "', '" & resultSet("VT_TMS_PINCODE") & "', '" & resultSet("VT_OPENINGSTIJDEN_WD_BEGIN") & "', '" & resultSet("VT_OPENINGSTIJDEN_WD_EIND") & "', '" & resultSet("VT_OPENINGSTIJDEN_ZA_BEGIN") & "', '" & resultSet("VERANTWOORDELIJKE_GROEP") & "', '" & resultSet("VT_OPENINGSTIJDEN_ZA_EIND") & "', '" & resultSet("VT_OPENINGSTIJDEN_ZO_BEGIN") & "', '" & resultSet("VT_OPENINGSTIJDEN_ZO_EIND") & "', '" & resultSet("VT_CITYRING_JN") & "', '" & resultSet("VT_GRIP_JN") & "', '" & resultSet("VT_GRIPNET_JN") & "', '" & resultSet("VT_IGO_VERZENDING") & "', '" & resultSet("VT_LOCALCONNECT_JN") & "', '" & resultSet("LEVEL_1") & "', '" & resultSet("LEVEL_2") & "', '" & resultSet("LEVEL_3") & "', '" & resultSet("LEVEL_1_ID") & "', '" & resultSet("LEVEL_2_ID") & "', '" & resultSet("LEVEL_3_ID") & "', '" & resultSet("LEVEL_4") & "', '" & resultSet("LEVEL_4_ID") & "', '" & resultSet("LEVEL_1_ORG") & "', '" & resultSet("LEVEL_2_ORG") & "', '" & resultSet("LEVEL_3_ORG") & "', '" & resultSet("LEVEL_4_ORG") & "', '" & resultSet("SQL_UPDATE_NAME") & "', '" & resultSet("SQL_UPDATE__OLD__NAME") & "', '" & resultSet("SQL_UPDATE_MENU_NAME") & "', '" & resultSet("SQL_UPDATE_ORG_NAME") & "', '" & resultSet("VERANTWOORDELIJK") & "', '" & resultSet("VERANTWOORDELIJK_ID") & "', '" & resultSet("TOON_OP_MELDING_") & "', '" & resultSet("USE_ROW_LEVEL_ACCESS_") & "', '" & resultSet("GROUP_ID__CHAR_") & "', '" & resultSet("BOX_GROUP_ID__CHAR_") & "', '" & resultSet("BOX") & "', '" & resultSet("BIJLAGE3") & "', '" & resultSet("BIJLAGE4") & "', '" & resultSet("BIJLAGE5") & "', '" & resultSet("BIJLAGE6") & "')"
connect_target.execute sql_target
Call ErrorHandling()
resultSet.MoveNext
Loop
resultSet.Close
connect.Close
Set connect = Nothing
resultSet_target.Close
connect_target.Close
Set connect_target = Nothing
' CHANGE TO BE WRITING TO LOG FILE
Sub ErrorHandling()
If Err.Number <> 0 Then
MsgBox "ERROR:" & Err.Number & ", " & Err.Description
'MSGBOX resultSet("CHANGE_MANAGEMENT_ID")
Err.Clear()
End If
End Sub
command text was not set for the command objec
