Solved

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

Posted on 2004-09-09
14
178 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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
 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
OSQL to execute sql command 26 24
SQL STANDARD CORE 6 29
Powershell v3 - SQLCMD 3 26
SQL Syntax Grouping Sum question 7 24
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…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

790 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