Link to home
Start Free TrialLog in
Avatar of moazzam1574
moazzam1574

asked on

Passing parameters from MS-Access application to SQL-Server Stored Procedure

Hi,

I have created a stored procedure in SQL-Server that accepts parameters from user and based on the crietria it calls a report that runs the stored procedure and fetches the records. The Form in Access Application that inputs some values and those values must be passed to that Stored Procedure, now here is the Procedure,

CREATE PROCEDURE dbo.StoredProcedure3 (@company nvarchar(50),
                                    @from_open_date datetime,
                                    @to_open_date datetime,
                                    @from_close_date datetime,
                                    @to_close_date datetime,
                                    @by_customer nvarchar(50),
                                    @by_product nvarchar(50),
                                    @by_engineer nvarchar(50))
AS
SELECT Cases.Company, Cases.[Case Status], Cases.[UPL Call No], Cases.[SR No], Cases.Caller, Cases.[Caller Email], Cases.Engineer, Cases.[Open Date], Cases.[Customer Site], Cases.City, Cases.[Closed Date], Cases.Technology, Cases.Hardware, Cases.Product, Cases.Component, Cases.Software, Cases.System, Cases.[Problem Description], Cases.Summary, Cases.[Contract Type], Cases.Priority
FROM Cases
WHERE ((Cases.[Open Date] Between @from_open_date And @to_open_date) OR (Cases.[Closed Date] Between @from_close_date And @to_close_date) OR ((Cases.Company=@by_customer) Or @by_customer Is Null) And ((Cases.Product=@by_product) Or @by_product Is Null) And ((Cases.Engineer=@by_engineer) Or @by_engineer Is Null))
GO

Now when inputing the values through that form and calling the report that access this stored procedure, when the procedure is accessed, it again prompts the user for values. How can I avoid this issue. Can any 1 tell me the solution plz.

Regards.

Moazzam
Avatar of Partha Mandayam
Partha Mandayam
Flag of India image

are you passing the parameters in the form forms!<formname>.<fieldname>.<value> when you call the stored procedure.
Post the code where you are calling the stored proc from Access
Avatar of moazzam1574
moazzam1574

ASKER

Hi mcp,

Well, all I am doing is through the form when the Paramaters are input then on the Button click event I have this code.

DoCmd.OpenReport "Case Summary Report", PrintMode

that calls the report, and in the report properties tab, I have set the RecordSource property to call that storeprocedure. Now when the fields entered and the button pressed the report is executed and which calls that storedprocedure, whcih again start prompting the user for the input.

Regards.

Moazzam

Yes, but how does the stored procedure know that it is supposed to take the parameters from the form.

What you should do is create a Pass through query like

exec dbo.StoredProcedure3 forms!<your form name>.company.value,   etc for all the parameters

and then base the report on that query.

Then your report knows to take the values that you entered on the form and it will not prompt you again.

Hi mcp,

I suppose no u got the better idea what I m askign about, I have embeded a piece of code now passing the values along with the storeprocedure, and now the reports dont asks me for values but it wont display any thing on the criteria. I dont know what's going on. What the code I have embed is here.


SQLSel = "StoredProcedure3'" & Forms![Case Summary Report Form]!BY_CUSTOMER & "', '" & Forms![Case Summary Report Form]!FROM_OPEN_DATE & "', '" & Forms![Case Summary Report Form]!TO_OPEN_DATE & "', '" & Forms![Case Summary Report Form]!FROM_CLOSE_DATE & "', '" & Forms![Case Summary Report Form]!TO_CLOSE_DATE & "', '" & Forms![Case Summary Report Form]!BY_PRODUCT & "', '" & Forms![Case Summary Report Form]!By_Engineer & "'"

Set rs = oconn.Execute(SQLSel)


any suggestions.

Regards

Moazzam
Create a new pass through query as follows

exec StoredProcedure3 Forms![Case Summary Report Form]!BY_CUSTOMER , all other parameters....

To create a pass through query, choose new query. click query/sql specific/pass through.

Use this pass through query for your report

Call the report from the print button of your form.

Everything should work fine.

But for that I have to be in an Access database, I mean .mdb file, as the condition now is I have created a Project file in Access and doing all these in that and I suppose pass through query option is not available in Access while u r in Project. :(.

Do I have to perform these all back in .mdb file, I have seen there a passthrough query option.

Regards.

moazzam
ASKER CERTIFIED SOLUTION
Avatar of Partha Mandayam
Partha Mandayam
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Love u guys, u ppls r real genious, it worked gr8, wow, now one more favour plz, Plz can u tell me that an access .mdb file can be converted into project file, i mean the MS-Access project file. Any tool, any software a freeware will be gr8.

Regards.

Moazzam
You don't need any freeware for that - it's built into Access!
Just choose File / New / Project ( Existing Database )
well, thanks alot again. I need another favour, say, if what we have talked here can be done in .mdb file i mean by pass through query and later i convert that .mdb file into project. What do i have to do in that case, as in the project i am calling a stored procedure through report, but here should be a query a pass through query.

Moazzam
well, no suggestions related to pass through query,

exec StoredProcedure3 forms!Case Summary Report Form.BY_CUSTOMER.value, Forms!Case Summary Report Form.FROM_OPEN_DATE.value, Forms!Case Summary Report Form.TO_OPEN_DATE.value, Forms!Case Summary Report Form.FROM_CLOSE_DATE.value, Forms!Case Summary Report Form.TO_CLOSE_DATE.value, Forms!Case Summary Report Form.BY_PRODUCT.value, Forms!Case Summary Report Form.By_Engineer.value

I am passing this query as a pass through query, but it gives error on "!" sign it does'nt recognizes it. Help needed urgently.

Regards.

Moazzam
should be forms![Case Summary Report Form].BY_CUSTOMER.value

since there are spaces in "case summary report form", it must be enclosed in [] to indicate it is one entity.
Should i be wrinting these in the pass through query window or u want me to write this code in the form button click event as, I tried this in the pass through query window, it give error on the "!" part and says invalid character.

Regards.

Moazzam
Forget the pass through query.

Since you already have an access project, do what I told you earlier

Right click on the report, choose properties and in the recordsource enter StoredProcedure3
In the Input parameters field enter [Forms]![Case Summary Report Form]![BY_CUSTOMER] , all other parameters....

and you should be fine.