COUNT in multiple mysql tables

Asked

Viewed 59 times

1

Good afternoon, someone can inform me that there is wrong in the code below ?

Because he just does the Count check on one table the other he ignores.

Insert is correct but "Select Count" does not check all only the first command.

follows the code:

'inicia uma FOR para verificação e impedir inserts de duplicados.

For r As Integer = 0 To DataGridView1.Rows.Count - 1

Dim resultquery As String = "SELECT 

(SELECT COUNT(cnpjcpf_cnpjcpf) FROM cnpjcpf WHERE cnpjcpf_cnpjcpf ='" & DataGridView1.Rows(r).Cells(7).Value & "'),

(SELECT COUNT(mov_identificacao) FROM movimento WHERE mov_identificacao ='" & DataGridView1.Rows(r).Cells(0).Value.ToString & "'),

(SELECT COUNT(imov_mov_identificacao) FROM imovimento WHERE imov_mov_identificacao ='" & DataGridView1.Rows(r).Cells(0).Value.ToString & "')"

 command = New MySqlCommand(resultquery, sqlcoon)
 resultquery = command.ExecuteScalar()

   If resultquery = 0 Then

' insere os dados do datagridview na tabela do banco de dados

  Dim Query As String = '"insert into cnpjcpf (cnpjcpf_cnpjcpf) values(@cnpjcpf_cnpjcpf);" +
                         "insert into movimento (mov_identificacao,mov_protocolo,mov_destinatario,mov_endereco,mov_bairro,mov_cep,mov_datadoc,mov_cnpj_cpf,mov_cli_codigo,mov_dataprocessa,mov_datavencimento) values(@mov_identificacao, @mov_protocolo, @mov_destinatario, @mov_endereco, @mov_bairro, @mov_cep, @mov_datadoc, @mov_cnpj_cpf, @mov_cli_codigo, @mov_dataprocessa, @mov_datavencimento);" +
                         "insert into imovimento (imov_mov_identificacao,imov_mov_protocolo,imov_dataprocessa) values(@imov_mov_identificacao,@imov_mov_protocolo,@imov_dataprocessa);"
                    Dim CmdSalvarItens As New MySqlCommand(Query, sqlcoon)

     'EXECUTA O INSERT NA TABELA CNPJ/CPF
      CmdSalvarItens.Parameters.Add("@cnpjcpf_cnpjcpf", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells(7).Value

       'EXECUTA O INSERT NA TABELA MOVIMENTOS
       CmdSalvarItens.Parameters.Add("@mov_identificacao", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells("Identificação").Value
       CmdSalvarItens.Parameters.Add("@mov_protocolo", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells("Protocolo").Value
       CmdSalvarItens.Parameters.Add("@mov_destinatario", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells("Destinatario").Value
       CmdSalvarItens.Parameters.Add("@mov_endereco", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells("Endereço").Value
       CmdSalvarItens.Parameters.Add("@mov_bairro", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells("Bairro").Value
       CmdSalvarItens.Parameters.Add("@mov_cep", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells("Cep").Value
       CmdSalvarItens.Parameters.Add("@mov_datadoc", MySqlDbType.Date).Value = dinicio
       CmdSalvarItens.Parameters.Add("@mov_cnpj_cpf", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells("CNPF/CPF").Value
       CmdSalvarItens.Parameters.Add("@mov_cli_codigo", MySqlDbType.VarChar).Value = txtcod.Text
       CmdSalvarItens.Parameters.Add("@mov_dataprocessa", MySqlDbType.Date).Value = dfim
       CmdSalvarItens.Parameters.Add("@mov_datavencimento", MySqlDbType.Date).Value = ddata

       'EXECUTA O INSERT NA TABELA IMOVIMENTOS
       CmdSalvarItens.Parameters.Add("@imov_mov_identificacao", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells("Identificação").Value
       CmdSalvarItens.Parameters.Add("@imov_mov_protocolo", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells("Protocolo").Value
       CmdSalvarItens.Parameters.Add("@imov_dataprocessa", MySqlDbType.VarChar).Value = dfim


          CmdSalvarItens.ExecuteNonQuery()

        Else

        End If

       Next

Can anyone tell me what is wrong with (string - Resultquery) where 3 select Count is being made??

mysql select vb.net

1 answer

1


I think this solves your problem:

Dim reader As MySqlDataReader
Dim CmdSalvarItens As MySqlCommand
Dim Query As String
Dim resultquery As String

Dim strCPF As String
Dim strMovimentos As String
Dim strImovimentos As String

'inicia uma FOR para verificação e impedir inserts de duplicados.
For r As Integer = 0 To DataGridView1.Rows.Count - 1
    strCPF = DataGridView1.Rows(r).Cells(7).Value.ToString
    strMovimentos = DataGridView1.Rows(r).Cells(0).Value.ToString
    strImovimentos = DataGridView1.Rows(r).Cells(0).Value.ToString

    resultquery = "SELECT 
        (SELECT COUNT(cnpjcpf_cnpjcpf) FROM cnpjcpf WHERE cnpjcpf_cnpjcpf ='" & strCPF & "'),
        (SELECT COUNT(mov_identificacao) FROM movimento WHERE mov_identificacao ='" & strMovimentos & "'),
        (SELECT COUNT(imov_mov_identificacao) FROM imovimento WHERE imov_mov_identificacao ='" & strImovimentos & "')"

    command = New MySqlCommand(resultquery, sqlcoon)
    reader = command.ExecuteReader()
    reader.Read()

    Dim objs(3) As Object
    Dim quant As Integer = reader.GetValues(objs)

    'TABELA CNPJ/CPF
    If objs(0) = 0 Then
        'insere os dados do datagridview na tabela do banco de dados
        Query = "insert into cnpjcpf (cnpjcpf_cnpjcpf) values(@cnpjcpf_cnpjcpf);"

        CmdSalvarItens = New MySqlCommand(Query, sqlcoon)
        CmdSalvarItens.Parameters.Add("@cnpjcpf_cnpjcpf", MySqlDbType.VarChar).Value = strCPF

        CmdSalvarItens.ExecuteNonQuery()
    End If

    'TABELA MOVIMENTOS
    If objs(1) = 0 Then
        'insere os dados do datagridview na tabela do banco de dados
        Query = "insert into movimento (mov_identificacao,mov_protocolo,mov_destinatario,mov_endereco,mov_bairro,mov_cep,mov_datadoc,mov_cnpj_cpf,mov_cli_codigo,mov_dataprocessa,mov_datavencimento) values(@mov_identificacao, @mov_protocolo, @mov_destinatario, @mov_endereco, @mov_bairro, @mov_cep, @mov_datadoc, @mov_cnpj_cpf, @mov_cli_codigo, @mov_dataprocessa, @mov_datavencimento);"

        CmdSalvarItens = New MySqlCommand(Query, sqlcoon)
        CmdSalvarItens.Parameters.Add("@mov_identificacao", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells("Identificação").Value
        CmdSalvarItens.Parameters.Add("@mov_protocolo", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells("Protocolo").Value
        CmdSalvarItens.Parameters.Add("@mov_destinatario", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells("Destinatario").Value
        CmdSalvarItens.Parameters.Add("@mov_endereco", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells("Endereço").Value
        CmdSalvarItens.Parameters.Add("@mov_bairro", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells("Bairro").Value
        CmdSalvarItens.Parameters.Add("@mov_cep", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells("Cep").Value
        CmdSalvarItens.Parameters.Add("@mov_datadoc", MySqlDbType.Date).Value = dinicio
        CmdSalvarItens.Parameters.Add("@mov_cnpj_cpf", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells("CNPF/CPF").Value
        CmdSalvarItens.Parameters.Add("@mov_cli_codigo", MySqlDbType.VarChar).Value = txtcod.Text
        CmdSalvarItens.Parameters.Add("@mov_dataprocessa", MySqlDbType.Date).Value = dfim
        CmdSalvarItens.Parameters.Add("@mov_datavencimento", MySqlDbType.Date).Value = ddata

        CmdSalvarItens.ExecuteNonQuery()
    End If

    'TABELA CNPJ/CPF
    If objs(2) = 0 Then
        'insere os dados do datagridview na tabela do banco de dados
        Query = "insert into imovimento (imov_mov_identificacao,imov_mov_protocolo,imov_dataprocessa) values(@imov_mov_identificacao,@imov_mov_protocolo,@imov_dataprocessa);"

        CmdSalvarItens = New MySqlCommand(Query, sqlcoon)
        CmdSalvarItens.Parameters.Add("@imov_mov_identificacao", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells("Identificação").Value
        CmdSalvarItens.Parameters.Add("@imov_mov_protocolo", MySqlDbType.VarChar).Value = DataGridView1.Rows(r).Cells("Protocolo").Value
        CmdSalvarItens.Parameters.Add("@imov_dataprocessa", MySqlDbType.VarChar).Value = dfim

        CmdSalvarItens.ExecuteNonQuery()
    End If

    reader.Close()
Next

The problem was that I was using ExecuteScalar(), that brings you only one value, when in fact we want to bring 3.

Having said that, we use the ExecuteReader() to read all the results (which in this case is just a row with 3 columns) and then we will one by one run the darlings.

  • thanks for the return John , but has not yet worked out his example. even if I modify the code the way he advised me. returns the following error : If myreader.Getint32(1) = 0 Then --> "You have specified an invalid column ordinal." and I had to add a Reader.Close() later from Reader.read() or I would point out that I already had a Reader being used.

  • Edited answer, there were some errors! Try again :)

  • John remained the error. I did a test. I commented on all the lines where it has ' Table Movement and Immovements . and left only the IF of the cnpj/Cpf table. and then I added a Reader.close() so -> If Reader.Getint32(0) = 0 Then Reader.Close() - It worked perfectly. I decoded the IF from the Movements table and tried to do the same though. error gave the same error (""You have specified an invalid column ordinal." I believe there’s only one detail left to work.

  • If you put the query in SQL what returns you?

  • the way you gave me without changing it returns: There is already an open Datareader associated with this connection that must be closed first. - so add Reader.close() to see if it worked. when you only have a Reader.close() solves. when I add one more doesn’t work anymore.

  • No, it wasn’t that. If you pick up the query SQL resulting from variable resultquery and put directly into SQL, what result gives you? Returns you 3 columns with integers?

  • yes returns 3 number . but because they are the first data to be entered. returns ( 0 , 0 , 0). I believe the error is that Some part of the code before or after the IF Reader needs to be closed. so much so that if you remove the comments from the 2 IF ' table movements and motions and add Reader.close() the error only comes in the third if.

  • Edited response with different code.

  • John, I rewrote the code according to the change you just made. Returned error in Cmdsalvaritems.Executenonquery() of First IF ( If objs(0) = 0 Then) error : "There is already an open Datareader Associated with this Connection which must be closed first "

  • john can solve by adding Read.close() Dim Quant As Integer = Getvalues(objs) solved the problem, I made the change in the code you edited. thank you very much for the solution, for the time and attention.

Show 5 more comments

Browser other questions tagged mysql select vb.net

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