• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• How to add , delete , update database records using sql query

How to add , delete , update database records using sql query

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



Rate How to add , delete , update database records using sql query

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

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