a view questions about using your provider

[jlo]

He Guys,

I just trie out the MySQLDirect .NET Provider 2.70 and there are many converting problems. To date I use the ByteFX-DataProvider and the new .NET-Connector 1.04 from mySQL.com. These providers works great. Here are a view questions about using your provider:

1:

Using the MySQLDirect .NET-Provider in my application does not work, e. g. with the following ConnectionString. The other Data-Providers do so... Is there any Idea, why...

Code: Select all

DatabaseConnectString = "Persist Security Info=False;Database='';Data Source=127.0.0.1;Connect Timeout=30;user id=root;password='';"

2:

Starting the VS.NET IDE provides an ErrorMessage "Unable to find view menu". I´ve found the thread about this issue and get the anwser, that this error doesn´t match. I hope you are rigth!!!!

3:

Another problem is using parameters. My following Code works great so far...

Code: Select all

/// Query.Builder
result = "";
result += "INSERT INTO adrKontakt ( ";
result += "AdressID, BenutzerID, Suchbegriff, Anrede, Titel, Vorname, Name, Telefon, ";
result += "Telefax, Mobil, eMail, Briefanrede ";
result += ") VALUES ( ";
result += "@a01, @a02, @a03, @a04, @a05, @a06, @a07, @a08, @a09, @a10, @a11, @a12"; 
result += " )"; 

Code: Select all

	
MySqlParameter a01 = new MySqlParameter("@a01", MySqlType.Text);
MySqlParameter a02 = new MySqlParameter("@a02", MySqlType.Text);
MySqlParameter a03 = new MySqlParameter("@a03", MySqlType.Text);
...
...
a01.Value = Public.MyFocusID;
a02.Value = Public.MyUserID;
a03.Value = this.textBox2.Text + ", " + this.textBox1.Text;
...				
...
myCmd.Parameters.Add(a01);
myCmd.Parameters.Add(a02);
myCmd.Parameters.Add(a03);
...				
myCmd.CommandText = GetCommand(1);
myCmd.ExecuteNonQuery();

With MySqlDirect .NET 2.70 there are problems. Could you give me a code snippet to convert my code to your conventions...


thx for any answer
jlo - germany

[Serious]

1. Try this code:

Code: Select all

DatabaseConnectString = "Data Source=127.0.0.1;Connect Timeout=30;user id=root;"

3. Please describe which part of code does not work. Here ia an example of correct code:

Code: Select all

      MySqlConnection connection = new MySqlConnection("host=localhost;database=test;user id=root;");
      MySqlCommand cmd = new CoreLab.MySql.MySqlCommand("insert into dept (dname,loc) values (@p1,@p2)", connection);

      cmd.Parameters.Add("@p1",MySqlType.Text);
      cmd.Parameters["@p1"].Value = 1;
      cmd.Parameters.Add("@p2",MySqlType.Text);
      cmd.Parameters["@p2"].Value = "loc1";
      connection.Open();
      try
      {
        cmd.ExecuteNonQuery();
      }
      finally
      {
        connection.Close();
      }

[Guest]

Hi Oleg,

thx for answering quickly.

1: DatabaseConnectString

If I delete the "Persist Security Info=False"-Statement from the DatabaseConnectString, the connection works fine!!!???


2: Parameters

Here is my complete code in this case. I´m using InnoDB-Tables for a correct use of Transactions...

Code: Select all

MySqlCommand myCmd = new MySqlCommand();
				try
				{				
					myCmd.Connection = DBBau._MySQLConnect;
					MySqlDataReader myReader;
					myCmd.Connection.Open();

					myCmd.CommandText = "USE " + Public.DatabaseName;
					myCmd.ExecuteNonQuery();
					myCmd.CommandText = "SET AUTOCOMMIT=0";
					myCmd.ExecuteNonQuery();
					myCmd.CommandText = "START TRANSACTION";
					myCmd.ExecuteNonQuery();

					try
					{
						/// 1. Query
						myCmd.CommandText = "";
						myCmd.CommandText += "INSERT INTO adresse (Kategorie, Suchbegriff, Firma1, ";
						myCmd.CommandText += "Strasse, Land, Plz, Ort, Telefon1, Telefax) ";
						myCmd.CommandText += "VALUES ('Mandant', '" + textBox1.Text + "', '"; 
						myCmd.CommandText += textBox1.Text + "', '" + textBox2.Text + "', '";
						myCmd.CommandText += textBox3.Text + "', '" + textBox4.Text + "', '";
						myCmd.CommandText += textBox5.Text + "', '" + textBox6.Text + "', '";
						myCmd.CommandText += textBox7.Text + "')";
						myCmd.ExecuteNonQuery();

						/// note Last_ID 
						myCmd.CommandText = "";
						myCmd.CommandText += "SELECT LAST_INSERT_ID()";
						myReader = myCmd.ExecuteReader();				
						while ( myReader.Read() ) {	adressID = myReader.GetValue(0).ToString(); }
						myReader.Close();

						/// 2. Query - add "adressID" with Parameter
						myCmd.CommandText = "";
						MySqlParameter p1 = new MySqlParameter("@p1", MySqlType.BigInt);
						MySqlParameter p2 = new MySqlParameter("@p2", MySqlType.Text);
						p1.Value = adressID;
						p2.Value = comboBox1.SelectedText;
						myCmd.Parameters.Add(p1);
						myCmd.Parameters.Add(p2);
						myCmd.CommandText += "INSERT INTO mandant (AdressID, Waehrung) VALUES (@p1, @p2)";
						myCmd.ExecuteNonQuery();
				
						/// note Last_ID()
						myCmd.CommandText = "";
						myCmd.CommandText += "SELECT LAST_INSERT_ID()";
						myReader = myCmd.ExecuteReader();				
						while ( myReader.Read() ) {	clientID = myReader.GetValue(0).ToString(); }
						myReader.Close();

						/// 3. Query - "mandantID" - 
						myCmd.CommandText = "";
						MySqlParameter p3 = new MySqlParameter("@p3", MySqlType.BigInt);
						p3.Value = clientID;
						myCmd.Parameters.Add(p3);
						myCmd.CommandText += "INSERT INTO jahrgang (MandantID, Start, Ende, Ear, Fibu, ";
						myCmd.CommandText += "Soll, Ist, Status, Anzeige, KRahmen, Archiv) ";
						myCmd.CommandText += "VALUES (@p3, '" + _myLib.Date2Sql(dateTimePicker1.Value);
						myCmd.CommandText += "', '" + _myLib.Date2Sql(dateTimePicker2.Value);
						myCmd.CommandText += "', 0, 1, 1, 0, 1, 'nicht abgeschlossen', '', 0)";


>>>>>> ERRORMESSAGE AT THIS LINE  <<<<<<<<<<< 
--------------------------------------------------------------------

myCmd.ExecuteNonQuery(); 

--------------------------------------------------------------------
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<






						myCmd.CommandText = "COMMIT";
						 myCmd.ExecuteNonQuery(); 

						result = true;
					}
					catch( Exception ex )
					{
						_progInfo.Fehler(ex);
						myCmd.CommandText = "ROLLBACK";
						myCmd.ExecuteNonQuery();					
					}

[/code]

= ErrorMessage at this line



What I did not understand is, that at this codeline the provider misses the "@p1"-Parameter.Value and not the "@p3"-Parameter.Value. The Statement before ( @p1, @p2 ) was executed without error....

thx for answering
jlo - germany

[Serious]

Try this

Code: Select all

/// 3. Query - "mandantID" - 
                  myCmd.Parameters.Clear(); // >>>>> ERRORMESSAGE AT THIS LINE  <<<<<<<<<<< 
-------------------------------------------------------------------- 

myCmd.ExecuteNonQuery(); 

-------------------------------------------------------------------- 
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 


                  myCmd.CommandText = "COMMIT"; 
                   myCmd.ExecuteNonQuery();

[jlo]

Hi oleg542,

that´s it!!!

Obviously the Paramater-List has to be clear before a new Execute-Command could be set...

But you have to do this all the time, after every Command...

thx
jlo - germany

[Oleg]

In order that the list of parameters automatically synchronize with the text of SQL in CommandText you need set ParameterCheck property to true.