[RESOLVED] MySQL query AND Statement Question..

Hi everyone!

I Have a question.. what is my error in the following query?

Code:

---

Dim sql As MySqlCommand = New MySqlCommand("SELECT * FROM idtime WHERE user_name = '" & ComboBox1.Text & "AND register_date = " & Convert.ToDateTime(ComboBox2.Text).ToString("yyyy-MM-dd") & "'", con)

---

In my combobox1 have a user and in the other combobox have a date..

If I use a individual query for each one work:

Code:

---

Dim sql As MySqlCommand = New MySqlCommand("SELECT * FROM idtime WHERE user_name = '" & ComboBox1.Text & "'", con)

---

But the problem is maybe in the "AND" statement..

Help!!

Best Regards!! :)

Hi,

There are a few things wrong there but the best thing you can do right now is to put your SQL String Concatenation into a message box and see what you are actually sending to the Database. You should then see what the error is.

Once you understand where you went wrong you then need to learn how to use Parameters in the construction of your SQL Queries to avoid these issues in the future. Just have a look around the forum for examples of using parameters since there are loads of examples.

Hope that helps.

Cheers,

Ian

Hi Ian, good afternoon!!

mmm I'm going to concatenate that user + register_date in a textbox ..
but.. What would be the query to select this concatenated value in my SQL table?

Thanks for the tips ..

Regards!

Hi,

Quote:

---

mmm I'm going to concatenate that user + register_date in a textbox ..

---

No your not since that does not actually make sense?

This is what your SQL query should look like and I will leave it to you to work out how to add the Parameter values to the SQL Query via the MySqlCommand Class.

Code:

---

"SELECT * FROM idtime WHERE user_name = @user_name AND register_date = @register_date"

---

Good luck, and like I said, have a look around the Forum for examples of adding Parameters to your query.

Cheers,

Ian

Yuuujuuuuuuuu!!

Hi Ian,

Thanks for your tips, it's the definitive query:

Code:

---

Dim sql As MySqlCommand = New MySqlCommand("SELECT * FROM idtime WHERE user_name = @user_name AND register_date = @register_date", con)


        sql.Parameters.AddWithValue("user_name", ComboBox1.Text)
        sql.Parameters.AddWithValue("register_date", Convert.ToDateTime(ComboBox2.Text).ToString("yyyy-MM-dd"))

---

Works Great!
Thanks a lot men!
:)

the reason your original query failed was because you had a misplaced quote mark...
SELECT * FROM idtime WHERE user_name = 'some nameAND register_date = 2014-08-27' -- the misplaced quote mark caused the entire thing to be trereated as a full string literal. Also, there was no space before the AND, causing it to slam into the text before it.

when it should have looked like this:
SELECT * FROM idtime WHERE user_name = 'some name' AND register_date = '2014-08-27'
All quote marks and spaces in the correct place.


That said... parameters is hands-down the best way to go since you then avoid things like what you experienced.

-tg