how to insert record set value in to the table?

Question

How to insert record set value

Am having 6 fields in the record set

I want to insert into the table? How can I insert.

Used Query

INSERT INTO table values (recordset (0), recordset (1) ….. recordset (6))

But It showing Undefined function “recordset”

Please how to insert record set value in to the table?

Query Help?

Answer 1

Dim cmdCommand As New ADODB.Command
If recordSet.EOF = False Then

recordSet.MoveFirst

cmdCommand.CommandText = "insert into table ( field1) values ( " + recordSet.Fields(0) + ")"
cmdCommand.Execute()
recordSet.MoveNext
Loop Until recordSet.EOF = True

Keep in mind that you will need quotes in varchar fields

Answer 2

Try with the recordsetname.fields("field_name") and check that the recorsetname variable was defined. You can also use the syntax recordsetname!field_name.

Answer 3

Konstantinos gives a good answer, but you just need to be careful of the potential for SQL Injection issues.

The simplest fix would be to just replace any single apostrophes with two.

.CommandText = "insert into table (field1) values ( '" & Replace(recordSet.Fields(0), "'", "''") & "')"

Answer 4

It sounds like you're attempting to INSERT multiple records using a single statement (one INSERT for 6 records instead of 6 INSERT for 1 record each). If that's the case, the INSERT syntax fully supports it.

Here's an example:

INSERT INTO MyTable
    (row1, row2)
SELECT 
    value1, value2
FROM
    OtherTable

When you look at it like this, you can basically take any SELECT statement and put the INSERT clause on top of it. Of course, the column names need to line up correctly for it to work.