ASPX dropdownlist selected item

Question

I have this code to show items in a dropdown list from DB.

 string query = "SELECT ID, Firstname, Lastname FROM Crew";
            string constr = ConfigurationManager.ConnectionStrings["constr1"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            ListItem item = new ListItem();
                            item.Text = sdr["Lastname"].ToString() + ' ' + sdr["Firstname"].ToString();
                            item.Value = sdr["ID"].ToString();
                            item.Selected = ""
                            ddlCrew.Items.Add(item);
                        }
                    }
                    con.Close();
                }
            }
            ddlCrew.Items.Insert(0, new ListItem("--Select Crew--", "0"));

How could I have item selected depending on value?

table Crew: CrewID, Firstname, Lastname, IsActive

table Report: ReportID, Crew

So I need, to be selected Firstname + Lastname from table Crew depending on the ID which is entered in column Crew in table Report.

EDITED:

As Tony adviced I now have this code:

            string query = "SELECT RepCrewID, Firstname, Lastname FROM tblRepCrew; SELECT ServiceTicketReportID, InspectedBy FROM tblServiceTicketReport WHERE ServiceTicketID = 123";
            string constr = ConfigurationManager.ConnectionStrings["constr1"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {

                            ListItem item = new ListItem();
                            item.Text = sdr["Lastname"].ToString() + ' ' + sdr["Firstname"].ToString();
                            item.Value = sdr["RepCrewID"].ToString();   

                      if ( sdr["InspectedBy"].ToString() == sdr["RepCrewID"].ToString() )
                            {                        
                                item.Selected = true;
                            }

                            ddlCrew.Items.Add(item);
                        }
                    }
                    con.Close();
                }
            }
            ddlCrew.Items.Insert(0, new ListItem("-- Select Crew --", "0"));

The only thing I need to make is the IF statment. As far as I understand I have to check if the ID in the column InspectedBy (table tblServiceTicketReport) is the same as RepCrewID. If its the same select it, if not or its black, to be able to select new crew member. Something like this:

             if ( sdr["InspectedBy"].ToString() == sdr["RepCrewID"].ToString() )
                            {                        
                                item.Selected = true;
                            }

EDITED 2

So far I have:

string query = "SELECT ServiceTicketReportID, InspectedBy FROM tblServiceTicketReport WHERE ServiceTicketID = 21336; SELECT RepCrewID, Firstname, Lastname FROM tblRepCrew";
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                      while (sdr.Read())
                        {
                           something = sdr["InspectedBy"].ToString(); 
                        }
                            if (sdr.NextResult())
                                {
                                    while (sdr.Read())
                                    {
                                        ListItem item = new ListItem();
                                        item.Text = sdr["Lastname"].ToString() + ' ' + sdr["Firstname"].ToString();
                                        item.Value = sdr["RepCrewID"].ToString();   

                  if ( something == sdr["RepCrewID"].ToString())
                        {                        
                            item.Selected = true;
                        }

                        ddlCrew.Items.Add(item);

                            }
                        }
                    }
                    con.Close();
                }
            }
            ddlCrew.Items.Insert(0, new ListItem("-- Select Crew --", "0"));

But im getting here this error:

>>> Error executing child request for ServiceTicketReportEdit.aspx.
>>> ServiceTicketReportEdit.aspx.cs(58): error CS0103: The name 'something' does not exist in the current context

Tables:

tblServiceTicketReport: ServiceTicketReportID, ServiceTicketID, InspectedBy

tblRepCrew: RepCrewID, Firstname, Lastname

