Filling a DataSet from a select statement with mutliple conditions

Question

I'm trying to fill this DataSet with the info that comes from the ARCHIVES_MASTER_TBL when I run a query selecting the products that match the decade and product line but it's giving me the error: "No value given for one or more required parameters."

 OleDbConnection dbConn;
 OleDbDataAdapter dbCmdDecade;

 dbConn = new OleDbConnection(connectionString);
 dbConn.Open();

 dbCmdDecade = new OleDbDataAdapter("Select * From ARCHIVE_MASTER_TBL WHERE DECADE_1=" + decade + "AND PRODUCT_LINE=" + productLine, dbConn);
            DataSet ds = new DataSet();
 dbCmdDecade.Fill(ds, "products");

It worked before adding the "+ "AND PRODUCT_LINE=" + productLine" part but I needed the search to be more specific and only choose the ones that fit both the decade and the product

Missing a space before the AND for the second condition. But this is not how you should write an sql command. Parameters is the only way to do it — Steve, May 20 '16 at 21:56
Why shouldnt I write it this way? It worked. But the space for the AND didnt solve the problem I posted about — Ben Cavenagh, May 20 '16 at 21:59
What are the datatype of the column PRODUCT_LINE and DECADE_1 ? — Steve, May 20 '16 at 22:01

score 1 · Accepted Answer · edited May 23 '17 at 12:15

You shouldn't write an sql command concatenating strings. This is a well known source of bugs (parsing errors) and a security risk called Sql Injection.
Instead you should use parameters that allow you to specify the exact datatype of your values and let the database engine use them without conversions

For example, your query fails because the PRODUCT_LINE column is of type text and thus, if you want to search on this column, the value should be enclosed between quotation marks, but then, what if the value contains one or more quotation marks?. You need to Replace any single quote with a couple of single quotes. In no time your query becomes unreadable and the possible path for bugs multiplies.

Instead with a parameterized query you have:

string cmd = @"Select * From ARCHIVE_MASTER_TBL 
               WHERE DECADE_1=@decade AND PRODUCT_LINE=@line";

using(OleDbConnection dbConn = new OleDbConnection(connectionString))
using(OleDbDataAdapter dbCmdDecade = new OleDbDataAdapter(cmd, dbConn))
{
     dbConn.Open();
     dbCmdDecade.SelectCommand.Parameters.Add("@decade", OleDbType.Integer).Value = decade;
     dbCmdDecade.SelectCommand.Parameters.Add("@line", OleDbType.VarWChar).Value = productline;
     DataSet ds = new DataSet();
     dbCmdDecade.Fill(ds, "products");
}

A lot clearer and no space for misunderstandings between your code and the database

Filling a DataSet from a select statement with mutliple conditions

1 Answers1