OleDb Adapter.Fill() "No value given for one or more parameters"-CodePudding

I am attempting to pull data from an Access database and display it in a DataViewGrid using the adapter.fill() method but keep receiving an error saying "No value given for one or more parameters." This only happens when I attempt to include the variables userID, userPIN, accountNo through concatenation. Those three variables are taken from a previous form's textBox data. When I simplify the query string to "SELECT * FROM STATEMENT", I simply receive a blank dataViewGrid which is also quite odd. I've searched for a solution for days but to no avail. Any help is much appreciated.

string query = "select * from statement where userID = '"   userID   "' and userPIN ='"   userPIN   "' and account_no = "   account_no; //cmd in string form for use with OleDbAdapter
string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\ATM Project Folder\\Bank1.accdb";
OleDbConnection conn = new OleDbConnection(connString);

conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(query, conn);

// dataset object
DataSet ds = new DataSet();
adapter.Fill(ds);

// Bind dataset  
dataGridView1.DataSource = ds.DefaultViewManager;

CodePudding user response：

Let's do this properly:

string query = "select * from statement where userID = @u and userPIN = @p and account_no = @a"

string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\ATM Project Folder\\Bank1.accdb";

var adapter = new OleDbDataAdapter(query, connString);

//call these in the same order that the placeholders appear in the sql. Ole does not use the names
//when you upgrade to sql server, cease using AddWithValue: https://www.dbdelta.com/addwithvalue-is-evil/
adapter.SelectCommand.Parameters.AddWithValue("@u", userID);
adapter.SelectCommand.Parameters.AddWithValue("@p", userPIN);
adapter.SelectCommand.Parameters.AddWithValue("@a", accoubt_no);


var dt = new DataTable();
adapter.Fill(dt);


dataGridView1.DataSource = dt;

Never, ever write an SQL like you did in your question; at best, it'll fall over if someone types something that generates a syntax error (such as an apostrophe). At worst it'll cause something like the vetch hack where data of over 6 million children was exposed just because a hacker could type a fragment of sql into a website and break into the database

This is really important; concatenating user-supplied data into your SQL is one of the number one ways of providing hackers easy access into a system today. Don't do it; it is always possible to operate more securely

The simplest way to use a data adapter to get data is to use the constructor that takes the command string and the connection string, add any parameters that are contained in the command, and call fill. DataAdapter will manage the connection for you. Some people dispose of the adapter; the MSDN official example doesn't so I've omitted that

Access OLE understands @namedParameters but it still treats them positionally; you must add parameters to your command object in the same order that they appear in the statement. The other option you have is to use ? as placeholders. Even though the names aren't used it makes it easier to debug problems. More sophisticated databases do use the names, which means the parameters can be supplied in any order and they can be repeated in the SQL but supplied once in the Parameters. If you do upgrade to SQLServer, cease using AddWithValue as it can cause performance problems, but other databases may not care; check on a case by case basis