Creating and then working with parameters in queries

Question

I am writing a small XAML/C#/MySQL database and wanted to create a query that accepts parameters. However, the test query that I am setting up fails when I try to create it

    var con = new MySqlConnection(ClsVariables.StrDb);
    con.Open();
    var command = new MySqlCommand("", con);

command =
    new MySqlCommand("Create View r2_Add_Edit_View as SELECT era.contact_id, era.n_family FROM era WHERE era.contact_id = @ContactID", con)            command.ExecuteNonQuery();
    con.Close();

When I change the @ContactID to a specific number - it works fine.

After that I will need to create a recordset, and pass the parameter to it (but I can ask that in a secondary question).

Thanks as always.

Please post full code

Vimal CK
– Vimal CK

2014-05-07 16:27:58 +00:00
Commented May 7, 2014 at 16:27 — Vimal CK
– Vimal CK, Commented May 7, 2014 at 16:27

Yair Nevet · Accepted Answer · 2014-05-07 17:03:17Z

1

When I change the @ContactID to a specific number - it works fine.

Well, you don't pass the parameter, so just add it to your command:

public class MySqlConnector
{
    private readonly string _connString;

    public MySqlConnector(string connString)
    {
        _connString = connString;
    }

    private MySqlCommand _command;
    const string Sql = "Create View r2_Add_Edit_View as SELECT era.contact_id, era.n_family FROM era WHERE era.contact_id = @ContactID";

    public void CreateView(int contactId)
    {
        if(_command == null)
        {
            _command = new MySqlCommand();

            _command.CommandText = Sql;
            _command.Connection = _connString;
        }
        _command.Parameters.AddWithValue("@ContactID", contactId);
        _command.ExecuteNonQuery();
        _command.Close();
    }
}

edited May 7, 2014 at 17:03

answered May 7, 2014 at 16:28

Yair Nevet

13.1k17 gold badges71 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

AndyDB Over a year ago

Thanks Yair - I want it to be something that I can call from within the C# app, so don't really want to create a new query each time.

Gigi Over a year ago

You don't have to create a new query. You can cache the command object in the class and just set different parameters each time.

AndyDB Over a year ago

Sorry I'm a bit new to this - so not quite sure how to do that. Also wouldn't that mean that the query won't be compiled.

Yair Nevet Over a year ago

@AndyDB See my update with a caching support for the command object.

AndyDB Over a year ago

Thanks Yari - but when I run that code, I get an exception error on the ExecuteNonQuery: An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll

|

malkam · Accepted Answer · 2014-05-07 16:33:29Z

0

Try to use Command AddParameters method.

command.Parameters.Add("@ContactID", SqlDbType.Int);

command.Parameters["@ContactID"].Value = value;

answered May 7, 2014 at 16:33

malkam

2,3551 gold badge18 silver badges19 bronze badges

3 Comments

AndyDB Over a year ago

I'm a bit new to this, could you give me a bit more help please.

Gigi Over a year ago

Google says this link should help you.

AndyDB Over a year ago

Thanks Gigi - Whilst that is interesting, it's not what I'm after here.

Collectives™ on Stack Overflow

Creating and then working with parameters in queries

2 Answers 2

8 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related