Solved

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

Posted on 2004-09-09
14
176 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:moazzam1574
  • 7
  • 7
14 Comments
 
LVL 6

Expert Comment

by:mcp111
ID: 12016411
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
0
 

Author Comment

by:moazzam1574
ID: 12016499
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

0
 
LVL 6

Expert Comment

by:mcp111
ID: 12017422
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.

0
 

Author Comment

by:moazzam1574
ID: 12017523
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
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12017708
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.

0
 

Author Comment

by:moazzam1574
ID: 12017796
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
0
 
LVL 6

Accepted Solution

by:
mcp111 earned 100 total points
ID: 12018112
If you have an  access project, you don't need any pass through query.

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.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:moazzam1574
ID: 12018293
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
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12018761
You don't need any freeware for that - it's built into Access!
Just choose File / New / Project ( Existing Database )
0
 

Author Comment

by:moazzam1574
ID: 12019044
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
0
 

Author Comment

by:moazzam1574
ID: 12025765
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
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12026783
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.
0
 

Author Comment

by:moazzam1574
ID: 12033723
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
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12035803
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.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now