Loop to insert records

J

JohnV

Active member
Joined
Feb 20, 2015
Messages
27
Programming Experience
3-5
Hi,

I have this code in vb.net to insert records to MS Access but i'm not successfull. I have a data from textboxes and DataGridview which i would like to insert into table and it was only 1 records insertedand also i got an error saying "The connection is was not close. the connection current state is open. Thank you in advance.

Below is my code.

VB.NET: 
[SIZE=2] 
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Button4_Click([/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] btnSave.Click[/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] xName [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] xTrndate [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Date[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] xDeptName [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] xIdNumber [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] xItmNum [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] xRepname [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] xRating [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] xRemark [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]            
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Dim conn = New OleDb.OleDbCommand[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]            
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'command.Connection = conn[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]            
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'sqlCom.Connection = conn[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]            
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Dim atdate As Date[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]            
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Dim id As String[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]

[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]            
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] x [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] = 0 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DataGridView1.Rows.Count - 1[/SIZE]
[SIZE=2]
                xName = txtName.Text

                xTrndate = txtDate.Text

                xIdNumber = txtIDnum.Text

                xDeptName = cmbDepartment.Text

                xItmNum = DataGridView1.Rows(x).Cells(0).Value

                xRepname = DataGridView1.Rows(x).Cells(1).Value

                xRating = DataGridView1.Rows(x).Cells(2).Value

                xRemark = DataGridView1.Rows(x).Cells(3).Value

                conn.Open()


                SQLstring = 
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"INSERT INTO tblFeedBackTran ([IdNumber], [Name],[TrnDate], [DeptName], [ItemNum], [RepName],[Ratings],[Remarks]) VALUES('"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & txtIDnum.Text & [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]" ',' "[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & txtName.Text & [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"','"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & txtDate.Text & [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"','"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & cmbDepartment.Text & [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"' ,@ItemNum,@RepName,@Ratings,@Remarks)"[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]                command = 
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] OleDbCommand(SQLstring, conn)[/SIZE]
[SIZE=2]

                command.Parameters.AddWithValue(
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"@ItemNum"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], xItmNum)[/SIZE]
[SIZE=2]
                command.Parameters.AddWithValue(
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"@RepName"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], xRepname)[/SIZE]
[SIZE=2]
                command.Parameters.AddWithValue(
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"@Ratings"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], xRating)[/SIZE]
[SIZE=2]
                command.Parameters.AddWithValue(
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"@Remarks"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], xRemark)[/SIZE]
[SIZE=2]
                command.ExecuteNonQuery()

                command.Dispose()

            
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]            
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'conn.Close()[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]

[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] OleDb.OleDbException[/SIZE]
[SIZE=2]
            MsgBox(ex.Message, MsgBoxStyle.Critical, 
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Oledb Error"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Exception[/SIZE]
[SIZE=2]
            MsgBox(ex.Message, MsgBoxStyle.Critical, 
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"General Error"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]
       
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Finally[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]            
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] conn.State = ConnectionState.Open [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] conn.Close()[/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]        MessageBox.Show(
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Registered Successfully!"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Register"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], MessageBoxButtons.OK, MessageBoxIcon.Information)[/SIZE]
[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/COLOR][/SIZE]
 
Sort by date Sort by votes
Hi Jim, thank you for your usual support. By the way, Still got an error. say Index was out of range... could not fix. I check already the table, spelling still getting the error. However, I modified the code and it was successful. May I ask your opinion on this code. thanks.

By the way, i have added a column for createdDateTime, just want to know if the code to insert this records is correct.

