C# SQL list and query

Vote count:

0

I'm having trouble creating the function that would return the right information, basically I have a list of rooms and after I get that list I need to do another query based on the rooms in the results.

private DataSet getList()
{
        SqlConnection con = Program.GetConnection;
        DataSet ds = new DataSet();
        try
        {
            SqlDataAdapter sda = new SqlDataAdapter("SELECT DISTINCT p.New_ProjectRoomID, p.new_roomname, p.new_roomviewname " +
                                            "FROM [MSCRM].[dbo].[New_ProjectRoom] p " 
                                            "order by new_roomname", con);

            sda.Fill(ds);
            con.Close();
            return ds;

        }
        catch (Exception ex)
        {
            mailError(ex.ToString());
        }
        con.Close();
        return ds;

    }

    private void getStatus()
    {

        SqlConnection con = Program.GetConnection;
        DataSet ds = getList();
        DataSet newDS = new DataSet();
        DataTable dt = new DataTable();

        try
        {
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                SqlDataAdapter sda = new SqlDataAdapter("SELECT top 1 i.[New_RoomServiceCallIdName] " +
                                                      ",p.[New_RoomViewName] " +
                                                      ",p.[New_RoomName] " +
                                                      ",i.[New_RoomServiceCallId] " +
                                                      ",i.[New_resolutionsummary] " +
                                                      ",i.TicketNumber " +
                                                      ",i.[New_VTCStatus] " +
                                                      ",i.[New_ATCStatus] " +
                                                      ",i.[New_ControlStatus] " +
                                                      ",i.[New_TVMovieStatus] " +
                                                      ",i.[New_LampHours] " +
                                                      ",i.[New_PresentationStatus] " +
                                                      ",i.CreatedOn " +
                                                      "FROM [MSCRM].[dbo].[Incident] i, [MSCRM].[dbo].[New_ProjectRoom] p " +
                                                      "where i.New_RoomServiceCallId = '" + ds.Tables[0].Rows[0][i].ToString() + "' " +
                                                      "and p.New_ProjectRoomId = '" + ds.Tables[0].Rows[0][i].ToString() + "' " +
                                                      "and i.TicketNumber not like 'CCTab%' " +
                                                      "and New_resolutionsummary is not null " +
                                                      "order by i.CreatedOn desc", con);

                sda.Fill(dt);
           }
            con.Close();
            dataGridView1.DataSource = dt;


        }
        catch (Exception ex)
        {
            mailError(ex.ToString());
        }
        con.Close();
    }


    private void mailError(string str)
    {
        MailMessage mm = new MailMessage("someone@mail.com", "me@mail.com", "service error", str);
        SmtpClient client = new SmtpClient(MySMTP);
        client.Send(mm);

    }

    private void button1_Click(object sender, EventArgs e)
                getCRMStatus();
    }

asked 1 min ago

perirose

1