Results 1 to 6 of 6

Thread: Checking if exist in access db problem

  1. #1
    josvink66 is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.0
    Join Date
    Oct 2011
    Posts
    3
    Reputation
    0

    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

  2. #2
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,798
    Reputation
    1704
    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?

  3. #3
    josvink66 is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.0
    Join Date
    Oct 2011
    Posts
    3
    Reputation
    0
    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,

  4. #4
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,798
    Reputation
    1704
    Quote Originally Posted by josvink66 View Post
    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.

  5. #5
    josvink66 is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.0
    Join Date
    Oct 2011
    Posts
    3
    Reputation
    0
    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!

  6. #6
    coolzero22 is offline VB.NET Forum Newbie
    .NET Framework
    .NET 2.0
    Join Date
    Aug 2013
    Posts
    8
    Reputation
    0
    Quote Originally Posted by josvink66 View Post
    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

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •