Multiple Updates using One Query Data (SELECT)

Asked

Viewed 38 times

1

I have this code to make a SELECT that returns multiple records or lines:

Public Sub ConsultaFimFerias()
    Dim sqlCmd As String = "SELECT Id FROM tb_ferias WHERE Data2 < '24/03/2020' "
    con = New SqlConnection(strCon.strCon2)
    cmd = New SqlCommand(sqlCmd, con)

    con.Open()
    dr = cmd.ExecuteReader

    Dim result As New ArrayList()
    While dr.Read()
        ' Insert each column into a dictionary
        Dim dict As New Dictionary(Of String, Object)
        For count As Integer = 0 To dr.FieldCount - 1
            dict.Add(dr.GetName(count), dr(count))
        Next
        ' Add the dictionary to the ArrayList
        result.Add(dict)
    End While
    dr.Close()

    For Each dat As Dictionary(Of String, Object) In result
        UpdateSituacaoByFerias(dat) '<----- NÃO SEI O QUE INSERIR AQUI
    Next

End Sub

The result is:

id (column)

  • 194
  • 147

I need to do an UPDATE in another table using the SELECT results, Id column.

Public Function UpdateSituacaoByFerias(id As Integer)
    strCmdUpdate = "UPDATE tb_funcionarios_ws SET situacao_contrato = '' WHERE id = " & id

    con = New SqlConnection(strCon.strCon2)
    cmd = New SqlCommand(strCmdUpdate, con)

    Try
        con.Open()
        cmd.ExecuteNonQuery()
        Return True
    Catch ex As Exception
        MsgBox("Não foi possível concectar ao banco de dados, erro: " & vbCrLf & ex.ToString, MsgBoxStyle.Information, "Erro ao concectar")
        Return False
    Finally
        con.Dispose()
        cmd.Dispose()
    End Try
    Return False

End Function

Anyway, how can I update every record that SELECT provides me?

vb.net

  • your method VerificaFerias returns a Boolean, as expected to make a For Each of its integer values? You need to read the values in Datareader and return an Array, List, etc, an object that is IEnumerable to make the foreach.

  • see this example which is exactly what you need: https://stackoverflow.com/questions/11978127/storing-results-of-a-datareader-into-an-array-in-vb-net

  • Thank you, @Ricardopunctual. How do I perform UPDATE with Dictionary(Of String, Object) ?

1 answer

1

To help those in need:

Public Sub ConsultaFimFerias()
    TextBox1.Text = Date.Now.ToShortDateString
    Dim sqlCmd As String = "SELECT Id FROM tb_ferias WHERE Data2 < '" & TextBox1.Text & "' AND SituacaoContrato = 'FERIAS' "
    con = New SqlConnection(strCon.strCon2)
    cmd = New SqlCommand(sqlCmd, con)

    con.Open()
    dr = cmd.ExecuteReader

    Dim result As New ArrayList()
    While dr.Read()
        ' Insert each column into a dictionary
        Dim dict As New Dictionary(Of String, Object)
        For count As Integer = 0 To dr.FieldCount - 1
            dict.Add(dr.GetName(count), dr(count))
        Next
        ' Add the dictionary to the ArrayList
        result.Add(dict)
    End While
    dr.Close()

    For Each dat As Dictionary(Of String, Object) In result
        UpdateSituacaoByFerias(dat.Item("Id"))
    Next

End Sub

After SELECT has the UPDATE function

Public Function UpdateSituacaoByFerias(id As String)
    strCmdUpdate = "UPDATE tb_funcionarios_ws SET situacao_contrato = '' WHERE id = " & id

    con = New SqlConnection(strCon.strCon2)
    cmd = New SqlCommand(strCmdUpdate, con)

    Try
        con.Open()
        cmd.ExecuteNonQuery()
        Return True
    Catch ex As Exception
        MsgBox("Não foi possível concectar ao banco de dados, erro: " & vbCrLf & ex.ToString, MsgBoxStyle.Information, "Erro ao concectar")
        Return False
    Finally
        con.Dispose()
        cmd.Dispose()
    End Try
    Return False

End Function

Browser other questions tagged vb.net

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