By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,202 Members | 1,521 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,202 IT Pros & Developers. It's quick & easy.

Vb6.0 help urgent

P: 19
i am new for vb6.0. i am using vb6.0 and msaccess. i have written following code for add new records to database table

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Command1_Click() 
  2. On Error Resume Next
  3. If Trim(Text2.Text) = Trim(Text3.Text) Then
  4. rs.MoveFirst
  5. While Not rs.EOF
  6. If Trim(Text1.Text) = Trim(rs.Fields(0)) Then
  7. MsgBox "You are already exist"
  8. Text1.Text = ""
  9. Text2.Text = ""
  10. End If
  11. rs.MoveNext
  12. Wend
  13. If rs.EOF Then
  14. rs.AddNew
  15. rs!uid = Trim(Text1.Text)
  16. rs!pwd = Trim(Text2.Text)
  17.  
  18. rs.Update
  19. MsgBox "New user created"
  20. End If
  21. Else
  22. MsgBox "Please correct Password"
  23. Text2.Text = ""
  24. Text3.Text = ""
  25. End If
  26. End Sub
  27.  
  28.  
present i need code for delete records fom the table condition is
"If Trim(Text1.Text) = Trim(rs.Fields(0)) Then click delete button automatically delete that record.....


Plz urgent help me

mailid is koti_bujji1@yahoo.com


Thanks
Jul 6 '06 #1
Share this Question
Share on Google+
4 Replies


sashi
Expert 100+
P: 1,754
Hi Kumar,

below are some sample codes that allow you to add, edit & delete records from database.. both the DAO & ADO samples are included.. good luck my fren.. :)

This is not about how to create or design a database, it is about how to connect to a database and manipulate a database using VB. It will work (with some minor alterations) in VBA as well.

There are several ways of connecting to a database (for example, Access), via data bound controls, DAO or ADO. On the whole I do not use data bound controls because I like to keep control of what is happening to the data so the rest is about DAO/ADO.

To start with you need to create a few variables of the following types


Workspace ADODB.Connection - This is required if you are using Transaction Processes (I will explain later)
Database - This connects to the database Recordset
ADODB.Recordset - This is the table/query level variable
Field ADODB.Field - This allows us to get info about the fields
Connecting to a Database
With an Access database it is possible to connect to the database in 2 ways, JET or ODBC. Personally I use ODBC because the file management is easier; to change the filename or path just use the ODBC administrator in the control panel.

Note: These examples are here to show what to do very simply, some of the commands have more options than are shown so please review the help files for more details.

DAO example - JET Connection

Expand|Select|Wrap|Line Numbers
  1. Dim ws as Workspace
  2. Dim db as Database
  3.  
  4. Set ws=DBEngine.Workspaces(0)
  5. set db=ws.OpenDatabase({databasepath and name})
  6.  
DAO example - ODBC Connection

Expand|Select|Wrap|Line Numbers
  1. Dim ws as Workspace
  2. dim db as database
  3. dim strConnection as string
  4.  
  5. set ws=DBEngine.Workspaces(0)
  6. let strConnection= "ODBC;DSN=" & DatabaseName & ";UID=" & UserName 
  7. & ";PWD=" & UserPassword
  8. set db=ws.OpenDatabase("", False, False, strConnection)
  9.  
ADO Example

Expand|Select|Wrap|Line Numbers
  1. Dim ad as ADODB.Connection
  2.  
  3. set ad=New ADODB.Connection
  4. Let ad.ConnectionString= "ODBC;DSN=" & DatabaseName & ";UID=" & 
  5. UserName & ";PWD=" & UserPassword
  6. ad.Open
  7.  
Opening a Table/Query for Viewing
Now we have the database connection established it is time to look at the data. The following example show how to open a table/query and move through it.

DAO Example

Expand|Select|Wrap|Line Numbers
  1. Dim rs as recordset
  2.  
  3. set rs=db.openrecordset({tablename or SQL})
  4. do while not rs.eof
  5.   'Put the code here for what to do with the information.
  6.   'The field information can be access by the field name
  7.   intID=rs!IDField
  8.   'Or by the order number it is in the list (starting at 0)
  9.   intString=rs.Field(1)
  10.   rs.movenext
  11. loop
  12.  
ADO example

