I have an enigma in my method , VALIDATE and INSERT code

Question

This is my INSERT code :

Dim _cmd as new SQLCommand()

Private Sub _btAdd_ANA_GRUPO_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles _btAdd_ANA_GRUPO.Click

    _resultCMD = 0

    'Por cada linha da grid analises
    For Ro = 0 To DataGrid_AnaEXT.Rows.Count - 1

        If DataGrid_AnaEXT.Rows(Ro).Cells("Selected").Value = 1 Then

            _cmd.Parameters.Clear() ' here i clear because i use the _cmd other sections

           '**enigma**'
           _query = "INSERT into dbo.tbl_ENC_GRUPO_ANA_LINHA VALUES( @idG, @idAna, @sigla, @nomeA, @_ck )"  / If i put the query string here ,the INSERT does nothing

            'Dim _cmd As New SqlCommand - I tried to declare here but same problem

            _cmd.Parameters.Add("@idG", SqlDbType.Int).Value = _dgvGruposA.CurrentRow.Cells("ID_GRUPO_ANA").Value
            _cmd.Parameters.Add("@idAna", SqlDbType.Char).Value = Trim(DataGrid_AnaEXT.Rows(Ro).Cells("ANA_ID").Value.ToString())
            _cmd.Parameters.Add("@sigla", SqlDbType.VarChar).Value = Trim(DataGrid_AnaEXT.Rows(Ro).Cells("ANA_SIGLA").Value.ToString())
            _cmd.Parameters.Add("@nomeA", SqlDbType.VarChar).Value = _selectAnaNome(Trim(DataGrid_AnaEXT.Rows(Ro).Cells("ANA_ID").Value.ToString()))
            _cmd.Parameters.Add("@_ck", SqlDbType.Bit).Value = DataGrid_AnaEXT.Rows(Ro).Cells("Selected").Value

            'Here i check if and ID exist in destination table "_checkAnaNoGrupo" , to not duplicate


            If _checkAnaNoGrupo(_dgvGruposA.CurrentRow.Cells("ID_GRUPO_ANA").Value, (DataGrid_AnaEXT.Rows(Ro).Cells("ANA_ID").Value.ToString)) = False Then

                _query = "INSERT into dbo.tbl_ENC_GRUPO_ANA_LINHA VALUES( @idG, @idAna, @sigla, @nomeA, @_ck )"


                _cmd.Connection = _con.connect
                _cmd.CommandText = _query
                _cmd.CommandType = CommandType.Text

                Try
                    _con.connect.Open()
                    _resultCMD = _resultCMD + _cmd.ExecuteNonQuery()
                    _con.connect.Close()

                Catch ex As Exception
                    _con.connect.Close()
                    MessageBox.Show("Erro Linha : " & ex.Message & " !")

                End Try

            End If

        End If
    Next

    If _resultCMD > 0 Then
        MessageBox.Show("Adicionou : " & _resultCMD _
                        & " análises ao Grupo : " & _dgvGruposA.CurrentRow.Cells("NOME_G").Value & " !")
    End If

    _selectAnaLinhaG(CInt(_dgvGruposA.CurrentRow.Cells(0).Value))

End sub

Here is the function that validate if exist -- It works Ok

Public Function _checkAnaNoGrupo(ByVal _grupoA As Integer, ByVal _codAna As String) As Boolean

    _tabCheckAnaNoGrupo() 

    Dim _ck As Boolean = False

    For Each _tRow As DataRow In _tabCheckAnaNoGrupo.Rows
        If _tRow("FK_ID_GRUPO_ANA").ToString.Contains(_grupoA) And _tRow("ID_ANA").ToString.Contains(_codAna) Then
            _ck = True
        Else
            _ck = False
        End If
    Next

    Return _ck

End Function

Here i get the data from SQL table into a DataTable:

 Public Function _tabCheckAnaNoGrupo() As DataTable

    Dim _tabCKana As New DataTable

    _query = "SELECT * FROM dbo.tbl_ENC_GRUPO_ANA_LINHA"

    _adapt.SelectCommand = New SqlCommand(_query, _con.connect)

    _adapt.Fill(_tabCKana)

    Return _tabCKana

End Function

My Problem is : If i run the code like this work fine the INSERT statment, but duplicate Lines.

But if i run the code with _query placed in 'enigma'(See UP), the INSERT does nothing . NO ERROR - NOTHING

I hope you can help me, may be a stupid mistake or not but already tried several ways, and the result is always the same

You may want to put the duplicate check into the insert query itself, maybe use a stored procedure? Also you seem to be using a lot of global variables which can cause trouble that is hard to debug. — PatFromCanada, Feb 21 '13 at 17:14

Flavius69 · Answer 1 · 2013-02-28T16:31:38.803

Ok! i found the problem and i fixit.

It was in my function that check if exist

Public Function _checkAnaNoGrupo(ByVal _grupoA As Integer, ByVal _codAna As String) As Boolean

    _tabCheckAnaNoGrupo()

    Dim _ck As Boolean = False

    For Each _tRow As DataRow In _tabCheckAnaNoGrupo.Rows
        If _tRow("FK_ID_GRUPO_ANA").ToString.Contains(_grupoA) And _tRow("ID_ANA").ToString.Contains(_codAna) Then
**ck = True**

**Return _ck**

**Exit Function**

'i exit the function when he found a match, else will do the for , and the _ck value will be always false.
'So when find the first match exit the function and return TRUE

        Else
            _ck = False
        End If
    Next

    Return _ck

End Function

My ENIGMA is SOLVED

I have an enigma in my method , VALIDATE and INSERT code

1 Answers1