Loop to insert records

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]
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
To answer your question specifically, the issue is that you're trying to open the connection on every iteration of the loop without having closed it. If you were going to do it with that loop, you'd call Open once, save all the data, then call Close once.

That said, I advise not doing it that way. I would recommend using a loop to populate a DataTable and then save the lot in a batch with one call to the Update method of a data adapter. I'll provide an example when I get the chance.
 

JohnV

Active member
Joined
Feb 20, 2015
Messages
27
Programming Experience
3-5
Thank you very much. By the way, Do I need to always initialize this in add button, edit button and delete button or one time only in the form load event. conn.ConnectionString =
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\jov\Desktop\FeedBackSystem\FBSystems\FBSystems\Data\KPI.accdb"
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
Thank you very much. By the way, Do I need to always initialize this in add button, edit button and delete button or one time only in the form load event. conn.ConnectionString =
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\jov\Desktop\FeedBackSystem\FBSystems\FBSystems\Data\KPI.accdb"
You shouldn't have the connection string hard-coded in your application regardless. If you do then you can't change it without recompiling the application. Normally you would add the connection string as a setting, which is then stored in the config file and accessed via My.Settings. You then use that setting in code as required.

As for where and when to create the connection using the connection string, it depends on circumstances. Ideally, you would have all your data access code in a separate class to your UI. You might then create one connection per data access object or one per operation. If you do have your data access code in your forms, you still might create one connection per form or one per operation. Just be aware that creating, opening and closing ADO.NET connections is relatively light-weight so do what makes most sense for the application design rather than giving more weight to efficiency than is reasonably required.

As for what I would consider to be a better way of structuring your code, here's that promised example:
Dim sql = <sql>
              INSERT INTO tblFeedBackTran (IdNumber,
                                           Name,
                                           TrnDate,
                                           DeptName,
                                           ItemNum,
                                           RepName,
                                           Ratings,
                                           Remarks)
               VALUES (@IdNumber,
                       @Name,
                       @TrnDate,
                       @DeptName,
                       @ItemNum,
                       @RepName,
                       @Ratings,
                       @Remarks)
          </sql>
Dim connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\jov\Desktop\FeedBackSystem\FBSyste ms\FBSystems\Data\KPI.accdb"
Dim table As New DataTable

With table.Columns
    .Add("ItemNum", GetType(String))
    .Add("RepName", GetType(String))
    .Add("Ratings", GetType(String))
    .Add("Remarks", GetType(String))
End With

For Each row In DataGridView1.Rows.Cast(Of DataGridViewRow).Where(Function(dgvr) Not dgvr.IsNewRow)
    With row.Cells
        table.Rows.Add(.Item(0).Value,
                       .Item(1).Value,
                       .Item(2).Value,
                       .Item(3).Value)
    End With
Next

Using adapter As New OleDbDataAdapter(sql.Value, connectionString)
    With adapter.InsertCommand.Parameters
        .AddWithValue("@IdNumber", CInt(txtIDnum.Text))
        .AddWithValue("@Name", txtName.Text)
        .AddWithValue("@TrnDate", CDate(txtDate.Text))
        .AddWithValue("@DeptName", cmbDepartment.Text)
        .Add("@ItemNum", OleDbType.VarChar, 50, "ItemNum")
        .Add("@RepName", OleDbType.VarChar, 50, "RepName")
        .Add("@Ratings", OleDbType.VarChar, 50, "Ratings")
        .Add("@Remarks", OleDbType.VarChar, 50, "Remarks")
    End With

    adapter.Update(table)
End Using
Even then, I don't think that you should be looping through the rows of the grid to populate the DataTable when you could have created a DataTable ahead of time and bound it to the grid. In that case, adding data to the grid would automatically populate the DataTable.

Also, it looks like you're inserting all your data as text. It looks like good design requires that, at the very least, your TrnDate column should be storing actual dates rather than text. It also looks like IdNumber should be numeric and DeptName should actually be a numeric foreign key from a table containing department IDs and names.
 

JohnV

