Hi Kohliamit,
I wrote out this whole chunk of code to send n number of rows from a MSFlexgrid control to oracle as one transaction, then I noticed you want to send your data to a Stored Procedure! So what the heck, I'll include it, anyway. I will call it methiod 1. I follow it up with Method 2 which makes use of a generic procedure that takes all your input values in a parameterized array. (Just a disclaimer - I think I gleaned this procedure from this forum a few years ago! Used it without problems ever since). You will have to call this for each row in your MSFlexgrid with the Flexgrid columns as the values of your Param Array. Method 2 would be more of an answer to your question. Hope this helps.
methiod 1 (Sending the contents of a MSFlexgrid to Oracle using a single transaction):
SQLText = "BEGIN " & vbCrLf
For i = 0 to MyFlex.rows - 1
SQLText = SQLText & "INSERT INTO Employee ( " _
& "FirstName, LastName, Salary) VALUES ('"_
& MyFlex.TextMatrix(i,1) & "', '" _
& MyFlex.TextMatrix(i,2) & "', " _
& MyFlex.TextMatrix(i,3) & "); " _
Next i
SQLText = SQLText & vbCrLf & "COMMIT; END;" & vbCrLf
methiod 2 (Sending the contents of a MSFlexgrid to an Oracle Stored procedure):
Function Ora_RunSP(ByVal strSP As String, ParamArray params() _
As Variant) As Variant
On Error GoTo errorHandler
' Create the ADO objects
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
' Init the ADO objects & the stored proc parameters
cmd.ActiveConnection = MyConn
cmd.CommandText = strSP
cmd.CommandType = adCmdStoredProc
'collectParams cmd, params
For i = 0 To UBound(params) - 1
If i = UBound(params) - 1 Then
cmd.Parameters.Append cmd.CreateParameter("@retval", _
adInteger, adParamOutput, 4)
Else
If TypeName(params(i)) = "String" Then
cmd.Parameters.Append cmd.CreateParameter(params(i), _
adVarChar, adParamInput, 4)
Else
cmd.Parameters.Append cmd.CreateParameter(params(i), _
adInteger, adParamInput, 4)
End If
End If
Next i
' Assume the last parameter is outgoing and named @retval
cmd.Parameters.Append cmd.CreateParameter("@retval", adInteger, _
adParamOutput, 4)
' Execute without a resulting recordset and pull out the "return
' value" parameter
cmd.Execute , , ADODB.adExecuteNoRecords
DoCmd_RunSP = cmd.Parameters("@retval").Value
' Clean up and return
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
Exit Function
errorHandler:
If MyConn.Errors.Count > 0 Then
Err.Raise MyConn.Errors(0).Number, , MyConn.Errors(0).Description
'On Error Resume Next0
Else
Err.Raise Err.Number, , Err.Description
End If
Set cmd = Nothing
End Function
'The following piece calls the above procedure.
'Notice that we assume that if the Stored procedure returns
'a non-zero value, it encountered an error. I handle this in
'a very crude manner below. You can be much more elegant to pinpoint
'which record actually returned the error.
Dim HasError as integer
HasError = 0
For i = 0 to MyFlex.rows - 1
HasError = Haserror + CInt(Ora_RunSP(SP_GetEmpSalary, MyFlex.TextMatrix(i,1), MyFlex.TextMatrix(i,2), MyFlex.TextMatrix(i,3)))
Next i
if HasError > 0 then
Msgbox "There was a problem with at least one of the records!"
end if
|
