June 19, 2003 at 8:14 am
I have an access form that requests the user to enter a date value in an unbound field.
When this date value is edited or updated I want my SQL stored procedure to fire. First the stored procedure needs to know which record I'm working with which is where my problem is.
In access I can refer to a field my saying:MyVariable = '" & Forms!frmEmployee!txtEmpID & "'
MyVariable = 1234
In SQL I don't know how to set MyVariable = """""""""""""""""
I have tried:
CREATE PROCEDURE UpdateInfo AS
Declare @EmpID int
Set @EmpID = '1%'
Select * from tblEmployee where EmpID = @EmpID
This will return all Employee ID's that begin with a 1, but I'm only looking for the EmpID of the record I'm working with.
Any assistance would be greatly appreciated.
Thank you,
Tracy Ramirez
Cape Fear Valley Health System
Tracy Ramirez
Tracy Ramirez
June 19, 2003 at 8:53 am
Hi Tracy!
In a stored procedure, you can have both input variables and output variables. If you modify your stored procedure such as:
CREATE PROCEDURE UpdateInfo
@EmpID int
AS
Select * from tblEmployee where EmpID = @EmpID
Return(0)
GO
Your @EmpID now becomes an input variable. Your stored procedure can now be called with something like the following...
Exec UpdateInfo @EmpID = '1432'
This should give you the records pertaining to employee id 1432.
I hope this helps shed some light on the problem...
Michael
Michael Weiss
Michael Weiss
June 19, 2003 at 8:54 am
CREATE PROCEDURE UpdateInfo @Empid Int AS
Select * from tblEmployee where EmpID = @Empid
go
SNIPED!!!!!!!!!
should get you your required result.
Edited by - nazim on 06/19/2003 08:57:29 AM
June 19, 2003 at 9:32 am
Thank you for your help.
Please forgive me for asking another ? (I'm a beginner)
This is going to work, however in my pass-through query in access I want to say
Exec UpdateInfo @EmpID = '" & field name & "'
Because the field value will constantly change. My syntax is not correct for my stored procedure to translate.
How do I set @EmpID to = a variable
Thank you for getting me this far. Any suggestions would be greatly appreciated.
Tracy
Tracy Ramirez
Tracy Ramirez
June 19, 2003 at 9:48 am
Hi Tracy...
I think you had it in your first post, didn't you?
Exec UpdateInfo @EmpId = "'" & Forms!frmEmployee!txtEmpID & "'"
I am not sure, but you could try it without the single and double quotes as well...but I think you will need them.
Let us know if this works for you...been a while since I have done anything with Access.
hth,
Michael
Michael Weiss
Michael Weiss
June 19, 2003 at 10:02 pm
in your pass-through query, you should say
Exec UpdateInfo '1432'
and that should work.
You have to set up properties for the connection in your pass-through query, do not forget that.
June 19, 2003 at 10:11 pm
sorry I did not read well. You cannot really do that in a pass-through query. Access is limited, you should know.
However, what I often do , is set up a parameter table with fields like:
ParameterID, Parameter description, ParValue
This table, you link it with a form in Access, so that you can have all your parameters in a form in Access or ASP/PHP, whatever you want. It is extremely convenient. My clients usually loves that.
In your stored procedure, you do that:
(I would had maybe a begin transaction, rollback and commit one if you are not sure it will be w/o error all the time; as it is purly a select stored proc, it is not really necessary as it does not affect the database)
CREATE PROCEDURE UpdateInfo
@ParameterID int
AS
-- Find the parameter in the parameter table
Declare @Parvalue as int
set @Parvalue = (select ParValue from tblParameters where ParameterID = @ParameterId)
Select * from tblEmployee where EmpID = @ParValue
go
June 20, 2003 at 8:42 am
If it is not possible to run a pass-through query as:
Exec UpdateInfo @EmpID = MyVariable
and you have to state exactly
Exec UpdateInfo @EmpID = '4321'
How would you pass a value (that's always changing) from access to a stored procedure?
Not unless you can't pass a changing value, but certainly people pass variables back and forth from SQL to Access I would think.
Please excuse all of my ?'s. I'm learning as I go. Thank you all for your patience.
Tracy Ramirez
Tracy Ramirez
June 20, 2003 at 9:34 am
To change the values in the pass-through query you will have to use a little bit of VBA. You will need to set up a QueryDef object and set your pass-through query to this object. Next, edit the .SQL parameter with the 'Exec UpdateInfo '" & Forms!frmEmployee!txtEmpID & "'. After this, execute the QueryDef object.
June 20, 2003 at 10:47 am
'in Access, in one of the events for the txtEmpID text box control
dim MyEmployeeID as long
MyEmployeeID = frmEmployee!txtEmpID
dim sql as string
sql = "UpdateInfo " & CStr(MyEmployeeID) 'no quotes because @empid is int, not varchar
'following calls a stored proc that returns a single result set
Dim qdf As dao.QueryDef
Set qdf = CodeDb.CreateQueryDef("")
qdf.Connect = SQLConnectString
qdf.ReturnsRecords = True
qdf.sql = sql
Set rs = qdf.OpenRecordset
You also need a way to get the SQLConnectString used for the stored procedure. this is used to pass to qdf.connect property
'in Access
Function SQLConnectString() as string
'tblEmployee must be a linked table in the access mdb
SQLConnectString = CodeDb.TableDefs("tblEmployee").Connect
End Function
--in SQL Server
CREATE PROCEDURE UpdateInfo
@EmpID int
AS
Select * from tblEmployee where EmpID = @EmpID
RETURN
June 20, 2003 at 10:53 am
Thank you so much. I believe that solved the problem.
I didn't even think about trying a QueryDef
Thanks for all of the help.
Tracy Ramirez
Tracy Ramirez
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply