CODEWITHSUNDEEP
c#sqlms-access
A. Abramov
A. Abramov

Reputation: 1865

Access SQL query missing more required parameters

In a web project, I'm 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

With breakpoints, I can see I attached to @value0 the value, 2.

Despite this, I get the following error:

No value given for one or more required parameters.

I understood this error is usually generated due to bad SQL syntax. Is there anything 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's just one item, so there are none at all.

And later on:

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 items[ii].ID is fine, breakpoints show that it equals 2 and the attachment goes well.

EDIT 2: I've editted the code as Krish and Hans advised me, and I get the following query 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 anything.

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

Upvotes: 6

Views: 1374

Answers (3)

HansUp
HansUp

Reputation: 97101

Name, Item, and Picture are problem words in Access queries. Enclose 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 those names still gave you a missing parameter complaint, I asked you to test that query in Access' query designer. In that context, Access presents a parameter input box which also includes the word which Access interprets as a parameter.

You reported Access thinks ItemPicture is a parameter. So by inspecting that table in Access Design View, you discovered 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);

Upvotes: 3

Krish
Krish

Reputation: 5917

You are not taking the Where condition from outside your application so string concatenation is safe. (at least i think so)

just add the parameters like this:

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 have a SQL query something like:

"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))";

Upvotes: 2

Maciej Los
Maciej Los

Reputation: 8591

MS Access does not know parameters like this: @value0!

Use ? instead or named parameters as follow:

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 query? Please read this: Accessing Microsoft Office Data from .NET Applications
For further information, see this: OleDbCommand.Parameters Property

[EDIT]

As per my understanding, you want to pass several ItemId's into query. So, you have to use IN clause:

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

Upvotes: 1

Related Questions