MySQL Query Doesn't Return Value, But Exists In DB

Question

I have a funky problem.. I have this code:

using (MySqlCommand cmd = new MySqlCommand())
{
    cmd.Connection = this.connection;
    cmd.CommandText = "SELECT id FROM links WHERE url LIKE '@url'";
    cmd.Parameters.AddWithValue("@url", parent_url.Trim());
    cmd.Prepare();

    using (MySqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            parent_id = reader.GetInt32(0);
        }
    }
}

What my problem is that.. sometimes it doesn't return a value when there is a an answer in the database. That's very strange. Also I have noticed that some items in the database have a space behind it so this is why I made this:

    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = this.connection;
        cmd.CommandText = "SELECT id FROM links WHERE url LIKE '@url'";
        cmd.Parameters.AddWithValue("@url", parent_url.Trim());
        cmd.Prepare();

        using (MySqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                parent_id = reader.GetInt32(0);
            }
        }
    }

    if(parent_id == 0)
    {
        using (MySqlCommand cmd = new MySqlCommand())
        {
            cmd.Connection = this.connection;
            cmd.CommandText = "SELECT id FROM links WHERE url LIKE '@url '";
            cmd.Parameters.AddWithValue("@url", parent_url.Trim());
            cmd.Prepare();

            using (MySqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    parent_id = reader.GetInt32(0);
                }
            }
        }
    }

But it still doesn't help me some times.. even if the item doesn't have a space after it. So yea.. can you guys please give me some advice.. what should I try.

What kind of column is url in the database table?

Steve
– Steve

2013-12-10 17:57:24 +00:00
Commented Dec 10, 2013 at 17:57 — Steve
– Steve, Commented Dec 10, 2013 at 17:57

Damith · Accepted Answer · 2013-12-10 18:09:47Z

2

"SELECT id FROM links WHERE url LIKE @url";

remove the '' from the parameter then only it will consider as parameter

otherwise it will be only a string value equal to @url

and also if you need to search given string on database column use % like below

cmd.Parameters.AddWithValue("@url", "%" + parent_url.Trim()+ "%");

UPDATE:

you can use trim function as below

"SELECT id FROM links WHERE trim(url) LIKE @url";

edited Dec 10, 2013 at 18:09

answered Dec 10, 2013 at 17:55

Damith

63.2k13 gold badges106 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jason Over a year ago

Well, I tried it and it still doesn't find some of the items.

Jason Over a year ago

I can't use % because there will be items similar to to the item I'm looking for. I could use parent_url.Trim() + "%", but it would give me tons of other answers as well.

Jason Over a year ago

Yes, I'm trying RTRIM right now. It will take some time because my database is huge

Jason Over a year ago

Okay well.. this trim(url) method is extremely slow. It should be slow?

Steve · Accepted Answer · 2013-12-10 18:14:33Z

0

Your first problem is the quoting around the parameter placeholder. In this way the parsing engine cannot link the placeholder to the parameter.

So your easy fix is

"SELECT id FROM links WHERE url LIKE @url";

But this is not enough to resolve the problem of the white space at the end of your url column.

You could fix the extra space problem using this script with MySql Workbench to remove the blank spaces at the end of the URL column once and forever.

update links
set url = rtrim(url) 
where url like '% '

NOT TESTED, keep a backup at hand please.

edited Dec 10, 2013 at 18:14

answered Dec 10, 2013 at 18:02

Steve

217k22 gold badges242 silver badges296 bronze badges

2 Comments

Jason Over a year ago

I'm using MySQL, I'll try to change my post's title

Steve Over a year ago

Not tested but the RTRIM function exists also in MySql

Collectives™ on Stack Overflow

MySQL Query Doesn't Return Value, But Exists In DB

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related