CODEWITHSUNDEEP
c#asp.netsql
Nico
Nico

Reputation: 237

Multiple data in gridview

I ran into a new problem.

I want to select every member in my database with a statusID higher than the current user's. For each of these members I want there filled in models to be showen in my gridview.

This means I'll have to repeat 1 SELECT query for each member found with a statusID higher than the current user's.

To fill my gridview I use programming code behind the ASPX (C#). So far as I know i can only inport 1 SQL query in my gridview at a time.

Is there any way to show all of these records in 1 gridview? Like F.E. for each user it will simply add the new data to the gridview in stead of replacing it ?

Here's the code I've got so far :

            int statushelp = int.Parse(statusid);

            SqlCommand cmd = new SqlCommand("SELECT COUNT (*) FROM Login WHERE Status_ID > " + statushelp + " ", con);
            int aantal = (int)cmd.ExecuteScalar();

            string[] onderdanen = new string[aantal];
            int teller = 0;

            SqlCommand cmd1 = new SqlCommand("SELECT User_ID FROM Login WHERE Status_ID > " + statushelp + " ", con);
            dr = cmd1.ExecuteReader();

            while (dr.Read()) 
            {
                onderdanen[teller] = dr["User_ID"].ToString();
                teller++;
            }

            dr.Close();
            string onderdaan;
            teller = 0;

            while (teller < onderdanen.Length) 
            {
                onderdaan = onderdanen[teller].ToString();

                SqlCommand cmd2 = new SqlCommand("SELECT M.Mod_ID AS Modelnr, M.Mod_Naam AS Modelnaam, M.Mod_Omschrijving AS Omschrijving, M.Taal_Id, M.User_ID FROM Toewijzing T, Model M WHERE T.User_ID = '" + onderdaan + "' AND T.Toe_Status = '" + "ja" + "' AND M.Mod_ID = T.Mod_ID", con);
                dr = cmd2.ExecuteReader();
                Debug.WriteLine(dr["M.Mod_ID"].ToString());
                gvModelAdmin.DataSource = dr;
                gvModelAdmin.HeaderStyle.HorizontalAlign = HorizontalAlign.Left;
                gvModelAdmin.DataBind();
                dr.Close();
                teller++;

            }

Really any help is welcome couse I've got absolutely NO clue how to do this..

Upvotes: 0

Views: 488

Answers (1)

gbs
gbs

Reputation: 7276

Two possible ways I can think of:

1: SQL Level(would be best one and I don't know how):I believe you can do all this in one DB call. Could be a complex SP but a SQL expert may be able to point you in right direction.

2: C# Coding: Here's what you need to do:

2a: Create a plain C# class that has properties that maps to the fields returned from your last query in the loop. i.e. Modelnr,Modelnaam, etc. Say you named it MyModelClass.

public class MyModelClass{

public string Modelnr {get;set;} //set the type of property according to the datatype of DB field

public string Modelnaam{get;set;}
...other properties
}

2b: Then modify your code like this:

List<MyModelClass> mmcList = new List<MyModelClass>();
while (teller < onderdanen.Length){
onderdaan = onderdanen[teller].ToString();
SqlCommand cmd2 = new SqlCommand("SELECT M.Mod_ID AS Modelnr, M.Mod_Naam AS Modelnaam, M.Mod_Omschrijving AS Omschrijving, M.Taal_Id, M.User_ID FROM Toewijzing T, Model M WHERE T.User_ID = '" + onderdaan + "' AND T.Toe_Status = '" + "ja" + "' AND M.Mod_ID = T.Mod_ID", con);
dr = cmd2.ExecuteReader(); 
if(dr!=null && dr.HasRows){
MyModelClass mmcObj= new MyModelClass();
dr.Read();
mmcObj.Modelnr = dr["Modelnr"].ToString(); //Modelnr is String property in MyModelClass
mmcObj.Modelnaam= dr["Modelnaam"].ToString();//Modelnaam is a String prop in MyModelClass
...///so on other properties

mmcList.Add(mmcObj);
}
dr.Close();                 
teller++;
} 
gvModelAdmin.DataSource = mmcList; //set gv datasourc to the list on myModelClass
gvModelAdmin.HeaderStyle.HorizontalAlign = HorizontalAlign.Left;
gvModelAdmin.DataBind();

Upvotes: 2

Related Questions