Your code is vulnerable to
SQL Injection[
^].
NEVER use string concatenation to build a SQL query.
ALWAYS use a parameterized query.
Rather than specifying the full path to the database file, use
|DataDirectory|
in your connection string. This will automatically point to the application directory for a Windows application, and the
App_Data
directory for a web application.
Wrap disposable objects in a
using
block to ensure that their resources are always cleaned up.
There's no need to use
ExecuteReader
when you're only reading a single value. Use
ExecuteScalar
instead.
And SQL uses the operators
AND
and
OR
, not the C-style
&&
and
||
.
const string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|StoreSys.mdb";
const string Query = "SELECT TOP 1 StockID FROM Stock WHERE ProName = ? Or ProType = ? Or ProSize = ?";
using (var connection = new OleDbConnection(ConnectionString))
using (var command = new OleDbCommand(Query, connection))
{
command.Parameters.AddWithValue("p0", comboBox1.Text);
command.Parameters.AddWithValue("p1", comboBox2.Text);
command.Parameters.AddWithValue("p2", comboBox3.Text);
connection.Open();
txtStockID.Text = Convert.ToString(command.ExecuteScalar());
}
Also, do yourself a favour and give your controls meaningful names. Accepting the default name assigned by the designer will only cause confusion later.
EDIT:
Based on the comments, it sounds like you want to match
all of the selected parameters, rather than matching
any of the selected parameters. If so, change the code to:
const string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|StoreSys.mdb";
const string Query = "SELECT TOP 1 StockID FROM Stock WHERE (ProName = ? Or ? = '') And (ProType = ? Or ? = '') And (ProSize = ? Or ? = '')";
using (var connection = new OleDbConnection(ConnectionString))
using (var command = new OleDbCommand(Query, connection))
{
command.Parameters.AddWithValue("p0", comboBox1.Text);
command.Parameters.AddWithValue("p1", comboBox1.Text);
command.Parameters.AddWithValue("p2", comboBox2.Text);
command.Parameters.AddWithValue("p3", comboBox2.Text);
command.Parameters.AddWithValue("p4", comboBox3.Text);
command.Parameters.AddWithValue("p5", comboBox3.Text);
connection.Open();
txtStockID.Text = Convert.ToString(command.ExecuteScalar());
}
(You have to add each parameter twice, since OleDb doesn't used named parameters.)