No DB Change on ExecuteScalar.... not sure why.

No DB Change on ExecuteScalar.... not sure why.

Postby hayedid » Thu 27 May 2010 20:36

Hi. I'm making an application where I have to execute two parameterized calls to ExecuteScalar. One of them works (I can see a comment get added to my comments table), one does not (the databased does not change).

I'm working in C# and stepping through the code, the ExecuteScalar command seems to execute and no exceptions are thrown. However, the database does not get changed.

In digging through the documentation, I think this may have something to do with the ParameterCheck value. I tried setting this to TRUE (the default is FALSE), but then devart seems to see my parameters as NULL.

Any ideas?

Posts: 3
Joined: Thu 27 May 2010 20:29

Postby hayedid » Thu 27 May 2010 21:50

I've rounded this down to an apparent bug.

If I executed two parameterized statments using parameters defined by ?, only the first of the two statement ran, but the second one would appear to run but with no error.

If I did the same thing using parameters defined by @identifier, everything started working. Since I like this syntax better anyway, I guess I'm good to go, but it certainly took a long time to figure it out.
Posts: 3
Joined: Thu 27 May 2010 20:29

Postby Shalex » Mon 31 May 2010 12:21

Parameters mentioned with the '?' symbol are unnamed parameters. It means that you need to add all parameters used in your CommandText to the MySqlCommand.Parameters collection in the same order as they were used in your query (CommandText). It is not allowed to use '?' two times in the query but add only one parameter to the collection.

Here is my script:
Code: Select all
CREATE TABLE texttable(
  textColumn TEXT NOT NULL

C# code:
Code: Select all
using (MySqlConnection connection = new MySqlConnection()) {
    connection.ConnectionString = "host=db;port=3309;uid=****;pwd=****;database=test;";
    MySqlCommand command = connection.CreateCommand();
    command.CommandText = "insert into texttable values (?); insert into texttable values (?)";
    command.Parameters.Add("p0", MySqlType.Text).Value = "some text";
    command.Parameters.Add("p1", MySqlType.Text).Value = "some text 2";

We recommend you to use the named parameters (":identifier" or "@identifier"), because they have the following advantages:
a) you do not have to care about the order in which parameters are created;
b) named parameter can appear more than once in query text, but you have to create only one instance of it in Parameters collection.

If there is a bug in dotConnect for MySQL, please tell us how we should modify our sample above to reproduce it. Also please tell us your current version (x.xx.xx) of dotConnect for MySQL (the Tools > MySQL > About menu of Visual Studio).
Devart Team
Posts: 7290
Joined: Thu 14 Aug 2008 12:44

Return to dotConnect for MySQL