below is the working code.
Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
        Dim myConstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\jv\Desktop\FeedBackSystem\FBSystems\FBSystems\Data\KPI.accdb"
        con.ConnectionString = myConstring
        Dim sqlFrame As String
        Dim command As New OleDbCommand
        Dim ds As DataSet = New DataSet

        command.Connection = con
        sqlFrame = "INSERT INTO tblFeedBackTran ([IdNumber],[EmployeeName],[TrnDate],[DeptName],[ItemNum],[RepName],[Ratings],[Remarks]) VALUES (@IdNumber,@EmployeeName,@TrnDate,@DeptName,@ItemNum,@RepName,@Ratings,@Remarks)"
        con.Open()
        command = New OleDbCommand(sqlFrame, con)
        Try
            For X As Integer = 0 To (dgvReport.RowCount - 1)
                command.Parameters.AddWithValue("@IdNumber", CInt(txtIdNum.Text))
                command.Parameters.AddWithValue("@EmployeeName", txtName.Text)
                command.Parameters.AddWithValue("@TrnDate", CDate(txtDate.Text))
                command.Parameters.AddWithValue("@DeptName", txtDept.Text)
                command.Parameters.AddWithValue("@ItemNum", dgvReport.Rows(X).Cells(0).Value)
                command.Parameters.AddWithValue("@RepName", dgvReport.Rows(X).Cells(1).Value)
                command.Parameters.AddWithValue("@Ratings", dgvReport.Rows(X).Cells(2).Value)
                command.Parameters.AddWithValue("@Remarks", dgvReport.Rows(X).Cells(3).Value)
                'command.Parameters.AddWithValue("@CreatedDatetime", System.Data.OleDb.OleDbType.DBTimeStamp).Value = Date.Now()

                command.ExecuteNonQuery()
                command.Parameters.Clear()
            Next
            MessageBox.Show("New Records have been successfully Inserted!", "Records", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try

        'close connection
        con.Close()
 
Upvote 0 Downvote
ah ok. why i did not encounter an error when i run the code. where should I place this code. thanks.
Btw, I just encounter this error. When i try to run or my first transaction (insert multiple records) is successful then i close the form, then i open again the the same form to do transact I get this error say "A column named ItemNumber" already belong to this datatable." thanks.

I place this code in the btnClose
dt.clear -still got this error.


Imports System.Data
Imports System.Data.OleDb
Imports System.Diagnostics
Public Class frmDataEntry
    Dim sName As String
    Dim sDept As String
    Dim dt As New DataTable
    Dim adp As New OleDbDataAdapter

    'Private cma As CurrencyManager = DirectCast(BindingContext(dt), CurrencyManager)
    Private Sub frmDataEntry_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'Call to populate combobox procedure
        popCboxReport()
        popCboxRate()

        'Initial(Variable)
        Dim dte As Date = Date.Today
        'Dim dte As Date = Date.ParseExact(edate, "dd/MM/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo)
        txtDate.Text = (dte)
        sName = Module1.MyGloabUsername
        sDept = Module1.myDeptname
        txtName.Text = sName
        txtDept.Text = sDept

        'Set Data GridView
        With dgvReport
            dt.Columns.Add("ItemNumber", GetType(String))
            dt.Columns.Add("ReportName", GetType(String))
            dt.Columns.Add("Ratings", GetType(String))
            dt.Columns.Add("Comment", GetType(String))
            'AddHandler cma.CurrentChanged, AddressOf CurrentChanged
            .ReadOnly = True
            .MultiSelect = False
            .AllowUserToAddRows = False
            .AllowUserToDeleteRows = False
            dgvReport.DataSource = dt

            For Each c As DataGridViewColumn In dgvReport.Columns
                c.Width = 200
            Next
        End With
    End Sub
 
Last edited:
Upvote 0 Downvote
Hi Jim, This is the code that display the form.
By the way, May I ask your help on how to automate the numbers in datagridview. once i entered an item it will automaticall create a number item. thanks.
its okey with you if i will ask question on this thread or i will create a new thread. thank you.
Private Sub NewFormToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewFormToolStripMenuItem.Click
        frmDataEntry.ShowDialog()


   End Sub


 
Upvote 0 Downvote

Similar threads

R
  • Question
how to transfer data to two different tables in one single query
Replies
1
Views
5K
jmcilhinney
jmcilhinney
M
  • Question
Question How to append records in master detail tables.
Replies
0
Views
2K
Mazhar
M
M
  • Question
Question How to SqlTransaction?
Replies
4
Views
3K
Mazhar
M
E
  • Question
Question populating data from datagridview into textboxes
Replies
8
Views
3K
jmcilhinney
jmcilhinney
J
  • Question
Question Unable to save survey answers into database
Replies
2
Views
4K
Jasmine Koh
J

Share this page

Latest posts

Back
Top