Solved

Insert and delete records in to oracle table using visual basic 6

Posted on 2008-10-31
10
4,175 Views
Last Modified: 2012-08-14
Hi I have managed to connect to oracle and retrieve records using the following coding.

Private Sub cmdPayments_Click()
  If cmboClient.ListIndex = -1 Then
  MsgBox ("Please select client name to continue")
  Else
Dim oconn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM PAYMENTS WHERE CPNY_NAME = " & "'" & cmboClient.Text & "' ORDER BY PAYMENT_DATE "
Set oconn = New ADODB.Connection
oconn.Open "Provider=msdaora;Data Source=xxx;User Id=xxx;Password=xxxx;"
rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.Open strSQL, oconn, , , adCmdText
Set DataGrid1.DataSource = rs
DataGrid1.Enabled = True
End If
End Sub

I am stuck and am unable to insert records or delete records. Can you help provide coding for this please.

I want to insert/delete in to table PAYMENTS
The fields are CPNY_NAME (TEXT), DESCRIPTION (TEXT), PAYMENT_DATE (DATE), DEBIT (NUMBER), CREDIT (NUMBER)

I have 5 objects which I want to type in the data and then insert from them.
- cmboClient (combo box)
- txtDescription (text box)
- txtPaymentDate (textbox)
- txtDebit (textbox)
- txtCredit (text box)

I have a form to insert and another form to delete with same ojects as above. However in delete form shold show the records which you can go through and delete.  

Please advise if you need more info. Thanks alot experts.
0
Comment
Question by:a4sha24
  • 5
  • 5
10 Comments
 
LVL 16

Expert Comment

by:HooKooDooKu
Comment Utility
Build an INSERT or DELETE query and use the following snippet to execute the query.

The query should get executed and the variable 'Count' should indicate the number of records that were modified.
Dim Count as Long

ocoon.Execute(strSQL, Count, adCmdText)

Open in new window

0
 

Author Comment

by:a4sha24
Comment Utility
Hi HooKooDooku, Im a beginner and am not sure how to make the above snippet work. Any chance you could provide a example of insert by using the info I have provided above. Many thanks
0
 
LVL 16

Expert Comment

by:HooKooDooKu
Comment Utility
When it comes to interacting with a database, there are two primary ways to do it.  You can either load and work with a record set, or you can execute queries.

Your snippet shows how to select a set of records from a database and connect those records to a record set.  The magic line that accomplished that above was...
rs.Open strSQL, oconn, , , adCmdText
Once you have a record set, you can interact with it directly if you want with commands like...
rs.MoveFirst (makes the 1st record the "current" record)
rs.MoveNext (makes the NEXT record the "current" record)
rs.EOF (used to test if the last rs.MoveNext has moved past the last record)
rs.Fields(n).Value = x (update the nth Field value to a value of x)
rs![FieldName] = x (same as previous line, but field accessed by name)
rs.Update (commit a set value changes, must be done before you move to next record).
rs.AddNew (inserts a new blank record where you must set all the field values).
rs.Delete (delete the record)

The other way to interact with a database is to execute SQL commands.  The previous snippet I gave you shows how to execute a SQL command.  An example of that would be...
SQL$ = "UPDATE PAYMENTS SET DESCRIPTION = 'Sample Description' WHERE CPNY_NAME = 'My Company Name'

To use the second method, you have to be familier with SQL syntax.

To use the 1st method, I've provided a VERY basic snippet below.  The code selects all records with a payment date of 12/31/2008 and modifies the Credit field.  Keep in mind that all those rs properties that you set (like cursor type) all can affect how things will behave, so you'll have to contiue doing so research on what all those options do.
'Assuming oconn is already open and rs parameters have been set

strSQL = "SELECT * FROM PAYMENTS WHERE PAYMENT_DATE = '12/31/2008'""

rs.Open strSQL, oconn, , , adCmdText
 

do until rs.EOF

  rs![Credit] = rs![Credit] + 1

  rs.Update

  rs.MoveNext

loop

Open in new window

0
 

Author Comment

by:a4sha24
Comment Utility
Hi the above did not really help. Can you give me coding example using the second method please. thanks
0
 
LVL 16

Accepted Solution

by:
HooKooDooKu earned 500 total points
Comment Utility

Dim Count as Long

Dim strSQL as String

strSQL = "INSERT INTO PAYMENTS " & _

         "(CPNY_NAME,DESCRIPTION,PAYMENT_DATE,DEBIT,CREDIT)" & _

         " VALUES " & _

         "('ABC','Test Comp','31-DEC-2006',1,0)"

ocoon.Execute(strSQL, Count, adCmdText)

strSQL = "DELETE FROM PAYMENTS WHERE CPNY_NAME = 'XYZ'"

ocoon.Execute(strSQL, Count, adCmdText)

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:a4sha24
Comment Utility
Im trying to use the following but is not working. I replaced the values to be taken from text boxes which i have assigned. Can you point out why isnt the below working. Cheers

strSQL = "INSERT INTO PAYMENTS " & _
         "(CPNY_NAME,DESCRIPTION,PAYMENT_DATE,DEBIT,CREDIT) VALUES ('" & ClientName & "','" & PaymentDate & "','" & Description & "','" & Debit & "','" & Credit & "');"
       
oconn.Execute strSQL, Count, adCmdText
0
 
LVL 16

Expert Comment

by:HooKooDooKu
Comment Utility
I believe I see two mistakes, and I'm guessing about a third.
You seemed to have indicated the fields Debit and Credit are numeric.  But the code line above is attempting to enclose that numeric in single quotes.  So you are attempting to place a string into a numeric field (so drop the single ticks for Debit and Credit field values).
The next POTENTIAL problem is date formating.  With the oracle database interface objects I use, the database requires dates to be formated 'dd-mmm-yyyy', such as '31-Dec-2008'.  The database does not understand a date (which does need single quotes) formated '12/31/2008' without running a special date format command after every time you open the database.
0
 

Author Comment

by:a4sha24
Comment Utility
Hi, thanks for your guidence. Is there any chance you could tell me how to change the date format to entered as 'dd-mmm-yyyy'

,Many thanks, appreciated.
0
 
LVL 16

Expert Comment

by:HooKooDooKu
Comment Utility
My notes indicate the SQL query to execute to change the format to 'mm/dd/yyyy hh:mm:ss am/pm' is "ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/RRRR HH:MI:SS AM'"
0
 

Author Closing Comment

by:a4sha24
Comment Utility
thanks
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now