Thread: Checking if exist in access db problem

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

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?

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,

Originally Posted by josvink66

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.

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!