I am trying retrieve data from a database. I have added a .sdf file and wrote the code shown below. My table name is info and it has three columns: id, name, and code.
What I want to do is to populate a CheckedListBox with this data, but nothing happens when I execute my code. CheckedListBoxis empty. What am I doing wrong?
SqlCeDataReader dr;
SqlCeConnection con;
SqlCeCommand cmd;
void loadData()
{
cmd.CommandText = "select column_name from Information_schema.columns where table_name='info' order by ordinal_position";
con.Open();
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read()) {
checkedListBox1.Items.Add(dr[0].ToString());
}
dr.Close();
con.Close();
}
private void Form1_Load(object sender, EventArgs e)
{
con = new SqlCeConnection();
con.ConnectionString=@"Data Source=c:\users\xxx\documents\visual studio 2012\Projects\WindowsFormsApplication1\WindowsFormsApplication1\Database1.sdf";
cmd = new SqlCeCommand();
cmd.Connection = con;
loadData();
}
You are basically asking for your code snippet to be debugged. This is tricky for a couple of reasons. Firstly your code calls out to a database, and does a slightly weird query in that it is getting information on columns from a system database rather than something from your own table. No-one here knows exactly what the result of that will be. It might be possible to replace that line with something else which has a similar effect (eg a list which is initialized with some dummy values in the code), but by doing so we might change the behavior so that the bug disappears, and we would be none the wiser. We will come back to this idea in a minute.
A question like this is not ideal for StackOverflow. One reason is that which I have just outlined - it could be very difficult to replicate exactly what your code does in order to help you. Therefore you might not get a useful answer. Another, slightly opposed reason is that someone (slightly more diligent and observant than me) might spot a simple typo or gotcha in your code. This might have already happened while I type this. They will post an answer or a comment pointing this out to you, and you will fix your code. However, the question is then not particularly useful to anyone else. No-one is likely to make quite the same typo or slip as you. Even if they do, it will probably be in a slightly different context, unrelated to CheckedListBox or SqlCeDataReader, and they will never find this question.
Those type of questions-and-answers essentially add nothing to this site. They also won't be particularly helpful to you, and this is why: you won't learn to debug.
In my experience, great programmers are almost always good debuggers. Most of us make a lot of small mistakes - finding and fixing these quickly is the difference between average productivity and great productivity. Being a good debugger also means you have a different relationship with your thoughts and your (or someone else's) code. You are able to think more flexibly, holding many different cases and possibilities in your mind at the same time. This is called divergent thinking, and is slowly being recognized to be just as important as convergent thinking, thinking which leads to an answer.
What you should do to progress with your problem and as a programmer, is debug properly. If you had done this and were still faced with a difficult question (or maybe a bug in your tools), you would have a much shorter code snippet to post, and would be able to describe the unintended behavior much better.
There are basically two ways of debugging code like this - stepping through it, and 'print lines'. (Weird and difficult code like kernel code, multithreading and message passing might be much harder to debug - these two techniques will get you a long way with everything else.) The main idea in both is that you look at intermediate values of your variables, at different stages of evaluation. Debugging support for stepping through C# code in Visual Studio is very good, but to keep things simple and reasonably language independent, I will use print lines as an example.
Just add a line anywhere in your program, which you want to know if the execution flow gets there or not. For example
Console.WriteLine("We do have some rows");
should go after the if (dr.HasRows) { line. If this is a console program and you run it, you will see this output appear (or not) in the console window. Otherwise you could use Debug.Print to send the text to the 'Output' window of VS, or a Winforms MessageBox to display the text in an alert window.
You will find out straightaway if your query result has any rows or not. If it doesn't, you have a problem with your query or the way you execute it. You should first of all run the same query on the same database in a different way, for example sqlcmd or Sql Server Management Studio. The fact that you hadn't done this, and that you didn't know if dr.HasRows was true or not, showed me immediately that you weren't debugging properly. [Edit: @Leonardo also pinpointed exactly this same thing in a comment.] If you get some valid response rows when running the query elsewhere, but not when running this code, there is some problem with how you set up the connection or run the query in C#. If you don't get any rows, there is a problem with your query itself. Try different queries directly until you get the right one, then put it back into the C# code. (The third possibility is that 'no rows' is the right response, and that your code should know how to deal with this case properly.)
Suppose on the other hand that dr.HasRows is true. There is some problem with dr.Read or with adding to the checkbox. To eliminate the former, try using a printline in the inner loop. This time output the value you are interested in:
Debug.Print("Value to be added is: " + dr[0].ToString());
You will quickly see if these look reasonable or not. If they do, try and find out what is going wrong with checkListBox1. Print the value of checklistBox1 and/or checkListBox1.Items after each iteration of the loop. Try writing some different values, possible a string constant or a hard-coded list of strings to Items instead and see if it works. Make sure that checkListBox1 actually is empty, and that you actually call the code to fill it up before accessing it.
Well done, you've just learnt the main two parts of one of the two main methods of debugging. You can identify how the code loops and branches (by putting in information print lines)
Console.WriteLine("Got this far!");
and you can find out what the value of your data is (by printing the actual values of variables)
Console.WriteLine("x is currently equal to " + x.ToString());
The nice thing about printlines is that whenever you control some kind of output or logging, you can debug like this without any other tools.
Now you are going to test each step of the code, observing the data passed to it and back from it. Try and find out exactly which parts are working as expected, and which parts are already using bad information passed to them by a previous step. Any bit you are not sure about (like the db accesses), you try and re-run in a different way to how it's used in your code. For example, write a two-line program which creates and displays a ListCheckBox, to make sure you know how to do it.
If you are using some extremely experimental tool, you might end up finding a bug in it, and that your own code is fine. This is very very unlikely with what you're doing, using technology that has been extensively tried and tested by others. The most likely outcome is that you find your (probably simple) bug and fix it. Other than that, it is possible you come back to StackOverflow with a much smaller test case, asking about something non-obvious in the way you use the components or some other straightforward query that is hard to figure out yourself. (Both Winforms and SQL Server do contain many gotchas, which lead to good questions that help others..) When you do, you will be able to share the results of your debugging to let answerers know exactly where the code doesn't seem to behave as expected. It's also very likely that if you do have a problem like this, you can search and find that someone else has already come across it and had it answered.
Don't forget, once you've finished debugging you can take your working code to http://codereview.stackexchange.com to get advice about making it shorter, more elegant, better performing and more error-proof. Good luck!