With ADO, the RS.AddNew and RS.Update are still functional. Therefore, if you wish, you may add new records with something similar to the following:
Private Sub cmdAdd_Click()ClearFieldsKirbyOrders
txtOrderID.SetFocus
rs.AddNew
End Sub
Private Sub cmdUpdate_Click()mdkcmd.ActiveConnection = conn
ReadFieldsKirbyOrders
rs.Update
End Sub
Public Sub ClearFields()txtCustomerID.Text = ""
txtCompanyName.Text = ""
txtLastName.Text = ""
txtFirstName.Text = ""
txtAddress.Text = ""
txtCity.Text = ""
txtState.Text = ""
txtZipCode.Text = ""
txtPhone.Text = ""
txtFax.Text = ""
End Sub
Public Sub ReadFieldsKirbyOrders()rs.Fields ("OrderID") & "" = txtOrderID.Text
rs.Fields ("CustomerID") & "" = txtCustomerID.Text
rs.Fields ("OrderDate") & "" = txtOrderDate.Text
rs.Fields ("ShipDate") & "" = txtShipDate.Text
rs.Fields ("PaidDate") & "" = txtPaidDate.Text
rs.Fields ("Status") & "" = txtStatus.Text
End Sub
However, for the purposes of this application, rather than using the RS.AddNew, reading the fields and subsequently issuing an RS.Update (Above), we will use Oracle Stored Procedures (See Adding New Records under SQL PLUS: Creating Oracle Stored Procedures)
to centralize processing at the Server level. This will keep network traffic to a minimum.
In the Click Event of the Add button, I simply clear out the fields on the form and Set Focus to the first input box:
Private Sub cmdAdd_Click()ClearFieldsKirbyOrders
txtOrderID.SetFocus
End Sub
When the data for the record to be added has been input into the text boxes and the user is ready to actually add the record, the Stored Procedure will be executed in the Click Event of the Update button using the Text Property of the Command Object.
Before we actually move on to the code for running the AddNew Stored Procedures, there are three items which need to be addressed first: The handling of Null Values, The Oracle Date Format and Apostrophes or Single Quotes. I will demonstrate two of the Stored Procedures in my application, the AddOrder and AddCustomer. First, we will look at handling Nulls; and secondly, we will look at the Oracle Date Format. Both of these issues will be demonstrated in the AddOrder stored procedure. Third and lastly, we will look at the Apostrophe (or Single Quote) issue which will be demonstrated with the AddCustomer stored procedure.
THE HANDLING OF NULL VALUES:
With other databases, it is possible to supply an Empty String ("") as a blank or Null value to be inserted into a table. However, Oracle requires that the actual word 'NULL' be supplied in order to accomplish this.
It is likely that the Ship Date and Paid Date fields in the Orders Table will contain Null values when an Order is initially entered. Therefore, it is necessary to handle Null values appropriately.
Visual Basic and Oracle do not recognize the same formats. VB will recognize a String data type Within Double Quotes where Oracle prefers that a String be within Single Quotes. Also, where Oracle will recognize a Text String of 'Null' within Single Quotes, VB will not supply the Null unless it is within Double Quotes. Subsequently the optimal manner in which to handle Nulls between VB and Oracle is via Variables.
Therefore, I have declared variables for these two items (Ship Date and Paid Date) in VB. When executing the Oracle Stored Procedure within VB to add a new Order, I will first check the value of the Ship Date and Paid Date text boxes and supply the appropriate item to Oracle. If the text box is empty, I will set its variable equal to the VB String "Null"; otherwise, I will convert the text into the appropriate Oracle Date Format. We'll cover the Date Format Next
ORACLE'S DATE FORMAT:
This particular table (Orders table) contains three Date Fields (Order Date, Ship Date and Paid Date).
In order to insert this data type into the Oracle Table, it is necessary to format the text appropriately.
Oracle's Default Date Format is: DD-MMM(First 3 letters of the Month)-YY
(For Example: 07-JUL-98).
Therefore, using an Oracle-Specific Function Called To_Date, I have taken the String from the Text Boxes and formatted it as MM/DD/YY.
NOTE: This now requires that the Text typed into these Text Boxes be typed in MM/DD/YY format. If entered any other way, an error will occur.
One Last NOTE Prior to reviewing the code for AddOrder:
Additionally, I have incorporated code to prevent the user from choosing an incorrect order Status.
Initially, if they have not chosen a status, they are directed to do so.
Subsequently, a Ship Date is required in order for the Filled status to be chosen. Conversely, the Backlogged status must be chosen if a Ship Date is not entered.
Notice that an Exit Sub must be called after checking these Status items.
These items are all handled prior to the execution of the Stored Procedure.
The code below, once data for the new Order has been input into the Text Boxes, executes the AddOrder stored procedure in the Click Event of the Update Button. The code first confirms the Active Connection. It then checks the chosen Status, Null Values and Date Formats which are handled appropriately as described above. Next, the Command Text is set equal to the Stored Procedure which is provided with the appropriate parameters with data from the Text Boxes. The Command is then Executed; and subsequently, a Requery and a FillFields is performed to enable the user to see the updated Recordset immediately:
NOTE: For purposes of clarity, I have used Line Continuations. This is not necessary, it simply makes the code much more readable.
Private Sub cmdUpdate_Click()mdkcmd.ActiveConnection = conn
If txtStatus.Text = "" ThenMsgBox "Please Enter the Status of the Order"
txtStatus.SetFocus
Exit Sub
End If
If txtShipDate.Text = "" And optFilled.Value = True Then
MsgBox "Please Provide a Ship Date for Filled Orders " & vbCrLf & _
" Otherwise, select the Backlogged Option"
txtStatus.SetFocus
Exit Sub
ElseIf txtShipDate.Text <> "" And optBacklogged.Value = True Then
MsgBox "If Order has been Shipped and Ship Date is completed " & vbCrLf & _
" Please Select Filled as the Status"
txtStatus.SetFocus
Exit Sub
End If
Dim strShipDate As String
Dim strPaidDate As String
If txtShipDate.Text = "" Then
strShipDate = "Null"
Else
strShipDate = "To_Date('" & txtShipDate.Text & "','MM/DD/YY')"
End If
If txtPaidDate.Text = "" Then
strPaidDate = "Null"
Else
strPaidDate = "To_Date('" & txtOrderDate.Text & "','MM/DD/YY')"
End If
mdkcmd.CommandText = "AddOrder(" & txtOrderID.Text & ", " & _
txtCustomerID.Text & ", " & _
"To_Date('" & txtOrderDate.Text & "','MM/DD/YY'), " & _
strShipDate & ", " & _
strPaidDate & ", " & _
"'" & txtStatus.Text & "')"
mdkcmd.Execute
rs.Requery
FillFieldsKirbyOrders
End Sub
HANDLING APOSTROPHEs and/or SINGLE QUOTES:
It is likely that some fields will contain Apostrophes or Single Quotes. Therefore, it is necessary to check for this special character in the Visual Basic front end prior to attempting to insert into the Oracle Table. As previously mentioned, Oracle prefers that Strings be surrounded with Single Quotes (''), whereas Visual Basic prefers Double Quotes (""). Therefore, I will check for Single Quotes and substitute a Chr(39) within VB.
The code below, once data for the new Customer has been input into the Text Boxes, executes the AddCustomer stored procedure in the Click Event of the Update Button. The code first confirms the Active Connection. It then checks for Null Values and Apostrophes (Single Quotes) which are handled appropriately as described above. Next, the Command Text is set equal to the Stored Procedure which is provided with the appropriate parameters with data from the Text Boxes. The Command is then Executed; and subsequently, a Requery and a FillFields is performed to enable the user to see the updated Recordset immediately:
NOTE: For purposes of clarity, I have used Line Continuations. This is not necessary, it simply makes the code much more readable.
mdkcmd.ActiveConnection = conn
Dim strCompanyName As String
Dim strAddress As String
Dim strCity As String
Dim strState As String
Dim strZipCode As String
Dim strPhone As String
Dim strFax As String
If txtCompanyName.Text = "" Then
strCompanyName = "Null"
Else: strCompanyName = "'" & txtCompanyName.Text & "'"
End If
If txtAddress.Text = "" Then
strAddress = "Null"
Else: strAddress = "'" & txtAddress.Text & "'"
End If
If txtCity.Text = "" Then
strCity = "Null"
Else: strCity = "'" & txtCity.Text & "'"
End If
If txtState.Text = "" Then
strState = "Null"
Else: strState = "'" & txtState.Text & "'"
End If
If txtZipCode.Text = "" Then
strZipCode = "Null"
Else: strZipCode = "'" & txtZipCode.Text & "'"
End If
If txtPhone.Text = "" Then
strPhone = "Null"
Else: strPhone = "'" & txtPhone.Text & "'"
End If
If txtFax.Text = "" Then
strFax = "Null"
Else: strFax = "'" & txtFax.Text & "'"
End If
Dim I As Integer
Dim Str As String
Dim CoNameStr As String
Dim LNameStr As String
Dim FNameStr As String
Dim AddrStr As String
For I = 1 To Len(txtCompanyName.Text)
Str = Mid(txtCompanyName.Text, I, 1)
If Str = Chr(39) Then
CoNameStr = CoNameStr & Str & Chr(39)
Else
CoNameStr = CoNameStr & Str
End If
Next I
For I = 1 To Len(txtLastName.Text)
Str = Mid(txtLastName.Text, I, 1)
If Str = Chr(39) Then
LNameStr = LNameStr & Str & Chr(39)
Else
LNameStr = LNameStr & Str
End If
Next I
For I = 1 To Len(txtFirstName.Text)
Str = Mid(txtFirstName.Text, I, 1)
If Str = Chr(39) Then
FNameStr = FNameStr & Str & Chr(39)
Else
FNameStr = FNameStr & Str
End If
Next I
For I = 1 To Len(txtFirstName.Text)
Str = Mid(txtFirstName.Text, I, 1)
If Str = Chr(39) Then
AddrStr = AddrStr & Str & Chr(39)
Else
AddrStr = AddrStr & Str
End If
Next I
mdkcmd.CommandText = "AddCustomer(" & txtCustomerID.Text & ", '" & CoNameStr & "', " &_
"'" & LNameStr & "', " & _
"'" & FNameStr & "', " & _
"'" & AddrStr & "', " & _
strCity & ", " & _
strState & ", " & _
strZipCode & ", " & _
strPhone & ", " & _
strFax & ")"
mdkcmd.Execute
rs.Requery
FillFields
End Sub