June 19, 2003 at 8:16 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 9:45 am
create procedure MyProc
@empid int
as
select * from emp where empid = @empid
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 19, 2003 at 10:43 am
Within your update event (click button event)
Make a call to your proc. passing it your variable value as a param.
First instantiate ADO's command objects to make your proc. call
Dim ObjCmd as ADODB.COMMAND
Set ObjCmd = New ADODB.COMMAND
MyVar = '" & Forms!frmEmployess!txtEmpID & "'
Within your user invoked event
1st do Update
2nd call proc like this:
With ObjCmd
.ActiveConnection = ObjConn --(Your connection string variable)
.CommandType = 4
.CommandText = "UpdateInfo" --(Proc Name)
.Parameters("@EmpID") = MyVar
.Execute
End With
MW
MW
June 19, 2003 at 11:28 am
quote:
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.
Actually, you'll need to use the LIKE operator to make this work, and you will have to use dynamic sql, because, unfortunately, sql server doesn't support wildcards in parameters, only static strings:
CREATE PROC MyProc
@EmpID VARCHAR(10)
AS
BEGIN
IF CHARINDEX(CHAR(37), @EmpID) > 0 BEGIN
DECLARE @sql NVARCHAR(2000)
SET @sql = 'SELECT * FROM tblEmployee WHERE EmpID LIKE ''' + @EmpID + ''''
EXEC sp_executesql @sql
END
ELSE BEGIN
SELECT * FROM tblEmployee WHERE EmpID = @EmpID
END
GO
HTH,
jay
June 19, 2003 at 12:11 pm
Hello jPipes
Your suggestion worked, however, from access I still have to specify the exact EmpID I'm working with.
Exec UpdateInfo @EmpId = '1234'
I have tried the other suggestions, but still unable to substitute a variable for '1234' and make it work.
Thank you all for your help. I am determined to figure this out.
Tracy Ramirez
Tracy Ramirez
June 19, 2003 at 12:15 pm
If you post your code, we'll be able to see the error more clearly...
June 19, 2003 at 12:41 pm
Stored Procedure (This works)
CREATE PROC UpdateInfo @EmpID VARCHAR(10)
AS
IF CHARINDEX(CHAR(37), @EmpID) > 0
BEGIN
DECLARE @sql NVARCHAR(2000)
SET @sql = SELECT * FROM tblEmployee WHERE EmpID LIKE @EmpID
EXECUTE sp_executesql @sql
END
ELSE
BEGIN
SELECT * FROM tblEmployee WHERE EmpID = @EmpID
END
Pass through query (Only works when EmpId = a specific number)
Exec UpdateInfo @EmpID = '" & forms!frmEmployee!txtEmpID & "';
I think everything would work if SQL liked my
'" & forms!frmEmployee!txtEmpID & "' string
Tracy Ramirez
Tracy Ramirez
June 19, 2003 at 12:44 pm
You didn't copy my code quite right. You left out the quotes, which indicate that the @sql variable is a string:
...
SET @sql = 'SELECT * FROM tblEmployee WHERE EmpID LIKE ''' + @EmpID + ''''
EXEC sp_executesql @sql
...
Without the quotes, it won't work.
June 19, 2003 at 1:15 pm
Thank you
Your correct my stored procedure is working properly now. (Sorry I thought it was working before since it didn't give any errors).
So now my pass-through query says:
Exec UpdateInfo @EmpID = '" & forms!frmEmployee!txtEmpID & "';
In a way this works, it does not give me an error, but returns no records. I am working on a form that has a record so there is no reason it should not work.
Unless it has something to do with txtEmpID begin unbound, but I wouldn't think that would matter since I refer to the field as '" & forms!frmEmployee!txtEmpID & "'
Thanks again for your patience with me.
Tracy Ramirez
Tracy Ramirez
June 19, 2003 at 1:21 pm
Do a quick MsgBox(Forms!frmEmployee!txtEmpID) when you execute this function to ensure that there is a correct value in the field at runtime. If there is, then MsgBox out the SQL string as you are passing it rhough to SQL Server. Take that string and execute it in Query Analyzer. If it returns results, then it is something to do with your VBA code...
June 19, 2003 at 1:48 pm
I'm sorry to be such a bother and I appreciate all of your help this far.
I did try that and I do get a value 3526
but when I move this to the analyzer and run it it gives an ! error because of the following string (syntax error I assume)
Exec UpdateInfo @EmpID = forms!frmEmployee!txtEmpID;
Now this is interesting when I do this
Exec UpdateInfo @EmpID = " & forms!frmEmployee!txtEmpID & ";
it displays all of my fields, but not the record (which I have proved holds EmpID 3526)
Tracy Ramirez
Tracy Ramirez
June 19, 2003 at 2:20 pm
OK, you cannot reference a MS Access Form field value in SQL Server Query Analyzer. Are you executing this string exactly?:
Exec UpdateInfo '3526'
June 19, 2003 at 3:16 pm
From my form in access in the after update event I am firing this pass-through query.With the following code.
docmd.RunQuery "UpdateInfo"
This is the code for the pass-through query.
Exec UpdateInfo @EmpID = forms!frmEmployee!txtEmpID;
Of course the forms!frmEmployee!txtEmpID does not have the correct syntax for the stored procedure to translate.
The following code would work, by pulling all employees with a 3 at the beginning of their employee number. But I can't hard code a number because each employee may begin with another number other than 3.
exec updateinfo @EmpId = '3%'
And replacing 3% with forms..... surrounded by single quotes doesn't work either.
Tracy Ramirez
Tracy Ramirez
June 19, 2003 at 3:19 pm
I'm sorry I misunderstood your ?. No I did not execute that string exactly. I will try that.
But I do know in access when I tried to remove @MedRecNum and just put the value in it gave an error stating the variable needed to be filled in. I will try your suggestion in the qry analyzer.
Tracy Ramirez
Tracy Ramirez
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply