OleDbCommand Parameters AddWithValue Not Working

TLDR - Ensure your parameters are IN THE SAME ORDER that they appear in the SQL you've created.

So you've gotten used to using MySqlCommand and then adding parameters. You then jump on some old skool system that uses MS Access as the DB in the background. Yesh, yeah, yarp, you've been meaning for eons to update and upgrade to MySQL but lets face it for this little system it's been working fine for years and years and if it ain't broke don't fix it huh.

But in the interest of good practice the new code being grafted into the old system is now C# as opposed to VB. And you're being a good boy and rather than creating SQL with the variables thrown into the text string (bad practice, risk of SQL injection) you're passing the variables as parameters.

And parameters do work in MS Access's implementation of SQL. Kinda. Sorta. Ish. 

But when you add the parameters you have to add them in the order they appear in the SQL


   SQL = "SELECT * FROM MyTable WHERE LineID = @LineID AND UserID = @UserID

Followed by

   using (OleDbCommand MyCommand = new OleDbCommand(qwe, dbconn))
      MyCommand.Parameters.AddWithValue("@UserID", UserID.Text));
      MyCommand.Parameters.AddWithValue("@LineID", LineID.Text)); 

Will NOT work but

   using (OleDbCommand MyCommand = new OleDbCommand(qwe, dbconn))
      MyCommand.Parameters.AddWithValue("@LineID", LineID.Text))
      MyCommand.Parameters.AddWithValue("@UserID", UserID.Text)); 

WILL work.

I get the gist from my quick search that essentially it doesn't really matter how you name the parameters, all you're ACTUALLY sending is "Parameter 1, Parameter 2, Parameter 3..." then when Access meets a parameter it just runs through them numerically, not matching "@UserID" with "@UserID" like MySQL would.


Reader's Comments

Post Your Comment Posts/Links Rules



Add a RELEVANT link (not required)

Upload an image (not required)

No uploaded image
Real person number
Please enter the above number below

Admin Ren's Biking Blog