Why use "using" with updating data to access database

Hello,

I have the following update statement to save data to an access 2007 DB:

Code:

---

Public Sub opslaan2()
    Using connection As New OleDbConnection(strConnectionstring)
      Using cmdGebruiker As New OleDbCommand("UPDATE tblMEDEWERKER " & _
      "SET tblMEDEWERKER.Achternaam = @Achternaam " & _
    "WHERE tblMEDEWERKER.Medewerker_ID = @Medewerker_ID", connection)

        cmdGebruiker.Parameters.AddWithValue("@Achternaam", txtAchternaam.Text)
        cmdGebruiker.Parameters.AddWithValue("@Medewerker_ID", intMedewerker_ID)

        connection.Open()
        cmdGebruiker.ExecuteNonQuery()
        cmdGebruiker.Dispose()
      End Using
      connection.Close()
    End Using
  End Sub

---

This works fine!

I tried another approach:

Code:

---

Public Sub opslaan()
    Dim connection1 As New OleDbConnection(strConnectionstring)
    Dim cmdGebruiker As New OleDbCommand("UPDATE tblMEDEWERKER " & _
    "SET tblMEDEWERKER.Achternaam = @Achternaam " & _
  "WHERE tblMEDEWERKER.Medewerker_ID = @Medewerker_ID", connection1)

    cmdGebruiker.Parameters.AddWithValue("@Medewerker_ID", intMedewerker_ID)
    cmdGebruiker.Parameters.AddWithValue("@Achternaam", txtAchternaam.Text)
    cmdGebruiker.Parameters.AddWithValue("@Voornaam", txtVoornaam.Text)

    connection1.Open()
    cmdGebruiker.ExecuteNonQuery()
    cmdGebruiker.Dispose()
    connection1.Close()

---

And this one doesnt work.
Whats the difference between the 2 and why should I use "using"?

Thanks everybody

Using keyword guarantees that the resources will be disposed at End Using so if you have a large sub or function you can use several Using...End Using constructions. If you use the 2nd approach your resources will be disposed at End Sub unless you dispose of them explicitly using the Dispose method.
I don't see any reason why the second example doesn't work. I used both variants many times and they both work. What exactly goes wrong?

Quote:

---

Originally Posted by cicatrix

Using keyword guarantees that the resources will be disposed at End Using so if you have a large sub or function you can use several Using...End Using constructions. If you use the 2nd approach your resources will be disposed at End Sub unless you dispose of them explicitly using the Dispose method.
I don't see any reason why the second example doesn't work. I used both variants many times and they both work. What exactly goes wrong?

---

Thanks for the explenation.
In the second example tha data just isnt saved to the db.

ExecuteNonQuery method returns an integer which reports the number of affected rows. See if its zero in the second case.
It should work. It works fine both ways. There must be something else beside the Using keyword.

With regards to the first code snippet, there's no point disposing the command or closing the connection. That's what the Using block is for: to do that implicitly.

Thanks everybody...

I figured something out that surprised me..

Now I use this code:

Code:

---

  Public Sub opslaan()
    Dim sqlUPDATE_Medewerker As String = "UPDATE tblMEDEWERKER SET tblMEDEWERKER.Achternaam = @Achternaam, tblMEDEWERKER.Voornaam = @Voornaam WHERE (((tblMEDEWERKER.Medewerker_ID)=@Medewerker_ID));"

    Dim connection1 As New OleDbConnection(strConnectionstring)
    Dim cmdGebruiker As New OleDbCommand(sqlUPDATE_Medewerker, connection1)

    cmdGebruiker.Parameters.AddWithValue("@Achternaam", txtAchternaam.Text)
    cmdGebruiker.Parameters.AddWithValue("@Voornaam", txtVoornaam.Text)
    cmdGebruiker.Parameters.AddWithValue("@Medewerker_ID", intMedewerker_ID)

    connection1.Open()
    cmdGebruiker.ExecuteNonQuery()
    cmdGebruiker.Dispose()
    connection1.Close()
  End Sub

---

And it works perfect.

The reason that the previous code didn't work was because I had the "paramaters.addwithvalue" in a different order then they where used in my query.

I didnt know that you had to put the parameters in order with the parameters in the query.