by king (24 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Thu 19th February 2004
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
How to add , delete , update database records using sql query
API Declarations
choose ado library fron reference
or choose the ado control and drag it on the form
VBcoders
public conn as new adodb.connection
public rs as new adodb.recordset
public cmd as new adodb.command
dim sql as string
***********Adding Records*************
sql = " select * from table name " (here name of the table the table you have in your database)
rs.open sql , conn , opendynamic , adlockpessimistic
with rs
.addnew
fieldname = rs.fields(0) ( here field name is the name of the field on your form and rs means the recordset we have defined on the module)
you can add as many as records you want remember for example
textfield = rs.fields(1)
textfield = rs.fields(2)
textfield = rs.fields(3)
.update
end with
rs.close
we have to close the recordset in order to add the records
***********Deleting Records*************
if you want to delete all the records from the database use this code to delete all the records from the database
conn.execute "delete from table name "
thats it ....
you can place a message box or anything you want to alert the user with
****************************************************************************
now if you want delete specific record from the database
here is the code
sql = " delete from table name where fields = '" & fieldname & "'"
( fields here is the database feildname and fieldname is the field on your form)
rs.open , sql , conn , openstatic , adlockpessimistic
with cmd
cmd is basically the command string we have defined in the module section
.activeconnection = conn
.commandtext = sql
.exexute
thats it .........
***********Update records*******
to update a single record use this code
sql = " update table name set field = '" & fieldname & "' where field = '" & fieldname & "'"
the last query where field = '" & fieldname & "'"
please note that this is the field which contains primary key in your database
rs.open , sql , conn , openfowardonly,adlockpessimistic
with cmd
.activeconnection = conn
.commandtext = sql
.execute
if you want to update more than 1 records than
sql = " update table name set field = '" & fieldname & "', field = '" & fieldname & "' where field = '" & fieldname & "'"
good luck thats it i hope you find it easy