How do I pass an integer in the sql stmt of Adodc RecordSource?

[michellecole]

I am working with an Adodc in VB with Access as the Back End. There is an ODBC Driver called "BranchErrors".

Here are the settings on the adodc1.property page:

CommandType = 8 - adCmdUnknown
ConnectionString = DSN=BranchErrors
CursorLocations = 3 - adUseClient
CursorType = 2 - adOpenDynamic
RecordSource = **This is where I need help

When I set the Adodc1.RecordSource property with SQL, how do I pass a long integer in the sql statement. If I replace the lngTrayID with a number in the sql statement, all is good - but I need to pass it as a variable. How do I pass a variable containing a number.

On the property page for the adodc I've tried the following sql statments in the RecordSource property:

1)
SELECT * FROM tblDocPrep_BatchScan
WHERE tblDocPrep_BatchScan.IsRecActive = 'Yes' AND tblDocPrep_BatchScan.TrayID = & lngTrayID ORDER BY tblDocPrep_BatchScan.Time;

and get the following Error Message:
[Microsoft][ODBC Microsoft Access Driver] Syntax Error (missing operator) in query expression 'tblDocPrep_BatchScan.IsRecActive = 'Yes' AND tblDocPrep_BatchScan.TrayID = & lngTrayID'.

2)
SELECT * FROM tblDocPrep_BatchScan
WHERE tblDocPrep_BatchScan.IsRecActive = 'Yes' AND tblDocPrep_BatchScan.TrayID = & lngTrayID & ORDER BY tblDocPrep_BatchScan.Time;

Error Message:
[Microsoft][ODBC Microsoft Access Driver] Syntax Error (missing operator) in query expression 'tblDocPrep_BatchScan.IsRecActive = 'Yes' AND tblDocPrep_BatchScan.TrayID = & lngTrayID &'.

3)
SELECT * FROM tblDocPrep_BatchScan
WHERE tblDocPrep_BatchScan.IsRecActive = 'Yes' AND tblDocPrep_BatchScan.TrayID = lngTrayID ORDER BY tblDocPrep_BatchScan.Time;

Error Message:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Maybe I have the other property settings not set correctly?

Thank you to the sql guru,
Michelle

 

[jebenson]

You will have to set the ADODC's RecordSource property in your VB code to be able to pass a variable's contents in with your SQL. Try this:

ADODC1.RecordSource="SELECT * FROM tblDocPrep_BatchScan
WHERE tblDocPrep_BatchScan.IsRecActive = 'Yes' AND tblDocPrep_BatchScan.TrayID = [red]" & lngTrayID & "[/red] ORDER BY tblDocPrep_BatchScan.Time"

ADODC1.Refresh

This will include the current value of lngTrayID in the SQL code.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[johnwm]

I usually prepare my SQL statement as a string so it can be easily checked:

strSQL="SELECT * FROM tblDocPrep_BatchScan
WHERE tblDocPrep_BatchScan.IsRecActive = 'Yes' AND tblDocPrep_BatchScan.TrayID = " & lngTrayID  & " ORDER BY tblDocPrep_BatchScan.Time"
'  Debug.Print strSQL
ADODC1.RecordSource=strSQL
ADODC1.Refresh

You can usually spot the problem directly with the Debug statement (REM removed of course!)

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[michellecole]

Thank you for the feedback. I think the lesson I've learned (from other sources as well) is to limit my use of the adodc control. I usually access the databases with ado with sql in the code - but wanted to allow the user to easily scroll through records on a particular form thus the adodc control. I am using the adodc control - but placed the recordsource sql in code as you suggested and all is good again!

Thank you,
Michelle