Partilhar via

ADO.NET : OleDbCommand Parameterized Query Sequence Rule

  • Artigo

While working with MS Access database with OleDbCommand you might have faced issue that your parameterized update statement is not working. The most frustrating part is that it also does not throw any runtime error. So ideally you paralyzedJ.

 

Golden rule is that you need to maintain the exact sequence of parameters you have specified in the sql statement. So while adding the parameter value sequence is very important.

 

Command parameter collection addition sequence has to match with the sql statement sequence.

 

String sSQL = "UPDATE PHONEDB SET [NAMES] = @pNames WHERE [ID] = @pID";

OleDbConnection conn = new OleDbConnection("ConnectionString");

OleDbCommand cmd = new OleDbCommand();

cmd.Connection = conn;

cmd.CommandType = CommandType.Text;

cmd.CommandText = sb.ToString();

cmd.Parameters.AddWithValue("@pNames", "Your Name");

cmd.Parameters.AddWithValue("@pID", 12345);

 

 

Now if you alter the sequence like,

 

cmd.Parameters.AddWithValue("@pID", 12345);

cmd.Parameters.AddWithValue("@pNames", "Your Name");

 

Things would not work as expected.

 

Namoskar!!!

Comments

  • Anonymous
    January 24, 2008
    While working with MS Access database with OleDbCommand you might have faced issue that your parameterized

  • Anonymous
    July 08, 2008
    I am trying to insert rows into an Access database using vb.net(2003).  I am having a problem setting up the command string, connection string, transaction code.  This should be farely easy but I cannot seem to be able to the right questions in Google.

  • Anonymous
    July 09, 2008
    Did you try my code? Try http://msdn.microsoft.com/en-us/library/ms971485.aspx Wriju

  • Anonymous
    August 14, 2008
    Great! Normally I use MS SQL and that can handle a different sequence. I have been very frustrated that it didn't work, couldn't figure out what the problem was. You helped me a lot! Thnx!

  • Anonymous
    August 24, 2008
    8mGood idea.3p I compleatly disagree with last post .  pil <a href="http://skuper.ru">ламинат и паркет</a> 9m

  • Anonymous
    September 30, 2008
    Is there a way of seeing the query once the parameter substitutions have been made? cmd.CommandText has the @params shown - what I want to see is the completed command before it is submitted to Access. Thanks

  • Anonymous
    May 18, 2009
    WOW! You rock!!! Thanks. Was struggling for 30 minutes!

  • Anonymous
    September 22, 2009
    Faced the exact same issue, but found the solution myself after about 30 minutes. Very, very, very, VERY surprised this has not been resolved yet. I did this before, using ASP.Net with VB syntax and there it worked just fine! Anyway, thx for the post, this at least reassures me I'm not being blind to some stupid mistake I might have made ;-)

  • Anonymous
    November 22, 2009
    Thanks Wriju. I was trying this for the last two days but couldn't solve! At last your article helped me. Thanks a lot.

  • Anonymous
    April 02, 2015
    very tanks.

  • Anonymous
    June 25, 2017
    Thank you Sir :)This Solution gives me a lot of help.After 2-3hr google I got your solution and solve the problem. Thanks you :)