Mysql query returning null value and 0 results

Question

i have an issue that is driving me crazy!

    query = "SELECT Formula  FROM filterPrice where idCode='" & txtCodigo.Text & "' and (FilterData<='" & cant & "')"
Dim selectCommand As New MySqlCommand(query, Conexion.conn)

        priceSelected = Conversions.ToString(selectCommand.ExecuteScalar())
        Conexion.Desconecta()
        Conexion.conn.Dispose()

but this return nothing, any hint?

if i delete all after AND it Works fie and returns a value, but i dunno why the complete query doesnt work — , Oct 10 '15 at 10:08
Please refer [SQL Injection] (http://stackoverflow.com/questions/601300/what-is-sql-injection) first — Mahadev, Oct 10 '15 at 10:08
Did you try and use breakpoint to check if you are correct values or not to query and you have related data in db ? any specific error — Mahadev, Oct 10 '15 at 10:10
there is no error, just gives nothing, the variable priceselected keeps empty — , Oct 10 '15 at 10:12
as i mentioned, if i just use query = "SELECT Formula FROM filterPrice where idCode='" & txtCodigo.Text & "'"Dim selectCommand As New MySqlCommand(query, Conexion.conn) priceSelected = Conversions.ToString(selectCommand.ExecuteScalar()) Conexion.Desconecta() this gives me a vaule, but if i try to add the and and the other condition it doesnt produce a value — , Oct 10 '15 at 10:14
Add a Console.WriteLine(query) just before creating the command and add the resulting text in your output window to your question above. — Steve, Oct 10 '15 at 10:14
Try this, same query but changed for security : query = "SELECT Formula FROM filterPrice where idCode=@IdCode and (FilterData<=@Cant)" query.Parameters.AddWithValue("@IdCode", txtCodigo.Text) query.Parameters.AddWithValue("@Cant", cant) Dim selectCommand As New MySqlCommand(query, Conexion.conn) priceSelected = Conversions.ToString(selectCommand.ExecuteScalar()) Conexion.Desconecta() Conexion.conn.Dispose() If you have a valid data, then the query should work. Btw what's datatype for `Cant` ? — Mahadev, Oct 10 '15 at 10:15
I think your query should be like this `query = "SELECT Formula FROM filterPrice where idCode='" & txtCodigo.Text & "' and (FilterData like %'" & cant & "'%)"` — Sankar, Oct 10 '15 at 10:23
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%'11'%)' at line 1 — , Oct 10 '15 at 10:28
@SankarRaj : Its just a suggestion, but kindly use `Parameterised queries` or `Stored Procedures` for security and better performance. — Mahadev, Oct 10 '15 at 10:30
It is so difficult to add the values of the variables used in this query? — Steve, Oct 10 '15 at 10:53

score 1 · Accepted Answer · answered Oct 10 '15 at 10:29

This is a detailed example for Data retrieval using Parameterised queries :

Dim con as new MySQLConnection("Data Source=<server name>;Initial Catalog=<Db Name>; Integrated Security=True;")
Dim cmdSelectData as new MySQLCommand("SELECT Formula  FROM filterPrice where idCode=@IdCode and FilterData<=@Cant", con)
cmdSelectData.Parameters.AddWithValue("@IdCode", txtCodigo.Text)
cmdSelectData.Parameters.AddWithValue("@Cant", cant)
If not con.State=ConnectionState.Open then con.open()
priceSelected=cmdSelectData.ExecuteScalar()
con.Close()

This is a more secure way with databases. Hope it helps.

Mysql query returning null value and 0 results

1 Answers1