Fill table and add rows as Json result

Asked

Viewed 785 times

0

I am trying to fill a table that generates the number of lines automatically as the result of Json, I can do the Json work, it brings all the values I need, however, when updating the table with this information, the fields comes filled with the last value only.

Example: array[1; 2; 3],

Campos: Field = 3, field2 = 3, field3 = 3.

I believe he is updating all the fields every time he goes through the routine. What I want is for him to bring the values of Json into each created field, each time a value is placed it passes to the next and only updates the next created field.

The code is like this:

$(document).ready(function () {

    $('inp:matricula').blur(function () {
        var IdAluno = $('inp:matricula').val();

        $.getJSON("..JsonAprEst.aspx?inpMatricula=" + IdAluno, function (data) {
            var items = data.rows;
            if (items.length > 0) {
                $.each(items, function (i, row) {
                    InsertNewRow($('table#TabelaDisciplinas button#BtnInsertNewRow')[0], true);
                    var vDisciplina = $("inp:disciplina");//.closest("tr").find("input[xname='inpdisciplina']");
                    vDisciplina.val(row.Disciplina);
                    console.log(vDisciplina);
                });
            }
        });
    });
});

Follows below the Json used:

<%@ Page Language="C#" ContentType="application/json" Debug="true" %>
<%

    Response.Charset = "iso-8859-1";

    StringBuilder json = new StringBuilder();
    json.Append("{");

    string parametroPrincipal = Request["inpMatricula"];

    if (string.IsNullOrEmpty(parametroPrincipal))
    {
        json.AppendFormat("\"{0}\" : {1},", "success", "false");
        json.AppendFormat("\"{0}\" : \"{1}\",", "error", "Parâmetro principal não foi passado ou está vazio.");
    }
    else
    {
        json.AppendFormat("\"{0}\" : {1},", "success", "true");

        // tratar o parametro parametroPrincipal
        if (parametroPrincipal.IndexOf("'") != -1)
        {
            parametroPrincipal = parametroPrincipal.Replace("'", "''");
        }

        parametroPrincipal = System.Web.HttpContext.Current.Server.UrlDecode(parametroPrincipal);
    }

    // INICIO BLOCO CORPO JSON
    if (!string.IsNullOrEmpty(parametroPrincipal))
    {
        // cria os objetos de banco de dados
        System.Data.Common.DbProviderFactory dbFactory = null;
        System.Data.Common.DbConnection dbConnection;
        System.Data.Common.DbCommand dbCommand;
        System.Data.Common.DbParameter dbParameter;
        System.Data.Common.DbDataReader dbDataReader;
        System.Data.DataTable table;

        string connectionString = "Data Source=10.2.100.161;User ID=Cadsoftread; Password=Cadsoft$Uva; Initial Catalog=Universus_UVA";

        // SQL SERVER
        dbFactory = System.Data.SqlClient.SqlClientFactory.Instance;

        dbConnection = dbFactory.CreateConnection();
        dbConnection.ConnectionString = connectionString;

        // abre conexao
        dbConnection.Open();

        // Consulta sql a ser executada

        string sql;

        // sql = "SELECT CodAluno, CodAlunoEscola ";
        // sql += "FROM Aluno WITH (NOLOCK) "; 
        // sql += "WHERE CodAlunoEscola = @Matricula";

        sql = "select  ";
        sql += "    ESCOLA.CODESCOLA,  ";
        sql += "    ALUNO.CODALUNO,  ";
        sql += "    CURSO.CODCURSO,  ";
        sql += "    CAMPUS.CODCAMPUS,  ";
        sql += "    CURRICULO.CODCURRICULO, ";  
        sql += "    escola.nome as Escola, ";
        sql += "    aluno.codaluno, ";
        sql += "    aluno.CODALUNOESCOLA as Matricula, ";
        sql += "    pessoa.nome as Aluno, ";
        sql += "    campus.nome as Campus, ";
        sql += "    pessoa.email as Email, ";
        //sql += "    pessoa.telefone as Telefone";
        sql += "    curriculo.descurriculo as Curriculo, ";
        sql += "    curso.nome as Curso, ";
        sql += "    turno.nome as Turno, ";
        sql += "    Aluno_CargaHoraria.TotalExigido, ";
        sql += "    Aluno_CargaHoraria.MediaAluno, ";
        sql += "    Aluno_CargaHoraria.ETAPANATURAL ";
        sql += "    , convert(numeric, substring(Aluno_CargaHoraria.TotalExigido, 1, charindex(':', Aluno_CargaHoraria.TotalExigido) - 1)) as CH_TotalExigida ";
        sql += "    , (case  ";
        sql += "        when convert(numeric, substring(Aluno_CargaHoraria.ChAtivCompCursada, 1, charindex(':', Aluno_CargaHoraria.ChAtivCompCursada) - 1)) > convert(numeric, substring(Aluno_CargaHoraria.CHAtvCompExigObr, 1, charindex(':', Aluno_CargaHoraria.CHAtvCompExigObr) - 1)) then convert(numeric, substring(Aluno_CargaHoraria.CHAtvCompExigObr, 1, charindex(':', Aluno_CargaHoraria.CHAtvCompExigObr) - 1))  ";
        sql += "        else convert(numeric, substring(Aluno_CargaHoraria.ChAtivCompCursada, 1, charindex(':', Aluno_CargaHoraria.ChAtivCompCursada) - 1)) ";
        sql += "     end) +  ";
        sql += "     (case  ";
        sql += "        when convert(numeric, substring(Aluno_CargaHoraria.ChOptExtraCursada, 1, charindex(':', Aluno_CargaHoraria.ChOptExtraCursada) - 1)) > convert(numeric, substring(Aluno_CargaHoraria.CHOptExigObr, 1, charindex(':', Aluno_CargaHoraria.CHOptExigObr) - 1)) then convert(numeric, substring(Aluno_CargaHoraria.CHOptExigObr, 1, charindex(':', Aluno_CargaHoraria.CHOptExigObr) - 1))  ";
        sql += "        else convert(numeric, substring(Aluno_CargaHoraria.ChOptExtraCursada, 1, charindex(':', Aluno_CargaHoraria.ChOptExtraCursada) - 1)) ";
        sql += "     end) + convert(numeric, substring(Aluno_CargaHoraria.CHObrigatCursada, 1, charindex(':', Aluno_CargaHoraria.CHObrigatCursada) - 1)) as CH_TotalCursada ";
        sql += "    , convert(numeric, substring(Aluno_CargaHoraria.CHTotalExigObr, 1, charindex(':', Aluno_CargaHoraria.CHTotalExigObr) - 1)) as CH_ObrigatoriaExigida ";
        sql += "    , convert(numeric, substring(Aluno_CargaHoraria.CHObrigatCursada, 1, charindex(':', Aluno_CargaHoraria.CHObrigatCursada) - 1)) as CH_ObrigatoriaCursada ";
        sql += "    , convert(numeric, substring(Aluno_CargaHoraria.CHAtvCompExigObr, 1, charindex(':', Aluno_CargaHoraria.CHAtvCompExigObr) - 1)) as CH_AtivComplementarExigida ";
        sql += "    , convert(numeric, substring(Aluno_CargaHoraria.ChAtivCompCursada, 1, charindex(':', Aluno_CargaHoraria.ChAtivCompCursada) - 1)) as CH_AtivComplementarCusada ";
        sql += "    , convert(numeric, substring(Aluno_CargaHoraria.CHOptExigObr, 1, charindex(':', Aluno_CargaHoraria.CHOptExigObr) - 1)) as CH_OptativaExigida ";
        sql += "    , convert(numeric, substring(Aluno_CargaHoraria.ChOptExtraCursada, 1, charindex(':', Aluno_CargaHoraria.ChOptExtraCursada) - 1)) as CH_OptativaCursada ";
        sql += "    , convert(numeric, substring(Aluno_CargaHoraria.CHoptCursando, 1, charindex(':', Aluno_CargaHoraria.CHoptCursando) - 1)) as CH_OptativaCursando ";
        sql += "    , convert(numeric, substring(Aluno_CargaHoraria.CHobrCursando, 1, charindex(':', Aluno_CargaHoraria.CHobrCursando) - 1)) as CH_ObrigatoriaCursando ";
        sql += "    , Aluno_CargaHoraria.QtdCreditoCursado as CreditosCursados ";
        sql += "    , Aluno_CargaHoraria.QtdCreditoCursando as CreditosCursando ";
        sql += "    , convert(numeric(9,2),(((case  ";
        sql += "        when convert(numeric, substring(Aluno_CargaHoraria.ChAtivCompCursada, 1, charindex(':', Aluno_CargaHoraria.ChAtivCompCursada) - 1)) > convert(numeric, substring(Aluno_CargaHoraria.CHAtvCompExigObr, 1, charindex(':', Aluno_CargaHoraria.CHAtvCompExigObr) - 1)) then convert(numeric, substring(Aluno_CargaHoraria.CHAtvCompExigObr, 1, charindex(':', Aluno_CargaHoraria.CHAtvCompExigObr) - 1))  ";
        sql += "        else convert(numeric, substring(Aluno_CargaHoraria.ChAtivCompCursada, 1, charindex(':', Aluno_CargaHoraria.ChAtivCompCursada) - 1)) ";
        sql += "     end) +  ";
        sql += "     (case  ";
        sql += "        when convert(numeric, substring(Aluno_CargaHoraria.ChOptExtraCursada, 1, charindex(':', Aluno_CargaHoraria.ChOptExtraCursada) - 1)) > convert(numeric, substring(Aluno_CargaHoraria.CHOptExigObr, 1, charindex(':', Aluno_CargaHoraria.CHOptExigObr) - 1)) then convert(numeric, substring(Aluno_CargaHoraria.CHOptExigObr, 1, charindex(':', Aluno_CargaHoraria.CHOptExigObr) - 1))  ";
        sql += "        else convert(numeric, substring(Aluno_CargaHoraria.ChOptExtraCursada, 1, charindex(':', Aluno_CargaHoraria.ChOptExtraCursada) - 1)) ";
        sql += "     end) + convert(numeric, substring(Aluno_CargaHoraria.CHObrigatCursada, 1, charindex(':', Aluno_CargaHoraria.CHObrigatCursada) - 1))) / convert(numeric, substring(Aluno_CargaHoraria.TotalExigido, 1, charindex(':', Aluno_CargaHoraria.TotalExigido) - 1))) * 100) as PercentualCursado ";
        sql += "from aluno with(nolock) ";
        sql += "inner join pessoa with(nolock) ";
        sql += "    on pessoa.codpessoa = aluno.codpessoa ";
        sql += "inner join escola with(nolock) ";
        sql += "    on escola.codescola = aluno.codescola ";
        sql += "    and escola.codescola = aluno.codescola ";
        sql += "inner join ALUCURRICULO with(nolock) ";
        sql += "    on ALUCURRICULO.codescola = aluno.codescola ";
        sql += "    and ALUCURRICULO.codaluno = aluno.codaluno ";
        sql += "inner join campus with(nolock) ";
        sql += "    on campus.codescola = alucurriculo.codescola ";
        sql += "    and campus.codcampus = alucurriculo.codcampus ";
        sql += "inner join mudstacur with(nolock) ";
        sql += "    on mudstacur.CODESCOLA = ALUCURRICULO.CODESCOLA ";
        sql += "    and mudstacur.codaluno = ALUCURRICULO.CODALUNO ";
        sql += "    and mudstacur.CODCURRICULO = ALUCURRICULO.CODCURRICULO ";
        sql += "    and MUDSTACUR.CODMUDSTACUR = (select max(X.CodMudstacur) from MUDSTACUR as X with(nolock) ";
        sql += "                                    where mudstacur.CODESCOLA = X.CODESCOLA ";
        sql += "                                    and mudstacur.codaluno = X.CODALUNO) ";
        sql += "inner join CURRICULO with(nolock) ";
        sql += "    on ALUCURRICULO.codescola = CURRICULO.codescola ";
        sql += "    and ALUCURRICULO.codCURRICULO = CURRICULO.codCURRICULO ";
        sql += "inner join turno with(nolock) ";
        sql += "    on turno.codturno = curriculo.codturno ";
        sql += "inner join CURSO with(nolock) ";
        sql += "    on CURSO.codescola = CURRICULO.codescola ";
        sql += "    and CURSO.CODCURSO = CURRICULO.CODCURSO ";
        sql += "inner join Aluno_CargaHoraria with(nolock) ";
        sql += "    on Aluno_CargaHoraria.Codescola = aluno.CODESCOLA ";
        sql += "    and Aluno_CargaHoraria.CodAluno = aluno.CODALUNO ";
        sql += "    and Aluno_CargaHoraria.CodCurriculo = ALUCURRICULO.CODCURRICULO  ";
        sql += "inner join CONFIGGERAL with(nolock) ";
        sql += "    on CONFIGGERAL.ANOPADRAO = Aluno_CargaHoraria.ANO ";
        sql += "    and CONFIGGERAL.REGIMEPADRAO = Aluno_CargaHoraria.REGIME ";
        sql += "    and CONFIGGERAL.PERIODOPADRAO = Aluno_CargaHoraria.PERIODO ";
        sql += "WHERE CodAlunoEscola = @Matricula ";


        // criar comando sql
        dbCommand = dbFactory.CreateCommand();
        dbCommand.Connection = dbConnection;
        dbCommand.CommandText = sql;
        dbCommand.CommandType = System.Data.CommandType.Text;

        // criar parametro
        dbParameter = dbFactory.CreateParameter();
        dbParameter.DbType = System.Data.DbType.String;
        dbParameter.ParameterName = "@Matricula";
        dbParameter.Value = parametroPrincipal;

        // adiciona parametro ao comando
        dbCommand.Parameters.Add(dbParameter);

        dbDataReader = dbCommand.ExecuteReader();

        table = new System.Data.DataTable();
        table.Load(dbDataReader);

        json.AppendFormat("\"{0}\" : {1}", "rows", "[");

        // percorre linhas e colunas de forma generica
        // permite criar o JSON para qualquer query
        for (int i = 0; i < table.Rows.Count; i++)
        {
            json.Append("{");

            for (int j = 0; j < table.Columns.Count; j++)
            {
                json.AppendFormat("\"{0}\" : \"{1}\"{2}", table.Columns[j].ColumnName, table.Rows[i][j].ToString(), ((j < table.Columns.Count-1) ? "," : "" ));
            } // end for j

            json.Append("}");

            if (i < table.Rows.Count-1)
            {
                json.Append(",");
            }

        } // end for i

        json.Append("]");

        // liberar objetos da memoria
        table.Dispose();
        dbDataReader.Close();
        dbDataReader.Dispose();

        // fechar conexao
        dbConnection.Close();
        dbConnection.Dispose();

    } // end if
    // FIM BLOCO CORPO JSON

    json.Append("}");

    Response.Write(json.ToString());

%>
  • 3

    If you can, share with us your json and the Insertnewrow function

  • I unfortunately do not have the code of the function Insertnewrow, it was a shared work, only that the last programmer left the area and did not pass the complete code. But what the function does is add a new row, with the same name, and same columns in the previous one. Already Json, I’ll put.

  • In cases where I need to create a table dynamically using a JSON, I usually use the Jquery Datatables plugin. Here is an example of how to use it: https://datatables.net/examples/data_sources/ajax.html. More specific example: https://stackoverflow.com/questions/9669812/how-can-i-use-a-local-json-object-a-data-source-for-jquery-datatables.

  • Why didn’t you add the language C#in tags, it gets hard to help like this.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.