Adding many records in MS Access

[some-1]

I have been searching for a couple of days for some code to help me add new rows to MS Access database. I found some useful code in this forum. I am trying to modify the code here to add many new records but I could not get it to work.

I really appreciate some help

Public Sub test4()

        'Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDataBase.mdb")

        Dim insertCommand As New OleDb.OleDbCommand("INSERT INTO StudentsTable (ID, Name) VALUES ('1','John')", Connection)

        Try

            Connection.Open()

            MsgBox("Connection Opened")
            
            insertCommand.ExecuteNonQuery()

            MsgBox("The query has been executed")

        Catch ex As Exception

            MessageBox.Show(ex.ToString())


        Finally

        End Try

        Connection.Close()

    End Sub

 

[Paszt]

It would help if you would say what is not working.
The declaration of Connection is commented out, unless you have a class variable named Connection the code won't work.
If you want to insert many new rows then you would need to create a loop of some sort.

 

[some-1]

Paszt - thanks for your qick reply and input. Sorry I forgot the commentation for the Connection by mistake as a result of copy/paste in this forum Message.

Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDataBase.mdb")

The code pasted is working fine. But you understod me correct. I need to create some kind of a loop but I do not know how to do that. I need to create a loop for example to add autonumber in the ID value like: 1,2,3,4 etc.

Can you help me please

 

[Paszt]

Autonumber fields are just that, auto-numbered. You don't need to send a value for those fields, just insert the other fields and the database will assign the correct number for the autonumber (ID) field.
I'm not sure what your goal is here so I won't suggest code for the loop. For/next or while loops are available, depending on what you want to do.

 

[some-1]

Thanks. The auto-number solves adding IDs per each row. Here is my goal: my application has list of user names loaded inside 'ListBox'. I need to implement For/Next loop so that to read every name in the 'ListBox' and passes it as string valu into the 'Name' field.

The problem is that I tried to add the For/Next loop in the code but it does not work. I am not sure, but it seems that the 'ExecuteNonQuery()' method only allows to add one row, not multiple rows. Is that correct?

Thanks for helping me with your suggestions.

 

[some-1]

I managed to solve the problem of adding many records and also use Auto-increment ID number. I was able to create a simple loop as you suggested and worked fine Now how can I pass String values to the fileds 'Name' and 'Age' from my ListBox?

Thank you

Public Sub test4()

        Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDataBase.mdb")

Dim x As Integer
        Connection.Open()
For x = 0 To 2

        Dim insertCommand As New OleDb.OleDbCommand("INSERT INTO StudentsTable (Name, Age) VALUES ('John', '30')", Connection)

        Try
         
            MsgBox("Connection Opened")
            
            insertCommand.ExecuteNonQuery()

            MsgBox("The query has been executed")

        Catch ex As Exception

            MessageBox.Show(ex.ToString())


        Finally

        End Try
Next x

        Connection.Close()

    End Sub

 

[Paszt]

Where is the data in the listbox coming from?
See this article for a good walk-through: Walkthrough: Updating Data Using a Database Update Query in Web Forms