Question Checking if exist in access db problem

[josvink66]

Hello,

i have writen a little function to check if something exist in my access db, if it does not exist then it have to be created.

this is the code i wrote:

Public Function tankstationexist(ByVal tankstation As String)
        con.Open()
        'controleren of tankstation bestaat in db
        Dim dt As New DataTable("tankstations")
        Dim rs As OleDb.OleDbDataAdapter
        rs = New OleDb.OleDbDataAdapter("SELECT * FROM tankstations where naam='" & tankstation & "'", con)
        On Error GoTo failedtank
        rs.Fill(dt)
        'verbinding verbreken
        con.Close()
        'functie afsluiten
        MsgBox(1)
        rs.Dispose()
        Exit Function
failedtank:
        MsgBox(2)
        'bestaat tankstation niet dan word de waarde aangemaakt
        Dim rs2 As New OleDb.OleDbCommand(
                           "INSERT INTO tankstations (naam) VALUES ('" &
                          tankstation & "')", con)
        'Het SQL command uitvoeren
        rs2.ExecuteNonQuery()
        'Verbinding verbreken
        rs.Dispose()
        rs2.Dispose()
        con.Close()

    End Function

i've put some msgbox's in it to check witch one it takes but it keeps taking 1 so that means the record exist but i'm 100% sure it does not...
Ive found out that when i put a msgbox between "On Error GoTo failedtank" and "rs.Fill(dt)" it works.. so i think it is a problem with the speed off vb.net that access doesn't have the time to work..

Anyone any idea?

Greetz,
Jos Vink

Sorry for my bad english i'm dutch

 

[jmcilhinney]

First up, NEVER use GoTo. There's never a reason to do so and there's no reason here. All you need is an If statement to decide whether to execute some code or not.

As for the issue, can you point out the part of that code that checks whether the record exists?

 

[josvink66]

hello,

Tanks for you reply,

what i do is. i let the program try to get the value out of the database with this code


Dim dt As New DataTable("tankstations")
Dim rs As OleDb.OleDbDataAdapter
rs = New OleDb.OleDbDataAdapter("SELECT * FROM tankstations where naam='" & tankstation & "'", con)
On Error GoTo failedtank
rs.Fill(dt)

if the record does not exist vb.net give's a error and i use that error to make the record..

Do you have a sample of any better code to check if a record exist in a access database?

Greetz,

 

[jmcilhinney]

if the record does not exist vb.net give's a error

Um, nope. You're getting all the records that match that condition, whether than be zero, one or more. There's no error unless something bad happens like a syntax error or the connection fails.

If you don't need the data then don't get the data. Just get the count of the matching records with your query and then that is either zero or not zero.

 

[josvink66]

so if i understand it correcly i need to try to get the value and then check the amount of records that has been found?

you have a sample code?

Sorry i'm not to good jet! i'm trying to learn!

 

[coolzero22]

hello,

Tanks for you reply,

what i do is. i let the program try to get the value out of the database with this code


Dim dt As New DataTable("tankstations")
Dim rs As OleDb.OleDbDataAdapter
rs = New OleDb.OleDbDataAdapter("SELECT * FROM tankstations where naam='" & tankstation & "'", con)
On Error GoTo failedtank
rs.Fill(dt)

if the record does not exist vb.net give's a error and i use that error to make the record..

Do you have a sample of any better code to check if a record exist in a access database?

Greetz,

try this one maybe this will work...

Try
Dim dt As New DataTable("tankstations")
Dim rs As OleDb.OleDbDataAdapter
rs = New OleDb.OleDbDataAdapter("SELECT * FROM tankstations where naam='" & tankstation & "'", con)

rs.Fill(dt)

tankstation.text=dt.rows(0).item("naam")

Catch ex As Exception

msgbox("Record don't Exist!")
end try