ADO.NET : OleDbCommand Parameterized Query Sequence Rule

ADO.NET : OleDbCommand Parameterized Query Sequence Rule

  • Comments 9

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!!!

Leave a Comment
  • Please add 4 and 2 and type the answer here:
  • Post
  • While working with MS Access database with OleDbCommand you might have faced issue that your parameterized

  • 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.

  • Did you try my code?

    Try

    http://msdn.microsoft.com/en-us/library/ms971485.aspx

    Wriju

  • 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!

  • 8mGood idea.3p I compleatly disagree with last post .  pil

    <a href="http://skuper.ru">ламинат и паркет</a> 9m

  • 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

  • WOW! You rock!!! Thanks. Was struggling for 30 minutes!

  • 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 ;-)

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

Page 1 of 1 (9 items)