i want to add New Record to my database in Ms Acess 2016

[Asare kawawa]

This is the code i used

Dim con As ADODB.connection
Dim rs As Recordset
rs.Fields("RollNo").value = textbox1.text
rs.Fields("Name").value=textbox2.text
rs.Fields("DOB").value=DtpDOB.text
rs.Fields("Dept").value=comboBox1.text
rs.Fields("course").value=comboBox2.text
rs.Fields("semester").value=comboBox3.text
rs.Fields("Address").value=TextBox3.text
rs.Fields("phone").value=textbox4.text
rs.Fields("photo").value=text
Msgbox("record added successfully)

Thread[4812]

[dday9]

Could you please clarify if the code is for Visual Basic .NET or Visual Basic Classic (such as VB6 or earlier).

Also, I editted your post to include [CODE][/CODE] tags. In the future, if you select you code and hit the # icon in the reply box toolbar, it will add the tags for you.

[Asare kawawa]

Pls is vb.net(vb13)

[dday9]

OK, since this is Visual Basic .NET, then you will want to abandon ADO and move onto ADO.NET. The following is an example of how to insert a new row into an Access database:

Private Sub InsertRow(ByVal rollNo As String, ByVal name As String, ByVal dob As DateTime, ByVal deptartment As String, ByVal course As String, ByVal semester As String, ByVal address As String, ByVal phone As String, ByVal photo As String)
    'Declare the connection object
    Dim con As OleDbConnection

    'Wrap code in Try/Catch
    Try
        'Set the connection object to a new instance
        'TODO: Change "My Connection String Here" with a valid connection string
        con = New OleDbConnection("My Connection String Here")

        'Create a new instance of the command object
        'TODO: Change [MyTable] to a valid table name
        Using cmd As OleDbCommand = New OleDbCommand("INSERT INTO [MyTable] ([RollNo], [Name], [DOB], [Dept], [course], [semester], [Address], [phone], [photo]) VALUES (@rollNo, @name, @dob, @dept, @course, @semester, @address, @phone, @photo)", con)
            'Parameterize the query
            With cmd.Parameters
                .AddWithValue("@rollNo", rollNo)
                .AddWithValue("@name", name)
                .AddWithValue("@dob", dob)
                .AddWithValue("@dept", deptartment)
                .AddWithValue("@course", course)
                .AddWithValue("@semester", semester)
                .AddWithValue("@address", address)
                .AddWithValue("@phone", phone)
                .AddWithValue("@photo", photo)
            End With

            'Open the connection
            con.Open()

            'Use ExecuteNonQuery to return a single value
            cmd.ExecuteNonQuery()

            'Close the connection
            con.Close()
        End Using
    Catch ex As Exception
        'Display the error
        Console.WriteLine(ex.Message)
    Finally
        'Check if the connection object was initialized
        If con IsNot Nothing Then
            If con.State = ConnectionState.Open Then
                'Close the connection if it was left open(exception thrown)
                con.Close()
            End If

            'Dispose of the connection object
            con.Dispose()
        End If
    End Try
End Sub

A couple of notes: Be sure to plug in your correct connection string. Be sure to change [MyTable] to the actual name of your Access table. Be sure to fix any typos in the column names in the SQL query, these are the values in the brackets after the [MyTable] and before VALUES.

[Asare kawawa]

OK, since this is Visual Basic .NET, then you will want to abandon ADO and move onto ADO.NET. The following is an example of how to insert a new row into an Access database:

Private Sub InsertRow(ByVal rollNo As String, ByVal name As String, ByVal dob As DateTime, ByVal deptartment As String, ByVal course As String, ByVal semester As String, ByVal address As String, ByVal phone As String, ByVal photo As String)
    'Declare the connection object
    Dim con As OleDbConnection

    'Wrap code in Try/Catch
    Try
        'Set the connection object to a new instance
        'TODO: Change "My Connection String Here" with a valid connection string
        con = New OleDbConnection("My Connection String Here")

        'Create a new instance of the command object
        'TODO: Change [MyTable] to a valid table name
        Using cmd As OleDbCommand = New OleDbCommand("INSERT INTO [MyTable] ([RollNo], [Name], [DOB], [Dept], [course], [semester], [Address], [phone], [photo]) VALUES (@rollNo, @name, @dob, @dept, @course, @semester, @address, @phone, @photo)", con)
            'Parameterize the query
            With cmd.Parameters
                .AddWithValue("@rollNo", rollNo)
                .AddWithValue("@name", name)
                .AddWithValue("@dob", dob)
                .AddWithValue("@dept", deptartment)
                .AddWithValue("@course", course)
                .AddWithValue("@semester", semester)
                .AddWithValue("@address", address)
                .AddWithValue("@phone", phone)
                .AddWithValue("@photo", photo)
            End With

            'Open the connection
            con.Open()

            'Use ExecuteNonQuery to return a single value
            cmd.ExecuteNonQuery()

            'Close the connection
            con.Close()
        End Using
    Catch ex As Exception
        'Display the error
        Console.WriteLine(ex.Message)
    Finally
        'Check if the connection object was initialized
        If con IsNot Nothing Then
            If con.State = ConnectionState.Open Then
                'Close the connection if it was left open(exception thrown)
                con.Close()
            End If

            'Dispose of the connection object
            con.Dispose()
        End If
    End Try
End Function

A couple of notes: Be sure to plug in your correct connection string. Be sure to change [MyTable] to the actual name of your Access table. Be sure to fix any typos in the column names in the SQL query, these are the values in the brackets after the [MyTable] and before VALUES.

Plz the code is not working for my project....it might be the version will be (visual basic or erlia)

[techgnome]

What do you mean it's not working? that could mean a number of different things. For we all know, it means that your fingers fell off. We're not psychic here. And none of us are sitting in your lap, so we don't see what you see. So you have to pick up your fingers, glue them back on and paint us a picture of words about what you're experiencing.

-tg

[kareninstructor]

Hello,

Try the following pattern where data is entered into TextBox controls and one selection from a ComboBox.

Imports System.Data.OleDb
Public Class Operations
    Private Builder As New OleDbConnectionStringBuilder With
    {
        .Provider = "Microsoft.ACE.OLEDB.12.0",
        .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
    }

    Private mExceptiom As Exception
    Public ReadOnly Property Exception As Exception
        Get
            Return mExceptiom
        End Get
    End Property

    Public Property CustomersDataTable As DataTable
    Public Property Titles As String()

    Public Function Load() As Boolean
        Try

            CustomersDataTable = New DataTable

            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT Identifier, CompanyName, ContactName, ContactTitle FROM Customers"

                    cn.Open()

                    CustomersDataTable.Load(cmd.ExecuteReader)
                    CustomersDataTable.DefaultView.Sort = "CompanyName"
                    CustomersDataTable.Columns("Identifier").ColumnMapping = MappingType.Hidden

                    cmd.CommandText = "SELECT ContactTitle FROM Customers GROUP BY ContactTitle"
                    Dim reader = cmd.ExecuteReader
                    Dim contactTitles As New List(Of String)
                    If reader.HasRows Then
                        While reader.Read
                            contactTitles.Add(reader.GetString(0))
                        End While
                        Titles = contactTitles.ToArray
                    End If
                End Using
            End Using

            Return True
        Catch ex As Exception
            mExceptiom = ex
            Return False
        End Try
    End Function
    ''' <summary>
    ''' Delete a customer by their primary key
    ''' </summary>
    ''' <param name="CustomerId"></param>
    ''' <returns></returns>
    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 CompanyName As String, ByVal ContactName 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 =
                        <SQL>
                            UPDATE Customer 
                            SET CompanyName = @CompanyName, ContactName = @ContactName 
                            WHERE Identifier = @Identifier
                        </SQL>.Value

                    cmd.Parameters.AddWithValue("@CompanyName", CompanyName)
                    cmd.Parameters.AddWithValue("@ContactName", ContactName)
                    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 AddNewRow(ByVal Name As String, ByVal ContactName As String, ByVal ContactTitle As String, ByRef Identfier As Integer) As Boolean
        Dim Success As Boolean = True

        Try
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            INSERT INTO Customers (CompanyName,ContactName, ContactTitle) 
                            Values(@CompanyName,@ContactName, @ContactTitle)
                        </SQL>.Value

                    cmd.Parameters.AddWithValue("@CompanyName", Name)
                    cmd.Parameters.AddWithValue("@ContactName", ContactName)
                    cmd.Parameters.AddWithValue("@ContactTitle", ContactTitle)

                    cn.Open()
                    Dim Affected As Integer = 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
            mExceptiom = ex
        End Try

        Return Success

    End Function
End Class

Form code

Public Class Form1
    Private ops As New Operations
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ops.Load()
        cboTitle.Items.AddRange(ops.Titles.ToArray)
    End Sub
    Private Sub addButton_Click(sender As Object, e As EventArgs) Handles addButton.Click
        If Not Controls.OfType(Of TextBox).Any(Function(tb) String.IsNullOrWhiteSpace(tb.Text)) AndAlso cboTitle.SelectedIndex > -1 Then
            Dim newId As Integer = 0
            If ops.AddNewRow(txtCompanyName.Text, txtContactName.Text, cboTitle.Text, newId) Then
                FinishAdd(newId)
            Else
                MessageBox.Show($"Failed with: {ops.Exception.Message}")
            End If
        Else
            MessageBox.Show("Please populate all controls")
        End If
    End Sub
    Private Sub FinishAdd(ByVal newId As Integer)
        DataGridView1.Rows.Add(New Object() {newId, txtCompanyName.Text, txtContactName.Text, cboTitle.Text})
        txtCompanyName.Text = ""
        txtContactName.Text = ""
        cboTitle.SelectedIndex = -1
        ActiveControl = txtCompanyName
    End Sub
End Class