none
Visual Basic Application To Insert Data Into an Access Database

    Question

  • I need help with my code because I'm trying to connect to an Access Database and insert data into it, but I'm running into many problems with connection string itself and getting the database to open. Any suggestions? Also I keep running into these warnings here:

    An unhandled exception of type 'System.ArgumentException' occurred in System.Data.dll.

    Additional information: Format of the initialization string does not conform to specification starting at index 0.

    An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

    Additional information: The ConnectionString property has not been initialized.

    Imports System.Data.OleDb

    Public Class Form1

        Dim provider As String
        Dim datafile As String
        Dim connString As String
        Dim myConnection As OleDbConnection = New OleDbConnection

        Private Sub btnexit_Click(sender As Object, e As EventArgs) Handles btnexit.Click
            Me.Close()
        End Sub



        Private Sub btnsubmit_Click(sender As Object, e As EventArgs) Handles btnsubmit1.Click
            Dim songTitle As String
            Dim songArtist As String
            Dim songAlbum As String
            Dim yearReleased As Double
            Dim str As String

            provider = "Microsoft.ACE.OLEDB.12.0"
            datafile = "H:\Music Session Database\Music Database.accdb"

            connString = provider & datafile
            myConnection.ConnectionString = connString
            myConnection.Open()
            str = "Insert into Music Database([Song Title], [Song Artist], [Song Album], [Year Released]) Values (songTitle, songArtist, songAlbum, yearReleased)"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            cmd.Parameters.Add(New OleDbParameter("Song Title", CType(songTitle, String)))
            cmd.Parameters.Add(New OleDbParameter("Song Artist", CType(songArtist, String)))
            cmd.Parameters.Add(New OleDbParameter("Song Album", CType(songAlbum, String)))
            cmd.Parameters.Add(New OleDbParameter("Year Released", CType(yearReleased, String)))

            Try
                cmd.ExecuteNonQuery()
                cmd.Dispose()
                myConnection.Close()
            Catch ex As Exception

            End Try


        End Sub


        Private Sub btnsumbit2_Click(sender As Object, e As EventArgs) Handles btnsumbit2.Click
            Dim songTitle As String
            Dim songArtist As String
            Dim songAlbum As String
            Dim yearReleased As Double
            Dim datePlayed As Date
            Dim timePlayed As String
            Dim genre As String


            provider = "Microsoft.ACE.OLEDB.12.0"
            datafile = "H:\Music Session Database\Music Database.accdb"

            connString = provider & datafile
            myConnection.ConnectionString = connString
            myConnection.Open()
            Dim str As String
            str = "Insert into Music Database([Song Title], [Song Artist], [Song Album], [Year Released], [Date Played], [Time Played], [Genre]) Values (?,?,?,?,?,?,?)"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            cmd.Parameters.Add(New OleDbParameter("Song Title", CType(songTitle, String)))
            cmd.Parameters.Add(New OleDbParameter("Song Artist", CType(songArtist, String)))
            cmd.Parameters.Add(New OleDbParameter("Song Album", CType(songAlbum, String)))
            cmd.Parameters.Add(New OleDbParameter("Year Released", CType(yearReleased, String)))
            cmd.Parameters.Add(New OleDbParameter("Date Played", CType(datePlayed, Date)))
            cmd.Parameters.Add(New OleDbParameter("Time Played", CType(timePlayed, String)))
            cmd.Parameters.Add(New OleDbParameter("Genre", CType(genre, String)))

            Try
                cmd.ExecuteNonQuery()
                cmd.Dispose()
                myConnection.Close()
            Catch ex As Exception

            End Try

        End Sub

        Private Sub btnsubmit3_Click(sender As Object, e As EventArgs) Handles btnsubmit3.Click
            Dim songArtist As String
            Dim genre As String
            Dim origin As String
            Dim artistInformation As String


            provider = "Microsoft.ACE.OLEDB.12.0"
            datafile = "H:\Music Session Database\Music Database.accdb"


            Dim connString = provider & datafile
            myConnection.ConnectionString = connString
            myConnection.Open()
            Dim str As String
            str = "Insert into Music Database([Song Title], [Song Artist], [Song Album], [Year Released]) Values (?,?,?,?)"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            cmd.Parameters.Add(New OleDbParameter("Song Artist", CType(songArtist, String)))
            cmd.Parameters.Add(New OleDbParameter("Genre", CType(genre, String)))
            cmd.Parameters.Add(New OleDbParameter("Year Released", CType(origin, String)))
            cmd.Parameters.Add(New OleDbParameter("Artist Information", CType(artistInformation, String)))

            Try
                cmd.ExecuteNonQuery()
                cmd.Dispose()
                myConnection.Close()
            Catch ex As Exception
            End Try
        End Sub

        Private Sub btnclear_Click(sender As Object, e As EventArgs) Handles btnclear.Click
            songTitle.Clear()
            songArtist.Clear()
            songAlbum.Clear()
            yearReleased.Clear()
            datePlayed.Clear()
            timePlayed.Clear()
            genre.Clear()
            artistInformation.Clear()
        End Sub


    End Class


    • Edited by WyvernFrog Sunday, April 16, 2017 8:19 PM
    Sunday, April 16, 2017 8:08 PM

