0

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.

AndyDB
  • 413
  • 6
  • 22

2 Answers2

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();
    }
}
Yair Nevet
  • 12,725
  • 14
  • 66
  • 108
  • 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. – AndyDB May 07 '14 at 16:36
  • 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. – Gigi May 07 '14 at 16:37
  • 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. – AndyDB May 07 '14 at 16:39
  • @AndyDB See my update with a caching support for the `command` object. – Yair Nevet May 07 '14 at 16:44
  • 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 – AndyDB May 07 '14 at 16:56
  • You marked the answer as a good one, that's work for you now? – Yair Nevet May 07 '14 at 16:58
  • Sorry Yari - But I've just spotted that it creates the query, but with `.`contact_id` = '10'). Doesn't that defeat the object as I could have set that up from the start? – AndyDB May 07 '14 at 17:03
  • @AndyDB Please check out my new code in the answer. – Yair Nevet May 07 '14 at 17:03
0

Try to use Command AddParameters method.

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

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

malkam
  • 2,337
  • 1
  • 14
  • 17