Help with SQL Syntax for LIKE

I'm trying to select UserID, FirstName, SecondName, PicturePath(from Pictures table) LIKE FirstName (string search = textbox.text)

OdbcCommand("SELECT UserID, FirstName, SecondNa开发者_开发知识库me, p.PicturePath FROM User LEFT JOIN Pictures p ON p.UserID = u.UserID WHERE FirstName LIKE '%" + search + "%' ORDER BY UserID DESC", cn))

My syntax is way messed up.

My table structure:

---

If you're going to use correlation names at all, you should usually use them throughout. It helps the programmers, even if the database doesn't need it.

SELECT u.UserID, u.FirstName, u.SecondName, p.PicturePath 
FROM User u
LEFT JOIN Pictures p ON (p.UserID = u.UserID) 
WHERE u.FirstName LIKE '%" + search + "%' ORDER BY u.UserID DESC

Reformat that for a single line, quote it, and plug it in. It might overcome the syntax problem. Don't know whether it will give you the result you're looking for.

When you have trouble getting something like this to work, start with the simplest, and build up from that. I'd go in this order, myself. (If you put a gun to my head an made me concatenate SQL.)

"SELECT u.UserID, u.FirstName, u.SecondName
FROM User u;"
--
"SELECT u.UserID, u.FirstName, u.SecondName
FROM User u
WHERE u.FirstName LIKE '%Cat%';"
--
"SELECT u.UserID, u.FirstName, u.SecondName
FROM User u
WHERE u.FirstName LIKE " + "'%Cat%';"
--
"SELECT u.UserID, u.FirstName, u.SecondName
FROM User u
WHERE u.FirstName LIKE '%" + "Cat" + "%';"
--
SELECT u.UserID, u.FirstName, u.SecondName
FROM User u
WHERE u.FirstName LIKE '%" + search + "%';"
--

and so on.

---

OdbcCommand("SELECT UserID, FirstName, SecondName, p.PicturePath FROM User LEFT JOIN Pictures p ON p.UserID = u.UserID WHERE FirstName LIKE '%" + search + "%' ORDER BY UserID DESC", cn))

---

use % (as dos *), or _ (as dos ?)

quick look, but you may need to reverse your left join.

//edit: and naturally, your search-strings need to be quoted //end edit

regards. /t

---

First off you should use a parameters so you don't need to worry about SQL injection. So I would do something like this:

        using (System.Data.Odbc.OdbcConnection connect = new System.Data.Odbc.OdbcConnection("Connection String"))
        {
            using (System.Data.Odbc.OdbcCommand command = connect.CreateCommand())
            {
                string sql = "SELECT u.UserID, u.FirstName, u.SecondName, p.PicturePath FROM User u " +
                    "LEFT JOIN Pictures p ON p.UserID = u.UserID WHERE u.FirstName LIKE '%' + @search + '%' " +
                    "ORDER BY u.UserID DESC";
                command.CommandText = sql;
                command.CommandType = System.Data.CommandType.Text;
                command.Parameters.AddWithValue("@search", searchValue);

                using (System.Data.Odbc.OdbcDataReader dr = command.ExecuteReader())
                {
                    //Do what you need here
                }
            }
        }

Hope this helps!