Dataset search/filter

[lecfox]

hello,
I want to enter records into an sql database but before i do so i would like to load data from the sql database to a dataset and test if the record i want to enter is already in the database. if yes it does not add the record - preventing duplicated records...

for example: name | grade | subject
leo | 7A | maths

i knw how to load the database to a dataset but cant rap my mind around the test to see if the record is already there..

[m.davide]

query the columns you want to check using the values you want to add. if it .HasRows > 0 then it means it already exists.

[lecfox]

thank u for replying but umm... i dont understand what you mean.. can i see an example?

[m.davide]

No problem.. This is using oracle.

Public Sub saveNewBlockToDB()
        If checkBlockMissedFields(blockDetailsFrm) > 0 Then
            MsgBox("Please fill up the highlighted fields.", MsgBoxStyle.Exclamation)
        Else
            conn.Open()
            Dim cmd, cmd1 As New OracleCommand

            cmd.Connection = conn
            cmd.CommandText = "select block_id from block where map_id =" & mainMenuFrm.map_id_label.Text & "and block_name ='" & blockDetailsFrm.blockName_tb.Text & "'"
            cmd.CommandType = CommandType.Text
            cmd.ExecuteReader()

            'check if block exist in map
            If cmd.ExecuteReader.HasRows Then
                MsgBox("Block name is already taken! Please choose another block name.", MsgBoxStyle.Exclamation)
                conn.Close()
            Else
                'insert new block
                cmd1.Connection = conn
                cmd1.CommandText = "insert into block(block_name, block_x_coord, block_y_coord, main_street, brgy, city, mapper1, mapper2, map_id, user_id ) " &
                                  "values('" & blockDetailsFrm.blockName_tb.Text & "', '" & blockDetailsFrm.Label1.Text & "', '" & blockDetailsFrm.Label2.Text & "', '" & blockDetailsFrm.blockStreet_tb.Text & "', '" & blockDetailsFrm.blockBrgy_tb.Text & "', '" & blockDetailsFrm.blockCity_tb.Text & "', '" & blockDetailsFrm.blockMap1_tb.Text & "', '" & blockDetailsFrm.blockMap2_tb.Text & "', '" & mainMenuFrm.map_id_label.Text & "', '" & mainMenuFrm.user_id_label.Text & "')"
                cmd1.CommandType = CommandType.Text
                cmd1.ExecuteReader()
                conn.Close()
                blockDetailsFrm.Close()
                currentLabel.Text = UCase(blockDetailsFrm.blockName_tb.Text)
                MsgBox("Block successfully added!", MsgBoxStyle.Information)
            End If
        End If
    End Sub

jmcilhinney will prolly see my code and laugh at my useless a$$ for not using parameters

[jmcilhinney]

You certainly should be using parameters although, in this case, that's for completeness and beyond the scope of the actual question. My main issue is that you are over-using ExecuteReader. ExecuteReader should ONLY be used when reading records, which is not happening anywhere here. In this case you should be using ExecuteScalar to get a record count to determine whether there is an existing matching record and then ExecuteNonQuery to insert the data. If you want to follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data you will see examples of both. The ExecuteScalar example uses SUM but that can easily be changed to COUNT.

[m.davide]

oh yeah, i already did that on most of my code.
is this an acceptable way of checking if a value exists? (disregard my mistakes)

[lecfox]

i dont think this code is gonna work for me.. let me be more clear... i did a bulkcopy from excel to a datagrid in my application.. meaning all the data is transferred to the gridview.. i want to now check if any of the data in the grid matches with the database tht i loaded in a dataset.. then it only stores the records tht are not in the database....

[lecfox]

i did everything up to the part to see if its there already..


also i think i need some kind or looping...

[m.davide]

yeah it won't.. but that's how i check for duplicate items. just giving you an idea.

[lecfox]

ok i did some googling and found this..

'Create a dataview
dim dv as new dataview
 
'Associate the dataview to _oDs (Dataset table)
dv.Table = _oDs.Tables("tblCapitalCategory")
 
Dim drv As DataRowView 'Data Row View object to query DataView object
 
'Filter based on a combo box value selected
dv.RowFilter = "[pkCapitalCategoryID] = " & CInt(cboComboBox.SelectedValue)
 
'Retrieve my values returned in the result
For Each drv In dv
     _dteDateModified = drv("dteDateModified")
Next

but i really dont know how to implement this.. plz help

[jmcilhinney]

also i think i need some kind or looping...

You'll basically have to perform this process for each record. The other option would be to use a stored procedure that does the conditional stuff at the database end, which could be more efficient overall.

[m.davide]

a basic loop:

Dim i As Integer = 0
        For Each row As DataGridViewBand In Me.DataGridView1.Rows
            'use a select clause here instead, if the your select has rows it means its a duplicate.
            MsgBox(Me.DataGridView1.Item(1, i).Value)
            i = i + 1
        Next

I really can't help you anymore since Im only a novice and I only know oracle.