DAO parameterized query problem

[jal]

The following code works fine, except when the string called "Explanation" is an especially large string. The line of code in red throws an error when the string is long (probably over 255 chars I'm guessing). The error throws BEFORE I run the query - it throws when I set the parameter value (see the red line). The database column is type "Memo."


Dim qdef As QueryDef
Set qdef = CurrentDb.CreateQueryDef("")
qdef.SQL = "INSERT INTO Ncodes (Code, Explanation) VALUES (@Code, @Explanation)"
qdef.Parameters("@Code").Value = txtCode
qdef.Parameters("@Explanation").Value = txtExplanation.Value
qdef.Execute


What's the easiest way to modify this code as to make it accept long strings?

 

[Ron_dK]

As far as I know the qdef.parameters is read only. That's maybe why you're getting the error.

From access help :

The FieldValue parameter is only valid when adding a Field object to a Record object, not to a Recordset object. With a Record object, you may append fields and provide values at the same time. With a Recordset object, you must create fields while the Recordset is closed, then open the Recordset and assign values to the fields.

 

[jal]

As far as I know the qdef.parameters is read only. That's maybe why you're getting the error.

I have no idea what you mean by "read-only".

if I shorten the string, it works fine. And I've been using parameters in this way in other projects for months now (although with shorter strings). What I mean by shortening the string-to-insert is changing something like this:


qDef.Parameters("@Expl") = "The dog walked accross the yard"


to this:


qDef.Parameters("@Expl") = "The dog walked"


(Well, that's a bad example because neither of these exceed 255 chars in size - but you get the idea). With the short string it works fine.

 

[Ron_dK]

I thought you were copying data from one field to another. Doing this with qdef.parameters could give an error, since qdef.parameters data is read only. Hope that makes sense.

Anyway, what happens if you change :

qdef.Parameters("@Explanation").Value = txtExplanation.Value

to

qdef.Parameters("@Explanation").Value = txtExplanation

 

[jal]

I've already tried that and it doesn't help.

Again, this is not a "syntax" issue. It's a length-of-string issue. I just found at least two other threads on the internet where this issue is raised but I haven't seen a solution as yet.

What a pity as I was beginning to fall in love with DAO. As I recall, one of the people who complained about this was using Access 97. Here we are in Access 2003 and we still can't insert a parameterized Memo?

By the way the exact error is 3271 "Invalid property value." Here's a thread where the issue was raised (but not solved):

http://archives.postgresql.org/pgsql-interfaces/2005-10/msg00003.php

 

[Ron_dK]

Agree Jal.

Found this :
http://www.access-programmers.co.uk/forums/showthread.php?p=799516

and appearantly this issue can't be resolved.

Sorry for not being any kind of help.

 

[Banana]

Just to correct something: You certainly can read and write to the parameters... I do that all time, in fact.

However, looking at jal's code, it looks more like ADO and not what I usually do for DAO. Jet is a little different from most RDMBS in that you can declare the parameters and assign a data type. I wonder if the parameter being created on fly may not have the desired type you need for this operation.

This is how i usually do this:

Parameter Query SQL:

PARAMETERS ifoo INT, tbar TEXT, mbaz MEMO;
SELECT foo, bar, baz FROM iter WHERE foo = [ifoo] AND bar = [tbar] AND baz = [mbaz];

Code calling the query:

Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Dim i As Long
Dim s As String
Dim t As String

i=1000000
s="This is a cute and flurry dog.
t="We soooo love pink and fluffy snugglebunnies!"

Set qdf = CurrentDb.QueryDefs("MyParameterQuery")

With qdf
   .Parameters("iFoo") = i
   .Parameters("tBar") = Left(s, 255) 'Truncate the string to 255 maximum
   .Parameters("mBaz") = t 'No truncate is needed here
   Set rst = .OpenRecordset
End With

In your case, you seem to be wanting to use a temporary query. I would probably want to declare the parameters inside the SQL with the data types as well, rather than creating parameters in VBA, just to be sure we're using Jet "natively" for a lack of better description. Jet is different from most RDBMS, so I treat it thus.

See if this fix the problem. I did a quick test with a bogus query and it worked with a memo, but didn't try in VBA.


Also, a word of caution- Jet's datatypes are not what we may always expect it to be in standard SQL. Refer to VBA's help file on "Comparison of Data types" for keywords and possible synonyms to make sure we're talking about same thing between different libraries.

 

[Banana]

Addendum:

I opened up a scratch database and tried this code out. It doesn't like the MEMO keyword, but LONGTEXT works fine.

Private Sub foo()

Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.CreateQueryDef("", "PARAMETERS mtest LONGTEXT; SELECT Testing FROM test WHERE Testing=[mtest];")

qdf.Parameters("mtest") = "Alice is a dolt. She will be against the wall when the revolution comes."
    
With qdf.OpenRecordset
    Debug.Print .Fields(0)
End With

End Sub

Debug output:

Alice is a dolt. She will be against the wall when the revolution comes.

 

[jal]

Banana, looks like you are doing SELECT queries. I am getting errors trying this with an INSERT query. Still trying to adapt your idea to my INSERT but it's almost time for me to leave home for work...

 

[jal]

Well I got the syntax right, finally (turned out the PARAMETERS clause doesn't like the @-prefix for param names such as @Code).

Dim qdef As QueryDef
Set qdef = CurrentDb.CreateQueryDef("")
qdef.SQL = "PARAMETERS paramCode TEXT, paramExplanation LONGTEXT; INSERT INTO Ncodes (Code, Explanation) VALUES(paramCode, paramExplanation)"
qdef.Parameters("paramCode").Value = txtCode
qdef.Parameters("paramExplanation").Value = Me.txtExplanation.Value

But this is still generating the original error - I still have to shorten the string to get this to work. I tried using "Memo" instead of LONGTEXT but this threw a syntax error.

 

[jal]

Agree Jal.

Found this :
http://www.access-programmers.co.uk/forums/showthread.php?p=799516

and appearantly this issue can't be resolved.

I am inclined to agree at this point...Maybe it would work if I used ADO instead of DAO.

 

[Banana]

Sorry about that @ thingy. I forgot to mention that @ doesn't really work with Jet. As I said, it's a bit weird compared to other RDBMS.

Anyway, I copied the table structure and ran it in VBA on my scratch database. There was no problem at all running the code, inserting the record with "Hello, World!" and "There's 99 bottles on the wall... One bottle fell off, now there's 98 bottles of beer on the wall".

I'm thinking there's something else factoring in...

What version are you using, and service packs if any? What happens if you actually created a query in the query builder and run it there?

 

[jal]

Banana, you are probably testing a small string. My code works fine with small strings. Long strings are the problem here - that's the point of this thread.

 

[Banana]

Didn't you see my test string:

"There's 99 bottles on the wall... One bottle fell off, now there's 98 bottles of beer on the wall"

That string works just fine in my test...

 

[jal]

What version are you using, and service packs if any?

Access 2003 with the latest office service pack as far as I know. And Jet service pack 8.

What happens if you actually created a query in the query builder and run it there?

Maybe I'll try that later, but I really need to run this from VBA, as far as I can see. I did try creating a query object like this:

Set qdef = CurrentDb.CreateQueryDef("qryINSERT")

but that had the same result as this:


Set qdef = CurrentDb.CreateQueryDef("")

meaning it works fine only with small strings.

 

[jal]

Didn't you see my test string:

"There's 99 bottles on the wall... One bottle fell off, now there's 98 bottles of beer on the wall"

That string works just fine in my test...

Ok, but are you showing me the whole string (you've got an ellipsis in the middle). Because what you posted is not a long string - it's a lot less than the 255-char threshhold.

 

[Banana]

Okay, egg's on my face. I thought it was >255 characters, but apparently not.

I tried again with longer string, and got same error as you did. Googling a bit, it sounds like other has encountered the same thing.

A workaround would be to use recordset, which should be okay if you're just inserting one record at a time, as it can accept longer string.

That really sucks.

 

[ak_ls]

Jal

Not sure if my understanding is correct because I have not tested it myself.

When a text field on a form is assigned to a qdef parameter, the type of qdef parameter is set to Text that will not allow string size of more than 255 chars. This happens because the size of the string is not checked while determining the source type.

You may try assigning a field of Memo type from a recordset (RS) instead of the textbox on a form. If it works, my assumption is currect.

qdef.Parameters("paramExplanation").Value = RS!Explanation

 

[Banana]

Well, the odd thing is that this is valid:

PARAMETERS foo TEXT(65535);
SELECT bar FROM iter WHERE bar = [foo];

Of course, it won't accept any >255 characters, either way. It's as if the support is there, but someone forgot to set the parameter's Value property to allow for long string.

 

[jal]

Yes, I realize that a recordset would work,but I was hoping to avoid recoding, and I did generally love the QueryDef object (until now). Well, at least there seems to be a consensus that the qDef won't accept large strings. Weird. Thanks everyone for looking into it.