Calling a stored procedure with a parameter. 2

[wakgtech]

I have a stored proc in SQL express:

ALTER PROCEDURE [dbo].[getValues]
@atty_no char(5)
AS
SELECT (attorney_no) FROM TBL1 WHERE attorney_no = @atty_no

I want to call that SP from MS Access and assign a value to the parameter. how would I go about that? Everything I have tried so far has failed...

Thanks

 

[PWise]

what have you tried

 

[wakgtech]

getValues(08322)
getValues('08322')
getValues 08322
getValues(myVar)

 

[PWise]

create a pass through qurey

getValues '08322'

 

[dhookom]

I would use a little DAO code to change the SQL property of an Access pass-through query. Assuming you have a text box (txtAtty_no) on your form and a p-t query (qsptGetValues):

Currentdb.QueryDefs("qsptGetValues").SQL = "EXEC GetValues " & Me.txtAtty_no

If Atty_No is not numeric, you will need to pass in some single quotes.

Duane
Hook'D on Access
MS Access MVP

 

[wakgtech]

//create a pass through qurey

Pwise, I tried that and it did not work.

dhookom, do I need to use anything else with that DAO? Other statements? Or would that line be all that I need?

 

[dhookom]

wkagtech,
Did you try the code? You need to set a reference to the DAO object library to get the code to work.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

The usual way to execute a stored procedure is o use an ADODB.Command object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wakgtech]

Thanks for the assistance all! I think I am getting somewhere. I created the ADO command object and I can connect and pass the value into the sp. However I am now getting the "type-mismatch" error when I run the procedure. I know that the sp's parameter is set to "varchar(5)" and it works from SQL. I have declared the parameter as varchar and I dim the variable as string and set it to the parameter. However I am still getting the message. Ideas? I appreciate all the help!

Thanks!

 

[PHV]

What is your actual code and which line raises the error ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wakgtech]

myValue = "391"
strCS = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Office ContributionSQL;Data Source=ADMPCLKVCGR0\SQLEXPRESS"

Set cn = CreateObject("ADODB.Connection")
cn.Open strCS

Set cnCmd = CreateObject("ADODB.Command")
cnCmd.ActiveConnection = cn
cnCmd.CommandType = 4
cnCmd.CommandText = "getEmployee"
cnCmd.NamedParameters = True

Set cnParam = cnCmd.CreateParameter("@EmpNo", 18, 1, 3, Trim(myValue))
cnCmd.Parameters.Append cnParam
Set cnRecordset = cnCmd.Execute

 

[wakgtech]

..or maybe I am looking at this all wrong (sorry, sp is very new to me) after retreiving the cnrecordset, how do I view the results? I have tried a message box, (msgbox cnRecordset(it should only retreive 1 field of 1 record)) but that is when we get the message. If i do this "msgbox err.number" I get "0" and the code proceeds to finish. Am I missing something here?

O

 

[PHV]

What about this ?
MsgBox cnRecordset.Fields(0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wakgtech]

THAT IT IT!!!!!!!!!!!! How could i have been so blind??!!! Thank you thank you! This is cool stuff, I will use this all the time now!


Thanks again!

 

[dhookom]

Cool stuff" always deserves a star of thanks ;-)

Duane
Hook'D on Access
MS Access MVP