Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Access SQL query missing more required parameters

Tags:

c#

sql

ms-access

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?

like image 259
A. Abramov Avatar asked Apr 29 '15 05:04

A. Abramov


2 Answers

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);
like image 148
HansUp Avatar answered Oct 17 '22 05:10

HansUp


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))";
like image 38
Krish Avatar answered Oct 17 '22 06:10

Krish