?
Solved

Execute stored procedure on SQL server

Posted on 2008-11-10
23
Medium Priority
?
773 Views
Last Modified: 2013-11-27
Dear Experts,

I created my first stored procedure on SQL server and need to excute this procedure from a ms Access FE application using two parameters. The resultset returned should populate the listbox D1K1.
The stored procedure is attached. The query works fine but calling the stored procedure using code below does not populate the listbox.

Dim dtmDate As Date
Dim intMonteur As Integer
Dim stSql As String
dtmDate = Me.Date1
intMonteur = Me.MonteurKolom1
stSql = "EXEC dbo.GETPLANRECORDS " & intMonteur & ", '" & dtmDate & "'"
D1K1.RowSource = stSql
D1K1.Requery

Can anybody help me please?

Regards, Johan
-- ================================================
-- Stored Procedure voor Planrecords in Planningoverview
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- =============================================
CREATE PROCEDURE GETPLANRECORDS 
	-- Add the parameters for the stored procedure here
	@Monteur Int,
	@Plandatum DateTime
 
AS
 
SELECT     SGH_OrderDetail.Orderdetail_Key, SGH_OrderHead.Meldkamer_Plaats, SGH_OrderDetail.MaterieelString, SGH_PlanningHead.Planning_Datum, 
           SGH_PlanningHead.Planning_Tijdseenheden, SGH_PlanningHead.Planning_Dagdeel, SGH_PlanningHead.Planning_Tijdseenheden AS Expr1, 
           SGH_PlanningDetail.User_ID, SGH_PlanningDetail.Planning_Sequence
FROM       SGH_OrderHead INNER JOIN
           SGH_OrderDetail ON SGH_OrderHead.Orderhead_Key = SGH_OrderDetail.Orderhead_Key INNER JOIN
           SGH_PlanningHead ON SGH_OrderDetail.Orderdetail_Key = SGH_PlanningHead.Orderdetail_Key INNER JOIN
           SGH_PlanningDetail ON SGH_PlanningHead.PlanningHeader_Key = SGH_PlanningDetail.PlanningHeader_Key
WHERE		
		   SGH_PlanningHead.Planning_Datum = @Plandatum AND SGH_PlanningDetail.User_ID = @Monteur
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
END
GO

Open in new window

0
Comment
Question by:jrameuwissen
  • 13
  • 10
23 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22925287
> SGH_PlanningHead.Planning_Datum = @Plandatum
does your field .Planning_Datum include some time information?
0
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22925402
Jip. When I look at the column in SQL server, the time information is always 00:00:00.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22925434
so, I must assume that
dtmDate = Me.Date1

does not contain any (non-00:00:00) time portion neither.

now, what format does that give?

you might try:

stSql = "EXEC dbo.GETPLANRECORDS " & intMonteur & ", CONVERT(datetime, '" & Format(dtmDate, "YYYY-MM-DD") & "', 120) "

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:jrameuwissen
ID: 22925880
Angel, i tried but still an empty listbox. I also tried to convert the planning_datum field in the stored procedure to the same format (just in case) but still the list is empty.

I saw the column headers in the listbox also do not show. As far as I know this indicates there is something wrong with the query.
However, when I execute the query in SQL server (sql-pane) without the parameters the results show up fine...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22925955
can you show if you can run the stored proc directly in sql with parameters getting the correct results?
0
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22926171
When i run attached query in the sql pane SQL server asks me to enter 2 parameters. I enter 10-11-2008 as @Plandatum and 68 as monteur. The resultset looks fine....
0
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22926175

SELECT     SGH_OrderDetail.Orderdetail_Key, SGH_OrderHead.Meldkamer_Plaats, SGH_OrderDetail.MaterieelString, SGH_PlanningHead.Planning_Datum, 
                      SGH_PlanningHead.Planning_Tijdseenheden, SGH_PlanningHead.Planning_Dagdeel, SGH_PlanningDetail.User_ID, 
                      SGH_PlanningDetail.Planning_Sequence
FROM         SGH_OrderHead INNER JOIN
                      SGH_OrderDetail ON SGH_OrderHead.Orderhead_Key = SGH_OrderDetail.Orderhead_Key INNER JOIN
                      SGH_PlanningHead ON SGH_OrderDetail.Orderdetail_Key = SGH_PlanningHead.Orderdetail_Key INNER JOIN
                      SGH_PlanningDetail ON SGH_PlanningHead.PlanningHeader_Key = SGH_PlanningDetail.PlanningHeader_Key
