Solved

C# MySqlDataReader missing one result

Posted on 2014-03-13
9
509 Views
Last Modified: 2014-03-20
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
Comment
Question by:itnifl
  • 5
  • 3
9 Comments
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 375 total points
ID: 39925996
case sensitive vs insensitive ?
0
 
LVL 2

Author Comment

by:itnifl
ID: 39926029
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
 
LVL 2

Accepted Solution

by:
itnifl earned 0 total points
ID: 39926089
Actually, this is the fix:
tempTable.Add("?nodeName", nodeName + "%");

Open in new window

0
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 375 total points
ID: 39926130
>>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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 74

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 125 total points
ID: 39926838
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
 
LVL 2

Author Comment

by:itnifl
ID: 39928865
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
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 375 total points
ID: 39929004
>>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
 
LVL 2

Author Comment

by:itnifl
ID: 39931191
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
 
LVL 2

Author Closing Comment

by:itnifl
ID: 39941781
Tanks for participating! :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Data Saving(2) 2 28
Chat Room 1 30
SQL anywhere 11 databases 1 37
install app on other machine 13 16
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now