Crystal Report-Stored Procedure Parameter
i m using crystal report 7.0. i am using a stored procedure in the report and i have to pass parameters from vb. the problem i am facing is that whenever i am calling the report from vb, 'Enter Parameter value' dialog box appearing. how can i avoid this dialog box.
also how can i pass date value as a stored procedure parameter?
any one can help me.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Stored Procedure Parameter- Crystal Report
Hello,
I have created a report in segate crystal report on a stored procedure in SQL7
And I want to change the parameters in runtime without prompting to the user through vb codes. My crystal report object and the parameter field is as shown bellow
m_crwReport.ParameterFields(1) ,and how I can change the value to this parameters
I got syntax from crystal report developers help like
CrystalReport1.ParameterFields(0) = parameter1;red;TRUE
But I don’t know how to give the right hand side ( I mean after the = parameter1;red;true) part of this; my values are Parameter name (@FromDate, “20/03/2003”,True) how can I set this value Please Do any one help me in this regard.
Thanking you
Shaju
Crystal Reports - Error 20533 - Invalid Stored Procedure Parameter
I'm using Crystal Reports (ver 4) and VB6
Using the crystal custom control(CCC) I pass parameters to a stored procedure and then print the report. This works fine until ...
I send 5 parameters to stored procedure A
then I send say 3 parameters to stored procedure B.
As soon as I send less parameters as with my previous call (to a different stored procedure) I get the error message:
Runtime error 20533
-Invalid Stored Procedure Parameter-
So it seems to me that VB is trying to send 5 parameters to a 3 parameter stored procedure.
Is there a reset command for the CCC?
So here's 2 of the procedures I use.....
Private Sub Print_Letter_Occupancy(Par1, Par2, Par3, Par4, Par5)
With Crystal 'This is the crystalReport control
.ReportFileName = ""
.ReportFileName = App.Path & "Letter_Occupancy.rpt"
.StoredProcParam(0) = Par1
.StoredProcParam(1) = Par2
.StoredProcParam(2) = Par3
.StoredProcParam(3) = Par4
.StoredProcParam(4) = Par5
.Connect = "DSN = database1;UID = 12;PWD = 1234"
.Destination = crptToWindow
.Action = 1
End With
Crystal.ReportFileName = ""
End Sub
Private Sub Print_Letter_TowerCrane(Par1, Par2, Par3)
With Crystal
.ReportFileName = ""
.ReportFileName = App.Path & "Letter_towerCrane.rpt"
.StoredProcParam(0) = Par1
.StoredProcParam(1) = Par2
.StoredProcParam(2) = Par3
.StoredProcParam(3) = Par4
.StoredProcParam(4) = Par5
.Connect = "DSN = database1;UID = 12;PWD = 1234"
.Destination = crptToWindow
.Action = 1
End With
Crystal.ReportFileName = ""
End Sub
How To Pass An Array To An IN Parameter Of Stored Procedure(of Oracle) When This Stored Procedure Is
first of all thanks for all the answers you guys are sending.they are really helping me in my project.here comes another problem.
i have this stored procedure in oracle
CREATE OR REPLACE PROCEDURE VARRAY_TEST
(SUBJECT_IN IN SUBJECT_TYPE)
IS
I NUMBER ;
BEGIN
FOR I IN SUBJECT_IN.FIRST..SUBJECT_IN.LAST LOOP
INSERT INTO empl VALUES(SUBJECT_IN(I));
END LOOP;
END;
where SUBJECT_TYPE is a varray declared using the following statement:
CREATE OR REPLACE TYPE SUBJECT_TYPE AS VARRAY(10) OF VARCHAR2(10);
and empl is a table name that exists in the database.
i want to call this stored procedure from VB ,passing the values to IN Parameter SUBJECT_IN.
how can i do that.
i tried this code :
Private Sub Command4_Click()
Dim passarray(9) As String
Dim cmdarr As New ADODB.Command
Dim pararr As New ADODB.Parameter
Dim i As Integer
For i = 0 To 9
passarray(i) = "maths"
Next i
Set cmdarr = New ADODB.Command
cmdarr.ActiveConnection = goConn
cmdarr.CommandText = "varray_test"
cmdarr.CommandType = adCmdText
Set pararr = New ADODB.Parameter
pararr.Name = "passarray"
pararr.Direction = adParamInput
pararr.Type = adArray
pararr.Size = 10
pararr.Value = passarray
cmdarr.Parameters.Append pararr
cmdarr.Execute
End Sub
but it doesnt work.gives an error message which says " Arguments are of the wrong type,are out of acceptable range,or are in conflict with one another."
please also try to a sample code.
thanks
regards
Sumit Gulati
Store Procedure Parameter In Crystal Report
how can i pass store procedure parameter's in crystal report from VB ?...
i put store procedure in detail section of CR and i need to pass record result from CR to store procedure.
example.
store procedure :
create proc spCredit
@DocumentCode nvarchar(14),
@Documentno nvarchar(14),
@itemcode nvarchar(14)
as
select sum(qty) from tablename where doccode =@documentcode and docno=@documentno and itemcode =@itemcode group by doccode,docnum,itemcode
in crystal report, detail
document document no product total
doccode docno itemcode spcredit
doc-01 001 ABC 10
can pass parameter of SP for each record resulted by CR ??
in this case passing 'doc-01','001','ABC' to the SP from VB.
Crystal Report 7 And SQL 7 Stored Procedure
I have been trying for more than 2 days now simply trying to display a report with sql 7 stored procedure parameters. The fact is, the report is displayed correctly when I call it without the parameter values. But once i supply values to the stored procedures, nothing happens, not even an error message is displayed.
The code I use is as follows:
dim m_Report as String
m_Report = "
eportsemployer_last_actionProc.rpt"
'Connect to the server
CrystalReport1.LogonInfo(0) = CrystalReport1.LogOnServer("pdsodbc.dll", "Social", "Social", "sa", "")
CrystalReport1.DiscardSavedData = True
CrystalReport1.StoredProcParam(0) = "@mDate1; " & mskFromDate & ";True"
CrystalReport1.StoredProcParam(1) = "@mDate2; " & mskToDate & ";True"
CrystalReport1.ParameterFields(0) = "@mStatus; " & m_Status & ";True"
CrystalReport1.WindowState = crptMaximized
CrystalReport1.ReportFileName = App.Path & m_Report
CrystalReport1.PrintReport
Your kind help is rugently needed.
Thanks if advance.
Passing Stored Procedure To Crystal Report In VB
Hello all
How do I pass a stored procedure to crystal report in VB correctly? I have a
stored procedure named "spTesitng" and I want to execute it and pass the
result to crystal report at run time. But I can't get it to work ...
Following is part of my code:
.....
Set rptReport = Appl.NewReport
Set adoCommand = New ADODB.Command
Set adoCommand.ActiveConnection = adoConnection
adoCommand.CommandText = "[spTesting]"
adoCommand.CommandType = adCmdStoredProc
adoCommand.Execute
rptReport.Database.AddADOCommand adoConnection, adoCommand
...
Where do I need to make changes?
Thanks for all advise
KUEK Joe Sern
Crystal Report - Stored Procedure Question - Thanks
I used "select a.f1, b.f2, c.f3, from test a, test b, test 3" in stored procedure to list f1, f2, f3 fields in crystal report. But I got problem. Every above field has (forced to have) the same number of records (there is a duplication for some of the fields). What I want to do is to show some fields from table A in Section A and some fields from table B in Section B in crystal report. They are separate data and have different number of records. The point is I have to use stored procedure. Is there any method besides the Select statement I used above that I can use to get what I want to get (get the fields listed in crystal report)? Please help me out. thanks!
Opening Crystal Report ( Source Store Procedure With Parameter From SQL)
i have created a Crystal report that it source its a Store Procedure from SQL, this Store procedure has parameters.
Whe i use the preview window on Crystal it prompts on crystal for the parameters and the reports comes out fine.
The problem is when i pass the parameters throw VB the report cames with no Data.
what could be the problem?
lovalles
Crystal Report + Stored Procedure Returns No Data
Hello,
My manager is all over me because of this issue ... please can some help me
I am working on an VB6 application which use Crystal Report 8.0.1.0 for reports and the backend database is SQL Server 2005.
My report uses stored procedure to get the records from database. The Stored Procedure access multiple database to get the records.
I am having trouble with few Crystal Reports which I am calling from VB6. The report is the cumulative report and the issue is that the crystal Report is not returning any data.
I am passing 2 parameter to the report:
1. DATE passed as varchar
2. VARCHAR
Reference in Code
1. Crystal Viewer Control (cviewer.oca)
2. Crystal ActiveX Report Viewer Library 11.0
3. Crystal Reports ActiveX Designer Run Time Library 11.0
Components used in Code
1. Crystal ActiveX Report Viewer Library 11.0 (crviewer.dll)
2. Crystal Report Control (Crystl32.OCX)
Here is the code I am using
--------------------------
Option Explicit
Dim crApp As New CRAXDRT.Application
Dim crRep As CRAXDRT.Report
Dim crParamDefs As CRAXDRT.ParameterFieldDefinitions
Dim crParamDef As CRAXDRT.ParameterFieldDefinition
Dim crDBTab As CRAXDRT.DatabaseTable
Dim HasExportOptionShown As Boolean
Dim HasReportPrinted As Boolean
Dim strDefaultPrinter As String, defaultindex As Byte
Dim ValueName As String
Dim rs As New ADODB.Recordset
Dim rsItem As New ADODB.Recordset
Dim DriverName, PrinterName, Port As String
Dim DontPrint As Boolean
Dim today As Date
Private Sub Form_Load()
today = DateWithCentury(Date)
End Sub
Private Sub Command1_Click()
'''''rpt2 is Crystal Report Control on the form (Crystl32.OCX)
rpt2.PrintFileType = crptExcel50
rpt2.Destination = crptToFile
rpt2.ProgressDialog = False
rpt2.WindowState = crptMinimized
rpt2.DiscardSavedData = True
Dim filesys,demofile
Dim foldername As String
Dim fso As New FileSystemObject
'variables for date range and work formula
Dim WorkFormula, mBeginDate, mEndDate
mBeginDate = Format(CStr(Month(today)) + "/" + "1/" + CStr(Year(today)), "YYYY/MM/DD")
If CDate(today) = CDate("1/30/08") Or CDate(today) = CDate("1/31/08") Then
mBeginDate = "12/30/2007"
End If
mBeginDate = Format(mBeginDate, "YYYY/MM/DD")
mEndDate = Format(today, "YYYY/MM/DD")
WorkFormula = "{sometable.somedatefield}>=#" & CDate(mBeginDate) & " 00:00:00# AND "
WorkFormula = WorkFormula & " {sometable.somedatefield}<=#" & CDate(mEndDate) & " 23:59:59#"
Set filesys = CreateObject("Scripting.FileSystemObject")
foldername = CStr(Format(today, "mm_dd_YY"))
If fso.FolderExists("C:FOLDER" & foldername & "") = False Then
fso.CreateFolder "C:FOLDER" & foldername & ""
End If
rpt2.ReportFileName = "C:FOLDERReportsReport.rpt"
rpt2.PrintFileName = "C:FOLDER" & foldername & "Report.xls"
rpt2.DiscardSavedData = True
rpt2.ReportTitle = "From: " & mBeginDate & " To: " & mEndDate
rpt2.StoredProcParam(0) = mBeginDate
rpt2.StoredProcParam(1) = "SOMEPARAM"
LoadReport rpt2.ReportFileName, rpt2.PrintFileName, 2
End Sub
Private Sub LoadReport(ByVal reportname As String, ByVal ExcelName As String, ByVal Method As Integer)
Dim crSections As CRAXDRT.Sections
Dim crSection As CRAXDRT.Section
Dim crRepObjs As CRAXDRT.ReportObjects
Dim crSubRepObj As CRAXDRT.SubreportObject
Dim crSubReport As CRAXDRT.Report
Dim j As Integer, k As Integer
Dim i As Integer
Dim ConnectionInfo As CRAXDRT.ConnectionProperties
Dim ServerName As String
ServerName = "MYDBSERVERNAME"
Set crRep = crApp.OpenReport(reportname, 0)
'If crRep.Database.Tables(1).DllName = "crdb_ado.dll" And InStr(reportname, "SOMEREPORT") <= 0 Then
' Set ConnectionInfo = crRep.Database.Tables(1).ConnectionProperties
' crRep.Database.Tables(1).DllName = "crdb_odbc.dll"
' ConnectionInfo.DeleteAll
' ConnectionInfo.Add "DSN", ServerName
' ConnectionInfo.Add "User ID", "xxxxx"
' ConnectionInfo.Add "Password", "xxxxx"
'ElseIf crRep.Database.Tables(1).DllName = "crdb_odbc.dll" Then
'Else
crRep.Database.LogOnServer "p2ssql.dll", ServerName, "MYDBNAME", "xxxx", "xxxx"
For Each crDBTab In crRep.Database.Tables
crDBTab.SetLogOnInfo ServerName, "MYDBNAME", "xxxx", "xxxx"
Next
'End If
crRep.EnableParameterPrompting = False
With rpt2
If .DiscardSavedData = True Then
crRep.DiscardSavedData
End If
For i = 1 To crRep.ParameterFields.Count
crRep.ParameterFields.Item(i).ClearCurrentValueAndRange
crRep.ParameterFields.Item(i).AddCurrentValue .StoredProcParam(i - 1)
DoEvents
Next
Set crSections = crRep.Sections
For i = 1 To crSections.Count
Set crSection = crSections.Item(i)
Set crRepObjs = crSection.ReportObjects
For j = 1 To crRepObjs.Count
If crRepObjs.Item(j).Kind = crSubreportObject Then
Set crSubReport = crRep.OpenSubreport(crRepObjs.Item(j).SubreportName)
For k = 1 To crSubReport.ParameterFields.Count
crSubReport.ParameterFields(k).ClearCurrentValueAndRange
crSubReport.ParameterFields(k).AddCurrentValue .StoredProcParam(k - 1)
Next
End If
Next
Next
crRep.ReportTitle = .ReportTitle
rpt.ReportSource = crRep
crRep.FormulaSyntax = crCrystalSyntaxFormula '
If .SelectionFormula <> "" Then
If crRep.RecordSelectionFormula = "" Then
crRep.RecordSelectionFormula = .SelectionFormula
Else
crRep.RecordSelectionFormula = crRep.RecordSelectionFormula & " AND " & .SelectionFormula
End If
End If
End With
rpt2.Destination = Method
Select Case rpt2.Destination
Case 1 'Printer
Me.WindowState = vbMinimized
rpt.ViewReport
DoEvents
crRep.PrintOut False
Case 0 'Screen
Me.WindowState = vbMaximized
rpt.EnableDrillDown = rpt2.WindowAllowDrillDown
rpt.EnableExportButton = True
rpt.EnableGroupTree = False
rpt.EnableRefreshButton = True
rpt.EnablePrintButton = True
rpt.EnableSelectExpertButton = True
rpt.Zoom (100)
rpt.ViewReport
Case 2 'Excel
Me.WindowState = vbMinimized
rpt.ViewReport
DoEvents
HasExportOptionShown = False
Export (ExcelName)
End Select
End Sub
Private Sub Export(ByVal ExcelName As String)
If Not HasExportOptionShown Then
HasExportOptionShown = True
Else
Exit Sub
End If
With crRep.ExportOptions
.ExcelTabHasColumnHeadings = True
.FormatType = crEFTExcel50
.DestinationType = crEDTDiskFile
.DiskFileName = ExcelName
End With
crRep.Export False
End Sub
Private Sub PrintOut()
If Not HasReportPrinted Then
HasReportPrinted = True
Else
Exit Sub
End If
End Sub
--------------------------
Thank you everyone for answer in advance
Crystal Report + Stored Procedure Returns No Data
Hello,
My manager is all over me because of this issue ... please can some help me
I am working on an VB6 application which use Crystal Report 8.0.1.0 for reports and the backend database is SQL Server 2005.
My report uses stored procedure to get the records from database. The Stored Procedure access multiple database to get the records.
I am having trouble with few Crystal Reports which I am calling from VB6. The report is the cumulative report and the issue is that the crystal Report is not returning any data.
I am passing 2 parameter to the report:
1. DATE passed as varchar
2. VARCHAR
Reference in Code
1. Crystal Viewer Control (cviewer.oca)
2. Crystal ActiveX Report Viewer Library 11.0
3. Crystal Reports ActiveX Designer Run Time Library 11.0
Components used in Code
1. Crystal ActiveX Report Viewer Library 11.0 (crviewer.dll)
2. Crystal Report Control (Crystl32.OCX)
Here is the code I am using
--------------------------
Option Explicit
Dim crApp As New CRAXDRT.Application
Dim crRep As CRAXDRT.Report
Dim crParamDefs As CRAXDRT.ParameterFieldDefinitions
Dim crParamDef As CRAXDRT.ParameterFieldDefinition
Dim crDBTab As CRAXDRT.DatabaseTable
Dim HasExportOptionShown As Boolean
Dim HasReportPrinted As Boolean
Dim strDefaultPrinter As String, defaultindex As Byte
Dim ValueName As String
Dim rs As New ADODB.Recordset
Dim rsItem As New ADODB.Recordset
Dim DriverName, PrinterName, Port As String
Dim DontPrint As Boolean
Dim today As Date
Private Sub Form_Load()
today = DateWithCentury(Date)
End Sub
Private Sub Command1_Click()
'''''rpt2 is Crystal Report Control on the form (Crystl32.OCX)
rpt2.PrintFileType = crptExcel50
rpt2.Destination = crptToFile
rpt2.ProgressDialog = False
rpt2.WindowState = crptMinimized
rpt2.DiscardSavedData = True
Dim filesys,demofile
Dim foldername As String
Dim fso As New FileSystemObject
'variables for date range and work formula
Dim WorkFormula, mBeginDate, mEndDate
mBeginDate = Format(CStr(Month(today)) + "/" + "1/" + CStr(Year(today)), "YYYY/MM/DD")
If CDate(today) = CDate("1/30/08") Or CDate(today) = CDate("1/31/08") Then
mBeginDate = "12/30/2007"
End If
mBeginDate = Format(mBeginDate, "YYYY/MM/DD")
mEndDate = Format(today, "YYYY/MM/DD")
WorkFormula = "{sometable.somedatefield}>=#" & CDate(mBeginDate) & " 00:00:00# AND "
WorkFormula = WorkFormula & " {sometable.somedatefield}<=#" & CDate(mEndDate) & " 23:59:59#"
Set filesys = CreateObject("Scripting.FileSystemObject")
foldername = CStr(Format(today, "mm_dd_YY"))
If fso.FolderExists("C:FOLDER" & foldername & "") = False Then
fso.CreateFolder "C:FOLDER" & foldername & ""
End If
rpt2.ReportFileName = "C:FOLDERReportsReport.rpt"
rpt2.PrintFileName = "C:FOLDER" & foldername & "Report.xls"
rpt2.DiscardSavedData = True
rpt2.ReportTitle = "From: " & mBeginDate & " To: " & mEndDate
rpt2.StoredProcParam(0) = mBeginDate
rpt2.StoredProcParam(1) = "SOMEPARAM"
LoadReport rpt2.ReportFileName, rpt2.PrintFileName, 2
End Sub
Private Sub LoadReport(ByVal reportname As String, ByVal ExcelName As String, ByVal Method As Integer)
Dim crSections As CRAXDRT.Sections
Dim crSection As CRAXDRT.Section
Dim crRepObjs As CRAXDRT.ReportObjects
Dim crSubRepObj As CRAXDRT.SubreportObject
Dim crSubReport As CRAXDRT.Report
Dim j As Integer, k As Integer
Dim i As Integer
Dim ConnectionInfo As CRAXDRT.ConnectionProperties
Dim ServerName As String
ServerName = "MYDBSERVERNAME"
Set crRep = crApp.OpenReport(reportname, 0)
'If crRep.Database.Tables(1).DllName = "crdb_ado.dll" And InStr(reportname, "SOMEREPORT") <= 0 Then
' Set ConnectionInfo = crRep.Database.Tables(1).ConnectionProperties
' crRep.Database.Tables(1).DllName = "crdb_odbc.dll"
' ConnectionInfo.DeleteAll
' ConnectionInfo.Add "DSN", ServerName
' ConnectionInfo.Add "User ID", "xxxxx"
' ConnectionInfo.Add "Password", "xxxxx"
'ElseIf crRep.Database.Tables(1).DllName = "crdb_odbc.dll" Then
'Else
crRep.Database.LogOnServer "p2ssql.dll", ServerName, "MYDBNAME", "xxxx", "xxxx"
For Each crDBTab In crRep.Database.Tables
crDBTab.SetLogOnInfo ServerName, "MYDBNAME", "xxxx", "xxxx"
Next
'End If
crRep.EnableParameterPrompting = False
With rpt2
If .DiscardSavedData = True Then
crRep.DiscardSavedData
End If
For i = 1 To crRep.ParameterFields.Count
crRep.ParameterFields.Item(i).ClearCurrentValueAndRange
crRep.ParameterFields.Item(i).AddCurrentValue .StoredProcParam(i - 1)
DoEvents
Next
Set crSections = crRep.Sections
For i = 1 To crSections.Count
Set crSection = crSections.Item(i)
Set crRepObjs = crSection.ReportObjects
For j = 1 To crRepObjs.Count
If crRepObjs.Item(j).Kind = crSubreportObject Then
Set crSubReport = crRep.OpenSubreport(crRepObjs.Item(j).SubreportName)
For k = 1 To crSubReport.ParameterFields.Count
crSubReport.ParameterFields(k).ClearCurrentValueAndRange
crSubReport.ParameterFields(k).AddCurrentValue .StoredProcParam(k - 1)
Next
End If
Next
Next
crRep.ReportTitle = .ReportTitle
rpt.ReportSource = crRep
crRep.FormulaSyntax = crCrystalSyntaxFormula '
If .SelectionFormula <> "" Then
If crRep.RecordSelectionFormula = "" Then
crRep.RecordSelectionFormula = .SelectionFormula
Else
crRep.RecordSelectionFormula = crRep.RecordSelectionFormula & " AND " & .SelectionFormula
End If
End If
End With
rpt2.Destination = Method
Select Case rpt2.Destination
Case 1 'Printer
Me.WindowState = vbMinimized
rpt.ViewReport
DoEvents
crRep.PrintOut False
Case 0 'Screen
Me.WindowState = vbMaximized
rpt.EnableDrillDown = rpt2.WindowAllowDrillDown
rpt.EnableExportButton = True
rpt.EnableGroupTree = False
rpt.EnableRefreshButton = True
rpt.EnablePrintButton = True
rpt.EnableSelectExpertButton = True
rpt.Zoom (100)
rpt.ViewReport
Case 2 'Excel
Me.WindowState = vbMinimized
rpt.ViewReport
DoEvents
HasExportOptionShown = False
Export (ExcelName)
End Select
End Sub
Private Sub Export(ByVal ExcelName As String)
If Not HasExportOptionShown Then
HasExportOptionShown = True
Else
Exit Sub
End If
With crRep.ExportOptions
.ExcelTabHasColumnHeadings = True
.FormatType = crEFTExcel50
.DestinationType = crEDTDiskFile
.DiskFileName = ExcelName
End With
crRep.Export False
End Sub
Private Sub PrintOut()
If Not HasReportPrinted Then
HasReportPrinted = True
Else
Exit Sub
End If
End Sub
--------------------------
Thank you everyone for answer in advance
Passing Parameters To Stored Procedure To Crystal Report RPT At Runtime
Hi all,
Can any tell me how to pass parameters to a stored procedure to RPT atruntime in VB? I am using Crystal Reports 8.5 and VB6
I have the following code, the stored procedure "spGetApptLoadingDaily"has one parameter, it keep asking me to select a recordset everytime Irun it (prompt me for a table name / sql). How can I get rid of thedialog box ?
adocomm.CommandText = "[spGetApptLoadingDaily]"
adocomm.CommandType = adCmdStoredProc
adocomm.Execute , 100
Report.Database.AddADOCommand Conn, adocomm
Report.Sections("D").AddFieldObject "{ado.FullName}", 750, 5
CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Thanks and Regards,
KUEK Joe Sern
How To Make Stored Procedure Data Source For Crystal Report.Net
I'm writing a sales software and i want to display a report of sum of sales of each product within a time frame. I've created a stored procedure (parameter querry) in MSAccess 2003, which is able to do display exactly what i want. the problem is that i'm using VB.NET and Crystal Report for .NET and i want to use the stored procedure as my datasource for the report and also assign value to the parameters of the stored procedure from the form on which i call the report.
How do i first make a stored procedure (parameter querry) the data source for my report
How do i make it accept value from a form.
Parameter Problem In Stored Procedure
Whe I try to pass several parameters to a stored procedure, it constantly fails if an Integer value is empty.
The procedure is below.
THe problem is that when the user doesn't choose a value from a dropdown list, there is no TAG in the field txtAfdeling.Tag so that particular parameter will be empty. When passing empty String parameters, there's nothing going on but I guess empty Integer parameters will be sent as 'String-parameter' because the value will be ""
(two double quotes!).
Anybody ever dealed with this??
Thanks in advance...
Code:
ALTER PROCEDURE dbo.insertClientSP
/*
*/
-- parameters
(
@Zoeknaam varChar(50) = Null,
@Geslacht bit,
@Voorvoegsels varchar(10) = Null,
@Achternaam varchar(50) = Null,
@Voorletters varchar(10) = Null,
@Voornamen varchar(50) = Null,
@Roepnaam varchar(40) = Null,
@Adres varchar(50) = Null,
@Postcode varchar(7) = Null,
@Woonplaats varchar(50) = Null,
@Telefoon varchar(11) = Null,
@ID_afdeling varchar(50) = Null,
@Kamer varchar(7) = Null,
@Geboortedatum datetime = Null
)
-- query
AS
SET NOCOUNT ON
DECLARE @ID_afdeling_int int
If @ID_afdeling = Null
SET @ID_afdeling_int = CONVERT(int, @ID_afdeling)
SET @ID_afdeling_int = ''
INSERT INTO dbo.tblClienten
(Zoeknaam, Geslacht, Voorvoegsels, Achternaam, Voorletters, Voornamen, Roepnaam,
Adres, Postcode, Woonplaats, Telefoon, ID_afdeling, Kamer, Geboortedatum, PrintDatum)
VALUES (@Zoeknaam, @Geslacht, @Voorvoegsels, @Achternaam, @Voorletters, @Voornamen, @Roepnaam,
@Adres, @Postcode, @Woonplaats, @Telefoon, @ID_afdeling_int, @Kamer, @Geboortedatum, getDate())
RETURN
The VBCode has been stated below:
Code:
Public Sub saveNewClient()
Dim objParam As ADODB.Parameter
Dim objSPRs As ADODB.Recordset
Dim objSPComm As ADODB.Command
Set objSPComm = New ADODB.Command
With objSPComm
.ActiveConnection = gobjConn
.CommandType = adCmdStoredProc
.CommandText = "insertClientSP"
End With
'Zoeknaam
Set objParam = objSPComm.CreateParameter("Zoeknaam", adVarChar, adParamInput, 50, Me.txtZoeknaam.Text)
objSPComm.Parameters.Append objParam
'geslacht
Set objParam = objSPComm.CreateParameter("Geslacht", adBoolean, adParamInput, 10, Me.optSex(0).Value)
objSPComm.Parameters.Append objParam
'voorvoegsels
Set objParam = objSPComm.CreateParameter("Voorvoegsels", adVarChar, adParamInput, 10, Me.txtVoorvoegsels.Text)
objSPComm.Parameters.Append objParam
'achternaam
Set objParam = objSPComm.CreateParameter("Achternaam", adVarChar, adParamInput, 200, Me.txtAchternaam.Text)
objSPComm.Parameters.Append objParam
'voorletters
Set objParam = objSPComm.CreateParameter("Voorletters", adVarChar, adParamInput, 50, Me.txtVoorletters.Text)
objSPComm.Parameters.Append objParam
'voornamen
Set objParam = objSPComm.CreateParameter("Voornamen", adVarChar, adParamInput, 150, Me.txtVoornamen.Text)
objSPComm.Parameters.Append objParam
'Roepnaam
Set objParam = objSPComm.CreateParameter("Roepnaam", adVarChar, adParamInput, 100, Me.txtRoepnaam.Text)
objSPComm.Parameters.Append objParam
'Adres
Set objParam = objSPComm.CreateParameter("Adres", adVarChar, adParamInput, 100, Me.txtAdres.Text)
objSPComm.Parameters.Append objParam
'Postcode
Set objParam = objSPComm.CreateParameter("Postcode", adVarChar, adParamInput, 7, Me.txtPostcode.Text)
objSPComm.Parameters.Append objParam
'Woonplaats
Set objParam = objSPComm.CreateParameter("Woonplaats", adVarChar, adParamInput, 100, Me.txtWoonplaats.Text)
objSPComm.Parameters.Append objParam
'Telefoon
Set objParam = objSPComm.CreateParameter("Telefoon", adVarChar, adParamInput, 11, Me.txtTelefoon.Text)
objSPComm.Parameters.Append objParam
'afdeling_ID
'LET OP : hier halen we de TAG op van de gekozen afdeling!!!!!!!
Set objParam = objSPComm.CreateParameter("ID_afdeling", adInteger, adParamInput, 10, Me.txtAfdeling.Tag)
objSPComm.Parameters.Append objParam
'kamernummer
Set objParam = objSPComm.CreateParameter("Kamer", adVarChar, adParamInput, 10, Me.txtKamernummer.Text)
objSPComm.Parameters.Append objParam
'geboortedatum
Set objParam = objSPComm.CreateParameter("Geboortedatum", adDate, adParamInput, , Me.DatePicker1.Value)
objSPComm.Parameters.Append objParam
Set objSPRs = objSPComm.Execute(, objParam)
Set objSPRs = Nothing
Set objSPComm = Nothing
Unload Me
Set frmAddClient = Nothing
End Sub
Passing A Parameter To A Stored Procedure
What's going on here?
I'm passing a couple dates to a stored procedure but I'm getting the following error:
"Syntax error converting datetime from character string"
Here are code snippets...
Dim statements...
Public mstrFromDate As String
Public mstrToDate As String
Execution of stored proc (at this point a msgbox verifies that mstrFromDate contains '7/27/2003' & mstrToDate contains '8/7/2003'...
If m_cnnXact.State = adStateOpen Then
gstrSel = "EXEC rgarner.spFixErrors @parm1 = 'GETSUB', @ToDate = mstrToDate, @FromDate = mstrFromDate"
Set m_rsXact = New Recordset
m_rsXact.CursorLocation = adUseClient
m_rsXact.Open gstrSel, m_cnnXact, adOpenStatic, adLockBatchOptimistic
Call cmdFirst_Click
End If
parameters defined in stored proc...
ALTER PROC spFixErrors
(@parm1 varchar(255),
@ToDate char(10)=NULL,
@FromDate char(10)=NULL OUTPUT)
stored proc code...
If (@parm1 = 'GETSUB')
BEGIN
SELECT *
FROM TransactionError
WHERE error_summary in (1, 3, 5, 7)
AND CREATE_DATE BETWEEN
CONVERT(datetime, @FromDate) AND
CONVERT(datetime, @ToDate)
END
If I replace the 2 date variables with teh same hard coded dates, everything works fine.
Any help would be appreciated.
thanks
Output Parameter From A Stored Procedure
I'm clueless about how to do this. Any help is welcome. I need to call a stored procedure, accept the return vallue (an integer) and then refresh the recordset with the new number and display it. It's kicked off with a button click. The following is what I have but I can't get it working. I'm using a data environment.
Dim spResult As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim param As New ADODB.Parameter
Set param = cmd.CreateParameter
param.Name = "@returnInstNbr"
param.Type = adInteger
param.Direction = adParamReturnValue
cmd.Parameters.Append param
cmd.CommandText = "spInstGetNewInstNbr"
cmd.CommandType = adCmdStoredProc
Set spResult = de.cn.Execute() ' missing argument
cmd.Prepared = True
cmd.Parameters.Refresh
txtInstNbr2.Text = cmd.Parameters("0").Value 'filling text box with new number returned from stored procedure.
I've looked at other postings but still am not able to get it running.
Integer Parameter In Stored Procedure
I have a stored procedure that takes in integer parameters, among others. When I call it from VB, I want to pass in values from a MSHFlexgrid so I'm using something like this:
Code:
cmd.Parameters.Append cmd.CreateParameter("C_in", adInteger, adParamInput, , MSHF.TextMatrix(8,2))
But, if the cell value is blank it causes an error. So I decided to check for a blank value in the cell and if there is one, pass in no value for that parameter (I can't pass in 0 because 0 means something). So I use:
Code:
cmd.Parameters.Append cmd.CreateParameter("C_in", adInteger, adParamInput)
If MSHF.TextMatrix(8,2) <> "" then cmd("C_in").value = cint(MSHF.TextMatrix(8,2))
I'm just wondering if there is a faster/better way to do this? The procedure takes in a lot of integers and it's a lot of code to keep doing it like this....
Oracle Stored Procedure OUT Parameter Value
Hi everyone,
I migrated objects from SQL Serever 2000 to Oracle. Now I am changing VB code and current code used rs.open to execute procedure. I am trying to keep code as is if possible. Only trying to change SQLstr.
I am having problem getting OUT parameter value using rs.open command. Following code will be used.
Oracle Procedure:
CREATE OR REPLACE P1(
IN_C1 IN NUMBER,
IN_C2 IN NUMBER,
IN_C3 IN NUMBER,
Retval IN NUMBER)
)
IS
BEGIN
INSERT INTO T1 (C1, C2, C3)
VALUES ( IN_C1, IN_C2, IN_C3);
COMMIT;
Retval := 0; -- To confirm that successfully execute
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
Retval = -1; -- To check if any error occurs
END:
VB Code:
Dim objconn as ADODB.Connection
Dim objcmd as ADODB.Command
Dim rs as ADODB.Recordset
dim param1, param2, param3, Retval as ADODB.Parameter
Set objconn = New ADODB.Connection
connectionstring = "......."
objconn.open connectionstring
***** This code works ******************************
set objcnd.activeconnection = objconn
objcmd.commandtext = "P1"
objcmd.commandtype = adcmdstoredproc
set param1 = objcmd.createparameter("in_c1",adinteger,adparaminput,1)
parameters.append param1
set param1 = objcmd.createparameter("in_c2",adinteger,adparaminput,2)
parameters.append param2
set param1 = objcmd.createparameter("in_c3",adinteger,adparaminput,3)
parameters.append param3
set param1 = objcmd.createparameter("Retval",adinteger,adparamReturnValue)
parameters.append Retval
set rs = objcmd.execute
debug.print objcmd.parameters("rc").value
With this code return value will be display
********************************* OK with above code ************
I would like to know how I can execute procedure with rs.open command:
SQLstr = "{EXECUTE P1({1,2,3,{Resultset 1, retval})}"
rs.open SQLstr, objconn, adopenforwardonly,adlockreadonly,adcmdtext *** I am getting Object variable or with block variable not set error *****
if rs.fields("Retval") = 0 then
....
...
else
....
....
end if;
rs.close
Please let me how I can resolve this issue.
Thanks in advance.
rakrana
Stored Procedure TEXT Parameter
I'm writing my first stored procedure! Everything was going well until I tried to execute the procedure thru VB. I have an input parameter that is a TEXT datatype and I think that's causing the problem. On the execute command, I get an error saying "Parameter type is not supported." Some light shedding on this problem would be greatly appreciated!
~m.
Stored Procedure OUTPUT Parameter
Hey all....I'm real new to using stored procedures so pls bear with me.
I'm getting this error when trying to execute. "Formal parameter was defined as OUTPUT but the actual parameter not declared OUTPUT."
The SP header looks like this:
CREATE PROCEDURE GENPROC
(
@p_SessionID_in INTEGER,
@p_NumReqGen_out INTEGER OUTPUT,
@p_AppErrorID_out INTEGER = 0 OUTPUT,
@p_AppErrorText_out VARCHAR(255) = '' OUTPUT
)
This is the code I'm using:
Code:
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim prmInput As ADODB.Parameter
Dim prmOutput As ADODB.Parameter
With cmd
Set .ActiveConnection = cn 'cn is previously defined & open
.CommandText = "GENPROC"
.CommandType = adCmdStoredProc
.CommandTimeout = 15
Set prmInput = .CreateParameter("@p_SessionID_in", adInteger, adParamOutput, 8)
Set prmInput = .CreateParameter("@p_NumReqGen_out", adInteger, adParamOutput,10)
Set prmOutput = .CreateParameter("@p_AppErrorID_out", adInteger, adParamOutput, 255)
Set prmOutput = .CreateParameter("@p_AppErrorText_out", adVarWChar, adParamOutput, 255)
.Parameters.Append prmInput
.Parameters.Append prmOutput
.Execute
End With
Set cmd = Nothing
Date Parameter For Stored Procedure
Hello, I am getting an "Optional feature not implemented" error when executing a stored procedure and have isolated it to a parameter which passes a date value.
I have declared the parameter as type DateTime in the Stored Procedure.
Here is how I am declaring it in VB:
Code:
.Parameters.Append .CreateParameter("@ServiceDate", adDate, adParamInput, 10, mObjDVisitas.ServiceDate)
The date value has been formatted correctly. Maybe it could have something to do with the size, does it have the right size for a date value?
Output Parameter From Stored Procedure
Hi all
I wrote a stored procedure which inserts one row and get the maximum value of an auto increment field using @@identity as output parameter.
I'm not using a command object.
How can I get this value (output parameter) if I'm using a 'connection.execute' or 'recordset.open' ?
thanks
Receiving A Parameter From A Stored Procedure
Hi everyone,
I have a Stored Procedure in SQL Server that I want it to return to me the @@IDENTITY of the Record that I just modified. How can i do this in the Stored Procedure and how can i accept it in VB?
Im stuck on this so any help would be of great help.
Thanks guys!!
Multiple Parameter On Stored Procedure
Hi there,
I've used this code to execute a stored procedure how would I use it if i've got multiple parameters in my sp. I also cant get it to work with only one parameter but it works fine with no parameters. Please help.
Werner.
-------------------------------------------------------------------
Private Sub DoADO()
Dim oDBConn As New ADODB.Connection
Dim oDBComm As New ADODB.Command
Dim oParam1 As New ADODB.Parameter
Dim orS As New ADODB.Recordset
With oDBConn
.ConnectionString = "Provider=SQLOLEDB;Data Source=serverName;Initial Catalog=databaseName; User ID=userName;Password=userPassword;"
.CursorLocation = adUseServer
.Mode = adModeReadWrite
.Open
End With
With oDBComm
.ActiveConnection = oDBConn
.CommandText = "SPROC"
.CommandType = adCmdStoredProc
Set oParam1 = .CreateParameter("PARAMNAME", adVarChar, adParamInput, 50, "HELLOWORLD")
.Parameters.Append oParam1
End With
'Do some reading on the line below it'll help you
orS.Open oDBComm, , adOpenKeyset, adLockOptimistic
End Sub
Parameter Not Return From Stored Procedure
Hi
I put my code vb as:
with cmd
..
..
.Parameters.Append .CreateParameter("@myout", adChar,adParamOutput, 1, myvar)
end with
inside the procedure It put 3 or 5 or other value, but inside vb It return 0 (ZERO)
what happened, It only work when I put
myvar = cmd.parameters(("@myout")
VBA Uses OUTPUT Parameter From Stored Procedure
Hi there,
I am developing an Access97 application in VBA and try to get the OUTPUT parameter from a stored procedure that I try to run. I have some sample code from MSDN that works fine in VB, but when I try it in VBA I get a 'Type mismatch' error at this line:
Set param = Cmd.CreateParameter("Return", adInteger, adParamReturnValue, , 0)
param is defined as a Parameter, Cmd is an ADODB.Command
Anyone came across this before?
Passing A Parameter To A Stored Procedure.
I have a stored procedure on a server that I am trying to execute in VB6.
I want to use the following command:
EXEC myDB..ACTIVITY_CODES.
I want to be able to pass a parameter @UserName to the procedure using the EXEC command.
How do I code this?
I would appreciate any help I can get.
Thanks
Parameter Array To SQL Stored Procedure
With cn
'Establish DSN-less connection
.ConnectionString = ConnectionString
.ConnectionTimeout = 10
.Open
End With
Set Qr.ActiveConnection = cn
Qr.CommandText = "sp_get_comments"
Qr.CommandType = adCmdStoredProc
'create parameter code
Set ordno = Qr.CreateParameter("@ordno", adChar, adParamInput, 10)
ordno.Value = frmParts.txtOrderNo.Text
Can anyone help
I am accessing a stored procedure with three input parameters, I can pass one parameter using the code below but how do you pass an array of parameters. I've also tried using the append method to add the parameters, when I run the sp the first time it works, but if I change the vales for the parameters on the input sreen and run the SP again I get an error message saying too many parameters supplied.
'create parameter code
Set partno = Qr.CreateParameter("@partno", adChar, adParamInput, 20)
partno.Value = frmParts.flxParts.TextMatrix(frmParts.flxParts.RowSel, 0)
'create parameter code
Set duedate = Qr.CreateParameter("@duedate", adChar, adParamInput, 8)
duedate.Value = frmParts.flxParts.TextMatrix(frmParts.flxParts.RowSel, 3)
Set rs = Qr.Execute("sp_get_parts", ord_no)
basically all I'm asking for is some sample code that shows me how to pass more than one parameter to a sql stored procedure.
Any help would be greatly appreciated
thanks
Matt
Parameter To Stored Procedure Problem.
Given the Stored Procedure fragment below:
CREATE PROCEDURE [dbo].[V100_STATS]
@recipe_code [VARCHAR](20),
@scaling_code int,
@tMIN real output,
@tMAX real output,
@tAVG real output,
@tSTD real output
Given the VB code fragment below:
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim param1 As Parameter, param2 As Parameter
Set param1 = cmd.CreateParameter("rc", adVarChar, adParamInput)
Set param2 = cmd.CreateParameter("sc", adInteger, adParamInput)
cmd.Parameters.Append param1 ***ERROR ***
cmd.Parameters.Append param2
I get the following error when trying to append the first parameter. I have not been able to find any answers in the VB or SQL Server online documentation.
VB ERROR
Run-time error '3708':
Parameter object is improperly defined. Inconsistent or incomplete
information was provided.
How do I pass a string to a [VARCHAR] in SQL?
When A Stored Procedure Has An Output Parameter
In VB6.0 how do I execute and get the value back of a stored procedure that has an output parameter?
Currently I have a function that calls a stored procedure that has an output parmeter but I run into when no values come back it returns NULL so I have to do the ISNULL Check.
My question, is there a better way to write this so I don't have all this code just to get the output parameter? If no value comes back is there anyway to return blank instead of null (I do have the ISNULL(FieldName, '') in my stored procedure...
With objCmd
.ActiveConnection = adoConn
.CommandType = adCmdStoredProc
.CommandText = "sp_Storedproc"
.Parameters(1).value = value1
.Execute
If Not IsNull(Trim(.Parameters(2).value)) Then
FunctionReturn= Trim(.Parameters(2).value)
Else
FunctionReturn = ""
End If
Set objCmd = Nothing
End With
How To Get Information Of Stored Procedure Parameter In VB?
How to find out whether a particular parameter of a stored procedure can be null or not?
Example:
Create Procedure Temp1
@Num int,
@Num1 int = NULL
as
.
.
While calling this SP the second parameter(Num1) can be ignored.
The call to this SP can be:
Execute Temp1 10
How to identify, which parameters of a stored procedure are mandatory?
How To Call A Stored Procedure With Parameter In ADO? Pls Help...
Hi all,
I'm stucked, I'm trying to call a Stored Procedure that require a input parameter using the following code:
Dim rdcnnVB As New ADODB.Connection
Dim rdrslVB As New ADODB.Recordset
Dim lngValue As Long
Dim lngLength As Long
Dim mc_strConn As String
mc_strConn = "PROVIDER=SQLOLEDB; DATA SOURCE=Server; INITIAL CATALOG=myTable; USER ID=sa; PASSWORD=123456;"
Set objContext = GetObjectContext()
Set rdcnnVB = CreateObject("ADODB.Connection")
rdcnnVB.Open mc_strConn
Set rdrslVB = CreateObject("ADODB.recordset")
rdrslVB.CursorLocation = adUseClient
rdrslVB.Open "exec pr_clsIDCtrl_mtdIDGet '00'", rdcnnVB
lngValue = rdrslVB.Fields("Val")
lngLength = rdrslVB.Fields("length")
'---------------------------------------------------------
And VB give me this error: "Item cannot be found in the collection corresponding to the requested name or ordinal."
I'm sure my SP will return some data... here is my SP code:
CREATE PROCEDURE pr_clsIDCtrl_mtdIDGet @strTranPrefix varchar(5) AS
Declare
@val int,
@length tinyint
BEGIN TRAN
/* Get the previous value */
SELECT @val = val, @length = length FROM tb_id WHERE tran_prefix = @strTranPrefix
/* Increment the value */
UPDATE tb_id SET val = val + 1 WHERE tran_prefix = @strTranPrefix
/* Return the result */
SELECT @val AS val, @length AS length
COMMIT TRAN
GO
'---------------------------------------------------------
I'm a missing something? Pls. help...
Thks.
Stored Procedure Parameter Problem
Hello
I'm having problems talking to the third parameter in the stored procedure header here bellow:
CREATE PROCEDURE [sp_AddToHistory_tblFileHistory]
(@lFileID [int],
@sUser [nvarchar](20),
@sComment [ntext],
@dtCheckedInTime [int])
Here is how I talk to the first
Set prmChecktblFiles = cmdChecktblFiles.CreateParameter( _
"lFileID", _
adBigInt, _
adParamInput, , _
lFileID)
If anyone knows how to create the parameter for the third, the please let me know
Thanks
BLOB As Out Parameter With Stored Procedure
Hi
I am retrieving an image from database using BLOB as out parameter to a stored procedure .
I am using VB with oracle.
.
When I am retrieving image upto size 70KB
the image bytes are retrieved correctly and written to a temporary file and the image is shown in picture box.
But if the size exceeds 70KB it gives an error. The BLOB out parameter does not return all the data, it return some 23KB odd
bytes which do not form complete image.
Why it is happening?
I tried to retrieve the same image as a field in recordset using GetChunck() method.
It returns the complete image in one shot or in small parts correctly.
Parameter object does not have any GetChunck() method to retrieve the data in small parts.
So how it can be? Is there any limitation with Parameter object.
Thanks
Execute Stored Procedure With Default Parameter
I have this stored procedure:
CREATE PROC sp_Invoice
@Vendor Char (4) = '%'
AS
BEGIN
SELECT dbo.Hdr_InvHeader.Hdr_VdrNbr, dbo.Hdr_InvHeader.Hdr_InvNbr AS InvNbr FROM dbo.Hdr_InvHeader WHERE Hdr_VdrNbr LIKE @Vendor
END
RETURN
GO
My understanding is that if I use this statement it will use my default value for @Vendor, but I don't get any records:
execute sp_Invoice
However, if I run this in the Query Analyzer, it gives me the correct results:
SELECT dbo.Hdr_InvHeader.Hdr_VdrNbr, dbo.Hdr_InvHeader.Hdr_InvNbr AS InvNbr FROM dbo.Hdr_InvHeader WHERE Hdr_VdrNbr LIKE '%'
Any thoughts about why the default value is not working?
Thank you,
Heidi
ADO Pass XML Document As Parameter To Stored Procedure
I have a stored procedure as below
Stored Procedure
-------------------
Code:
Create Procedure sp_employee ( @xml TEXT )
AS
BEGIN
DECLARE @idoc INT
,@emp_id INT
,@emp_add VARCHAR(100)
EXEC sp_xml_preparedocument @idoc OUTPUT, @XmlDoc
SELECT @emp_id = ISNULL(emp_id , 0 )
,@emp_add = ISNULL (emp_add , 'N/A')
FROM OPENXML(@idoc, 'ROOT', 2)
WITH ( emp_id INT
,emp_add VARCHAR(100)
)
IF @emp_id <> 0
INSERT INTO employee ( empid , empadd ) VALUES ( @emp_id , @emp_add )
EXEC sp_xml_removedocument @idoc
END
How how i pass the xml document to this stored procedure ?
Basically i know i have to use
i)ADODB.Connection
ii)ADODB.Command
iii)ADODB.Parameter
My parameter is an input parameter so it's adParamInput.
But what about the type of parameter pass in ? any idea ? i know it has adVarchar. Does it has adText ?? Sorry for the out running indent.
Anyone done it before or anyone has any idea ? thanks. i'm newbie over here.
Return Output Parameter From Stored Procedure.
I have a problem getting an output parameter from a stored procedure in VB6.
I have declared the output parameter as below:
Set pRegId = .CreateParameter("@NonStandardReplyID", adInteger, adParamOutput, , mudtProps.NonStandardReplyID)
.Parameters.Append pRegId
And then i execute the SP as below reading the result set returned by the SP into my ADODB.Recordset object SaveToDatabse. And then i need to pick up the value of the output parameter pRegId. See below:
Set SaveToDatabse = .Execute
mudtProps.NonStandardReplyID = Val(pRegId)
-This doesn't return a value for pRegId
The SP is definetly returning the output parameter (i have checked via SQL profiler what is executed and returned by the SP), but I the value of pRegId is returned as an empty string back in the VB code above, UNLESS i execute the ADODB.Command without reading it back into my recordset. I.e. as below:
.Execute
mudtProps.NonStandardReplyID = Val(pRegId)
-This DOES return a value for pRegId!!
Now I would have concluded that the ADODB.Command object does not allow you to both read in a result set into a recordset object AND pick up the value returned by an output parmeter at the same time - except that i have done exactly this with seemingly identical code and SP output in another class within my application!!! As seen below:
1.declare the output param:
Set pRegId = .CreateParameter("@RegistrationID", adInteger, adParamOutput, , mudtProps.RegistrationID)
.Parameters.Append pRegId
2.Execute the sp and read in the output parm value:
Set SaveToDatabse = .Execute
mudtProps.RegistrationID = Val(pRegId)
--THIS DOES WORK!!
any help here would be appreciated
Pasing Date As A Parameter To A Stored Procedure
I am passing a parameter to a stored procedure for the order by clause.
depending on what i pass as the parameter, then the appropriate field is
used for the order by
the parameter is defined as a Char. This is so because I pass in a string.eg
This is the last portion of the SQL in Sybase Central
Order By
Case SortBy
When SortBy ='DateVar' then DATEFORMAT(DateFilled,'yyyy-mm-dd')
When SortBy ='Patient' then PatientLastName
When SortBy ='Presc' Then presdription
Else
PatientLastName
End ASC
When I pass the DateVar it works but when I pass Patient or Presc then I get
the error cannot convert to date
Please help
Sampson Adu-Poku
Opus Health Information Systems, Inc.
Tel: 631-439-0909 ext 229
Output Parameter From Stored Procedure In Oracle
Hi to all,
I have a problem while retrieving a output parameter from Oracle.
I tried following code for SqlServer database and it works fine,
but the same code for Oracle database gives me error "Type Mismatch"
Here goes the code:
------------------ ***** --------------------
Option Explicit
Private Sub Command1_Click()
StoreProc
End Sub
Private Sub StoreProc()
Dim dbConnection As ADODB.Connection
Dim dbCommand As ADODB.Command
Set dbConnection = New ADODB.Connection
Set dbCommand = New ADODB.Command
Dim DSNNAME As String
Dim USERNAME As String
Dim PASSWORD As String
DSNNAME = "Mydb"
USERNAME = "user"
PASSWORD = "pass"
dbConnection.Open DSNNAME, USERNAME, PASSWORD
dbCommand.ActiveConnection = dbConnection
Dim TheDate As Date
TheDate = Now
Dim p1 As Parameter
Dim p2 As Parameter
Set p1 = dbCommand.CreateParameter("@thedate", adDBTimeStamp,
adParamInput, 0, TheDate)
Set p2 = dbCommand.CreateParameter("@NumEmployees", adInteger,
adParamOutput, 0)
dbCommand.CommandText = "GetEmployeeInfo"
dbCommand.CommandType = adCmdStoredProc
dbCommand.Parameters.Append p1
dbCommand.Parameters.Append p2
dbCommand.Execute
Dim strTheString As String
strTheString = "There are " & dbCommand.Parameters("@numemployees") & "
employees who were hired before " & TheDate
MsgBox strTheString, vbOKOnly, "Demonstration"
End Sub
------------------ ***** --------------------
Stored procedure :
------------------ ***** --------------------
CREATE PROCEDURE GetEmployeeInfo
(@thedate as datetime,
@NumEmployees as numeric OUTPUT)
AS
SELECT @NumEmployees = count(*) from employee where hire_date < @thedate
------------------ ***** --------------------
Regards,
Vinay
Data Environment + Stored Procedure With Parameter
Hi!
Please help me with the DE.
When I use a stored proc which doesn´t require parameters everything is fine with Datalists and Datacombos filled but as soon as I open with parameters the controls are empty!
The recordset however is full so I can get all the data from it. Has somebody please an idea about what is wrong? Is there a propery I could have missed or could it be something else?
I would be most greatful for help
Jenny
Oracle Oledb-stored Procedure With Optional Parameter
I have a oracle(8i) stored procedure with two optional IN parameters and a REFCURSOR OUT parameter.
Search_Test (AA IN VARCHAR2 DEFAULT NULL,XX IN VARCHAR2 DEFAULT NULL,RC1 OUT Omwb_emulation.globalPkg.RCT1)
I need to call this procedure from VB, passing one of more of the optional parameters using the parameter name in any order.
1. SQL = "{ call Search_Test(XX=>'',AA=>'1111') }"
2. SQL = "{ call Search_Test(AA=>'1111',XX=>'1111') }"
3. SQL = "{ call Search_Test(XX=>'') }"
4. SQL = "{ call Search_Test(AA=>'') }"
Using Provider=MSDAORA, statements 1 & 2 works fine and 3& 4 gives the following error
PLS-00306: Wong number or type of arguments in call to Search_Test. PL/SQL statement ignored
Using OraOledb.oracle, all the 4 statements fails with runtime error -2147467259
Please let me know if you any of you have a solution to this .
Thanks..
Bhuvana
Unable To Return Parameter From Stored Procedure Called By VB App To The App
I need to return a value that is a record count/query result that is run at the end of a stored procedure
called by my VB application.
This code does not return parameters from the stored procedure:
DoEvents
'Set up stored procedure command object
Set cmdSP = New Command
With cmdSP
.CommandText = "sp_My_Stored_Procedure"
.CommandType = adCmdStoredProc
.ActiveConnection = connSQL
.CommandTimeout = 600
Set rstResults = .Execute
rstResults.Close
End With
Set rstResults = Nothing
Set cmdSP = Nothing
Unload Me
I need to return a string with a numeric recored count (ie. result
of a SQL query at the end of the stored procedure in SQL server) to the VB application.
This code does not return anything.
Can anyone help please?
How To Pass An Array As In Parameter To A Stored Procedure(in Oracle) From VB.
first of all thanks for all the answers you guys are sending.they are really helping me in my project.here comes another problem.
i have this stored procedure in oracle
CREATE OR REPLACE PROCEDURE VARRAY_TEST
(SUBJECT_IN IN SUBJECT_TYPE)
IS
I NUMBER ;
BEGIN
FOR I IN SUBJECT_IN.FIRST..SUBJECT_IN.LAST LOOP
INSERT INTO empl VALUES(SUBJECT_IN(I));
END LOOP;
END;
where SUBJECT_TYPE is a varray declared using the following statement:
CREATE OR REPLACE TYPE SUBJECT_TYPE AS VARRAY(10) OF VARCHAR2(10);
and empl is a table name that exists in the database.
i want to call this stored procedure from VB ,passing the values to IN Parameter SUBJECT_IN.
how can i do that.
i tried this code :
Private Sub Command4_Click()
Dim passarray(9) As String
Dim cmdarr As New ADODB.Command
Dim pararr As New ADODB.Parameter
Dim i As Integer
For i = 0 To 9
passarray(i) = "maths"
Next i
Set cmdarr = New ADODB.Command
cmdarr.ActiveConnection = goConn
cmdarr.CommandText = "varray_test"
cmdarr.CommandType = adCmdText
Set pararr = New ADODB.Parameter
pararr.Name = "passarray"
pararr.Direction = adParamInput
pararr.Type = adArray
pararr.Size = 10
pararr.Value = passarray
cmdarr.Parameters.Append pararr
cmdarr.Execute
End Sub
but it doesnt work.gives an error message which says " Arguments are of the wrong type,are out of acceptable range,or are in conflict with one another."
please also try to a sample code.
thanks
regards
Sumit Gulati
How To Pass Ntext Input Parameter To Stored Procedure?
How to pass ntext input parameter to stored procedure (MS SQL 200)?
Set param = cmd.CreateParameter("@myNText", PType, adParamInput, PSize, PValue)
cmd.Parameters.Append param
What datatype (size) to use?
Thanks for any help!
|
TRACKER