Sounds like your query needs to join the Crew and Report tables ? — sh1rts, Jan 06 '17 at 00:22
@sh1rts > basiclly just select statment is ok. See answers from Tony Dong. Im just not able to use if statment to get correct row from the other table. — , Jan 06 '17 at 01:44
@TonyDong, hey. I have updated my topic, please advise me on IF statement. thanks. — , Jan 06 '17 at 18:05
Assume you want to use user name as IF statement, see the new code update. — Tony Dong, Jan 06 '17 at 19:09
@TonyDong, I will have RepCrewID inserted into InspectedBy not name. so thats why i need if ( sdr["InspectedBy"].ToString() == sdr["RepCrewID"].ToString() ) — , Jan 06 '17 at 20:29
@Tony Dong, im sorry but im getting an error here. "Something" is not used in the solution.. — , Jan 06 '17 at 23:46
Looks like the first select did not have value return, please check your query and make sure the first select is return value. May be the serviceTicketID you passed in not exist in your table. — Tony Dong, Jan 09 '17 at 16:45
@Tony Dong, im still getting this error: >>> Error executing child request for ServiceTicketReportEdit.aspx. >>> ServiceTicketReportEdit.aspx.cs(58): error CS0103: The name 'something' does not exist in the current context .I have updated topic, pls advice. — , Jan 09 '17 at 17:49
Please declare string something = null; before you use it, look my code below. — Tony Dong, Jan 09 '17 at 17:56
@TonyDong, Now this error: >> Object reference not set to an instance of an object. << Here is from my DB: RepCrewID -> int, Firstname - Lastname -> varchar, ServiceTicketID -> int, InspectedBy -> nvarchar — , Jan 10 '17 at 20:19
Which line of code did you get error, could you debugging into it? — Tony Dong, Jan 10 '17 at 22:36

score 1 · Answer 1 · answered Jan 06 '17 at 00:00

1

dropdownlist.ClearSelection(); //for clearing previous selection
dropdownlist.Items.FindByValue(value).Selected = true; //select item by value

answered Jan 06 '17 at 00:00

Moshe D

768
1
4
13

Tony Dong · Accepted Answer · 2017-08-02T22:39:28.800

1

You can check the condition inside the while loop and make default selected

        string query = "SELECT ServiceTicketReportID, InspectedBy FROM tblServiceTicketReport WHERE ServiceTicketID = 123; SELECT RepCrewID, Firstname, Lastname FROM tblRepCrew";
        string constr = ConfigurationManager.ConnectionStrings["constr1"].ConnectionString;
        string something = null; 
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(query))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                con.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                       something = sdr["InspectedBy"].ToString(); 
                    }
                    if (sdr.NextResult())
                    {
                    while (sdr.Read())
                    {

                        ListItem item = new ListItem();
                        item.Text = sdr["Lastname"].ToString() + ' ' + sdr["Firstname"].ToString();
                        item.Value = sdr["RepCrewID"].ToString();   

                  if ( something == sdr["RepCrewID"].ToString())
                        {                        
                            item.Selected = true;
                        }

                        ddlCrew.Items.Add(item);
                    }
                  }
                }
                //using auto close the connection
                //con.Close();

            }
        }
        ddlCrew.Items

edited Aug 02 '17 at 22:39

answered Jan 06 '17 at 00:07

Tony Dong

3,213
1
29
32

Looks thats what i need. But how to check (compare) the values between the tables? the if statement. I mean, how to check which value is in table Report under Crew column ? – Jan 06 '17 at 00:13
ID is the value you entered in column if (ID ==sdr["ID"].ToString()) – Tony Dong Jan 06 '17 at 00:17
yup, but that ID is in different table, table Report. how could i 'reach' it? – Jan 06 '17 at 00:27
Do you need join two tables or need to make another select to get the ID from table Report? – Tony Dong Jan 06 '17 at 00:30
Selection would be easier way to do so, right? I have this selection SELECT ReportID, Crew FROM Report in the same query sting. but how could I use it in If statement? some like > if (Crew.ToString() == sdr["ID"].ToString()) – Jan 06 '17 at 00:40
You can put your select statement together SELECT ReportID, Crew FROM Report WHERE ReportID=123; SELECT ID, Firstname, Lastname FROM Crew and read it use this URL http://stackoverflow.com/questions/12969318/multiples-table-in-datareader – Tony Dong Jan 06 '17 at 00:48
yes, that is what i already have. string query = "SELECT ID, Firstname, Lastname FROM Crew; SELECT ReportID, Crew FROM Report"; BUT I dont get it, how to use it in IF statement, the one you wrote before?? somehow I should be able to retrieve the right row from table Report. – Jan 06 '17 at 01:06
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/132449/discussion-between-fresher-and-tony-dong). – Jan 06 '17 at 01:13

ASPX dropdownlist selected item

2 Answers2