verifying a filled field in a select in the database

Question

verifying a filled field in a select in the database

Navigation

#1 by (0 votes)

0

Good morning ...

I need a help from the forum, and nen I know how I can do this in C #.

I'm trying to explain, I have form and in this form I have a button that generates a sequential two-digit code, right up to the beauty generating, there I write this information in a table in sql, my table contains cod_product, lot and cod_seq until ai blz recording everything right on the bench. Now comes my problem, when I enter the form again and enter product code and also the batch number, I have to check in these fields in this sql table, if the batch already exists, for that product code, if yes the batch recorded there and it generates a next sequential code, if not, there is not the batch that I am typing recorded in the table yet it starts the sequential code of 01 again.

I'm going to post below my sql table and my button code generate code. ID_ETQN COD_PROD LOT COD_SEQ 1 000842 160309 1 2 000842 160309 2

public partial class frmCodNatura : Form
    {
        private int _contador = 0;

    SqlConnection conex = new SqlConnection(Properties.Settings.Default.DADOSADVConnectionString);
    SqlConnection conex1 = new SqlConnection(Properties.Settings.Default.USUARIOSConnectionString);

    SqlCommand comando = null;

    public frmCodNatura()
    {
        InitializeComponent();


    }

    private void frmCodNatura_Load(object sender, EventArgs e)
    {

    }

    private void ListaGrid()
    {
        string strSQL = @"SELECT 
SB.B1_COD                             AS [COD. PRODUTO],
SB.B1_DESC                            AS DESCRIÇÃO,
S8.B8_LOTECTL                         AS LOTE,
SUBSTRING(S8.B8_DFABRIC, 1, 4)        AS ANO,
SUBSTRING(S8.B8_DFABRIC, 5, 2)        AS MES,
CASE SUBSTRING(S8.B8_DFABRIC, 1, 4)
WHEN 1999 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 1999)
WHEN 2000 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2000)
WHEN 2001 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2001)
WHEN 2002 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2002)
WHEN 2003 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2003)
WHEN 2004 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2004)
WHEN 2005 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2005)
WHEN 2006 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2006)
WHEN 2007 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2007)
WHEN 2008 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2008)
WHEN 2009 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2009)
WHEN 2010 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2010)
WHEN 2011 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2011)
WHEN 2012 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2012)
WHEN 2013 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2013)
WHEN 2014 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2014)
WHEN 2015 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2015)
WHEN 2016 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2016)
WHEN 2017 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2017)
WHEN 2018 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2018)
WHEN 2019 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2019)
WHEN 2020 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2020)
WHEN 2021 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2021)
WHEN 2022 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2022)
WHEN 2023 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2023)
WHEN 2024 THEN  (SELECT A.LETRA FROM USUARIOS.dbo.tbl_Ano A WHERE A.ANO = 2024)
END AS COD_ANO,
CASE SUBSTRING(S8.B8_DFABRIC, 5, 2)
WHEN 01 THEN (SELECT M.COD_MES FROM USUARIOS.dbo.tbl_Mes M WHERE M.COD_MES = '1')
WHEN 02 THEN (SELECT M.COD_MES FROM USUARIOS.dbo.tbl_Mes M WHERE M.COD_MES = '2')
WHEN 03 THEN (SELECT M.COD_MES FROM USUARIOS.dbo.tbl_Mes M WHERE M.COD_MES = '3')
WHEN 04 THEN (SELECT M.COD_MES FROM USUARIOS.dbo.tbl_Mes M WHERE M.COD_MES = '4')
WHEN 05 THEN (SELECT M.COD_MES FROM USUARIOS.dbo.tbl_Mes M WHERE M.COD_MES = '5')
WHEN 06 THEN (SELECT M.COD_MES FROM USUARIOS.dbo.tbl_Mes M WHERE M.COD_MES = '6')
WHEN 07 THEN (SELECT M.COD_MES FROM USUARIOS.dbo.tbl_Mes M WHERE M.COD_MES = '7')
WHEN 08 THEN (SELECT M.COD_MES FROM USUARIOS.dbo.tbl_Mes M WHERE M.COD_MES = '8')
WHEN 09 THEN (SELECT M.COD_MES FROM USUARIOS.dbo.tbl_Mes M WHERE M.COD_MES = '9')
WHEN 10 THEN (SELECT M.COD_MES FROM USUARIOS.dbo.tbl_Mes M WHERE M.COD_MES = 'A')
WHEN 11 THEN (SELECT M.COD_MES FROM USUARIOS.dbo.tbl_Mes M WHERE M.COD_MES = 'B')
WHEN 12 THEN (SELECT M.COD_MES FROM USUARIOS.dbo.tbl_Mes M WHERE M.COD_MES = 'C')
END AS COD_MES,
COD_EMPRESA = 'PQ'
FROM SB1010 AS SB
INNER JOIN SB8010 AS S8 WITH (NOLOCK) ON S8.B8_PRODUTO = SB.B1_COD 
WHERE SB.B1_COD = '" + txtCodigo.Text + "' AND S8.B8_LOTECTL = '" + txtLote.Text + "' AND S8.B8_LOCAL = '01' ";

        comando = new SqlCommand(strSQL, conex);

        try
        {
            SqlDataAdapter dados = new SqlDataAdapter(comando);
            DataTable dtLista = new DataTable();
            dados.Fill(dtLista);

            DGW_CodNatura.DataSource = dtLista;
        }
        catch
        {
            MessageBox.Show("Não existem dados a serem encontrados");
        }         
    }

    public string retornaNome(string DESC)
    {
        // criei uma variável para retornar o nome
        string ret = string.Empty;

        {
            // mando abrir
            conex.Open();

            // faço a query, concatenando com o txtCodigo ( A1_COD = '" + txtCodigo + "'";  )
            string strSQL = @"SELECT 
                              SB.B1_DESC
                              FROM SB1010 SB
                              WHERE SB.B1_COD = '" + txtCodigo.Text + "'";

            // rodo o comando, passando a query e a conexão
            comando = new SqlCommand(strSQL, conex);

            // Lendo o resultado atraves do command.ExecuteReader
            using (SqlDataReader reader = comando.ExecuteReader())
            {
                // se tiver dados
                while (reader.Read())
                {
                    // mando ler o primeiro campo (e único também = A1_NOME)
                    reader.GetString(0);

                    // jogo na variavel de retorno, como é um só, eu dou break pra sair do laço (nem precisava)
                    ret = reader[0].ToString();
                    break;
                }
            }
        }

        // retorno o valor (A1_NOME) desejado
        return ret;
    }
    private void txtCodigo_KeyDown(object sender, KeyEventArgs e)
    {
        if (e.KeyCode == Keys.Enter)
        {
            // Ele vai lá na função que criei, passando o codCliente e retorna no campo do nome (txtNomeCliente.Text)
            txtProduto.Text = retornaNome(txtCodigo.Text);

            // Manda o cursor dar foco no txtDataDe
            txtLote.Focus();
        }
    }

    private void btnPesquisar_Click(object sender, EventArgs e)
    {
        ListaGrid();
    }

    private void DGW_CodNatura_CellClick(object sender, DataGridViewCellEventArgs e)
    {
        DataGridViewRow row = this.DGW_CodNatura.Rows[e.RowIndex];

        this.txtCodAno.Text = row.Cells[5].Value.ToString();
        this.txtCodMes.Text = row.Cells[6].Value.ToString();
        this.txtInicalEmpresa.Text = row.Cells[7].Value.ToString();

    }

    private void btnFechar_Click(object sender, EventArgs e)
    {
        Close();
    }

    private void btnGeraCod_Click(object sender, EventArgs e)
    {

        this._contador++;
        this.txtCodSeq.Text = this._contador.ToString().PadLeft(2, '0');

        txtSkuNatura.Text = txtCodAno.Text + txtCodMes.Text + txtInicalEmpresa.Text + txtCodSeq.Text;
    }

    private void btnSalvar_Click(object sender, EventArgs e)
    {
        if (MessageBox.Show("Confirma a inclusão do cadastro", "Atenção", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO tbl_CodSeq (COD_PROD, LOTE, COD_SEQ ) VALUES ('" + txtCodigo.Text + "', '" + txtLote.Text + "', '" + txtCodSeq.Text + "')";
            cmd.Connection = conex1;

            conex1.Open();
            cmd.ExecuteNonQuery();
            conex1.Close();
        }
    }
  }
}

c#

asked by anonymous 14.06.2017 / 15:31

1 answer

I used the code below, and the code below. But it does not save the title of the Jtable lines, how to save the titles of the lines? How to change maximum limit of simultaneous connections to db

score 0 · Accepted Answer

You will have to check if there is any record in the tbl_CodSeq table with the batch and product code equal, if it exists, it generates the next sequence.

You did not specify the type of the sequential code column, but I'll assume it's numeric, so you can do a query that returns the next sequence if it exists and if it does not exist.

Ex: select isnull (max (sequential_code), 0) + 1 from tbl_CodSeq where ....

So if there is no record with that where the value of the code that will return will be 1 and if it exists it will return the largest + 1 for you to use as a sequential new product to add.