Make your own free website on Tripod.com

ADDING NEW RECORDS


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.


RUNNING THE ORACLE STORED PROCEDURE IN THE VB APPLICATION

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 = "" Then

MsgBox "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


RETURN TO HOME PAGE