[RESOLVED] System should not insert selected record/s if it's already existing in the database.

[anamada]

The system should not insert selected records from my datagridviewcheckboxcolumn if it's already existed in the database.

It's like when you're registering online. If you're username/email address already registered,it would inform you that youre username/email is already registered and the site would ask you to change your username/email to new ones.

The difference in my system is that i have 2 datagridviewcheckbox columns not just textboxes . Parent and Child in the datagridview. What i want to happen is that once the Code has been registered as Parent it cant be registered again as Child anymore.

I dont have a logic on that so far, i hope some could help me out here, but here's my code so far.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try

            connectionString = ";"
            sql_connection = New SqlConnection(connectionString)
            Dim cmdText As String = "INSERT INTO Conso_Holder (CONSO_PARENT_STOCKHOLD_CODE, CONSO_CHILD_STOCKHOLD_CODE, CONSO_STOCK_NAME) VALUES (@CONSO_PARENT_STOCKHOLD_CODE, @CONSO_CHILD_STOCKHOLD_CODE, @CONSO_STOCK_NAME)"
            Dim cmdTextDelChild As String = "DELETE Conso_Holder WHERE CONSO_CHILD_STOCKHOLD_CODE = @CONSO_CHILD_STOCKHOLD_CODE"
            Dim command As New SqlCommand(cmdText, sql_connection)
            Dim command2 As New SqlCommand(cmdTextDelChild, sql_connection)
            Dim parentCode As String = ""


            For i As Integer = 0 To DataGridView1.Rows.Count - 1
                If CBool(DataGridView1.Rows(i).Cells(0).Value) = True Then
                    parentCode = DataGridView1.Rows(i).Cells(2).Value.ToString

                    Exit For
                End If
            Next i

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

                If CBool(DataGridView1.Rows(i).Cells(1).Value) = True Then

                    command2.Parameters.AddWithValue("@CONSO_CHILD_STOCKHOLD_CODE", DataGridView1.Rows(i).Cells(2).Value)
                    sql_connection.Open()
                    command2.ExecuteNonQuery()
                    sql_connection.Close()
                    command2.Parameters.Clear()
                End If
            Next i


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

                sql_connection.Open()

                Dim slcString As String = " SELECT CONSO_PARENT_STOCKHOLD_CODE FROM Conso_Holder Where  CONSO_CHILD_STOCKHOLD_CODE = @CONSO_CHILD_STOCKHOLD_CODE "

                Dim command4 As New SqlCommand(slcString, sql_connection)

                'command4.Parameters.AddWithValue("@CONSO_PARENT_STOCKHOLD_CODE", parentCode)
                command4.Parameters.AddWithValue("@CONSO_CHILD_STOCKHOLD_CODE", DataGridView1.Rows(i).Cells(2).Value)

                datareadersql = command4.ExecuteReader

                'If CInt(datareadersql.HasRows) = 0 Then
                '    sql_connection.Close()


                '    MsgBox("Records Inserted")

                '    Exit For

                'ElseIf CInt(datareadersql.HasRows) <> 0 Then

                '    MsgBox("Records can't be inserted")

                '    Exit For


                'End If


                sql_connection.Open()
                Dim result As String = command4.ExecuteScalar.ToString
                sql_connection.Close()
                command4.Parameters.Clear()
            Next i






            For i As Integer = 0 To DataGridView1.Rows.Count - 1
                If CBool(DataGridView1.Rows(i).Cells(1).Value) = True Then
                    command.Parameters.AddWithValue("@CONSO_PARENT_STOCKHOLD_CODE", parentCode)
                    command.Parameters.AddWithValue("@CONSO_CHILD_STOCKHOLD_CODE", DataGridView1.Rows(i).Cells(2).Value)
                    command.Parameters.AddWithValue("@CONSO_STOCK_NAME", DataGridView1.Rows(i).Cells(3).Value)

                    sql_connection.Open()
                    command.ExecuteNonQuery()
                    sql_connection.Close()
                    command.Parameters.Clear()
                End If

            Next i





            Dim hasParentCode As Boolean = False
            For i As Integer = 0 To DataGridView1.Rows.Count - 1
                If CBool(DataGridView1.Rows(i).Cells(0).Value) = True Then
                    hasParentCode = True
                    Exit For
                End If
            Next
            If hasParentCode = False Then
                MsgBox("Select a Parent Code first.", vbInformation, "STO Masterfile System")
            End If


            Dim hasChildCode As Boolean = False
            For i As Integer = 0 To DataGridView1.Rows.Count - 1
                If CBool(DataGridView1.Rows(i).Cells(1).Value) = True Then
                    hasChildCode = True
                    Exit For
                End If
            Next
            If hasChildCode = False Then
                MsgBox("Select atleast one Child Code first.", vbInformation, "STO Masterfile System")
            End If

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
        itemlist.Clear()


    End Sub

