Insert Into

[user name]

i use this to grab the fields from my database but how do you add a new row using the INSERT INTO.

Set rec = New Recordset
 
rec.Open "SELECT * FROM Tdetails", con, adOpenKeyset, adLockOptimistic
 
 Do While Not rec.EOF
  Set itm = lv.ListItems.Add(, , rec.Fields(0))
   itm.SubItems(1) = rec.Fields(1)
   itm.SubItems(2) = rec.Fields(2)
   itm.SubItems(3) = rec.Fields(3)
   itm.SubItems(4) = rec.Fields(4)
   itm.SubItems(5) = rec.Fields(5)
  rec.MoveNext
 Loop
 
rec.Close
Set rec = Nothing

thanks.

[robbedaya]

Why not use "AddNew"??

Set rec = New Recordset
 
rec.Open "SELECT Top 1 * FROM Tdetails", con, adOpendynamic, adLockPessimistic
 
with rec
 .addnew
 .Fields(0).value = "value 0"
 .Fields(1).value = "value 1"
 .Fields(2).value = "value 2"
 .Fields(3).value = "value 3"
 .update
end with
 
rec.Close
Set rec = Nothing

replace "value x" by the value you want to insert in the database

[user name]

that was perfect robbedaya.

another question if you dont mind.
i see you put adOpendynamic, adLockPessimistic.
is adOpenKeyset, adLockOptimistic correct for just showing the data and yours for updating it ? and why have you put "Top 1" in the statement ?

thanks for your help.

[Pradeep1210]

A simpler method is to use the connection object to execute the query like this:

con.Execute "INSERT INTO Tdetails VALUES( value1, value2, value3 ....)"

This will directly execute the query on ur database.

[user name]

thank you both.

[robbedaya]

i see you put adOpendynamic, adLockPessimistic.
is adOpenKeyset, adLockOptimistic correct for just showing the data and yours for updating it ?

Don't exactly know when to use adopenkeyset en adlock optimistic

i use adOpenStatic and adLockReadOnly to read data from the database, because nothing is editable, so nothing can be changed by accident

i use adOpenDynamic and adLockPessimistic to write data to the database, the data is editable and is the record locked properly so the date kan not be altered by a third party while you're editing.

and why have you put "Top 1" in the statement ?

When inserting you don't need to load the whole recordset (if your table contains thousands of records it would take a lot of time) so i took the top 1 the only one record is loaded.

Normally i do it like this

"SELECT * FROM table Where ID_column=0" (if the id_column never contains 0, so no records are loaded when inserting.)

[Pradeep1210]

U r doing it OK username.
adLockOptmistic and adOpenStatic. This is sufficient in most of the situations. u may consider adOpenKeySet also if u want to see records changed by other users.

i use adOpenDynamic and adLockPessimistic to write data to the database, the data is editable and is the record locked properly so the date kan not be altered by a third party while you're editing.

we should avoid dynamic recordsets and pessimistic locking where ever possible.
I think u are wrong here. Pessimistic locking means locking the whole table when updating it. Optimistic locking locks only the current row.

When inserting you don't need to load the whole recordset (if your table contains thousands of records it would take a lot of time) so i took the top 1 the only one record is loaded.

Normally i do it like this

"SELECT * FROM table Where ID_column=0" (if the id_column never contains 0, so no records are loaded when inserting.)

I too prefer this method. But a bettter method to open empty recordsets is to avoid any field name in where clause like this:

SELECT * FROM TableName WHERE 0=1

This will always return an empty recordset.

Pradeep

[robbedaya]

is it possible to edit a record that isn't opened "adDynamic"?

[Pradeep1210]

is it possible to edit a record that isn't opened "adDynamic"?

Yes dynamic recordsets are editiable.
Rerecordsets are readonly only when they are opened as adLockReadOnly
.