Access to an SQL query that lacks more required parameters

Question

In a web project, I am trying to execute the following query:

SELECT ItemName as Name, ItemPicture as Picture, ItemHeroModif as Assistance, ItemTroopModif as Charisma, HerbCost as Herbs, GemCost as Gems FROM Item WHERE ItemId = @value0 

Using breakpoints, I see I attached a value of 2 to @value0 .

Despite this, I get the following error:

Missing value for one or more required parameters.

I realized that this error usually occurs due to incorrect SQL syntax. Is there something wrong with what I did?

EDIT :

Attachment Code:

 var madeForCommand = "SELECT ItemName as Name,ItemPicture as [Picture],ItemHeroModif as Assistance,ItemTroopModif as Charisma, HerbCost as Herbs, GemCost as Gems FROM Item WHERE "; OleDbCommand command = new OleDbCommand(); for (int ii = 0; ii < items.Count; ii++)// items is a list of items with IDs I want to get from the query. { madeForCommand += "ItemId =@value "+ii+" OR "; } madeForCommand = madeForCommand.Substring(0, madeForCommand.Length - 4); // making sure I trim the final or; In the case I shown, it just one item, so there are none at all. 

And later:

 OleDbCommand forOperations = new OleDbCommand(madeForCommand, _dbConnection); //_dbConnection is the connection to the database, it seems to work pretty well. for (int ii = 0; ii < items.Count; ii++) { string attach = "@value" + ii; command.Parameters.AddWithValue(attach, items[ii].ID); } 

I'm pretty sure that items[ii].ID is fine, breakpoints show that it is 2, and the attachment is going well.

EDIT 2 : I edited the code as advised by Krish and Hans, and I get the following request without any attachments:

 SELECT ItemName as [Name],ItemPicture as Picture,ItemHeroModif as Assistance,ItemTroopModif as Charisma, HerbCost as Herbs, GemCost as Gems FROM [Item] WHERE (ItemID in (2)); 

I still get the same error if it changes something.

EDIT 3 : Running a query in Access asks me to give a value to the "ItemPicture" ... Odd; ItemPicture is a column, isn't it?

Answer 1

Name , Item and Picture are problematic words in Access queries. Fix them in square brackets:

 SELECT ItemName as [Name], ItemPicture as [Picture], ItemHeroModif as Assistance, ItemTroopModif as Charisma, HerbCost as Herbs, GemCost as Gems FROM [Item] WHERE ItemID in (2); 

Since bracketing these names still made you the missing complaint, I asked you to test this request in the Access query constructor. In this context, Access is a parameter input field that also includes a word that Access interprets as a parameter.

You reported that Access considers ItemPicture to be a parameter. So, looking at this table in the Access Design View, you find that the actual field name is ItemImageURL.

 SELECT ItemName as [Name], ItemImageURL as [Picture], ItemHeroModif as Assistance, ItemTroopModif as Charisma, HerbCost as Herbs, GemCost as Gems FROM [Item] WHERE ItemID in (2); 

Answer 2

You do not accept the Where clause outside the application, so string concatenation is safe. (at least I think so)

just add these parameters:

 var madeForCommand = "SELECT ItemName as Name,ItemPicture as Picture,ItemHeroModif as Assistance,ItemTroopModif as Charisma, HerbCost as Herbs, GemCost as Gems " + "FROM Item WHERE (ItemID in ("; OleDbCommand command = new OleDbCommand(); for (int ii = 0; ii < items.Count; ii++)// items is a list of items with IDs I want to get from the query. { if (i<=1) { madeForCommand += items[ii].ID }else { madeForCommand += "," + items[ii].ID; } } madeForCommand += "))" 

at the end you will get an SQL query something like this:

 "SELECT ItemName as Name,ItemPicture as Picture,ItemHeroModif as Assistance,ItemTroopModif as Charisma, HerbCost as Herbs, GemCost as Gems " + "FROM Item WHERE (ItemID in (1,2,3))"; 

Answer 3

MS Access does not know such parameters: @value0 !

Use ? instead of or named parameters as follows:

 PARAMETERS [value0] INT; SELECT ItemName as Name, ItemPicture as Picture, ItemHeroModif as Assistance, ItemTroopModif as Charisma, HerbCost as Herbs, GemCost as Gems FROM Item WHERE ItemId = [value0]; 

How to call this request? Read the following: Access Microsoft Office data from .NET applications
For more information, see This: OleDbCommand.Parameters Property

[EDIT]

As I understand it, you want to pass multiple ItemId requests to the request. So you should use IN :

 SELECT ... FROM ... WHERE ItemID IN (1, 2, 5, 8)