mysql syntax error

Question

I get an error unsure how to fix it:

ERROR [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.5.9]Column 'UserID' in field list is ambiguous

My code:

using System.Data.Odbc;

public partial class Search : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    private void PopulateWallPosts(string search)
    {

        using (OdbcConnection cn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=gymwebsite2; User=root; Password=commando;"))
        {
            cn.Open();
            using (OdbcCommand cmd = new 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))
            {
                using (OdbcDataReader reader = cmd.ExecuteReader())
                {
                    test1.Controls.Clear();

                    while (reader.Read())
                    {

                        System.Web.UI.HtmlControls.HtmlGenericControl div = new System.Web.UI.HtmlControls.HtmlGenericControl("div");
                        div.Attributes["class"] = "test";


                        div.ID = String.Format("{0}", reader.GetString(0)); //userid
                        string id = Convert.ToString(div.ID);
                        //store the div id as a string
                        Image img = new Image();
                        img.ImageUrl = String.Format("{0}", reader.GetString(3)); //p.picturepath
                        img.AlternateText = "Test image";

                        div.Controls.Add(img);
                        div.Controls.Add(ParseControl(String.Format("&nbsp&nbsp&nbsp;" + "{0} {1}", reader.GetString(1), reader.GetString(2)))); // FirstName, SecondName
                        div.Attributes.Add("onclick", "confirm_delete(" + id + ");");
                        // send the div id to javascript
                        div.Style["clear"] = "both";
                        test1.Controls.Add(div);

                    }
                }
            }
        }
    }
    protected void Button2_Click(object sender, EventArgs e)
    {


        string search = TextBox2.Text;

        PopulateWallPosts(search);

        }
    }

Table Structure: enter image description here

Darin Dimitrov · Accepted Answer · 2011-04-02 14:24:13Z

Try specifying which UserId column you are using in your query:

using (OdbcCommand cmd = new OdbcCommand("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", cn))

Also I would recommend you using the MySQL ADO.NET driver/connector instead of ODBC. And finally use parametrized queries as currently your code is vulnerable to SQL injection with this string concatenation of the search parameter.

So here's how your improved code might look like:

using (var cn = new MySqlConnection("Server=localhost; Database=gymwebsite2; User=root; Password=commando;"))
using (var cmd = cn.CreateCommand())
{
    cn.Open();
    cmd.CommandText = "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";
    cmd.Parameters.AddWithValue("@search", search);
    using (var reader = cmd.ExecuteReader())
    {
        test1.Controls.Clear();
        while (reader.Read())
        {
            ...
        }
    }
}

dagge · Accepted Answer · 2011-04-02 14:16:24Z

1

You need to specify which UserId you're refering to in your query.

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

answered Apr 2, 2011 at 14:16

dagge

1,1559 silver badges14 bronze badges

Comments

Flavio CF Oliveira · Accepted Answer · 2011-04-02 14:22:01Z

0

put a table prefix on your userID query column, specifying the table source

answered Apr 2, 2011 at 14:22

Flavio CF Oliveira

5,28514 gold badges43 silver badges64 bronze badges

Collectives™ on Stack Overflow

mysql syntax error

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related