• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 568
  • Last Modified:

C# MySqlDataReader missing one result

I am missing a result from my query.

using MySql.Data.MySqlClient;

public List<int> getNodeIDOfServerLike(String nodeName) {
         String fetchString = "SELECT NODE_ID FROM server_info_table WHERE NODE_NAME LIKE ?nodeName";
         Hashtable tempTable = new Hashtable();
         tempTable.Add("?nodeName", nodeName);
         openConnection();
         MySqlCommand cmd = createMySQLCommand(fetchString, tempTable);
         tempTable.Clear();
         MySqlDataReader dataReader = cmd.ExecuteReader();
         List<int> resultList = new List<int>();
         Console.WriteLine("Running: " + cmd.CommandText);
         while (dataReader.Read()) {
            Console.WriteLine("Got: " + dataReader["NODE_ID"].ToString());
            if (!String.IsNullOrEmpty(dataReader["NODE_ID"].ToString())) resultList.Add(int.Parse(dataReader["NODE_ID"].ToString()));
         }
         dataReader.Close();
         closeConnection();
         return resultList;
}

Open in new window


internal MySqlCommand createMySQLCommand(String commandText, Hashtable parameters) {
         if(SqlConnection != null) {
            MySqlCommand cmd = SqlConnection.CreateCommand();
            cmd.CommandText = commandText;
            cmd.Prepare();
            foreach (DictionaryEntry entry in parameters) {
               cmd.Parameters.Add((String)entry.Key, entry.Value);
            }
            return cmd;
         }
         return null;
      }

Open in new window


Result is:
Running: SELECT NODE_ID FROM server_info_table WHERE NODE_NAME LIKE ?nodeName
Got: 189

When I run the the same query directly agains the mysql server, I get two results:
189
457

That would be correct. But when I run the code above, I only get the first result. So what am I doing wrong here?
0
itnifl
Asked:
itnifl
  • 5
  • 3
5 Solutions
 
AndyAinscowFreelance programmer / ConsultantCommented:
case sensitive vs insensitive ?
0
 
itniflAuthor Commented:
It doesn't look that way. It doesn't matter what case I supply the nodeName in. Also I get the other ID(457)  if I supply the whole node name for that ID.
0
 
itniflAuthor Commented:
Actually, this is the fix:
tempTable.Add("?nodeName", nodeName + "%");

Open in new window

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
AndyAinscowFreelance programmer / ConsultantCommented:
>>Actually, this is the fix:

Odd - I don't see why that would make any difference between the code and the direct approach (unless you didn't use the identical SQL statement that is when testing it directly)
0
 
käµfm³d 👽Commented:
Why are you using a question mark in your parameter name? I've always seen an @ symbol used.

i.e.

http://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-parameters.html
0
 
itniflAuthor Commented:
When I test the query directly I write as follows:
SELECT NODE_ID FROM server_info_table WHERE NODE_NAME LIKE 'OHTHATSERVERNAME%';

When I in the code use:
SELECT NODE_ID FROM server_info_table WHERE NODE_NAME LIKE ?nodeName%
it won't accept ?nodeName as a parameter.

I don't remember or know why I use a question mark as a parameter mark, this code was written some time ago. I am guessing the choice of a parameter mark is optional?

Obviously the wild card has to be treated as a part of the parameter value. I can't say that this is very obvious or intuitive.
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
>>When I test the query directly I write as follows:
SELECT NODE_ID FROM server_info_table WHERE NODE_NAME LIKE 'OHTHATSERVERNAME%';
When I in the code use:
>>SELECT NODE_ID FROM server_info_table WHERE NODE_NAME LIKE ?nodeName%


That isn't what you posted in the question - there was no trailing % in the code based SQL query.

So, basically you got different results because you used different queries.
0
 
itniflAuthor Commented:
This does not work in the code:
SELECT NODE_ID FROM server_info_table WHERE NODE_NAME LIKE ?nodeName%

And this does not work in the code:
SELECT NODE_ID FROM server_info_table WHERE NODE_NAME LIKE ?nodeName

However, this works:
SELECT NODE_ID FROM server_info_table WHERE NODE_NAME LIKE ?nodeName
and then adding the % with the parameter value:
tempTable.Add("?nodeName", nodeName + "%");

In the question I am not seeking why there are differences, I am asking how to get the result I want(all results from the query). The answer is to supply % with the parameter value, for some reason.
0
 
itniflAuthor Commented:
Tanks for participating! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now