How do I create parameterized queries

Question

5.00/5 (1 vote)

See more:

I have a method that accepts data and and is being called approx every second. I tried creating a parameterized query which inserts the results into a MySQL database table but I keep getting error messages telling me that the parameter has already been defined. Can someone please explain how this works and what I'm doing wrong? I've read the mysql doc on using prepared statements and it doesn't go in depth enough on using prepared statements.

C#

private void (string _fname, string _lname, string _age) 
{ 
string sql = "Insert Into myTbl (firstname,lastname,age) Values(@fname,@lname,@age)"; 
cmd.CommandText = sql; 
cmd.PrePare(); 
cmd.Parameters.AddWithValue("@fname", _fname); 
cmd.Parameters.AddWithValue("@lname", _lname); 
cmd.Parameters.AddWithValue("@age" , _age ); 

cmd.ExecuteNonQuery(); 
}

Posted 7-Feb-11 11:33am

d.allen101

Updated 7-Feb-11 11:57am

JOAT-MON

v2

Add a Solution

Comments

JOAT-MON 7-Feb-11 17:58pm

EDIT - added code block

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Nish Nishant · Answer 1 · 2011-02-07T11:37:00

Solution 1

Call cmd.Parameters.Clear() before you re-add the parameters. Or add a flag so that you only add them once (there is no need to re-add them each time).

C#

if(!parametersCreated)
{
  // add parameters here

  parametersCreated = true;
}

[Updated code based on our discussion via comments]

C#

private bool parametersCreated;

private void Foo(string _fname, string _lname, string _age)
{
  string sql = ". . .";
  cmd.CommandText = sql;

  cmd.Prepare();

  if(!parametersCreated)
  {
    // cmd.Parameters.Clear(); <-- No need for this now
    cmd.Parameters.AddWithValue("@fname", _fname);
    . . .

    parametersCreated = true;
  }
  else
  {
    cmd.Parameters["@fname"].Value = _fname;
    . . .
  }

  cmd.ExecuteNonQuery();
}

Posted 7-Feb-11 11:37am

Nish Nishant

Updated 7-Feb-11 13:05pm

v5

Comments

Espen Harlinn 7-Feb-11 17:38pm

Good answer :)

Nish Nishant 7-Feb-11 17:38pm

Thanks.

d.allen101 7-Feb-11 17:40pm

so that's it? just cmd.Parameters.Clear() and that's it?

Nish Nishant 7-Feb-11 17:49pm

You need to call Clear if you have to re-create the parameters for some reason. Otherwise use the flag-technique I mentioned.

d.allen101 7-Feb-11 17:49pm

THANKS Nishant!!!! I just tried in a demo app and it worked just fine!

Nish Nishant 7-Feb-11 17:50pm

You're welcome.

d.allen101 7-Feb-11 17:52pm

Hey Nishant I have one other question...suppose I have 4 different tables where I'm updating 2 of them and inserting into the other 2 tables and each process is ran on it's own thread. should I use multiple command objects so they won't conflict with on another as far as the parameters are concerned?

Nish Nishant 7-Feb-11 17:55pm

Yes, you should use separate command objects. It would be a dangerous approach to use the same command object from multiple threads.

d.allen101 7-Feb-11 18:55pm

just to make sure I'm clear on the cmd.Parameters.Clear()...in the code example I provided where exactly should cmd.Parameters be placed? Or where and how should I use the flag example you suggested?

Nish Nishant 7-Feb-11 18:57pm

private void Foo(string _fname, string _lname, string _age)
{
string sql = ". . .";
cmd.CommandText = sql;

cmd.Prepare();

cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@fname", _fname);
cmd.Parameters.AddWithValue("@lname", _lname);
cmd.Parameters.AddWithValue("@age" , _age );

cmd.ExecuteNonQuery();
}

Nish Nishant 7-Feb-11 19:04pm

Donald, check out my updated answer for a more detailed code example.

d.allen101 7-Feb-11 18:58pm

but you're saying there's no need for this? would it make more sense to use the flag? if so why?

Nish Nishant 7-Feb-11 19:00pm

If the number and type of your parameters change, then you do need to re-add them each time after calling Clear. If they don't, then you only need to add the parameters once (using the flag), although you still need to set the values each time you call the method, since those change during each call.

d.allen101 7-Feb-11 19:04pm

i'm not understanding...would look like this:
private void Foo(string _fname, string _lname, string _age)
{
string sql = ". . .";
cmd.CommandText = sql;

cmd.Prepare();

if( !flag)
{
cmd.Parameters.AddWithValue("@fname", _fname);
cmd.Parameters.AddWithValue("@lname", _lname);
cmd.Parameters.AddWithValue("@age" , _age );

flag = true;
}

cmd.ExecuteNonQuery();
}

if so, then how and where would I be able to pass new data to it?

Nish Nishant 7-Feb-11 19:06pm

Check my updated answer. I've shown how to set the values the 2nd and later times.

d.allen101 7-Feb-11 19:07pm

YESSSS! i just checked your updated answer and I understand! cool! plus that's the correct way to do it! i shouldn't be clearing, right?

Nish Nishant 7-Feb-11 19:07pm

Yes, this is the better way to do it. (Clear has its uses, but not in your scenario).

d.allen101 7-Feb-11 19:14pm

thanks again Nishant! I'm REALLY THANKFUL! one more thing, when I was asking about using multiple command objs on each thread to handle my inserts and updates I forgot to ask you why do I get an error message: "DataReader associated with this connection must be closed first"?

Nish Nishant 7-Feb-11 20:01pm

There is something called MARS (Multiple Active Result Sets) that allows the same connection object to return multiple result-sets at the same time. MySQL does not support MARS, which is why you get that error.

d.allen101 7-Feb-11 20:55pm

yeah, i read that so what can i do to get around it? do you know of any solutions?

JOAT-MON 7-Feb-11 22:16pm

Create multiple connection objects to use with your multiple threads. Each thread will need its own connection if they are operating simultaniously. Just be sure to close/dispose them when they are not in use.

d.allen101 7-Feb-11 22:20pm

so it's alright to have multiple connection objects? but won't that consume a lot resources? by the way, i don't close out the connection because i'm updating and inserting approximately 4 or 5 times per second...

d.allen101 7-Feb-11 22:21pm

so, should each thread have it's own connection and command object?

JOAT-MON 7-Feb-11 22:26pm

Yep. It is the only way I can think of to circumvent the error you were getting and accomplish the simultaneous access/update/insert you are after.

d.allen101 7-Feb-11 22:30pm

ok...even if that gets around the error is that the correct way to manage the the threads ie (individual command and connection objected per thread)? also, should i still be opening and closing each connection if if performing inserts and updates 4 or 5 times per/sec ?

JOAT-MON 8-Feb-11 0:20am

Not certain, since I do not use MySql, but you could try using one connection for access and another for insert/update since they are not returning result sets. This would get you down to two connections. I do not know how much other stuff is going on in your program, but in mine I still close the connection, though not dispose, if I am doing anything else between database calls, in case the program throws an exception or something, I do not want to leave my connections orphaned.