newbie needs help with asp.net C# sql code

[fpower]

Hello All,

I am very new to programming and would appreciate any help. I am creating a website with Visual Studio 2008 and sqlexpress. The database has 4 tables, companies, contacts, state, and sstateandcompany. I have an asp.net C# page that when you select a state from a drop down list it returns a list of companies and their address and all contact personel from that state on the screen. I am using a DataSource with an sql select statement to return the data, which it does. The problem is that it lists the company name for every contact person. For example:

company1 first contact person
123 main st.
suite 1
some city, state 12345
company1 second contact person
123 main st.
suite 1
some city, state 12345

How can I have it list the compny name once, and to the right list all the contact personel for that company. For example:
company1 first contact person
123 main st. second contact person
suite 1
some city, state 12345

Thank you in advance for any help you can provide.

 

[jbenson001]

What control are you using to display the data? You can use a nested gridview or nested repeater to do what you want. Also, DON'T use the datasource controls. The will cause you problems as soon as you do something a little more complicated that just diplaying data.

 

[engineer02]

Hi

Take 2 listbox and write select query to display it on page so that it will display distinct address and in listbox it will display no. of members.If u want proper query do reply i will let u know.

Regards
Prit

 

[fpower]

Thank you for your quick replies...
I have been trying to do as you have suggested and am using nested repeater, but am having a little trouble. I keep getting errors about the SQLDataAdapter. Could you tell me what I am doing wrong?
Thanks again for all the help!

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

public partial class showrooms : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection conn;
DataSet ds = new DataSet();
SqlCommand strSql;
string connectionString = ConfigurationManager.ConnectionStrings["fmp"].ConnectionString;
strSql = new SqlCommand("SELECT * FROM companies");
SqlDataAdapter daCompanies = new SqlDataAdapter(strSql, conn);
daCompanies.Fill(ds, "Companies");

strSql = new SqlCommand("SELECT * FROM contacts");
SqlDataAdapter daContacts = new SqlDataAdapter (strSql, conn);
daCompanies.Fill(ds, "Contacts");
daContacts.Fill(ds, "contactsDS");


DataRelation rel = new DataRelation("companyContacts",
ds.Tables["companies"].Columns["companyID"],ds.Tables["contacts"].Columns["companyID"]);
ds.Relations.Add(rel);
myRepeater.DataSource =ds.Tables["companies"].DefaultView;
myRepeater.DataBind();
}

void showCompanyContact(Object sender, RepeaterItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
((Repeater)e.Item.FindControl("nestedRepeater")).DataSource=((DataRowView)e.Item.DataItem).CreateChildView("companyContacts");
((Repeater)e.Item.FindControl("nestedRepeater")).DataBind();
}
}



}

 

[jmeckley]

kudos for not use a datasource control!

when you get errors you need to post the full exception
type, message, stack trace. this is either displayed on the page when the error is thrown (ugly yellow error screen) or you can catch the error and display the exception.ToString() to get all this data.

this info will tell you where to start looking for the problem. I would start with 2 changes:
1. the number of times to fill the dataset
2. how you bind to the repeater

        SqlConnection conn;
        DataSet ds = new DataSet();
        SqlCommand strSql;
        string connectionString = ConfigurationManager.ConnectionStrings["fmp"].ConnectionString;
        strSql = new SqlCommand("SELECT * FROM companies");
        SqlDataAdapter daCompanies = new SqlDataAdapter(strSql, conn);
        daCompanies.Fill(ds, "Companies");
 
        strSql = new SqlCommand("SELECT * FROM contacts");
        SqlDataAdapter daContacts = new SqlDataAdapter (strSql, conn);
        daContacts.Fill(ds, "Contacts");


        DataRelation rel = new DataRelation("companyContacts",
        ds.Tables["Companies"].Columns["companyID"],ds.Tables["Contacts"].Columns["companyID"]);
        ds.Relations.Add(rel);

        myRepeater.DataSource = ds;
        myRepaeter.Datamember = "Companies"; // name of table
        myRepeater.DataBind();

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[fpower]

Thank you again for all the help, I am VERY new to this and really appreciate the help... I am still having trouble with the SqlDataAdapter and I have no idea how to correct it. I am getting an error that references this line of code:
SqlDataAdapter daCompanies = new SqlDataAdapter(strSql, conn);
The full error is listed below:
error CS1502: The best overloaded method match for 'System.Data.SqlClient.SqlDataAdapter.SqlDataAdapter(string, string)' has some invalid arguments
error CS1503: Argument '1': cannot convert from 'System.Data.SqlClient.SqlCommand' to 'string'
error CS1503: Argument '2': cannot convert from 'System.Data.SqlClient.SqlConnection' to 'string'
error CS1502: The best overloaded method match for 'System.Data.SqlClient.SqlDataAdapter.SqlDataAdapter(string, string)' has some invalid arguments
error CS1503: Argument '1': cannot convert from 'System.Data.SqlClient.SqlCommand' to 'string'
error CS1503: Argument '2': cannot convert from 'System.Data.SqlClient.SqlConnection' to 'string'

Also, would this be the correct way to fill the ds only once?
SqlDataAdapter myadapter = new SqlDataAdapter("SELECT * FROM companies; SELECT * FROM contacts", conn);
myadapter.TableMappings.Add("Table", "compnaies");
myadapter.TableMappings.Add("Table1", "contacts");
myadapter.Fill(ds);

 

[jmeckley]

your defining strSql as a SqlCommand but new SqlDataAdapter (strSql, conn) wants a string for the first argument, not a SqlCommand.

try this instead.
SqlDataAdapter daContacts = new SqlDataAdapter ("SELECT * FROM contacts", conn);

Also, would this be the correct way to fill the ds only once?

Don't know. I'm not familiar with DataAdapters. I use ORM frameworks for data access.
I can say that whether there is 1 db hit or 2 shouldn't matter in this scenario. If you were loading 1000's of records then you need to use a paging technique to gain any efficiency. not reducing queries.

it's also considered a good practice to define columns explicitly instead of using select *...

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[fpower]

well, I think I am making a little progress but am running into yet another problem...
I am getting an error from the following line:
DataRelation rel = new DataRelation("companyContacts",
ds.Tables["companies"].Columns["companyID"],ds.Tables["contacts"].Columns["companyID"]);
ds.Relations.Add(rel);

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

NullReferenceException: Object reference not set to an instance of an object.]

What does this mean and how can I correct it?
Once again, I thank you for all your help!

 

[jmeckley]

this means you are trying to reference an object that does not exist. because you are using a generic dataset, instead of a strongly typed dataset there is greater room for error.

either the table or name is not correct. to figure out which one assign the tables and columns to variables. then step through the code to determine which one is null. C# is case sensative, so companyID != CompanyID != companyId...

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[fpower]

thank you for the help jmeckley, can you give an example of a strongly typed dataset... I am afraid I dont understand.
thank you again for your help!

 

[jmeckley]

do a google search on strongly typed dataset. the main differences are:
1. dataset is defined at compile time vs. runtime. This gives you compile time checks, instead of breaking during run time.
2. properties can be explicit

//weakly typed dataset
DataSet ds = GetAnInstanceOfADataSet();
DateTime aDate = (DateTime)ds.Tables["mytable"].columns["adate"];

//strongly typed dataset
MyDataSet ds = GetAnInstanceOfMyDataSet();
DateTime aDate = ds.Mytable.Adate;

Jason Meckley
Programmer
Specialty Bakers, Inc.