Parameters doesn't work.

jmerchan · Post by **jmerchan** » Thu 01 Apr 2010 08:15

tmpCalle="Cardedeu, 9"

In the table only insert "Cardedeu," the "9" desapear and with the rest of the fields the same.

I have test this in the last version too.

Using ComandoSQL As New MySqlCommand("REPLACE INTO utilidades.ofertas ( Referencia, Tipo, Calle, Dormitorios, Planta, MetrosCuadrados, PrecioM2, Precio, Estado, Observaciones, Latitud, Longitud ) VALUES " + _
"( :Referencia, :Tipo, :Calle, :Dormitorios, :Planta, :MetrosCuadrados, :PrecioM2, :Precio, :Estado, :Observaciones, :Latitud, :Longitud )", Acceso.Conexion)
' Añadimos parámetros.
ComandoSQL.Parameters.Add(":Referencia", MySqlType.Char, 25).Value = tmpReferencia
ComandoSQL.Parameters.Add(":Tipo", MySqlType.VarChar, 40).Value = tmpTipo
ComandoSQL.Parameters.Add(":Calle", MySqlType.Char, 25).Value = tmpCalle
ComandoSQL.Parameters.Add(":Dormitorios", MySqlType.VarChar, 20).Value = tmpDormitorios
ComandoSQL.Parameters.Add(":Planta", MySqlType.VarChar, 20).Value = tmpPlanta
ComandoSQL.Parameters.Add(":MetrosCuadrados", MySqlType.VarChar, 10).Value = tmpMetrosCuadrados
ComandoSQL.Parameters.Add(":PrecioM2", MySqlType.VarChar, 10).Value = tmpPrecioM2
ComandoSQL.Parameters.Add(":Precio", MySqlType.VarChar, 25).Value = tmpPrecio
ComandoSQL.Parameters.Add(":Estado", MySqlType.Text).Value = tmpEstado
ComandoSQL.Parameters.Add(":Observaciones", MySqlType.Text).Value = tmpObservaciones
ComandoSQL.Parameters.Add(":Latitud", MySqlType.Float).Value = tmpLatitud
ComandoSQL.Parameters.Add(":Longitud", MySqlType.Float).Value = tmpLongitud

FilasAfectadas = ComandoSQL.ExecuteNonQuery()
End Using

Shalex · Post by **Shalex** » Fri 02 Apr 2010 12:25

Please give us the following information:
1) are there any errors when you are executing your command?
2) please post here the DDL script of your ofertas table. Does the Calle column have sufficient size for storing 11 symbols ("Cardedeu, 9")?
3) tell us your connection string to determine the mode of your connection (direct, client, ssh, ssl, etc);
4) the version of your MySQL server;
5) the exact version (x.xx.xx) of your dotConnect for MySQL. You can find it via the Tools | MySQL | About menu of Visual Studio.

Here is the Using Parameters article in our documentation: http://www.devart.com/dotconnect/mysql/ ... eters.html.

jmerchan · Post by **jmerchan** » Tue 06 Apr 2010 05:34

1) No
2) CREATE TABLE `ofertas` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Referencia` char(25) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`Tipo` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`Calle` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`Dormitorios` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`Planta` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`MetrosCuadrados` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`PrecioM2` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`Precio` varchar(25) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`Estado` text COLLATE utf8_unicode_ci NOT NULL,
`Observaciones` text COLLATE utf8_unicode_ci NOT NULL,
`Latitud` float(9,3) DEFAULT '0.000',
`Longitud` float(9,3) DEFAULT '0.000',
PRIMARY KEY (`ID`),
UNIQUE KEY `Referencia` (`Referencia`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Yes, as you can see the colums have enough space.
If I execute the sqltext in phpmyadmin works perfect.

3) User Id=root;Password=xxx;Host=192.168.1.1;Database=utilidades;Persist Security Info=True

4) * Servidor: localhost via TCP/IP
* Versión del servidor: 5.1.32-community-log
* Versión del protocolo: 10
* Usuario: root@localhost
* Juegos de caracteres de MySQL: UTF-8 Unicode (utf8)

5) 5.70.120.0

Shalex · Post by **Shalex** » Tue 06 Apr 2010 12:59

I cannot reproduce the issue at the moment.
1. Please install DBMonitor and check the query which is executed by dotConnect for MySQL against your database. Does your :Calle parameter contain correct ("Cardedeu, 9") value in the View -> Parameters window of DBMonitor? The Using dbMonitor article is here: http://www.devart.com/dotconnect/mysql/ ... nitor.html.
2. Tell us the type, version, and capacity of your operating systems where the MySQL server and your client applications reside.

jmerchan · Post by **jmerchan** » Wed 07 Apr 2010 06:33

1) DbMonitor show all ok, the paremeters are perfect, see image for prove. In the second image you can see the result in mysql

2) My developer machine is a windows 7 x86 in spanish, 4 cores, 4Gb and working with vs2008.
The server for probes is a windows 2008 x64 spanish, 8 cores, 8Gb ram.

Image 1:
http://www.catsa.com/zzz/1.png
Image 2:
http://www.catsa.com/zzz/2.png

jmerchan · Post by **jmerchan** » Wed 07 Apr 2010 08:37

I Have seen that my account have been expired and won't can download a newer version when you found the mistake then

I have found a solution, poor but a solution. I have made a few replaces and the same code work perfect in sqlserver with default sqlclient.

Thanks for your time.

Shalex · Post by **Shalex** » Thu 08 Apr 2010 12:15

1. Please contact our sales department (sales*devart*com) to check the status of your subscription.
2. Why did you decide that the truncation is caused by our provider? Maybe, values of particular columns are cut because of the settings of the grid you are using in your application. Try checking the content of your ofertas table with standard MySQL Command Line Client or with Devart dbForge Studio for MySQL.
3. Please make sure that there are no triggers in your database that can modify data before inserting into ofertas.

jmerchan · Post by **jmerchan** » Fri 09 Apr 2010 07:14

I know that the problem is in your conector because I have downloaded Connector-Net 6.2.2 from mysql website and work perfect with the same code in the same table with the same client in the same server.

I'm using your conector for 5 years and I have test it in many situations and when I say it doesn't work is because I'm sure.

May be is the language of the so?.

Sorry for my English.

Shalex · Post by **Shalex** » Mon 12 Apr 2010 11:37

We have sent a test project to your e-mail address. If you don't receive it, please contact me (alexsh*devart*com) and provide another e-mail address that will not reject attachments. Please run this project in your environment and notify us about the results.

hayedid · Post by **hayedid** » Thu 27 May 2010 20:38

The documentation indicated that although your SQL statement may have :identifier in it, when adding as a parameter, it should be "identifier" without the colon.

Shalex · Post by **Shalex** » Mon 31 May 2010 12:20

Hayedid, you are right. The mentioned article is here. Please specify your question.