Hi!

Not sure if I've posted this one in the correct topic, so feel free to move it. Anyway, here goes.

I am building a music website where I want to display albums. I have built a database that works fine, but I have a problem with the SQL command.

In my database I have a table called DBProducer, with the two columns ProducerID and ProducerName. I have inserted two values this far (see list below). I am using the following SQL command.

With this I display the following:

On my website I have a DataList that displays this. By adding "(DBProducer.ProducerID = 1)" to the WHERE command, I only display the first line (John). If I don't add this, the site displays all the information twice, with John as producer in the first set and Adam as the producer in the second. What I want to do is the DataList to display both John and Adam together in one set, and not display the same information twice with different producers, so that in my aspx file the code should look something like this:

(The first label shall display the name John and the second label shall display the name Adam)

How do I do that? I suppose I need a JOIN command but I can't figure it out.

Help would be much appreciated!

//Peter

Program that uses SqlCommand instance: C#

using System;
using System.Data.SqlClient;

class Program
{
static void Main()
{
//
// First, the program accesses the connection string and uses it on a connection.
//
string conString = ConsoleApplication1.Properties.Settings.Default.Co nnectionString;
using (SqlConnection connection = new SqlConnection(conString))
{
connection.Open();
//
// The SqlCommand should be created inside a using statement.
// ... It receives the SQL statement as the first argument.
// ... It receives the connection object as the second argument.
// ... The SQL text only works with a specific database.
//
using (SqlCommand command = new SqlCommand(
"SELECT TOP 3 * FROM Dogs1 ORDER BY Weight",
connection))
{
//
// Instance methods can be used on the SqlCommand instance.
// ... These read data from executing the command.
//
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.WriteLine(reader.GetValue(i));
}
Console.WriteLine();
}
}
}
}
}
}

Output

7
Candy
Yorkshire Terrier

I think the issue is with your database design.

If it was me and I had an album with multiple producers I would create a table called Producers. Then I would have an intermediary table with an ID column, then one for AlbumID and one for ProducerID called AlbumProducers.

Then when I wanted to query info on one album I could grab all the producers at one time and not have the excessive rows for albums that is repeating the same information.

Look up "normalization" for your database design.

Brian: what's the point of posting all this unrelated stuff that you copied from other forums? Please try not to just bluntly copy and paste other people's work without adding any value.

Cheers,

Imar

__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!

Richard, I think the OP already has a normalized database:

It's all the cross joins that duplicate the data. Inner joins would solve this.

But, this topic is 3 years old so I doubt the OP is still interested in an answer ;-)

Cheers,

Imar

__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!

Hi Imar,

Yeah.. You're right about the normalization. Just going too fast.

OK... thanks