• Hello and welcome to our new forums. We upgraded our forum sites to a more robust and modern system which we hope you will enjoy. Be sure to check out your profile by clicking the button on the top right and configure your preferences, signature, time zone, avatar, etc. as you wish. If you need help with using this new forum'ware try the help link on the bottom right.

    Click here to review your account now.

Question Checking if exist in access db problem

josvink66

New member
Joined
Oct 6, 2011
Messages
3
Programming Experience
Beginner
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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,027
Location
Sydney, Australia
Programming Experience
10+
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

New member
Joined
Oct 6, 2011
Messages
3
Programming Experience
Beginner
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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,027
Location
Sydney, Australia
Programming Experience
10+
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

New member
Joined
Oct 6, 2011
Messages
3
Programming Experience
Beginner
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

Member
Joined
Aug 6, 2013
Messages
8
Programming Experience
Beginner
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
 
Top Bottom