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
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.
 
Upvote 0 Downvote
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"
 
Upvote 0 Downvote
JohnV said:
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"
Click to expand...
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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:
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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:
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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:
Upvote 0 Downvote
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.
Click to expand...
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.
 
Upvote 0 Downvote

Similar threads

R
  • Question
how to transfer data to two different tables in one single query
Replies
1
Views
4K
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