Excel Update using OLEDB

#1
Hello All

I am working on updating the excel using oledb query it is working for other cells but for one cell updation it is giving error as "Internal : Could not execute code stage because exception thrown by code stage: No value given for one or more required parameter"

Connection string : "Provider=""Microsoft.ACE.OLEDB.12.0"";Data Source=""" &[global_coll_Config_File.Local_Folder] & "\" & [global_num_CurrentYear] & "\" & [global_txt_CurrentMonth] & "\" & [global_num_CurrentDate] & "\" & [global_coll_Config_File.Report]& """;Extended Properties=""Excel 12.0;HDR=YES;"""

Update Query : "UPDATE ["&[txt_PreviousMonth]&"$A2:Q] SET [Comment Value]='"&
[ip_txt_CommentValue]&"' WHERE [Sales Category Name] ='"&[coll_TemplateExcel.Sales Categories]&"' AND [Dept] ='"&[ip_WorksheetName]&"'"

The above query is giving me issue and below is the query which is working perfectly fine.

"UPDATE ["&[txt_PreviousMonth]&"$A2:Q] SET [AMOUNT]= '"&
[coll_TemplateExcel.Amount]&"' WHERE [Sales Category Name] ='" &[coll_TemplateExcel.Sales Categories]&"' AND [Dept] = '"&[ip_WorksheetName]&"'"

All the Column are General format except Amount and Comment Value.

Please help me in finding the real issue for the above error
 
#2
Hello All

I am working on updating the excel using oledb query it is working for other cells but for one cell updation it is giving error as "Internal : Could not execute code stage because exception thrown by code stage: No value given for one or more required parameter"

Connection string : "Provider=""Microsoft.ACE.OLEDB.12.0"";Data Source=""" &[global_coll_Config_File.Local_Folder] & "\" & [global_num_CurrentYear] & "\" & [global_txt_CurrentMonth] & "\" & [global_num_CurrentDate] & "\" & [global_coll_Config_File.Report]& """;Extended Properties=""Excel 12.0;HDR=YES;"""

Update Query : "UPDATE ["&[txt_PreviousMonth]&"$A2:Q] SET [Comment Value]='"&
[ip_txt_CommentValue]&"' WHERE [Sales Category Name] ='"&[coll_TemplateExcel.Sales Categories]&"' AND [Dept] ='"&[ip_WorksheetName]&"'"

The above query is giving me issue and below is the query which is working perfectly fine.

"UPDATE ["&[txt_PreviousMonth]&"$A2:Q] SET [AMOUNT]= '"&
[coll_TemplateExcel.Amount]&"' WHERE [Sales Category Name] ='" &[coll_TemplateExcel.Sales Categories]&"' AND [Dept] = '"&[ip_WorksheetName]&"'"

All the Column are General format except Amount and Comment Value.

Please help me in finding the real issue for the above error
This query runs fine
Insert into [Sheet1$] values ('DBT39435M','Daniel','B','Tonini','5','75',0877,'01-01-1990 00:00:00')
but this one fails with the exact same error that you see in your case...
Insert into [Sheet1$] values ('DBT39435M','Daniel','B','Tonini','5','75','0877',[01-01-1990 00:00:00])
so i assume this error only shouts when a parameter is missing. I would suggest you check if the values of Sales Category Name and ip_WorksheetName and Dept are blank or not formatted properly?
 
#3
Hi All, The above query is to insert all columns in the sheet. But how can we insert just 1 value against a row EX: Can I use something like
"Update [Red$] set [Team] = 'L2' Where UCASE([Message]) = 'ABC'" This is not working !!. Can somebody help ?
Red is my sheet name. Team adn Message is my column

Even Delete * from [Red$] where [Message]='ABC' is not working. Only select * is workinng
 
Last edited:
#4
Hi All, The above query is to insert all columns in the sheet. But how can we insert just 1 value against a row EX: Can I use something like
"Update [Red$] set [Team] = 'L2' Where UCASE([Message]) = 'ABC'" This is not working !!. Can somebody help ?
Red is my sheet name. Team adn Message is my column

Even Delete * from [Red$] where [Message]='ABC' is not working. Only select * is workinng
Hi Nancy,

Delete Operation is not possible using oledb. We can just insert, update the records in excel.

Thanks,
Babjee
 
Top