DataReader does not return rows - rows are present in the database

Question

Alright world, I have a problem. No, this is not "homework"

I have code that should do two things:
1) Read the list of artists from the database
2) Find all the tracks written by this artist and save them later

What am I doing:
1) SELECT ArtistID FROM artists gets a list of my artists. I create it in a List<string> type using a DataReader , etc. It works great. 2) SELECT count(*) as track_numbers from tracks WHERE ArtistID = @ArtistID does not work so well

My problem is :
The second select statement will succeed if I manually specify ArtistID (i.e.: SELECT count(*) as track_numbers FROM Tracks WHERE ArtistID = 0 works), but if I repeat through my list, it will always return null results.

I am using Connector / Net 6.3.6, Visual Studio 2010 (all updates), .NET 4.0 Client Profile, MySQL Community Server 5.5.9 x64, Windows 7 Home Premium x64

I tried:

• Using string concatenation to create my query string using artid file
• casting artistid as a string, then int, and then vice versa
• redefinition, where parameters are defined, entered value, command text

The corresponding (I think) code below:

 List<string> list = new List<string>(); Hashtable table = new Hashtable(); DbProviderFactory factory = DbProviderFactories.GetFactory(setting.ProviderName); using (DbConnection conn = factory.CreateConnection()) { conn.ConnectionString = setting.ConnectionString; conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT ArtistID FROM artists;"; using (DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { list.Add(reader["ArtistID"].ToString()); //this works, and I can iterate no problem through this list } } } using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT count(*) as counts FROM tracks WHERE ArtistID = @ArtistID;"; //cmd.CommandText = "SELECT count(*) as counts FROM tracks WHERE ArtistID = 0;"; <-- this line works DbParameter param = cmd.CreateParameter(); param.ParameterName = "@ArtistID"; foreach (string artist in list) { param.Value = artist; cmd.Parameters.Add(param); using (DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Console.WriteLine(artist + "\t" + reader["counts"]); //reader["counts"] is always '0' } cmd.Parameters.Clear(); } } } 

}

Database table format: Artist has one value / key called ArtistID , enter int
Tracks have three columns, TrackID (int, pk) , artistid (int) , albumid(int)

Any suggestions? Thanks.

Answer 1

It seems to me that you need to delete the call cmd.Parameters.Add(param); . This adds a new parameter for each iteration. Simply set the parameter value each time for an existing parameter.

Answer 2

 foreach (string artist in list) { param.Value = artist; cmd.Parameters.Add(param) 

You are currently re-adding a parameter for each artist - I am surprised that this works at all, of course, it should not be for SqlParameter - if DbParameter works the way it should look:

 cmd.Parameters.Add(param); foreach (string artist in list) { param.Value = artist; 

Answer 3

After I hit my head on the wall for another hour, I discovered a problem: 1) I needed to make a “clean decision” for reasons that I can’t understand 2) Somehow, the relationship with the foreign key ensures that was actually an artist on tracks that matched the artist in the artists, I somehow managed to spoil

I am actually a huge idiot.

The above answers, although they did not fix my problem, taught me something new about C #, so I pay tribute to the 1st poster.