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_custom er) Or @by_customer Is Null) And ((Cases.Product=@by_produc t) Or @by_product Is Null) And ((Cases.Engineer=@by_engin eer) 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
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_custom
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
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
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.
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.
ASKER
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
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.
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.
ASKER
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
Do I have to perform these all back in .mdb file, I have seen there a passthrough query option.
Regards.
moazzam
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Regards.
Moazzam
You don't need any freeware for that - it's built into Access!
Just choose File / New / Project ( Existing Database )
Just choose File / New / Project ( Existing Database )
ASKER
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
Moazzam
ASKER
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
exec StoredProcedure3 forms!Case Summary Report Form.BY_CUSTOMER.value, Forms!Case Summary Report Form.FROM_OPEN_DATE.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.
since there are spaces in "case summary report form", it must be enclosed in [] to indicate it is one entity.
ASKER
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
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.
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.
Post the code where you are calling the stored proc from Access