LIKE query on an Access database via C# always returns COUNT(*) of 0

Question

Please look into following code:

using (OleDbConnection openCon = new OleDbConnection(ConfigurationManager.AppSettings["AccessConnectioString"]))
{
                openCon.Open();
                string tc = string.Empty;
                string ttc = string.Empty;
                if (!string.IsNullOrEmpty(QSetId))
                {
                    tc = "select count(*) as [Count] from ABC where QSetId = @qSetId and TText like 'RT*'";
                }
                else
                {
                    tc = "select count(*) as [Count] from PQR where TText like 'RT*'";
                }
                using (OleDbCommand qtc= new OleDbCommand(tc))
                {
                    qtc.Connection = openCon;
                    if (!string.IsNullOrEmpty(QSetId))
                        qtc.Parameters.Add("@qSetId", OleDbType.VarChar).Value = QSetId;
                    OleDbDataReader dr1 = qtc.ExecuteReader();
                    while (dr1.Read())
                    {
                        ttCnt = (int)dr1["Count"];
                    }
                }

                openCon.Close();
}

I am always getting count as 0 instead of some integer value. While debugging I take the query and execute in MS ACCESS 2013, it gives me correct result. I am not getting what is the issue.

You might want to start by reading the manual. http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter(v=vs.110).aspx *The OLE DB.NET Framework Data Provider uses positional parameters that are marked with a question mark (?) instead of named parameters.* What's the point of setting `getTotalTweetCount` if you never use it? `OleDbDataReader` implements `IDisposable` but you never call `Dispose`. Etc etc. — ta.speot.is, Jan 09 '14 at 09:26
@ta.speot.is Thanks for reply, 'AccessConnectioString' not a spelling mistake, and 'getTotalTweetCount' is not there, its 'tc' (I have edited it), @ is working fine, it also get replaced with proper value. I dont think that @ is creating problem as I am using same thing in other part of programs and its working. — user3030342, Jan 09 '14 at 09:33

score 4 · Accepted Answer · answered Jan 09 '14 at 09:52

You are getting tripped up by the difference in LIKE wildcard characters between queries run in Access itself and queries run from an external application.

When running a query from within Access itself you need to use the asterisk as the wildcard character: LIKE 'RT*'.

When running a query from an external application (like your C# app) you need to use the percent sign as the wildcard character: LIKE 'RT%'.

score 0 · Answer 2 · answered Jan 09 '14 at 09:32

0

Try ExecuteScalar() method

Replace This:

 OleDbDataReader dr1 = qtc.ExecuteReader();
 while (dr1.Read())
 {
    ttCnt = (int)dr1["Count"];
 }

With This:

 ttCnt = Convert.ToInt32(qtc.ExecuteScalar());

answered Jan 09 '14 at 09:32

Sudhakar Tillapudi

25,935
5
37
67

already tried,but its not working, even in QuickWatch dr1["Count"]; gives value as 0. – user3030342 Jan 09 '14 at 09:36
@user3030342: are you sure that query returns some integer other than zero? – Sudhakar Tillapudi Jan 09 '14 at 09:38
Ofcourse man, even I have checked it at run time using breakpoint, copying final query in variable and executing it in ms access sql view, there it gives me n no of rows. – user3030342 Jan 09 '14 at 09:41

LIKE query on an Access database via C# always returns COUNT(*) of 0

2 Answers2

Linked