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
 
#6
Following is an example of update query.

"update [SheetName$] set ColumnName= 'Value' where ColumnName="&[DataItemName]&""

SheetName----> This is the sheet in the Excel where you update values.
"&[DataItemName]&"--->In order pass value from data item you need pass this way

I'm not sure about CSV you need to change the connection string for that. Connection string is different for csv, xlsx, xlsm and xls file
 
Top