Need help adding a new record to a database using VB.NET

[Andy_Parr]

Hi,

I have a database linked to my VB.NET project using the data source wizard. I've created a log in form using a table in access that has the details required to log in.

The code is:

Imports System.Data
Public Class Form1

    Dim conn As OleDb.OleDbConnection
    Dim strSQL As String
    Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\Users\Andy\Desktop\ComputingProject.accdb"
    Dim dbReader As OleDb.OleDbDataReader
    Private Sub Button1_Click(ByVal sender As System.Object, _
                                 ByVal e As System.EventArgs) _
                                    Handles btnUserLogin.Click

        conn = New OleDb.OleDbConnection(strConn)
        conn.Open()

        strSQL = "SELECT * FROM tblLoginDetails WHERE (Username LIKE ?) AND (Password Like ?)"

        Dim dbCmd As New OleDb.OleDbCommand(strSQL, conn)
        dbCmd.Parameters.AddWithValue("@Username", txtUsername.Text)
        dbCmd.Parameters.AddWithValue("@Password", txtPassword.Text)
        dbReader = dbCmd.ExecuteReader()

        Using dbReader
            If dbReader.Read() Then
                Dim strResult1 As String = dbReader.Item("Username")
                Dim strResult2 As String = dbReader.Item("Password")
                MessageBox.Show("Login Success", "Login")
                Form2.Show()
            Else
                MessageBox.Show("Login Failed", "Login")
            End If
        End Using

        conn.Close()

    End Sub
End Class

The table in my database is called tblLoginDetails and the fieldnames are ID, Username and Password.

I now need help adding a new user to the system. To do this I would need a form to contain two textboxes and a button. One textbox relating to the Username Field and the other to the Password field. The button being the submit button.

Can anybody help me with the code to write the values entered in the two textboxes to the database when the submit button is clicked?

(I've tried to go into as much detail as neccessary, sorry if it comes off as I think you have no idea what to do.)

[__avd]

>Need help adding a new record to a database using VB.NET

Do you want to find or add a record? INSERT statement is needed to add a record.

[NoOneSpecial]

If you are still looking for an answer, I may be able to help you out. I am assuming that the ID is an autonumber. This method will allow for writing to the database even if it is being viewed in Access and will allow for multiple users to add records simultaneously if a situation like that exists. This code worked in VB2008 and 2010.

Imports System.Data

Public Class ClassName

    Private Sub SaveRecord(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddRecord.Click

        Dim connection As OleDb.OleDbConnection
        Dim command As OleDb.OleDbCommand
        Dim trans As OleDb.OleDbTransaction

        Dim dbPath As String = "C:\Users\Andy\Desktop\ComputingProject.accdb"

        Dim connStr As String ="Provider=Microsoft.ACE.OLEDB.12.0;" _
            & "Data Source=" & dbPath & "; Persist Security Info = False;"

        'SQL Insert statement - ID will be automatically generated with new records if autonumber
        Dim sqlInsert As String = "INSERT INTO tblLoginDetails (Username, Password) " _
            "VALUES ('" & txtUserName.Text & "', '" & txtPassword.Text & "')"

        Try
           'Initialize new connection
           connection = New OleDb.OleDbConnection(connString)

           'Make sure that no other program is blocking access to database
           If connection.State = ConnectionState.Open Then
               connection.Close()
           End If

           connection.Open()

           'Set transaction - this allows for writing to Access database
            trans = connection.BeginTransaction(IsolationLevel.ReadCommitted)
            
            'Initialize new command using sql statement, connection, and transaction as parameters
            command = New OleDb.OleDbCommand(sqlInsert, connection, trans)

            'Execute the command - NonQuery because it does not query the database
            command.ExecuteNonQuery()

            'Commits the new record, saving it to the database
            trans.Commit()

            connection.Close()

        Catch ex As Exception
            'Catch exceptions here (i.e. "File does not exist")
        End Try
    End Sub
End Class