by checking a field filled in a select in the database

Question

Good morning...

I’m in need of a help from the forum, and nen know how to do it in the c#.

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

I will post below my sql table and my button code generate code. ID_ETQN COD_PROD LOTE 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();
        }
    }
  }
}

Answer 1

You will have to before recording query if there is any record in table tbl_CodSeq with the same batch and product Cod, if there is generates the next sequence.

You did not specify the type of the sequential code column, but I will imagine that it is 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(cod_sequential),0) + 1 from tbl_CodSeq Where....

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