Expand|Select|Wrap|Line Numbers
  1. dim ar as ADODB.recordset
  2.  
  3. set ar=new adodb.recordset
  4. ar.open {SQL Statement}
  5. do while not ar.EOF
  6.   'Put the code here for what to do with the information.
  7.   'The field information can be access by the field name
  8.   intID=ar!IDField
  9.   'Or by the order number it is in the list (starting at 0)
  10.   intString=ar.Field(1).value
  11.   ar.movenext
  12. loop
  13.  
Change a Record
To edit/add/delete a record we can do it either using SQL or directly. Both DAO and ADO use the execute method for doing updates by SQL.

DAO

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim rs as recordset
  3.  
  4. set rs=db.openrecordset({tablename or SQL})
  5. rs.execute "INSERT INTO tb(ID,Name) VALUES (10,Anne)"
  6.  
ADO

Expand|Select|Wrap|Line Numbers
  1. dim ar as ADODB.recordset
  2.  
  3. set ar=new adodb.recordset
  4. ar.open {SQL Statement}
  5. ar.execute "INSERT INTO tb(ID,Name) VALUES (10,Anne)"
  6.  
These examples add a new record to the database directly.

DAO - Add New Record

Expand|Select|Wrap|Line Numbers
  1. Dim rs as recordset
  2.  
  3. set rs=db.openrecordset({tablename or SQL})
  4. rs.addnew
  5. rs!ID=intID
  6. rs!Name=strName
  7. rs.update
  8.  
ADO - Add new record

Expand|Select|Wrap|Line Numbers
  1. dim ar as ADODB.recordset
  2.  
  3. set ar=new adodb.recordset
  4. ar.open {SQL Statement}
  5. ar.addnew
  6. ar!ID=intID
  7. ar!Name=strName
  8. ar.update
  9.  
These examples show how to edit a record directly, after the recordset is open it checks that there is a record meeting the criteria in the open SQL. If not it creates one.

DAO - Edit record

Expand|Select|Wrap|Line Numbers
  1. Dim rs as recordset
  2.  
  3. set rs=db.openrecordset("SELECT * FROM Tb WHERE tdID=10")
  4. if rs.eof then
  5.   rs.addnew
  6. else
  7.   rs.edit
  8. end if
  9. rs!ID=intID
  10. rs!Name=strName
  11. rs.update
  12.  
ADO - Edit Record

Expand|Select|Wrap|Line Numbers
  1. dim ar as ADODB.recordset
  2.  
  3. set ar=new adodb.recordset
  4. ar.open "SELECT * FROM Tb WHERE tdID=10"
  5. if ar.eof then
  6.   ar.addnew
  7. else
  8.   ar.edit
  9. end if
  10. ar!ID=intID
  11. ar!Name=strName
  12. ar.update
  13.  
These examples show how to delete a record directly, after the recordset is open it checks that there is a record meeting the criteria in the open SQL. If not it does not do a delete.

DAO - Delete Record

Expand|Select|Wrap|Line Numbers
  1. Dim rs as recordset
  2.  
  3. set rs=db.openrecordset("SELECT * FROM Tb WHERE tdID=10")
  4. if not rs.eof then
  5.   rs.delete
  6. end if
  7.  
ADO - Delete Record

Expand|Select|Wrap|Line Numbers
  1. Dim ar as ADODB.recordset
  2.  
  3. set ar=new adodb.recordset
  4. ar.open "SELECT * FROM Tb WHERE tdID=10"
  5. if not ar.eof then
  6.   ar.delete
  7. end if
  8.  
Note: If you open an object when you have finished with it, close it and set it to nothing. For example...

Expand|Select|Wrap|Line Numbers
  1.     rs.close
  2.     set rs=nothing
  3.  
This is good programming practice and clears the memory.
Jul 6 '06 #2

P: 19
Thank u very much yaa sashi
Jul 7 '06 #3

P: 28
Hello Sashi Sir,


Please explain the above code (ADO Examples)using dataset instead of using recordset.

iam new to vb 6.0

workspace,
.index
.seek
.addnew
.edit
.update
.movefirst
.movelast
.moveprevious


how can i use the above using dataset.pls help me with examples.
Jul 22 '08 #4

debasisdas
Expert 5K+
P: 8,127
you need to use the corresponding methods of .NET.

check in Howto section for related discussions.
Jul 23 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.