Stored Procedures/Parameterized Queries

[dougcranston]

I have a question, that may be appropriate for this forum.

============
Is their a length constraint for using Access Stored Procedures/Parameterized Queries where one of the fields is passing in/through a MEMO field?
============

I "found" Access 2000 Stored Procedures/Parameterized queries recently and have bought into them.

However, the "simple" approach I have been using surfaced a problem that I have tried researching and I THINK I have the answer but I am not sure.

Sample call:

oConn.Execute(txtMySql3, null, adExecuteNoRecords + adCmdStoredProc );

where the query is defined as:

txtMySql3 += ("sp_AddReport '");
txtMySql3 += (strASIRNo + "', '");
txtMySql3 += (strRegionCd + "', '");
txtMySql3 += (strRptFName + "', '");
txtMySql3 += (strRptLName + "', '");
txtMySql3 += (strDescription + "', '");
txtMySql3 += (intView + "'");

[NOTE: strDescription is a MEMO field in the database.]

As you can see, I am not building the "normal parameters" to pass. Up till now that has not been a problem.


What I found was my app works fine as long as the MEMO field contents is less than 255 chars.

More than that I get a range of wierd errors.. But as soon as I reduce the MEMO fields contents to 255 or less it works.

Would appreciate any input you might be able to offer.

Thanks in advance.

Doug Cranston

 

[melaniecarr23]

txtMySql3 += (strDescription + "', '");

You mention Description is a memo field. I don't know a ton about this stuff, but it's almost like somehow you are trying to process a memo field as a text field and it gives errors because text fields can't be more than 255 characters. Is that the case?

Melanie

 

[dougcranston]

Melanie,

Thanks for the post.

Data on Access Stored Procedures/Query Defs is, atleast from my research very sketchy at best.

Some additional testing this AM has proven that the ASP app will in fact allow more than 255 characters to post to the Access Database via the existing Stored Procedure.

However, I can still get it to fail with the users original text until I cut it back to less than 255 chars.. So at this time I am not sure what the problem is. Tw one of my associates and it may be some high bit characters that ADO or Access does not like that is not visible within the page or within MS 2000 Server Application event log file..

Will continue my search.

Again thanks for your taking the time to respond.

DougCranston