I tried this but still not wrking.

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

                sql_connection.Open()

                Dim slcString As String = " SELECT CONSO_PARENT_STOCKHOLD_CODE FROM Conso_Holder Where  CONSO_CHILD_STOCKHOLD_CODE = @CONSO_CHILD_STOCKHOLD_CODE "

                Dim command4 As New SqlCommand(slcString, sql_connection)

                'command4.Parameters.AddWithValue("@CONSO_PARENT_STOCKHOLD_CODE", parentCode)
                command4.Parameters.AddWithValue("@CONSO_CHILD_STOCKHOLD_CODE", DataGridView1.Rows(i).Cells(2).Value)

                datareadersql = command4.ExecuteReader

                'If CInt(datareadersql.HasRows) = 0 Then
                '    sql_connection.Close()


                '    MsgBox("Records Inserted")

                '    Exit For

                'ElseIf CInt(datareadersql.HasRows) <> 0 Then

                '    MsgBox("Records can't be inserted")

                '    Exit For


                'End If


                sql_connection.Open()
                Dim result As String = command4.ExecuteScalar.ToString
                sql_connection.Close()
                command4.Parameters.Clear()
            Next i

Please review my code

Thanks

[ForumAccount]

The logic is quite simple, you need to determine what constitutes as a row being "equal" to another row. Once you have this, there are a variety of ways to test if a row exists using your criteria. I personally would use a Stored Procedure, but you seem to be just creating all the commands in code. That's fine.

So what I would do if I were you is create a SqlCommand object that is just a simple SELECT. The SQL of this SELECT command would have the logic to select the possibly hypothetical row that would be equal. I would then call ExecuteNonQuery on the command. This will return how many rows were affected by the query. If the result of this method call is > 0, then the record already exists.

Your best bet is to start in the data base to create the SQL that you will use in code. If it doesn't work in the data base it isn't going to work in code. If it doesn't work in code, check if it works in the data base.

[anamada]

The logic is quite simple, you need to determine what constitutes as a row being "equal" to another row. Once you have this, there are a variety of ways to test if a row exists using your criteria. I personally would use a Stored Procedure, but you seem to be just creating all the commands in code. That's fine.

So what I would do if I were you is create a SqlCommand object that is just a simple SELECT. The SQL of this SELECT command would have the logic to select the possibly hypothetical row that would be equal. I would then call ExecuteNonQuery on the command. This will return how many rows were affected by the query. If the result of this method call is > 0, then the record already exists.

Your best bet is to start in the data base to create the SQL that you will use in code. If it doesn't work in the data base it isn't going to work in code. If it doesn't work in code, check if it works in the data base.

Thanks for your suggestion Will be working on it right now..

[anamada]

The logic is quite simple, you need to determine what constitutes as a row being "equal" to another row. Once you have this, there are a variety of ways to test if a row exists using your criteria. I personally would use a Stored Procedure, but you seem to be just creating all the commands in code. That's fine.

So what I would do if I were you is create a SqlCommand object that is just a simple SELECT. The SQL of this SELECT command would have the logic to select the possibly hypothetical row that would be equal. I would then call ExecuteNonQuery on the command. This will return how many rows were affected by the query. If the result of this method call is > 0, then the record already exists.

Your best bet is to start in the data base to create the SQL that you will use in code. If it doesn't work in the data base it isn't going to work in code. If it doesn't work in code, check if it works in the data base.

I think I have the correct select statement. What's next sir?

[anamada]

Help me. Im stocked T_T dont know what to do next T_T

[ForumAccount]

Is this resolved? I already mentioned the next step, it would be to call ExecuteNonQuery and see how many rows are affected.

[anamada]

Is this resolved? I already mentioned the next step, it would be to call ExecuteNonQuery and see how many rows are affected.

Yes. This has been resolved. Thank you!