visual studio 2010: how to avoid adding in database that has been already save?

[marcmanlin2]

how can i avoid data duplicate in the database?
i mean if i add in the fields that has been already save it will still save.
i don't know how to query or filter not to add the same info.

i have the code below but it still save if i input same as already in the database.

myqry = "INSERT INTO tblInformation(Firstname,LastName,Address) "
myqry = myqry + "VALUES('" & txtFirst.Text & "','" & txtLast.Text & "','" & txtAddress.Text & "')"

mycmd = New OleDbCommand

With mycmd
     .CommandText = myqry
     .Connection = conn
     .ExecuteNonQuery()
End With

i want is to query first before it add the data to avoid same info.

[M.Waqas Aslam]

hello !
try this may be this will solve your prob

dim con as new sqlconnection("connectionstring")
con.open()
dim da as new sqldataadapter("select firstname,lastname,address from table where firstname='" +txtfirstname.text+"' and lastname='"+txtlastname.text +"' and address='"+ txtaddress.text+"'",con)
dim dt as new datatable
da.fill(dt)
if dt.rows.count = 0 then
'means record is not in db , you can save
else
'record is already in db , so dont save
end if

this is not the ideal solution as i think this thing make your program slow ,but it will solve your prob .
if your prob is solved then please mark this thread solved .

Regards

[poojavb]

hello !
try this may be this will solve your prob

dim con as new sqlconnection("connectionstring")
con.open()
dim da as new sqldataadapter("select firstname,lastname,address from table where firstname='" +txtfirstname.text+"' and lastname='"+txtlastname.text +"' and address='"+ txtaddress.text+"'",con)
dim dt as new datatable
da.fill(dt)
if dt.rows.count = 0 then
'means record is not in db , you can save
else
'record is already in db , so dont save
end if

this is not the ideal solution as i think this thing make your program slow ,but it will solve your prob .
if your prob is solved then please mark this thread solved .

Regards

U can also use a datareader instead of datatable....

if the reader returns true means the data is present so do not insert....else insert

Try
                Dim myCommand As OleDbCommand
                myCommand = New OleDbCommand("your sql query", Connection)
                Dim reader As OleDbDataReader = myCommand.ExecuteReader
                if reader.read=True
                 MsgBox("Data already exists")
                else
                 'insert the data
                reader.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

[M.Waqas Aslam]

i think datareader is slower then dataadapter , but yes we can use datareader also for this purpose.but speed is something which is imp for any application.

[Reverend Jim]

Another possibility (fewer layers) is to use ADO as in

Dim con As New ADODB.Connection
Dim rec As New ADODB.Recordset

con.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
rec.Open("select count(*) from admin where username='John'", con, CursorTypeEnum.adOpenForwardOnly)

If rec(0).Value = 1 Then
    'record exists
Else
    'record does not exist
End If

rec.Close()
con.Close()

A table should have a primary key defined. In the above example, the primary key is "username". Any attempt to add a duplicate username will result in an error. Because the primary key is unique, you only need to check that field to determine whether or not you can add a new record. You could easily encapsulate the duplicate check in a function as in

user = "John")

If KeyExists(user) Then
    MsgBox("User " & user & " already exists in database")
Else
    'do ADD code here
End If

[marcmanlin2]

hello !
try this may be this will solve your prob

dim con as new sqlconnection("connectionstring")
con.open()
dim da as new sqldataadapter("select firstname,lastname,address from table where firstname='" +txtfirstname.text+"' and lastname='"+txtlastname.text +"' and address='"+ txtaddress.text+"'",con)
dim dt as new datatable
da.fill(dt)
if dt.rows.count = 0 then
'means record is not in db , you can save
else
'record is already in db , so dont save
end if

this is not the ideal solution as i think this thing make your program slow ,but it will solve your prob .
if your prob is solved then please mark this thread solved .

Regards

but can you help me how to to do it with in OLEDB?
sorry i'm still fresh in VB 2010

[marcmanlin2]

U can also use a datareader instead of datatable....

if the reader returns true means the data is present so do not insert....else insert

Try
                Dim myCommand As OleDbCommand
                myCommand = New OleDbCommand("your sql query", Connection)
                Dim reader As OleDbDataReader = myCommand.ExecuteReader
                if reader.read=True
                 MsgBox("Data already exists")
                else
                 'insert the data
                reader.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

it's ok not slow. :)