Answers

  • Hi WyvernForog,

    Based on your description, please note that ConnectionString below, more info about ConnectionString, please refer to https://www.connectionstrings.com/access/

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
    Persist Security Info
    =False;

    Insert data into Access Database, please refer to code below:

    Private Sub Insertdata()
            Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\C#\Example(VB)\Example(VB)\Database1.accdb; Persist Security Info=False;"
            Using conn As New SqlConnection(connectionString)
                Using comm As New SqlCommand()
                    With comm
                        .Connection = conn
                        .CommandType = CommandType.Text
                        .CommandText = "INSERT INTO [Table] (ColumnID,ColumnName,ColumnSex,ColumnNumber) VALUES(@ColumnID,@ColumnName,@ColumnSex,@ColumnNumber)"
                        .Parameters.AddWithValue("@ColumnID", TextID.Text.ToString)
                        .Parameters.AddWithValue("@ColumnName", TextName.Text.Trim())
                        .Parameters.AddWithValue("@ColumnSex", TextSex.Text.Trim())
                        .Parameters.AddWithValue("@ColumnNumber", TextNumber.Text.Trim())
    
                    End With
                    Try
                        conn.Open()
                        comm.ExecuteNonQuery()
                        conn.Close()
                        MessageBox.Show("Successful")
                    Catch e As Exception
                        MessageBox.Show(e.Message)
                    End Try
                End Using
            End Using
    End Sub
    

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 17, 2017 1:54 AM
    Moderator
  • You are missing the argument names from the connection string:

            Provider = "Microsoft.ACE.OLEDB.12.0"
            DataFile = "H:\Music Session Database\Music Database.accdb"
    
            connString = String.Concat("Provider=", Provider, ";Data Source=", DataFile)


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by WyvernFrog Wednesday, April 19, 2017 4:31 PM
    Monday, April 17, 2017 1:02 PM
  • Hi WyvernForg,

    Please remember to close your thread by marking helpful post, it is beneficial to the other communities who face the same issue.

    Thanks for your understanding.

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by WyvernFrog Wednesday, April 19, 2017 4:31 PM
    Wednesday, April 19, 2017 8:52 AM
    Moderator

