I am uploading the data in an Excel sheet to an Oracle database of 167 columns using Excel macros. I am getting this error in the execute statement.
I am facing difficulties in debugging this error.
Run Time error: -2147217887(80040e21) The system cannot find a message text for the message number 0x80040e21 in the message file for OraOLEDB
strSQL = "Update T_SAP_ZSSTABL_NEW set TSZ_CHARG = ?, TSZ_MATNR = ?, TSZ_MAKTX = ?, TSZ_MATKL = ?, TSZ_SEC1_MAX = ?, TSZ_SEC2_MAX = ?, TSZ_THICKNESS = ?, TSZ_TDCNO = ?," _
& "TSZ_LENGTH = ?, TSZ_OUT_DIA = ?, TSZ_IN_DIA = ?, TSZ_FKDAT = ?, TSZ_WERKS = ?, TSZ_CUSCD = ?, TSZ_FTRAMT = ?, TSZ_VRKME = ?, TSZ_FKIMG = ?, TSZ_RECDATE1 = ?," _
& "TSZ_QCODE = ?, TSZ_ZWERKS = ?, TSZ_AUART1 = ?, TSZ_MRK_CUST_NM = ?, TSZ_SHTO = ?, TSZ_CHARG1 = ?, TSZ_MATNR1 = ?, TSZ_TOTVAL = ?, TSZ_SPART = ?, TSZ_MANDT = ?," _
& "TSZ_LZONE = ?, TSZ_ZSSCAMT = ?, TSZ_PROD_DATE = ?, TSZ_PROD_ITEM = ?, TSZ_POEDAT = ?, TSZ_ERDAT = ?, TSZ_REFSO = ?, TSZ_AR4DAT = ?, TSZ_AR4NO = ?, TSZ_AR4_TYPE = ?," _
& "TSZ_SECHECESS = ?, TSZ_LIFNR = ?, TSZ_SHIPMD = ?, TSZ_MRK_DEST = ?, TSZ_ZTILAMT = ?, TSZ_ZFC1AMT = ?, TSZ_ZFMCAMT = ?, TSZ_ZINAAMT = ?, TSZ_ZEF0AMT = ?, TSZ_ZWF0AMT = ?," _
& "TSZ_ZCHAAMT = ?, TSZ_ZDCAMT = ?, TSZ_KZWI4 = ?, TSZ_KZWI3 = ?, TSZ_LISTPRICE = ?, TSZ_GROSSWT = ?, TSZ_LAND1 = ?, TSZ_NIELS = ?, TSZ_MVGR1 = ?, TSZ_KONDM = ?, TSZ_AUART = ?," _
& "TSZ_KURRF = ?, TSZ_PGROUP = ?, TSZ_DRAW_TYPE = ?, TSZ_AESKD = ?, TSZ_LOADCHRG = ?, TSZ_LGORT = ?, TSZ_NAME2 = ?, TSZ_SHTNAME = ?, TSZ_TOCIND = ?, TSZ_MARK_CUST_DESC = ?, TSZ_TARE_WEIGHT = ?," _
& "TSZ_GROSS_WEIGHT = ?, TSZ_NET_WEIGHT = ?, TSZ_VKBUR = ?, TSZ_MVGR2 = ?, TSZ_VTWEG = ?, TSZ_SPEC = ?, TSZ_LEN2 = ?, TSZ_LEN1 = ?, TSZ_CLASS = ?, TSZ_END_FINISH = ?, TSZ_SUR_FINISH = ?," _
& "TSZ_CATEGORY = ?, TSZ_MILL = ?, TSZ_RUNDATE = ?, TSZ_CESSVAL = ?, TSZ_WAERK = ?, TSZ_IDLEFRT = ?, TSZ_FRTACCR2 = ?, TSZ_FRTACCR1 = ?, TSZ_STPRS = ?, TSZ_BSTDK = ?, TSZ_POSEX = ?," _
& "TSZ_BSTNK = ?, TSZ_WCAINV = ?, TSZ_J_1ICHID = ?, TSZ_LENGTH_CR = ?, TSZ_PCODE = ?, TSZ_EIKTO = ?, TSZ_KDMAT = ?, TSZ_CPUDT = ?, TSZ_TRGRP = ?, TSZ_DRCPT = ?, TSZ_TRTIME = ?," _
& "TSZ_GRQTY = ?, TSZ_GRNO = ?, TSZ_KDKG2 = ?, TSZ_WAGON_RLY = ?, TSZ_HUBLIFNR = ?, TSZ_TNAME1 = ?, TSZ_LLIEF = ?, TSZ_TRNSIND = ?, TSZ_REGIO = ?, TSZ_DESTDESC = ?, TSZ_DESTCD = ?," _
& "TSZ_VGBEL = ?, TSZ_POSNV = ?, TSZ_VBELV = ?, TSZ_AUPOS = ?, TSZ_AUBEL = ?, TSZ_MRP = ?, TSZ_DISCLP = ?, TSZ_LSTPRC = ?, TSZ_CAMNO = ?, TSZ_KZWI1 = ?, TSZ_DUEDT = ?," _
& "TSZ_ZTERM = ?, TSZ_NETWR = ?, TSZ_OTHERS = ?, TSZ_KWERT = ?, TSZ_SERIAL = ?, TSZ_STCEG = ?, TSZ_TOTAX = ?, TSZ_SRTAX = ?,TSZ_ADDSTAMT = ?, TSZ_STAMT = ?," _
& "TSZ_BSTCSTVAL = ?, TSZ_BSTCSTPERCENT = ?, TSZ_IND = ?, TSZ_EDAMT = ?, TSZ_MATLVAL = ?, TSZ_MATLRATE = ?, TSZ_QTY_METER = ?, TSZ_QTY_NO = ?, TSZ_KUNRG = ?," _
& "TSZ_CUSNAME = ?, TSZ_TYPE = ?, TSZ_PERIOD = ?, TSZ_VKORG = ?, TSZ_FKART = ?, TSZ_ETOIND = ?, TSZ_ZCHARG = ?, TSZ_ZMATNR = ?, TSZ_VTEXT = ?, TSZ_KALKS = ?," _
& "TSZ_VPRS_COST = ?, TSZ_WBS_ELEMENT = ?, TSZ_PRODCTN_ORDER_NO = ?, TSZ_SHOPPING_CART_NO = ?, TSZ_WOODEN_PALLET_WT = ?, TSZ_ZFINCHRG = ?, TSZ_ZATXAMT = ?, TSZ_VBELP1 = ?, TSZ_VBELN1 = ?, TSZ_VBELN_M = ? where TSZ_VBELN = ? and TSZ_POSNR = ?"
cmd.CommandText = strSQL
Set MyParameter = cmd.CreateParameter("CHARG", adVarChar, adParamInput, 10, VBELN)
cmd.Parameters.Append MyParameter
Set MyParameter = cmd.CreateParameter("MATNR", adVarChar, adParamInput, 6, POSNR)
cmd.Parameters.Append MyParameter
Set MyParameter = cmd.CreateParameter("MAKTX", adVarChar, adParamInput, 10, CHARG)
cmd.Parameters.Append MyParameter
.
.
.
.
.
Doing this for each parameter
And lastly
cmd.Execute
Advertisement
Answer
Guys the error was because i was trying to append more parameters than it actually existed. Having so many columns i lost the count of the number of append blocks. I should be following the best practices more diligently as suggested by Parfait. Lets give him an upvote. 😀