Insert Into

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

VB Code:

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.

Why not use "AddNew"??

VB Code:

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

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.

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.

thank you both.

Quote:

---

Originally Posted by user name

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.

Quote:

---

Originally Posted by user name

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.)

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.

Quote:

---

Originally Posted by robbedaya

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.

Quote:

---

Originally Posted by robbedaya

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 :)

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

Quote:

---

Originally Posted by robbedaya

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
.