All replies

  • Wijvern, 

    I see lately more question like yours and they are handled like you ask. 

    "Insert data". Are you sure that data has not to be updated and not to be deleted?

    If you ask it like this, you get replies how to "Insert" new data if it is only new. 


    Success
    Cor

    Sunday, April 16, 2017 8:14 PM
  • I need help with connection strings because they keep coming back with this warning: An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll Additional information: The ConnectionString property has not been initialized. An unhandled exception of type 'System.ArgumentException' occurred in System.Data.dll Additional information: Format of the initialization string does not conform to specification starting at index 0.
    Sunday, April 16, 2017 8:17 PM
  • I saw you made another question about that. 

    As advice: "Avoid that", you get then the same short replies likewise I did on that new one. 


    Success
    Cor

    Sunday, April 16, 2017 8:30 PM
  • Hi WyvernForog,

    Based on your description, please note that ConnectionString below, more info about ConnectionString, please refer to https://www.connectionstrings.com/access/

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
    Persist Security Info
    =False;

    Insert data into Access Database, please refer to code below:

    Private Sub Insertdata()
            Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\C#\Example(VB)\Example(VB)\Database1.accdb; Persist Security Info=False;"
            Using conn As New SqlConnection(connectionString)
                Using comm As New SqlCommand()
                    With comm
                        .Connection = conn
                        .CommandType = CommandType.Text
                        .CommandText = "INSERT INTO [Table] (ColumnID,ColumnName,ColumnSex,ColumnNumber) VALUES(@ColumnID,@ColumnName,@ColumnSex,@ColumnNumber)"
                        .Parameters.AddWithValue("@ColumnID", TextID.Text.ToString)
                        .Parameters.AddWithValue("@ColumnName", TextName.Text.Trim())
                        .Parameters.AddWithValue("@ColumnSex", TextSex.Text.Trim())
                        .Parameters.AddWithValue("@ColumnNumber", TextNumber.Text.Trim())
    
                    End With
                    Try
                        conn.Open()
                        comm.ExecuteNonQuery()
                        conn.Close()
                        MessageBox.Show("Successful")
                    Catch e As Exception
                        MessageBox.Show(e.Message)
                    End Try
                End Using
            End Using
    End Sub
    

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 17, 2017 1:54 AM
    Moderator
  • Hello,

    The following shows how to select, add, update and delete records from a MS-Access database table. Note all code is in a class, this is the best way to work with data, keep user interface separate form data operations. In the form you create an instance of the class and call a method.

    Note the database in this case is in the same folder as the application's executable file e.g. in Visual Studio project the Bin\Debug folder.

    Imports System.Data.OleDb
    Public Class DatabaseOperations
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
        }
    
        Public Property CustomersDataTable As DataTable
        Public Sub New()
            CustomersDataTable = New DataTable
            GetCustomerData()
        End Sub
        Public Sub GetCustomerData()
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                                SELECT Identifier, ContactTitle, Country, CompanyName
                                FROM Customers 
                                ORDER BY CompanyName ASC
                        </SQL>.Value
    
                    cn.Open()
                    CustomersDataTable.Load(cmd.ExecuteReader)
                End Using
            End Using
        End Sub
        Public Function DeleteCustomer(ByVal CustomerId As Integer) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "DELETE FROM Customers WHERE Identifier = @Identifier"
    
                        cmd.Parameters.AddWithValue("@Identifier", CustomerId)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            Success = True
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
        Public Function UpdateCustomer(ByVal CustomerId As Integer, ByVal Name As String, ByVal Contact As String) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "UPDATE Customer SET CompanyName = @CompanyName, ContactName = @ContactName WHERE Identifier = @Identifier"
    
                        cmd.Parameters.AddWithValue("@CompanyName", Name)
                        cmd.Parameters.AddWithValue("@ContactName", Contact)
                        cmd.Parameters.AddWithValue("@Identifier", Contact)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            Success = True
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
        Public Function AddNewRow(ByVal Name As String, ByVal Contact As String, ByRef Identfier As Integer) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName) Values(@CompanyName,@ContactName)"
    
                        cmd.Parameters.AddWithValue("@CompanyName", Name)
                        cmd.Parameters.AddWithValue("@ContactName", Contact)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            cmd.CommandText = "Select @@Identity"
                            Identfier = CInt(cmd.ExecuteScalar)
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
    End Class
    

    Simple form code examples

    Public Class Form1
        Private ops As DatabaseOperations = New DatabaseOperations
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            ' get all records into a data table
            Dim custDataTable = ops.CustomersDataTable
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim id As Integer = 0
            If ops.AddNewRow("Some company", "Jane Smith", id) Then
                ' add was successful, id now contains the new record id.
            End If
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, April 17, 2017 2:52 AM
    Moderator
  • You are missing the argument names from the connection string:

            Provider = "Microsoft.ACE.OLEDB.12.0"
            DataFile = "H:\Music Session Database\Music Database.accdb"
    
            connString = String.Concat("Provider=", Provider, ";Data Source=", DataFile)


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by WyvernFrog Wednesday, April 19, 2017 4:31 PM
    Monday, April 17, 2017 1:02 PM
  • Hi WyvernForg,

    Please remember to close your thread by marking helpful post, it is beneficial to the other communities who face the same issue.

    Thanks for your understanding.

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by WyvernFrog Wednesday, April 19, 2017 4:31 PM
    Wednesday, April 19, 2017 8:52 AM
    Moderator