Is there any proper way to retrieve data from OLEDB connection searched by some specific string value?

Question

I wanted to retrieve a Category from SQL Server using this query. I have data in the database and this query worked well in SQL Server. But when I use it with oledb command, it does not return any data from the server. What's the problem?

public Category GetCategoryByCategoryName(string categoryName)
{
    Category _category = null;

    using (OleDbConnection con = new OleDbConnection(_connectionString))
    {
        string sql = "select * from Categories where CategoryName=?";

        OleDbCommand cmd = new OleDbCommand(sql, con);
        cmd.Parameters.AddWithValue("@cName", categoryName);

        try
        {
            con.Open();
            OleDbDataReader rdr = cmd.ExecuteReader();

            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    _category.Id = Convert.ToInt32(rdr["Id"]);
                    _category.CategoryName = rdr["CategoryName"].ToString();
                }

                rdr.Close();
            }
        }
        catch (Exception ex)
        {
            _category = null;
        }
    }

    return _category;
}

Show the sql server query that works, the connection string and a dump of the value of`categoryName` — Caius Jard, Nov 19 '19 at 19:45
`select * from Categories where CategoryName='Amonium'` and it will pull data from table. — chandra shekhar das, Nov 19 '19 at 19:47
If it's sql server, why use OleDbCommand when SqlCommand exists? — Caius Jard, Nov 19 '19 at 19:48
actually I am developing for a remote hosting where the connection string is in oledb. — chandra shekhar das, Nov 19 '19 at 19:50
Try `if (rdr.Read())` instead of rdr.HasRows and get rid of the while loop. You are ignoring multiple hits anyway. Also your reader should be in its own using block as well. — Jonathan Willcock, Nov 19 '19 at 19:53
So `categoryName == "Amonium,Test and Measurement"` would returns true? — Caius Jard, Nov 19 '19 at 19:57
No they are the example of values. But value is only one from those two. @Caius Jard — chandra shekhar das, Nov 19 '19 at 19:59
The advantage of calling Read() without HasRows is that it throws an Exception if there is something wrong with the SQL, and I am not sure that HasRows does. — Jonathan Willcock, Nov 19 '19 at 20:00
@Jonathan Willcock no change. Is there anything with string searching? beacuse when I using search by id, it pull data correctly. — chandra shekhar das, Nov 19 '19 at 20:00
Can you try this: `_category.Id = Convert.ToInt32(rdr[0]["Id"]);` and see if the data is being pulled through? Or can you verify if this shows up something: `Console.WriteLine(rdr[0].ToString())` — Angelo, Nov 19 '19 at 20:00
@Jonathan Willcock `string sql = "select * from Categories where Id=?";` this command pull data correctly. — chandra shekhar das, Nov 19 '19 at 20:01
@Angelo it shows Can not apply indexing with [] to an expression of type object. — chandra shekhar das, Nov 19 '19 at 20:07
Which would suggest that CategoryName is not a valid field. Do you get an Exception, if you call Read without HasRows as I suggested? — Jonathan Willcock, Nov 19 '19 at 20:08
Which statement did that error occurred from? I may have mis-typed the first set of codes I provided. — Angelo, Nov 19 '19 at 20:10
I got the solution. previously I typed `Category _category = null;` But it should `Category _category = new Category();` — chandra shekhar das, Nov 19 '19 at 20:14
Because new object didn't create with only null values. But when I changed it by assigning **Category()** by **new** keyword then it worked. — chandra shekhar das, Nov 19 '19 at 20:18
I was about to suggest that your code has a NullRefernceException that is being masked - you thought the query wasn't working but it was fine, it was just that attempting to use the results was crashing and then you were catching the crash and returning null. Anyhows, i've posted some advice that will a) make your life easier and b) won't suffer from this problem — Caius Jard, Nov 19 '19 at 20:18

score 1 · Answer 1 · answered Nov 19 '19 at 20:17

I don't know about "proper" but a "good" way might be to install Dapper and reduce your code to:

    public Category GetCategoryByCategoryName(string categoryName)
    {
        using (OleDbConnection con = new OleDbConnection(_connectionString))
        {
            return con.QueryFirstOrDefault<Category>(
              "select * from Categories where CategoryName=?cn?",
              new { cn = categoryName }
            );
        }
    }

References: Passing query parameters in Dapper using OleDb

score 1 · Answer 2 · answered Nov 19 '19 at 20:23

1

It should: Category _category = new Category(); instead of: Category _category = null;

answered Nov 19 '19 at 20:23

chandra shekhar das

63
10

Is there any proper way to retrieve data from OLEDB connection searched by some specific string value?

2 Answers2