Solved

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

Posted on 2008-10-31
10
4,519 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 22851657
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
ID: 22851718
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
ID: 22852634
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:a4sha24
ID: 22885108
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
ID: 22885713

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
 

Author Comment

by:a4sha24
ID: 22885974
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
ID: 22887055
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
ID: 22887445
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
ID: 22887549
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
ID: 31512092
thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

749 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