Need help adding a new record to a database using VB.NET
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:
Public Class Form1
Dim conn As OleDb.OleDbConnection
Dim strSQL As String
Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
Dim dbReader As OleDb.OleDbDataReader
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
conn = New OleDb.OleDbConnection(strConn)
strSQL = "SELECT * FROM tblLoginDetails WHERE (Username LIKE ?) AND (Password Like ?)"
Dim dbCmd As New OleDb.OleDbCommand(strSQL, conn)
dbReader = dbCmd.ExecuteReader()
If dbReader.Read() Then
Dim strResult1 As String = dbReader.Item("Username")
Dim strResult2 As String = dbReader.Item("Password")
MessageBox.Show("Login Success", "Login")
MessageBox.Show("Login Failed", "Login")
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.)
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.
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 & "')"
'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
'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
'Commits the new record, saving it to the database
Catch ex As Exception
'Catch exceptions here (i.e. "File does not exist")