CODEWITHSUNDEEP
c#asp.netdataset
Ben Cavenagh
Ben Cavenagh

Reputation: 748

Filling a DataSet from a select statement with mutliple conditions

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

Upvotes: 1

Views: 46

Answers (1)

Steve
Steve

Reputation: 216343

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

Upvotes: 1

Related Questions