UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Form And SQL Sp Can't Get 2nd Parameter Syntax, SQL Server 2012    
 
   
ohiogto1969
post Nov 9 2017, 04:38 PM
Post#1



Posts: 134
Joined: 8-March 13



Hello All,
I am venturing to learn a little more about interfacing Access with MS SQL. My first journey is probably simple for most but the task has been daunting for me. What I have is an access sub that uses a combo box to populate a simple two field form. My problem is I can't find the correct syntax to pass a second parameter. I know the way I am going about it (using first and last name for search is odd) but I still would like to accomplish this task prior to moving on to others.
Here is the line of code where I wish to add a second parameter.
CODE
strSQL = "exec Test @strFirstName = '" & strFName & "'"


This is the whole procedure.

CODE
Private Sub cboSearch_AfterUpdate()
    Dim db As DAO.Database
    Dim qf As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strFName As String
    Dim strLName As String
    
    strLName = Me.cboSearch.Column(1)
    strFName = Me.cboSearch.Column(2)
    
      
    strSQL = "exec Test @strFirstName = '" & strFName & "'"
    Set db = CurrentDb()
    Set qf = db.CreateQueryDef("")
    qf.ReturnsRecords = True
    qf.Connect = "ODBC;DRIVER=SQL Server;SERVER=192.168.0.5;UID=testserver;PWD=mypassword;DATABASE=GSql;LANGUAGE=u
s_english;Regional=Yes;"
    qf.SQL = strSQL
    
'    Debug.Print strSQL
    Set rs = qf.OpenRecordset()
    
      
    Do While Not rs.EOF
        Me.txtFirstName = rs!strFirstName
        Me.txtLastName = rs!strLastName
          'Debug.Print rs!strLastName & ", " & rs!strFirstName
    rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    Set qf = Nothing
    Set db = Nothing
End Sub


Thanks for looking,
John
Go to the top of the page
 
GroverParkGeorge
post Nov 9 2017, 05:58 PM
Post#2


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


You're executing a stored procedure on the server. That SP requires two parameters.

The general syntax to do that in SQL Server would be:

Exec spYourSPNameGoesHere parameter1, parameter2



So, the SQL in your Passthru query will need to be the same:

"exec Test @strFirstName = '" & strFName & "', @strLastName = '" & strLName & "'"

I generally do NOT pass the entire parameter, though:


"exec Test '" & strFName & "', '" & strLName & "'"

--------------------
Go to the top of the page
 
ohiogto1969
post Nov 9 2017, 06:59 PM
Post#3



Posts: 134
Joined: 8-March 13



Thanks very much! I will try this at work tomorrow.
Go to the top of the page
 
ohiogto1969
post Nov 10 2017, 01:42 PM
Post#4



Posts: 134
Joined: 8-March 13



Please help smile.gif
I am still having a problem. I can not get this to work. Right now I am just trying to get this to work with a passthrough query and I am getting no results back.

Here is my stored procedure.

CODE
USE [GSql]
GO

/****** Object:  StoredProcedure [dbo].[Test2]    Script Date: 11/10/2017 1:36:11 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:        <John DeFalco>
-- Create date: <Nov. 1 2017>
-- Description:    
-- =============================================
CREATE PROCEDURE [dbo].[Test2]
    -- Add the parameters for the stored procedure here
    
    @strFirstName nvarchar (50) = Null,
    @strLastName nvarchar (50) = Null
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT strFirstName, strLastName
FROM dbo.tblCustomers
WHERE strFirstName = @strFirstName And strLastName = @strLastName;
END


GO


Here is what I have in my passthrough query.

CODE
exec Test2 'Anderson', 'Rob'


Any help is appreciated,
John
Go to the top of the page
 
LPurvis
post Nov 10 2017, 01:59 PM
Post#5


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


PMFJI...

But you have someone actually called "Anderson Rob"?

:-s

--------------------
Go to the top of the page
 
ohiogto1969
post Nov 10 2017, 02:19 PM
Post#6



Posts: 134
Joined: 8-March 13



Yes I do have that customer in my database. What is very strange is, if I put my name in "exec Test2 'DeFalco', 'John'" it returns a good record in the PT query. any other name does not work. iconfused.gif
Go to the top of the page
 
LPurvis
post Nov 10 2017, 02:22 PM
Post#7


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


So just to be clear...
You have two records in your table who have last names of "Rob" and "John" respectively?

--------------------
Go to the top of the page
 
ohiogto1969
post Nov 10 2017, 02:22 PM
Post#8



Posts: 134
Joined: 8-March 13



I think I have the parameters in the wrong order.. let me check for sure....
Go to the top of the page
 
ohiogto1969
post Nov 10 2017, 02:24 PM
Post#9



Posts: 134
Joined: 8-March 13



That was it the parameters were mixed up. Thanks all for the help. Time to move on to my next test. Yippee!
Go to the top of the page
 
LPurvis
post Nov 10 2017, 02:32 PM
Post#10


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


thumbup.gif

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 12:15 PM