0

I would like to store the result of my request in a list of character strings (List ) and I would like to store her in a dictionary (Dictionary ) but no matter the request which I use, I have this error message : "There is no row at position 0".

public Dictionary<string, object> getPICv2(string Tdate_d, string Ddl_fampic, string Ddl_donnee, string Ddl_detail, string Ddl_caracteristique, string Ddl_poste, string Ddl_ilot, string Ddl_nposte, string Ddl_atelier, string Ddl_tposte)
    {
        Dictionary<string, object> list = new Dictionary<string, object>();

        SqlDataAdapter dac, dad, dam;
        DataSet dsm = new DataSet();
        DataSet dsp = new DataSet();
        DataSet dsd = new DataSet();
        DataSet dsc = new DataSet();
        DataSet dsb = new DataSet();
        DataTable dtm = new DataTable();
        DataTable dtc = new DataTable();
        DataTable dtv = new DataTable();
        DataTable dts = new DataTable();
        DataTable dtp2 = new DataTable();
        DataTable dtp3 = new DataTable();


        SqlConnection cn, cn1;
        SqlCommand cmd1;
        string sql = "", test = "";

        cn = new SqlConnection(CS_PMI);
        cn.Open();

        cn1 = new SqlConnection(CS_DW);
        cn1.Open();
        sql = "";


        // entête

        // lecture des mois sur la période

        sql = "SELECT year(CAKJDATE),convert(varchar,DateName( month , DateAdd( month , month(CAKJDATE) , -1 ))),'01/'+substring(CAKJDATE,5,2)+'/'+substring(CAKJDATE,1,4),COUNT(*)  ";
        sql = sql + "FROM [PMI].[dbo].[CALEND] ";
        sql = sql + "where CAKTSOC='100' and CAKTTYPE='01' and CAKTCODE='' and CACTACTIF='O' ";
        sql = sql + "and cast(CAKJDATE as datetime)>=convert(varchar,'" + Tdate_d + "',103) and cast(CAKJDATE as datetime)< convert(varchar,cast('" + Tdate_d + "' as datetime)+390,103) ";
        sql = sql + "group by convert(varchar,DateName( month , DateAdd( month , month([CAKJDATE]) , -1 ))),month([CAKJDATE]),year([CAKJDATE]),'01/'+substring(CAKJDATE,5,2)+'/'+substring(CAKJDATE,1,4) ";
        sql = sql + "order by year([CAKJDATE]),month([CAKJDATE]); ";


        try
        {
            dam = new SqlDataAdapter(sql, cn);
            dsm = new DataSet();
            dam.Fill(dsm, "lstmois");
            dtm = dsm.Tables["lstmois"];

            //Lblerreur.Text = dtm.Rows[1][2].ToString();
        }
        catch (SqlException e)
        {

            cn.Close();
            cn1.Close();
        }

        // construction ligne entete du tableau
        sql = "(SELECT 'Périodes' as entite,'" + dtm.Rows[0][0].ToString() + "<br>" + dtm.Rows[0][1].ToString() + "' as M01,'" + dtm.Rows[1][0].ToString() + "<br>" + dtm.Rows[1][1].ToString() + "' as M02,'" + dtm.Rows[2][0].ToString() + "<br>" + dtm.Rows[2][1].ToString() + "' as M03,'" + dtm.Rows[3][0].ToString() + "<br>" + dtm.Rows[3][1].ToString() + "' as M04,'" + dtm.Rows[4][0].ToString() + "<br>" + dtm.Rows[4][1].ToString() + "' as M05,'" + dtm.Rows[5][0].ToString() + "<br>" + dtm.Rows[5][1].ToString() + "' as M06,'" + dtm.Rows[6][0].ToString() + "<br>" + dtm.Rows[6][1].ToString() + "' as M07,'" + dtm.Rows[7][0].ToString() + "<br>" + dtm.Rows[7][1].ToString() + "' as M08,'" + dtm.Rows[8][0].ToString() + "<br>" + dtm.Rows[8][1].ToString() + "' as M09,'" + dtm.Rows[9][0].ToString() + "<br>" + dtm.Rows[9][1].ToString() + "' as M10,'" + dtm.Rows[10][0].ToString() + "<br>" + dtm.Rows[10][1].ToString() + "' as M11,'" + dtm.Rows[11][0].ToString() + "<br>" + dtm.Rows[11][1].ToString() + "' as M12,'Total' as TT) ";

        try
        {
            dad = new SqlDataAdapter(sql, cn);
            //dsm = new DataSet();
            dad.Fill(dsm, "entete");

            List<string> ListEntete = new List<string>();

            for (int i = 0; i <= 13; i++)
            {
                ListEntete.Add(dsm.Tables["entete"].Rows[0][i].ToString());
            }

            list.Add("entete", ListEntete);
        }
        catch (SqlException e)
        {

            cn.Close();
            cn1.Close();
        }

Yet all my queries are correct because I use them in another project.

Thank you in advance for your help

Valentin Hrg
  • 71
  • 1
  • 10
  • 1
    Offtopic: Please use parameterized SQL queries: http://stackoverflow.com/questions/35163361/how-can-i-add-user-supplied-input-to-an-sql-statement – RvdK Nov 10 '16 at 15:05
  • ??? I don't understand, this is not the solution to my problem – Valentin Hrg Nov 10 '16 at 15:11
  • @ValentinHrg: that's why I said _offtopic_ – RvdK Nov 10 '16 at 15:12
  • `
    ` is not SQL?!     – Liam Nov 10 '16 at 15:13
  • 1
    For grins and giggles, try removing the literal "
    " from your query. You are creating a string and those will show up as
    in your SQL statement     – Paul Stoner Nov 10 '16 at 15:13
  • 1
    @ValentinHrg The parameterized SQL queries comment is saying that your code is vulnerable to SQL Injection. http://stackoverflow.com/questions/601300/what-is-sql-injection – squillman Nov 10 '16 at 15:13
  • 3
    Your problem *is* the fact that you create a sql statement by string concatenation instead of using parameters. As a result, invalid characters end up inside the query statement. That's how SQL injection attacks happen – Panagiotis Kanavos Nov 10 '16 at 15:14
  • What is the point of this SELECT statement? It simply takes string literals and changes their names? What are you trying to do? – Panagiotis Kanavos Nov 10 '16 at 15:16
  • 1
    Your tables are empty too - you just created them, never loading anything in them. There are no rows to check. What did you expect to find with `dtm.Rows[0][0]` on an empty table? – Panagiotis Kanavos Nov 10 '16 at 15:18

2 Answers2

1

It's the full code :

public Dictionary<string, object> getPICv2(string Tdate_d, string Ddl_fampic, string Ddl_donnee, string Ddl_detail, string Ddl_caracteristique, string Ddl_poste, string Ddl_ilot, string Ddl_nposte, string Ddl_atelier, string Ddl_tposte)
        {
            Dictionary<string, object> list = new Dictionary<string, object>();

            SqlDataAdapter dac, dad, dam;
            DataSet dsm = new DataSet();
            DataSet dsp = new DataSet();
            DataSet dsd = new DataSet();
            DataSet dsc = new DataSet();
            DataSet dsb = new DataSet();
            DataTable dtm = new DataTable();
            DataTable dtc = new DataTable();
            DataTable dtv = new DataTable();
            DataTable dts = new DataTable();
            DataTable dtp2 = new DataTable();
            DataTable dtp3 = new DataTable();


            SqlConnection cn, cn1;
            SqlCommand cmd1;
            string sql = "", test = "";

            cn = new SqlConnection(CS_PMI);
            cn.Open();

            cn1 = new SqlConnection(CS_DW);
            cn1.Open();
            sql = "";


            // entête

            // lecture des mois sur la période

            sql = "SELECT year(CAKJDATE),convert(varchar,DateName( month , DateAdd( month , month(CAKJDATE) , -1 ))),'01/'+substring(CAKJDATE,5,2)+'/'+substring(CAKJDATE,1,4),COUNT(*)  ";
            sql = sql + "FROM [PMI].[dbo].[CALEND] ";
            sql = sql + "where CAKTSOC='100' and CAKTTYPE='01' and CAKTCODE='' and CACTACTIF='O' ";
            sql = sql + "and cast(CAKJDATE as datetime)>=convert(varchar,'" + Tdate_d + "',103) and cast(CAKJDATE as datetime)< convert(varchar,cast('" + Tdate_d + "' as datetime)+390,103) ";
            sql = sql + "group by convert(varchar,DateName( month , DateAdd( month , month([CAKJDATE]) , -1 ))),month([CAKJDATE]),year([CAKJDATE]),'01/'+substring(CAKJDATE,5,2)+'/'+substring(CAKJDATE,1,4) ";
            sql = sql + "order by year([CAKJDATE]),month([CAKJDATE]); ";


            try
            {
                dam = new SqlDataAdapter(sql, cn);
                dsm = new DataSet();
                dam.Fill(dsm, "lstmois");
                dtm = dsm.Tables["lstmois"];

                //Lblerreur.Text = dtm.Rows[1][2].ToString();
            }
            catch (SqlException e)
            {

                cn.Close();
                cn1.Close();
            }

            // construction ligne entete du tableau
            sql = "(SELECT 'Périodes' as entite,'" + dtm.Rows[0][0].ToString() + "<br>" + dtm.Rows[0][1].ToString() + "' as M01,'" + dtm.Rows[1][0].ToString() + "<br>" + dtm.Rows[1][1].ToString() + "' as M02,'" + dtm.Rows[2][0].ToString() + "<br>" + dtm.Rows[2][1].ToString() + "' as M03,'" + dtm.Rows[3][0].ToString() + "<br>" + dtm.Rows[3][1].ToString() + "' as M04,'" + dtm.Rows[4][0].ToString() + "<br>" + dtm.Rows[4][1].ToString() + "' as M05,'" + dtm.Rows[5][0].ToString() + "<br>" + dtm.Rows[5][1].ToString() + "' as M06,'" + dtm.Rows[6][0].ToString() + "<br>" + dtm.Rows[6][1].ToString() + "' as M07,'" + dtm.Rows[7][0].ToString() + "<br>" + dtm.Rows[7][1].ToString() + "' as M08,'" + dtm.Rows[8][0].ToString() + "<br>" + dtm.Rows[8][1].ToString() + "' as M09,'" + dtm.Rows[9][0].ToString() + "<br>" + dtm.Rows[9][1].ToString() + "' as M10,'" + dtm.Rows[10][0].ToString() + "<br>" + dtm.Rows[10][1].ToString() + "' as M11,'" + dtm.Rows[11][0].ToString() + "<br>" + dtm.Rows[11][1].ToString() + "' as M12,'Total' as TT) ";

            try
            {
                dad = new SqlDataAdapter(sql, cn);
                //dsm = new DataSet();
                dad.Fill(dsm, "entete");

                List<string> ListEntete = new List<string>();

                for (int i = 0; i <= 13; i++)
                {
                    ListEntete.Add(dsm.Tables["entete"].Rows[0][i].ToString());
                }

                list.Add("entete", ListEntete);
            }
            catch (SqlException e)
            {

                cn.Close();
                cn1.Close();
            }
Valentin Hrg
  • 71
  • 1
  • 10
  • Update your question, don't add an answer. And check that the big query *does* return results. Most likely it doesn't – Panagiotis Kanavos Nov 10 '16 at 15:42
  • *And* it should be parameterized, the dates passed as dates and conversions between dates and varchar removed. 99,995% that's what's causing your query to return nothing – Panagiotis Kanavos Nov 10 '16 at 15:45
  • All you need is `and CAKJDATA between @startDate and @endDate` where `@starDate` and `@endDate` are passed as datetime parameters. What does the `group by` line do? – Panagiotis Kanavos Nov 10 '16 at 15:46
0

It looks to me that your problem is where you assign the query to the sql variable.

sql = "(SELECT 'Périodes' as entite,'" + dtm.Rows[0][0].ToString() + "<br>" + dtm.Rows[0][1].ToString() + "' as M01,'" + dtm.Rows[1][0].ToString() + "<br>" + dtm.Rows[1][1].ToString() + "' as M02,'" + dtm.Rows[2][0].ToString() + "<br>" + dtm.Rows[2][1].ToString() + "' as M03,'" + dtm.Rows[3][0].ToString() + "<br>" + dtm.Rows[3][1].ToString() + "' as M04,'" + dtm.Rows[4][0].ToString() + "<br>" + dtm.Rows[4][1].ToString() + "' as M05,'" + dtm.Rows[5][0].ToString() + "<br>" + dtm.Rows[5][1].ToString() + "' as M06,'" + dtm.Rows[6][0].ToString() + "<br>" + dtm.Rows[6][1].ToString() + "' as M07,'" + dtm.Rows[7][0].ToString() + "<br>" + dtm.Rows[7][1].ToString() + "' as M08,'" + dtm.Rows[8][0].ToString() + "<br>" + dtm.Rows[8][1].ToString() + "' as M09,'" + dtm.Rows[9][0].ToString() + "<br>" + dtm.Rows[9][1].ToString() + "' as M10,'" + dtm.Rows[10][0].ToString() + "<br>" + dtm.Rows[10][1].ToString() + "' as M11,'" + dtm.Rows[11][0].ToString() + "<br>" + dtm.Rows[11][1].ToString() + "' as M12,'Total' as TT) ";

You reference dtm.Rows[0] but dtm has not yet been filled with any rows as far as I can see.

izzy
  • 622
  • 9
  • 17
  • Thank you for your answer but dtm.Rows[0] is filled. I did not rewrite any code that filled dtm – Valentin Hrg Nov 10 '16 at 15:15
  • This SQL won't work `
    ` is not valid SQL     – Liam Nov 10 '16 at 15:17
  • @ValentinHrg the table is *not* filled, at least not by this code. Post the code that actually demonstrates the problem. Although I *strongly* advise cleaning it up first - eg why write `dtm.Rows[0][0]` all the time when you could store `dtm.Rows[0]` in a variable then use `myRow[0]`, `myRow[1]` ? – Panagiotis Kanavos Nov 10 '16 at 15:20
  • @ValentinHrg also make sure that `dtm` is actually loaded - if the query returns no results, the table remains empty. The error message doesn't lie – Panagiotis Kanavos Nov 10 '16 at 15:22
  • @ValentinHrg No code posted in your question properly fills the datatable dtm, if you are leaving code out intentionally due to length post a small snippet that represents your dataTable being filled. DataTable dtm = new DataTable() will not add rows to your data table, you have only initialized the DataTable Object you would need to manually add rows or fill from a dataset that was populated from a query. Neither of which you appear to be doing. – Bearcat9425 Nov 10 '16 at 15:28
  • how can I fill it concretely ? :/ – Valentin Hrg Nov 10 '16 at 15:30