Execute stored procedure on SQL server

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

LVL 1
jrameuwissenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
> SGH_PlanningHead.Planning_Datum = @Plandatum
does your field .Planning_Datum include some time information?
0
jrameuwissenAuthor Commented:
Jip. When I look at the column in SQL server, the time information is always 00:00:00.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jrameuwissenAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you show if you can run the stored proc directly in sql with parameters getting the correct results?
0
jrameuwissenAuthor Commented:
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
jrameuwissenAuthor Commented:

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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
jrameuwissenAuthor Commented:
Ok, format is MM-DD-YYYY (european).
I am not sure what you mean by 'contain after assignment'?
0
jrameuwissenAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the variable stSql, after the line stSQL = " exec ...", what it the value?
0
jrameuwissenAuthor Commented:
Value of stSQL is :
EXEC dbo.GETPLANRECORDS 68, CONVERT(datetime, '2008-11-10', 120)
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that looks fine.
does it return the same data when you run that sql directly?

0
jrameuwissenAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
jrameuwissenAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that leaves us to this question:
where exactly did you put the code you showed (to fill the listbox)?
0
jrameuwissenAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I assume you did put a breakpoint to see if that code is really executed?
error handler?
0
jrameuwissenAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
jrameuwissenAuthor Commented:
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
jrameuwissenAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.