WHERE     (SGH_PlanningHead.Planning_Datum = @Plandatum) AND (SGH_PlanningDetail.User_ID = @Monteur)

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22926280
as I don't know you data, nor your date format (is 10-11 MM-DD or DD-MM ?), you will have to give more information.
also, what does stSql contain after the assignment?
0
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22926306
Ok, format is MM-DD-YYYY (european).
I am not sure what you mean by 'contain after assignment'?
0
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22926331
When I was looking at the parameters in the stored procedure just now I saw that SQL server added a time to the parameter before running it in sql-pane : 10-11-2008 0:00:00
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22926338
the variable stSql, after the line stSQL = " exec ...", what it the value?
0
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22926394
Value of stSQL is :
EXEC dbo.GETPLANRECORDS 68, CONVERT(datetime, '2008-11-10', 120)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22926523
that looks fine.
does it return the same data when you run that sql directly?

0
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22926569
Yes, it does. I got the feeling ms Access is ot triggering the stored procedure at all.. Is there any way I can check if it does?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22926612
>Is there any way I can check if it does?
you could check with sql profiler if the procedure get's called indeed.

now, for the listbox, what are the settings in regards to bound column, and source type?
0
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22926643
Ok,
Name : D1K1
No Columns : 8
Columnwidths : 0cm;1,503cm;1,3cm;0cm;0cm;0cm;0,403cm;0,403cm
Bound Column : 1
Source type = Table/Query
Rowsource = {Empty}
Columnheaders = True
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22926701
that leaves us to this question:
where exactly did you put the code you showed (to fill the listbox)?
0
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22926744
At this moment I use a button which I click to test the procedure (OnClick). In the end the code should be executed OnCurrent (Form).

By the way, I clicked the test button a couple of times while SQL Server Profiler (which is completely new to me, so i might be mistaken) was running, but I did not see that anything was being added to the trace file. When I open another form I see a lot of activity but not when I click the test button...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22926770
I assume you did put a breakpoint to see if that code is really executed?
error handler?
0
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22926816
Nope,  this is all new to me. Could you please advise how to put in a breakpoint?
By errorhandler, do you mean a vba errorhandler in Access?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22926847
>By errorhandler, do you mean a vba errorhandler in Access?
yes

>Could you please advise how to put in a breakpoint?
put the text cursor into the line stSql = ..., and hit "F9".
then, run your form, and click the button.
if the code stops on the line with the breakpoint, you can inspect all your variable, and step on with F8 , or continue run with F5...
0
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22926909
Ok, added an errorhandler but no error is returned.
I set the breakpoint on the stSQL line. The parameters look good. However no rows are returned and no activity as far as i can see on SQL server...
Could it be a setting on SQL server which prevents stored procedures to be called from an application?
On Error GoTo errorhandler
 
Dim dtmDate As Date
Dim intMonteur As Integer
Dim stSql As String
intMonteur = Me.MonteurKolom1
dtmDate = Me.Date1
stSql = "EXEC dbo.GETPLANRECORDS " & intMonteur & ", CONVERT(datetime, '" & Format(dtmDate, "YYYY-MM-DD") & "', 120) "
D1K1.RowSource = stSql 
D1K1.Requery
Exit Sub
 
errorhandler:
MsgBox Err.Number & " " & Err.Description
Exit Sub

Open in new window

0
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22930691
Angel,

I managed to solve the issue. I found an article on the net which pointed me in the direction of using a pass through query in which the connection string and stored procedure are combined.
Thanks for trying to help me out!

Regards, Johan

Public Sub SetSchema(QDef As String, Mntr As Integer, dtmDate As Date, UpdateControl As Control, ctrlOpmerking As Control, UpdateTime As Control)
 
Dim strConnect As String
strConnect = "ODBC;DSN=ICO_PROD;Description=ICO_PROD;UID=Johan;DATABASE=ICO_PROD;Trusted_Connection=Yes"
 
'Bepaal de recordset
Dim intMonteur As Integer
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(QDef)
intMonteur = Mntr
sSql = "exec GETPLANRECORDS @monteur=" & intMonteur & ", @Plandatum='" & Format(dtmDate, "YYYY-MM-DD") & "'"
qdf.sql = sSql
qdf.Connect = strConnect
UpdateControl.RowSource = QDef
UpdateControl.Requery
 
End sub

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

839 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