adp Parameter
-
Hi
I've just made the changeover from mdb to adp. I'm finding somethings surprisingly problematic. I am following the book religiously, but some things don't seem to work. I can't, for instance, get a parameteter in a sp to work from a form.
I have the Recordsource of a form as:
exec sp_Students_Team
This works fine, by the way, until I enter a parameter in the form.
The sp works fine in Query Analyser, and it will accept the given parameter - but, as I say, it won't accept a parameter on the form. The sp is as follows:
CREATE PROCEDURE dbo.sp_Students_Team @CY varchar(15) ="%"
AS SELECT DISTINCT tblStudents_Current.*, tblStudents_Courses_Current.CourseYear
FROM tblStudents_Courses_Current INNER JOIN tblStudents_Current
ON tblStudents_Courses_Current.Person_Code = tblStudents_Current.Person_Code
INNER JOIN vwSysUserTeam_tblUsers
ON tblStudents_Courses_Current.CollegeTeam = vwSysUserTeam_tblUsers.Team
WHERE tblStudents_Courses_Current.CourseYear like @CY
ORDER BY tblStudents_Current.Surname, tblStudents_Current.Forename
The book says that I can use the Form's Input Parameters property as:
@CY varchar(15) = [Forms]![frmAcYear]![AcYear]
The parameter looks up from a field in another open form. However even if I write in '04/05' I get no joy.
When I open the form it returns the error message: Bad Query Parameter '@CY'.
Hope someone can give me a pointer here.
Thanks
Paul
-
Do you mind to try:
@CY char=[Forms]![frmAcYear]![AcYear]
Note: NO SPACE between "char" and "=" nor between "=" and Form Field
* Noel -
Thanks Noel
In the InputParameters property I tried:
@CY varchar=[Forms]![frmAcYear]![AcYear]
and it gives the error message:
'procedure sp_students_team expects parameter CY, which was not supplied'
I tried a different form and field for the parameter, but with no success. It seems it cannot find the parameter??
However, if I delete the InputParameters property and make the form Recordsource:
exec sp_Students_Team @CY='04/05'
instead of just
exec sp_Students_Team
then it works fine.
I been struggling with this for hours, and I can't get my brain around what is happening - or, rather, what isn't happening.
I'm using Access 2003 if that means anything, and have just installed all Office updates to no effect.
Many thanks again
Paul
-
If you put "EXEC" in the recordsource, then you will have to supply the parameters on the recordsource line. In this case, I think the InputParameters will be ignored.
To make you form parameters work, just delete the word EXEC from the recordsource. Then it will use the InputParameters property instead.
Alternatively, you could try "EXEC MySP ?" as an experiment. It just might work.
(ADPs are great, but the documentation is really bad!)
HTH,
Rich
-
Thanks, Rich
This flies in the face of everything I have been reading - but thankfully it works! And I was beginning to thing it never would...
Can I ask a further question - a similar thing -which you described in an earlier post - but again I can't make work.
You indicated that you can put something like the following in a forms Recordsource:
Select * from tblStuext Where AcademicYear=?
and in the InputParameters:
? varchar=[Forms]![frmAcYear]![AcYear]
Is there a similar easy error here?
Also, you mentioned IIRC and that "This will cause Access to create a prepared statement that reuses the execution plan for subsequent queries". Could you explain a little further what you mean by this.
Very many thanks again
Paul
-
Hi Paul,
I'm glad you got the SP to work. Regarding your parameterized SQL, it looks basically correct, so please provide some more details. For example:
--Error message returned
--Variable type for academic year in your table (datetime vs char/nchar/varchar....)
--ResyncCommand property, if any
--UniqueTable property, if any
--Double check that everything is spelled correctly!
--Could there be a conflict in formatting a datetime field or value?
--What happens if you enter the year directly in the SQL statement, instead of InputParameters?
Regarding the parameterized queries, IIRC, SQL Server Books Online (BOL) discusses prepared statements that are created when you use "?" placeholders in SQL statements. Apparently, it caches the query plan for these parameterized statements, to provide more efficient execution when the SQL is next encountered. After some period of time, the query plans are discarded, though. It's sort of like having a temporary stored procedure in terms of efficiency. For most simple SQL statements though, I doubt you will notice much of a difference. It's nearly hopeless trying to find Access documentation that discusses this. You really have to rely on the SQL Profiler to figure it out.
(
--Random Hints that probably won't help right now:
1) I always add a resync commmand to all ADP forms:
"Select * from tblStuext Where tblStuextPrimaryKey=?"
this sometimes seems to help when updates/Inserts/deletes produce errors.
2) I always add the UniqueTable, even if there is only one table involved. If you have a "? in your SQL, you will not be able to add the UniqueTable, so you would need to change the "?" to any valid value (This will cause your Input parameters to be deleted (rude Access behavior)). Then set the UniqueTable. Then change your SQL to use the "?" Then reset the InputParamaeters. It's easier to set all of these things in your Form's Open Event, in VBA code. Also, I store the InputParameters in the Form's tag value, since it is deleted every time you change the recorsource!!!
3) It often helps to have timestamp fields in all your tables, to better allow Access to detect update conflicts.
4) Make sure you test all of your forms with Inserts, updates, and deletions of one or more records, as many errors don't show up with the initial select statement. Also, test with multiple users working with the same record. Remember that Access generates its own T-SQL statements for these operations, and sometimes you need to help it with things like timestamps, UniqueTables, and Resync commands.
--END of random hints)
HTH,
Rich
-
Hi Rich
Thanks for a great answer. Your completeness really helps.
I have worked through the field types and variables to ensure there is no problem here (all now Char(5)), but I am still getting an error message. I have, and will continue to use Resync and Unique table as you suggest.
Error Message:
'syntax error or access violation'
Recordsource:
Select * from tblStuext where AcademicYear=?
Inputparameters:
? char =[Forms]![frmAcYear]![AcYear]
Resync Command:
Select * from tblStuext where person_code=?
Unique Table:
tblStuext
I have tried different spacings in the Input parameters, but with no success. The current spacing seems to be the correct one - as described:
?spaceCHAR=space[Forms]![frmAcYear]![AcYear]
Maybe the problem is the access violation?
What I have done in the meantime is to change the query to a sp - just to experiment. This is fine, but I now have a problem with synchronising the main and subforms. I just can't believe that I keep getting stuck on what should, I guess, be relatively simple stuff.
My Parent and Child fields in my old mdb linked the mainform and the subform on the field Person_code. This is not the field used as the parameter in the sp - the only circumstance which my book seems to describe - and can provide some way to produce the link.
I notice that the sp properties allow Parent and Child fields to be entered - but the descriptions in Help just don't (help). Can I create the link here?
I hope you can give me another nudge (or a kick) in the right direction.
Many thanks again
Paul
-
I forgot to mention that if I make the Recordsource:
select * from tblstuext where academicyear='04/05'
it works fine......
Paul
-
Hi Paul,
Last things first:
To link the subform, you should be able to simply add the linking field names in the subform properties. Access should read the fields from your SP definition, as long as you are returning a simple table(s), and your sp does not use dynamic SQL. (Remember, you can also use views and functions in the recordsource.) If you still have problems linking your subforms, you can always link the subform by setting its recordsource in the Current event of the Parent form, or better, using an input parameter in the subform that looks up your key field on the parent form (? int = Forms!MyParentForm!MyKeyField). The latter approach works quite well for me. (This is another undocumented trick that I've discovered.)
Now for your SQL problem in the Parent form:
1)Make sure you have all the proper permissions set (you probably do.
2) When you use SQL with the "?" parameter, are you able to see the field names in the in property sheet entries for ControlSource? If you can see the field names, the Access is already processing your query, and the error is probably with your parameter definition. If you can't see the field names, you must have some kind of SQL error in the recordsource. Please try it, and let me know.
3) Remove the brackets around the word [Forms]. You probably had an errror when you first entered the line, so Access thought it was a form or field name. Normally, this keyword should not have any brackets - I'm not sure if it will mess things up.
HTH,
Rich
-
Hi Rich
Thanks again for your reply.
I have managed to get the subform working and synchronised using you undocumented trick. I added another parameter to the sp and passed the parameter from the Inputparameters. Excellent.
Putting the linking fields into the sp didn't work. I guess that is because the sp contains a parameter (@..).
The Recordsource:
select * from tblstuext where academicyear=?
gives me a full field list. So far so good.
However, the Inputparameter:
? char = Forms!frmAcyear!Acyear
returns the same error message:
'Syntax error or access violation'
Interestingly enough, the sp Input parameters work both with the square brackets on the forms!field and without.
As the subform works with the Recordsource:
select * from tblstuext where academicyear='04/05'
I imagine that should mean that permissions are OK? Should I need further permissions to cope with the '?' ?
Thanks for your great help here. Just using the available literature, I would be really struggling - and certainly wouldn't have got as far as I have...
Paul
-
OK, good so far.
It looks like your permissions are fine.
I suspect that perhaps your field is returning a value from your form field(e.g. in a date format) that is misinterpreted by Access, so that Access is misformatting the parameter. For example, omitting the single quotes or something like that.
I think you will need to fire up the SQL Profiler to look at the SQL statement that Access is sending to SQL Server.
You might try using nchar or varchar or nvarchar or datetime and see if that works. You could also test a literal value as follows: ? char = '4/05'
HTH,
Rich
-
Hi Rich
I tried all types: nchar, nvarchar datetime etc. No go, I'm afraid. I also tried in the Inputparameters:
? char = '4/05'
and
? char = '04/05'
It put up a standard Enter Parameter dialog (i.e. enter the parameter for '04/05') - but it wouldn't accept a parameter.
I created a new form and tried to lookup a field in that - with the same result (same error message 'Syntax error or access violation').
I haven't really delved into Profiler, but have tried what seemed correct. This didn't uncover any error messages - though maybe I am not doing it in the right way for this problem...
I can't quite understand how the @Cy etc works for sp but not here. I guess there must be some logic somewhere. I can normally work this sort of thing through............
Many thanks
Paul
-
I just created a test database to search a text field (nvarchar) using an unbound form search field. I set up the InputParam exactly as you are doing, and it works perfectly. It works even if I change the text type to char or varchar etc. It also works fine with embedded slash characters
Therefore I suspect that there is something funny about the data type in your table, that is causing a type mismatch in the SQL statement. Are you sure the table is using a character type field?Also, are you sure that you have a unique primary key set up properly? Also, try removing your resync and UniqueTables properties. Perhaps there is an error there.
I bet it will turn out to be somethng really obvious, once you finally nail it down! Of course it could always be a bizarre Access bug.
You can try a test: create a new test table with an integer auto-increment primary key, two varchar fields, and a timestamp. Add about 10 rows of test data. Then create a simple form with InputParameters to search for your values based on a text field.
HTH,
Rich
-
One more thing: Instead of typing in your SQL, create it in the Query designer on the property sheet, just add a ? in the criteria box. I wonder if there are some hidden characters somewhere.
-
Hi again Rich
Thanks so much for your help here. I'm reading reams of stuff - but am getting more and more bogged down - really surprising!
I went through all scenarios you suggested - but no use. I created a test adp and got that to work in all the ways you suggested. So, I went back to the drawing board with the form and subform this morning and rebuilt them. Only then would it work - and to naked eye they are exactly what I had before. Maybe the upsizing caused a glitch?? The problem with learning new software is that you never know whether it is the software or yourself that is causing the problems. Also, as you say, the literature on the subject doesn't seem either to work or fit the issues.
Anyway I can't tell you how much I appreciate your support as I can feel time running out to complete this.....
I was hoping to be off and running, but unfortunately I now have another issue with combo boxes. I don't know if there is a problem you can quickly spot.
I tried what it says in the book. I created an sp (which again has a parameter - [which will again lookup the year from the same form]) called sp_lstTutorGroup as
CREATE PROCEDURE dbo.sp_lstTutorGroup(@CY char(5))
AS SELECT dbo.tblTutorGroup.TutorGroup, dbo.tblTutorGroup.CollegeTeam
FROM dbo.vwSysUserTeam_tblUsers INNER JOIN
dbo.tblTutorGroup ON dbo.vwSysUserTeam_tblUsers.Team =
dbo.tblTutorGroup.CollegeTeam
WHERE (dbo.tblTutorGroup.TutorGroup <> 'none') AND (dbo.tblTutorGroup.TutorGroup IS NOT NULL)
AND (dbo.tblTutorGroup.CourseYear = @CY)
ORDER BY dbo.tblTutorGroup.TutorGroup, dbo.tblTutorGroup.CollegeTeam
GO
In the On Click event of the combo box called Tutorgroup I added:
TutorGroup.RowSource = "exec sp_lstTutorGroup'" & Forms!frmacyear!AcYear.Value & "'"
I tried it without the exec - but it didn't work either.
I also tried a 'trick' from the book and added a field to the form called CY - which I gave the
value '04/05'. This, it suggests will be picked up by the sp, which has the parameter @CY for the courseyear
TutorGroup.RowSource = "exec sp_lsttutorgroup'" & CY & "'"
Again no joy.
Not in the book, but on the Microsoft Support site I added to On Enter:
Me.TutorGroup.Requery
Again no joy.
If you have any ideas I would be grateful to hear them.
Paul
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply