a view questions about using your provider

a view questions about using your provider

Postby jlo » Wed 09 Feb 2005 09:31

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
jlo
 
Posts: 7
Joined: Wed 09 Feb 2005 08:18

Postby Serious » Wed 09 Feb 2005 12:31

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();
      }
Serious
 

Postby Guest » Wed 09 Feb 2005 15:03

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

Image

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
Guest
 

Postby Serious » Wed 09 Feb 2005 16:43

Try this
Code: Select all
/// 3. Query - "mandantID" -
                  myCmd.Parameters.Clear(); // <----------------------
                  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();
Serious
 

Postby jlo » Wed 09 Feb 2005 19:05

Hi oleg542,

that´s it!!! :D

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
jlo
 
Posts: 7
Joined: Wed 09 Feb 2005 08:18

Postby Oleg » Thu 10 Feb 2005 16:09

In order that the list of parameters automatically synchronize with the text of SQL in CommandText you need set ParameterCheck property to true.
Oleg
Devart Team
 
Posts: 264
Joined: Thu 28 Oct 2004 13:56


Return to dotConnect for MySQL