MySQL Connector: parameters not being added

Question

Hey all!

Looking at my query log for MySQL, I see my parameters aren't being added. Here's my code:

MySqlConnection conn = new MySqlConnection(ApplicationVariables.ConnectionString());
                MySqlCommand com = new MySqlCommand();

                try
                {
                    conn.Open();
                    com.Connection = conn;
                    com.CommandText = String.Format(@"SELECT COUNT(*) AS totalViews
                                                      FROM pr_postreleaseviewslog AS prvl
                                                      WHERE prvl.dateCreated BETWEEN (@startDate) AND (@endDate) AND prvl.postreleaseID IN ({0})"
                                                      , ids);
                    com.CommandType = CommandType.Text;
                    com.Parameters.Add(new MySqlParameter("@startDate", thisCampaign.Startdate));
                    com.Parameters.Add(new MySqlParameter("@endDate", endDate));

                    numViews = Convert.ToInt32(com.ExecuteScalar());
                }
                catch (Exception ex)
                {
                }
                finally
                {
                    conn.Dispose();
                    com.Dispose();
                }

Looking at the query log, I see this:

SELECT COUNT(*) AS totalViews
                                                      FROM pr_postreleaseviewslog AS prvl
                                                      WHERE prvl.dateCreated BETWEEN (@startDate) AND (@endDate) AND prvl.postreleaseID IN (1,2)

I've used the MySQL .NET connector on countless projects (I actually have a base class that takes care of opening these connections, and closing them with transactions, etc.). However, I took over this application, and here I am now.

Thanks for the help!

Answer 1

Try like this.

mySqlCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5);
mySqlCommand.Parameters["@CustomerID"].Value = "T1COM";

Answer 2

Some SQL clients, especially for MySql use "?" instead of "@".

Answer 3

com.CommandText = String.Format(@"SELECT COUNT(*) AS totalViews
                                                      FROM pr_postreleaseviewslog AS prvl
                                                      WHERE prvl.dateCreated BETWEEN (@startDate) AND (@endDate) AND prvl.postreleaseID IN ({0})"
                                                      , ids);
                    com.CommandType = CommandType.Text;
                    com.Parameters.Add("@startDate", MySqlDbType.DateTime).Value = thisCampaign.Startdate;
                    com.Parameters.Add("@endDate", MySqlDbType.DateTime).Value = endDate;
                    numViews = Convert.ToInt32(com.ExecuteScalar());

Tried that, but to no avail. Also tried it with question marks. My query keeps coming back as 0. I've used String.Format and put the dates (formatted to MySQL) in, and the query works as expected. But for some reason, when using parameters it still doesn't work.

I'm also using SubSonic on this site, and am using MySQL .NET Connector 5.1.4.0.