Active member
Joined
Feb 20, 2015
Messages
27
Programming Experience
3-5
Thank you very much for help and for giving this example and info jmcilhinney. By the way, what is the purpose of having class or module? can you give me an example of data access code that was written in a class and how can be pass to a form or call. sorry i have a lot of questions. I'm new to vb.net and ms access environment.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
If you want more information about separation of data access code from presentation logic and business logic then do some research on "n-tier design". That is the most common term used for separating application code into multiple physical and/or logical layers.
 

JohnV

Active member
Joined
Feb 20, 2015
Messages
27
Programming Experience
3-5
Hi John,
Thank you very much John. I found your blog and this is a good read for me as beginner. By the way, can you please take a look my final code and also i found a data access code created in a class from other forum and i used it.

May i ask favor if you can check the code. I have an error with "Green color comment" in my final code. thanks.
VB.NET:
[SIZE=2]      
 With Error:
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] row [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DataGridView1.Rows.Cast([/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Of[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DataGridViewRow).Where([/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2](dgvr) [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Not[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] dgvr.IsNewRow)[/SIZE]
[SIZE=2]
            
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] row.Cells[/SIZE]
[SIZE=2]
                 table.Rows.Add(.Item(0).Value,

                                .Item(1).Value,    
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Error: property access must assign to the property or use its value[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]                                .Item(2).Value,    
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Error: property access must assign to the property or use its value[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]                                .Item(3).Value)    
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Error: property access must assign to the property or use its value[/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]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]With[/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]Next[/COLOR][/SIZE][/COLOR][/SIZE]



[SIZE=2] 
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Using[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] adapter [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] OleDbDataAdapter(strSql.value, dbConnector)  [/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'error  : value is not a member of string[/COLOR][/SIZE][/COLOR][/SIZE]

Data Access Code
VB.NET:
[SIZE=2][SIZE=2]
Public
[/SIZE][/SIZE][SIZE=2][SIZE=2]Class[/SIZE][/SIZE][SIZE=2] DatabaseConnector[/SIZE]
[SIZE=2]
    
[/SIZE][SIZE=2][SIZE=2]''' [/SIZE][/SIZE][SIZE=2][SIZE=2]<summary>[/SIZE][/SIZE]
[SIZE=2]
    
[/SIZE][SIZE=2][SIZE=2]''' Returns an OLEDB connection string to an Access 2010 database[/SIZE][/SIZE]
[SIZE=2]
    
[/SIZE][SIZE=2][SIZE=2]''' [/SIZE][/SIZE][SIZE=2][SIZE=2]</summary>[/SIZE][/SIZE]
[SIZE=2]
    
[/SIZE][SIZE=2][SIZE=2]''' [/SIZE][/SIZE][SIZE=2][SIZE=2]<returns>[/SIZE][/SIZE][SIZE=2][SIZE=2]The OLEDB connection string to the Access 2010 database[/SIZE][/SIZE][SIZE=2][SIZE=2]</returns>[/SIZE][/SIZE]
[SIZE=2]
    
[/SIZE][SIZE=2][SIZE=2]Private[/SIZE][/SIZE][SIZE=2][SIZE=2]Function[/SIZE][/SIZE][SIZE=2] GetConnectionString() [/SIZE][SIZE=2][SIZE=2]As[/SIZE][/SIZE][SIZE=2][SIZE=2]String[/SIZE][/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][SIZE=2]' Create the Connection string[/SIZE][/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][SIZE=2]Dim[/SIZE][/SIZE][SIZE=2] strConnection [/SIZE][SIZE=2][SIZE=2]As[/SIZE][/SIZE][SIZE=2][SIZE=2]String[/SIZE][/SIZE]
[SIZE=2]
        strConnection = _
                    
[/SIZE][SIZE=2][SIZE=2]"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\jov\Desktop\FeedBackSystem\FBSystems\FBSystems\Data\KPI.accdb"[/SIZE][/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][SIZE=2]'"Provider=Microsoft.ACE.OLEDB.12.0;" & _[/SIZE][/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][SIZE=2]'"Data Source=C:\Test\DatabaseFile.accdb;" & _[/SIZE][/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][SIZE=2]'"User ID=Admin;Password=;"[/SIZE][/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][SIZE=2]' Return the Connection string[/SIZE][/SIZE]
[SIZE=2]
        GetConnectionString = strConnection
    
[/SIZE][SIZE=2][SIZE=2]End[/SIZE][/SIZE][SIZE=2][SIZE=2]Function[/SIZE][/SIZE][SIZE=2][SIZE=2]' End of: Private Function GetConnectionString() As String[/SIZE][/SIZE]
[SIZE=2][SIZE=2]
[/SIZE][/SIZE][SIZE=2]
    
[/SIZE][SIZE=2][SIZE=2]''' [/SIZE][/SIZE][SIZE=2][SIZE=2]<summary>[/SIZE][/SIZE]
[SIZE=2]
    
[/SIZE][SIZE=2][SIZE=2]''' Allow the user to execute non-record returning queries[/SIZE][/SIZE]
[SIZE=2]
    
[/SIZE][SIZE=2][SIZE=2]''' [/SIZE][/SIZE][SIZE=2][SIZE=2]</summary>[/SIZE][/SIZE]
[SIZE=2]
    
[/SIZE][SIZE=2][SIZE=2]''' [/SIZE][/SIZE][SIZE=2][SIZE=2]<param name="SqlCode">[/SIZE][/SIZE][SIZE=2][SIZE=2]The SQL Statement to execute against the database[/SIZE][/SIZE][SIZE=2][SIZE=2]</param>[/SIZE][/SIZE]
[SIZE=2]
    
[/SIZE][SIZE=2][SIZE=2]''' [/SIZE][/SIZE][SIZE=2][SIZE=2]<returns>[/SIZE][/SIZE][SIZE=2][SIZE=2]True if successful, otherwise False[/SIZE][/SIZE][SIZE=2][SIZE=2]</returns>[/SIZE][/SIZE]
[SIZE=2]
    
[/SIZE][SIZE=2][SIZE=2]Public[/SIZE][/SIZE][SIZE=2][SIZE=2]Function[/SIZE][/SIZE][SIZE=2] RunSqlNonQuery([/SIZE][SIZE=2][SIZE=2]ByVal[/SIZE][/SIZE][SIZE=2] SqlCode [/SIZE][SIZE=2][SIZE=2]As[/SIZE][/SIZE][SIZE=2][SIZE=2]String[/SIZE][/SIZE][SIZE=2]) [/SIZE][SIZE=2][SIZE=2]As[/SIZE][/SIZE][SIZE=2][SIZE=2]Boolean[/SIZE][/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][SIZE=2]On[/SIZE][/SIZE][SIZE=2][SIZE=2]Error[/SIZE][/SIZE][SIZE=2][SIZE=2]GoTo[/SIZE][/SIZE][SIZE=2] HandleErrors[/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][SIZE=2]Dim[/SIZE][/SIZE][SIZE=2] bResult [/SIZE][SIZE=2][SIZE=2]As[/SIZE][/SIZE][SIZE=2][SIZE=2]Boolean[/SIZE][/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][SIZE=2]Dim[/SIZE][/SIZE][SIZE=2] cmd [/SIZE][SIZE=2][SIZE=2]As[/SIZE][/SIZE][SIZE=2] OleDb.OleDbCommand[/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][SIZE=2]' Create the OLEDB Command object[/SIZE][/SIZE]
[SIZE=2]
        cmd = 
[/SIZE][SIZE=2][SIZE=2]New[/SIZE][/SIZE][SIZE=2] OleDb.OleDbCommand(SqlCode)[/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][SIZE=2]' Open the Connection[/SIZE][/SIZE]
[SIZE=2]
        cmd.Connection = 
[/SIZE][SIZE=2][SIZE=2]New[/SIZE][/SIZE][SIZE=2] OleDb.OleDbConnection(GetConnectionString())[/SIZE]
[SIZE=2]
        cmd.Connection.Open()
        
[/SIZE][SIZE=2][SIZE=2]' Execute the SQL Statement[/SIZE][/SIZE]
[SIZE=2]
        cmd.ExecuteNonQuery()
        
[/SIZE][SIZE=2][SIZE=2]' It looks like we've succeeded - return True[/SIZE][/SIZE]
[SIZE=2]
        bResult = 
[/SIZE][SIZE=2][SIZE=2]True[/SIZE][/SIZE]
[SIZE=2]
ExitFunction:
        
[/SIZE][SIZE=2][SIZE=2]' Close the connection[/SIZE][/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][SIZE=2]If[/SIZE][/SIZE][SIZE=2] ([/SIZE][SIZE=2][SIZE=2]Not[/SIZE][/SIZE][SIZE=2] IsNothing(cmd.Connection)) [/SIZE][SIZE=2][SIZE=2]Then[/SIZE][/SIZE]
[SIZE=2]
            
[/SIZE][SIZE=2][SIZE=2]If[/SIZE][/SIZE][SIZE=2] (cmd.Connection.State <> ConnectionState.Closed) [/SIZE][SIZE=2][SIZE=2]Then[/SIZE][/SIZE]
[SIZE=2]
                cmd.Connection.Close()
            
[/SIZE][SIZE=2][SIZE=2]End[/SIZE][/SIZE][SIZE=2][SIZE=2]If[/SIZE][/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][SIZE=2]End[/SIZE][/SIZE][SIZE=2][SIZE=2]If[/SIZE][/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][SIZE=2]' Return the result and exit[/SIZE][/SIZE]
[SIZE=2]
        RunSqlNonQuery = bResult
        
[/SIZE][SIZE=2][SIZE=2]Exit[/SIZE][/SIZE][SIZE=2][SIZE=2]Function[/SIZE][/SIZE]
[SIZE=2]
HandleErrors:
        
[/SIZE][SIZE=2][SIZE=2]' Handle any errors here...[/SIZE][/SIZE]
[SIZE=2]
        MsgBox(
[/SIZE][SIZE=2][SIZE=2]"An error was raised!"[/SIZE][/SIZE][SIZE=2] & vbNewLine & [/SIZE][SIZE=2][SIZE=2]"Message: "[/SIZE][/SIZE][SIZE=2] & Err.Description, MsgBoxStyle.Critical, [/SIZE][SIZE=2][SIZE=2]"Error"[/SIZE][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]
        Err.Clear()
        bResult = 
[/SIZE][SIZE=2][SIZE=2]False[/SIZE][/SIZE][SIZE=2][SIZE=2]' Return failure[/SIZE][/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][SIZE=2]Resume[/SIZE][/SIZE][SIZE=2] ExitFunction[/SIZE]
[SIZE=2]
    
[/SIZE][SIZE=2][SIZE=2]End[/SIZE][/SIZE][SIZE=2][SIZE=2]Function[/SIZE][/SIZE][SIZE=2][SIZE=2]' End of: Public Function RunSqlNonQuery(SqlCode As String) As Boolean[/SIZE][/SIZE]
[SIZE=2][SIZE=2]
[/SIZE][/SIZE][SIZE=2][SIZE=2]
End
[/SIZE][/SIZE][SIZE=2][SIZE=2]Class[/SIZE][/SIZE]

my Final Code
VB.NET:
[SIZE=2]    
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'This connection is came from Database Connector Class[/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]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] dbConnector [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DatabaseConnector[/SIZE]
[SIZE=2]
        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] strSql [/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] table [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DataTable[/SIZE]
[SIZE=2]

        
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]' Create an INSERT SQL Statement[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]        strSql = _

              
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"INSERT INTO tblFeedBackTran (IdNumber,Name,TrnDate,DeptName,ItemNum, "[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & _[/SIZE]
[SIZE=2]
              
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"RepName,Ratings,Remarks) VALUES (@IdNumber,@Name,@TrnDate,@DeptName,@ItemNum,@RepName,@Ratings,@Remarks)"[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]

[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]        
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]' Execute the SQL Statement against the Access database[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]        dbConnector.RunSqlNonQuery(strSql)


        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] table.Columns[/SIZE]
[SIZE=2]
            .Add(
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"ItemNum"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]))[/SIZE]
[SIZE=2]
            .Add(
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"RepName"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]))[/SIZE]
[SIZE=2]
            .Add(
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Ratings"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]))[/SIZE]
[SIZE=2]
            .Add(
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Remarks"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]))[/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]With[/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]For[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] row [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DataGridView1.Rows.Cast([/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Of[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DataGridViewRow).Where([/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2](dgvr) [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Not[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] dgvr.IsNewRow)[/SIZE]
[SIZE=2]
            
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] row.Cells[/SIZE]
[SIZE=2]
                 table.Rows.Add(.Item(0).Value,

                                .Item(1).Value,    
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Error: property access must assign to the property or use its value[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]                                .Item(2).Value,    
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Error: property access must assign to the property or use its value[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]                                .Item(3).Value)    
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Error: property access must assign to the property or use its value[/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]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]With[/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]Next[/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]Using[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] adapter [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] OleDbDataAdapter(strSql.value, dbConnector)  [/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'error  : value is not a member of 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]With[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] adapter.InsertCommand.Parameters[/SIZE]
[SIZE=2]
                .AddWithValue(
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"@IdNumber"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]CInt[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2](txtIDnum.Text))[/SIZE]
[SIZE=2]
                .AddWithValue(
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"@Name"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], txtName.Text)[/SIZE]
[SIZE=2]
                .AddWithValue(
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"@TrnDate"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]CDate[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2](txtDate.Text))[/SIZE]
[SIZE=2]
                .AddWithValue(
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"@DeptName"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], cmbDepartment.Text)[/SIZE]
[SIZE=2]
                .Add(
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"@ItemNum"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], OleDbType.VarChar, 50, [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"ItemNum"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]
                .Add(
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"@RepName"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], OleDbType.VarChar, 50, [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"RepName"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]
                .Add(
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"@Ratings"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], OleDbType.VarChar, 50, [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Ratings"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]
                .Add(
[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"@Remarks"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], OleDbType.VarChar, 50, [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Remarks"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/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]With[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]            adapter.Update(table)

        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Using[/COLOR][/SIZE][/COLOR][/SIZE]


 
Last edited:

JohnV

Active member
Joined
Feb 20, 2015
Messages
27
Programming Experience
3-5
Hi Jim, Just run the code and i got an error message. "Object reference not set to an instance of an object"
Then I added this code

VB.NET:
[SIZE=2]        adp = 
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] OleDbDataAdapter[/SIZE]
[SIZE=2]
        adp.InsertCommand = 
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] OleDbCommand(strSql, con)[/SIZE]
[SIZE=2]
        adp.InsertCommand.ExecuteNonQuery()[/SIZE]


and here is the error message upon adding the this code." No value given for one or more required parameters"
by the eay i change the column Name to EmployeeName. Reading from forums it say that the Name is not reccomended to use as column Name. This is an access reserved word.
 

JohnV

Active member
Joined
Feb 20, 2015
Messages
27
Programming Experience
3-5
Hi Jim, Below is the code for insert records to ms access and also my dataGridview codes. thanks.



Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
        Dim conn As New System.Data.OleDb.OleDbConnection()
        conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\jv\Desktop\FeedBackSystem\FBSystems\FBSystems\Data\KPI.accdb"

        Try
            Dim strSql As String
            Dim table As New DataTable
            strSql = _
                  "INSERT INTO tblFeedBackTran ([IdNumber],[EmployeeName],[TrnDate],[DeptName],[ItemNum], " & _
                  "[RepName],[Ratings],[Remarks]) VALUES (@IdNumber,@EmployeeName,@TrnDate,@DeptName,@ItemNum,@RepName,@Ratings,@Remarks)"

            conn.Open()
            adp = New OleDbDataAdapter
            adp.InsertCommand = New OleDbCommand(strSql, conn)
            adp.InsertCommand.ExecuteNonQuery()

            With table.Columns
                .Add("ItemNum", GetType(String))
                .Add("RepName", GetType(String))
                .Add("Ratings", GetType(String))
                .Add("Remarks", GetType(String))
            End With

            For Each row In dgvReport.Rows.Cast(Of DataGridViewRow).Where(Function(dgvr) Not dgvr.IsNewRow)
                With row.Cells
                    table.Rows.Add(.Item(1).Value)
                    table.Rows.Add(.Item(2).Value)
                    table.Rows.Add(.Item(3).Value)
                    table.Rows.Add(.Item(4).Value)
                End With
            Next

            Using adapter As New OleDbDataAdapter(strSql, conn)
                With adapter.InsertCommand.Parameters
                    .AddWithValue("@IdNumber", CInt(txtIdNum.Text))
                    .AddWithValue("@EmployeeName", txtName.Text)
                    .AddWithValue("@TrnDate", CDate(txtDate.Text))
                    .AddWithValue("@DeptName", txtDept.Text)
                    .Add("@ItemNum", OleDbType.VarChar, 50, "ItemNum")
                    .Add("@RepName", OleDbType.VarChar, 50, "RepName")
                    .Add("@Ratings", OleDbType.VarChar, 50, "Ratings")
                    .Add("@Remarks", OleDbType.VarChar, 50, "Remarks")
                End With
                adapter.Update(table)
            End Using
            MessageBox.Show("Records have been successfully Inserted!", "Records", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            'MessageBox.Show("Failed to connect to Database..", "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            MessageBox.Show(ex.Message)
        End Try
    End Sub


for my DataGridView
    '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

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        'Dim i As Integer
        dt.Rows.Add("", CmbReportName.Text, CmbRate.Text, txtComment.Text)            ', dgvReport.Rows(i)) '.Cells("ItemNumber").Value, dgvReport.Rows(i).Cells("ReportName").Value)
    End Sub

Private Sub DataGridView1_RowPrePaint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewRowPrePaintEventArgs) Handles dgvReport.RowPrePaint
        If e.RowIndex >= 0 Then
            Me.dgvReport.Rows(e.RowIndex).Cells(0).Value = e.RowIndex + 1
        End If
    End Sub
 
Last edited:

JohnV

Active member
Joined
Feb 20, 2015
Messages
27
Programming Experience
3-5
This is the error that i have encountered right now. it say "Index was out of range. must be non negative and less thatn the size of the collection. parameter name: Index.
 

JohnV

Active member
Joined
Feb 20, 2015
Messages
27
Programming Experience
3-5
Hi Jim, Thank you Jim for your reply. Just removed the codes you mention above and still got an error it says" Object reference not set to an instance of an object". I notice when i debug the code the cursor stop in this portion "With adapter.insertcommand.parameters" the cursor loop and went to catch ex as exception and also this error "Index was out of range. must be non negative and less thatn the size of the collection. parameter name: Index.

another things, in your previous code for "Using adapter as new OleDbDataAdapter(sqlStr.value, connectionstring" the sqlStr.value was underline and say" value is not a member string. then i change to .toString and i try also to removed the value still got an error. May i know what is the exact command for this code. Maybe this is the reason for this error.

Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
        Try
            Dim strSql = "INSERT INTO tblFeedBackTran ([IdNumber],[EmployeeName],[TrnDate],[DeptName],[ItemNum],[RepName],[Ratings],[Remarks]) VALUES (@IdNumber,@EmployeeName,@TrnDate,@DeptName,@ItemNum,@RepName,@Ratings,@Remarks)"
            Dim ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\jv\Desktop\FeedBackSystem\FBSystems\FBSystems\Data\KPI.accdb"
            Dim table As New DataTable

            With table.Columns
                .Add("ItemNum", GetType(String))
                .Add("RepName", GetType(String))
                .Add("Ratings", GetType(String))
                .Add("Remarks", GetType(String))
            End With

            For Each row In dgvReport.Rows.Cast(Of DataGridViewRow).Where(Function(dgvr) Not dgvr.IsNewRow)
                With row.Cells
                    table.Rows.Add(.Item(0).Value)
                    table.Rows.Add(.Item(1).Value)
                    table.Rows.Add(.Item(2).Value)
                    table.Rows.Add(.Item(3).Value)
                End With
            Next

            Using adapter As New OleDbDataAdapter(strSql.ToString, ConnectionString)
                With adapter.InsertCommand.Parameters
                    .AddWithValue("@IdNumber", CInt(txtIdNum.Text))
                    .AddWithValue("@EmployeeName", txtName.Text)
                    .AddWithValue("@TrnDate", CDate(txtDate.Text))
                    .AddWithValue("@DeptName", txtDept.Text)
                    .Add("@ItemNum", OleDbType.VarChar, 50, "ItemNum")
                    .Add("@RepName", OleDbType.VarChar, 50, "RepName")
                    .Add("@Ratings", OleDbType.VarChar, 50, "Ratings")
                    .Add("@Remarks", OleDbType.VarChar, 50, "Remarks")
                End With
                adapter.Update(table)
            End Using
            MessageBox.Show("Records have been successfully Inserted!", "Records", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
 
Last edited:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
Sorry, I gave you some bad information earlier. That code should have been:
Dim sql = <sql>
      INSERT INTO tblFeedBackTran (IdNumber,
                                   Name,
                                   TrnDate,
                                   DeptName,
                                   ItemNum,
                                   RepName,
                                   Ratings,
                                   Remarks)
       VALUES (@IdNumber,
               @Name,
               @TrnDate,
               @DeptName,
               @ItemNum,
               @RepName,
               @Ratings,
               @Remarks)
  </sql>
Dim connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\jov\Desktop\FeedBackSystem\FBSyste ms\FBSystems\Data\KPI.accdb"
Dim table As New DataTable

With table.Columns
    .Add("ItemNum", GetType(String))
    .Add("RepName", GetType(String))
    .Add("Ratings", GetType(String))
    .Add("Remarks", GetType(String))
End With

For Each row In DataGridView1.Rows.Cast(Of DataGridViewRow).Where(Function(dgvr) Not dgvr.IsNewRow)
    With row.Cells
        table.Rows.Add(.Item(0).Value,
                       .Item(1).Value,
                       .Item(2).Value,
                       .Item(3).Value)
    End With
Next

Using connection As New OleDbConnection(connectionString),
      adapter As New OleDbDataAdapter With {.InsertCommand = New OleDbCommand(sql.Value, connection)}
    With adapter.InsertCommand.Parameters
        .AddWithValue("@IdNumber", CInt(txtIDnum.Text))
        .AddWithValue("@Name", txtName.Text)
        .AddWithValue("@TrnDate", CDate(txtDate.Text))
        .AddWithValue("@DeptName", cmbDepartment.Text)
        .Add("@ItemNum", OleDbType.VarChar, 50, "ItemNum")
        .Add("@RepName", OleDbType.VarChar, 50, "RepName")
        .Add("@Ratings", OleDbType.VarChar, 50, "Ratings")
        .Add("@Remarks", OleDbType.VarChar, 50, "Remarks")
    End With

    adapter.Update(table)
End Using
Note the change to the last Using block.
another things, in your previous code for "Using adapter as new OleDbDataAdapter(sqlStr.value, connectionstring" the sqlStr.value was underline and say" value is not a member string. then i change to .toString and i try also to removed the value still got an error. May i know what is the exact command for this code. Maybe this is the reason for this error.
That is not the issue but I was using an XML literal, not a String. Value is a member of an XElement so if you had done as I did then Value would have worked. If you're just going to use a String then just use a String. There's no point calling ToString on a String.
 

JohnV

Active member
Joined
Feb 20, 2015
Messages
27
Programming Experience
3-5
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()
 

JohnV

Active member
Joined
Feb 20, 2015
Messages
27
Programming Experience
3-5
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:

JohnV

Active member
Joined
Feb 20, 2015
Messages
27
Programming Experience
3-5
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


 
Top Bottom