Access Databases in VB.Net

[rumpleteaser33]

Hi,
I'm writing my first application in VB and need some help with my Access database. I have 2 problems; the first one is the most important, the second I would just be very grateful for any help/advice.

Firstly, I need help updating a record from my database. I have some code which is what I used for inserting a record but it doesn't work. What am I missing?

Dim conStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
           "Data Source=School1.accdb"
    Dim con As New OleDbConnection(conStr)
    Dim cmd As New OleDbCommand("select [username] from [Users] where [username]=@username", con)

        cmd.Parameters.AddWithValue("@username", teachersList.SelectedItem)
        con.Open()
        Dim obj As Object = cmd.ExecuteScalar()
        con.Close()

        If IsNothing(obj) Then
Try
            cmd = New OleDbCommand("UPDATE [Users] SET [Type]=@type WHERE [Username]=@username", con)
            'Add new user with following parameters
            Dim a As Object
            a = ListBox.SelectedItem
            cmd.Parameters.AddWithValue("@username", a)
            cmd.Parameters.AddWithValue("@type", "Admin")
            'Open database connection
            con.Open()
            'Execute query
            cmd.ExecuteScalar()
            'Close database connection
            con.Close()
            MsgBox("Changed")
        Catch
            MsgBox("NOT changed")
        End Try

Secondly, when I insert a new record, it adds one for that session, but when I restart the application, the new row is gone. I wasn't sure if there was a way around this and would be very grateful of any help.
Thank you

[kvprajapati]

>I need help updating a record from my database.

Use ExecuteNonQuery() method for non-query statements (other than select statement).

cmd = New OleDbCommand("UPDATE [Users] SET [Type]=@type WHERE [Username]=@username", con)
            'Add new user with following parameters
            Dim a As Object
            a = ListBox.SelectedItem
            cmd.Parameters.AddWithValue("@type", "Admin")
            cmd.Parameters.AddWithValue("@username", a)
           
            'Open database connection
            con.Open()
            'Execute query
            cmd.ExecuteNonQuery()
            'Close database connection
            con.Close()

>when I insert a new record, it adds one for that session, but when I restart the application

Use absolute path,

Dim conStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
           "Data Source=c:\test\School1.accdb"

[rumpleteaser33]

Thank you for the help! The second answer solved my problem perfectly. However, it didn't fix my updating a record problem. I've changed it as suggested so this is what I've got now:

Dim conStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
           "Data Source=E:\PPSP\SchoolProject2\School1.accdb"
    Dim con As New OleDbConnection(conStr)
    Dim cmd As New OleDbCommand("select [Username] from [Users] where [Username]=@username", con)

'Adds value to command
        Dim b As Object
        b = teachersList.SelectedItem
        cmd.Parameters.AddWithValue("@username", b)
        'Open database connectino
        con.Open()
        'Execute
        Dim obj As Object = cmd.ExecuteScalar()
        'Close database connection
        con.Close()

        If IsNothing(obj) Then
'Not found in database
        Try
            cmd = New OleDbCommand("UPDATE [Users] SET [Type]=@type WHERE [Username]=@username", con)
            'Add new user with following parameters
            Dim a As Object
            a = listBox.SelectedItem
            cmd.Parameters.AddWithValue("@type", "Admin")
            cmd.Parameters.AddWithValue("@username", a)
            'Open database connection
            con.Open()
            'Execute query
            cmd.ExecuteNonQuery()
            'Close database connection
            con.Close()
            MsgBox("Type changed")
        Catch
            MsgBox("Type NOT changed")
        End Try
        Else
        MsgBox("Unable to change type")
        End If

Any help and suggestions are greatly appreciated.

[kvprajapati]

Line #17.

If Not IsNothing(obj) Then
 ...
End If

[G_Waddell]

Hi,
I don't see in your code where you INSERT a record....
You only SELECT records and UPDATE an existing record (if there are any)

Also I'm not sure I follow exactly what it is you are doing.

I see that you get a username value from a list box called teachers
and try to find matching records - if you find none, you get a username value from another list box called listBox and update matching records to type admin. I'm just trying to understand why?

[rumpleteaser33]

Thank you very much for the help. I've managed to solve the problem now. You were right :)

[kevinnrobert]

Create a database named Emp in Microsoft Access in the C: drive of your machine. In the Emp database create a table, Table1 with EmpNo, EName and Department as columns, insert some values in the table and close it. Open Visual Studio .NET, on a new form drag three TextBoxes and a Button. The following code will assume that TextBox1 is for EmpNo, TextBox2 is for EName and TextBox3 is for Department. Our intention is to retrieve data from Table1 in the Emp Database and display the values in these TextBoxes without binding when the Button is clicked.