DAO parameterized query problem

[ak_ls]

I checked the type of qdef parameter using

MsgBox qdef.Parameters("@Explanation").Type

It displays 10 which is type Text. I also checked the Access help for type Text -

"A field data type. Text fields can contain up to 255 characters or the number of characters specified by the Size property of the Field object, whichever is less. If the Size property of the text field is set to 0, the text field can hold up to 255 characters of data."

I also tried assigning -

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

and

qdef.Parameters("@Explanation").Value = RS!fld2

where text1 is string type and fld2 is memo field.

The result is the same.

qdef.Parameters("@Explanation").Type is 10 for string size of upto 255 chars. If size is more then 255, it gives the error as mentioned by jal.

It show that Memo or String type is not supported by qdef property parameters. It supports only text type. Memo or String type gets converted to Text type if the size is up to 255. Beyond that it generates error.

This seems to apply to qdef parameters only. I have not yet seen other cases.

 

[jal]

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

That code gave me the same error. I tried

qdef.Parameters("@Explanation").Value = rs("Test")

where "Test" is a Memo column populated with a long string.

 

[jal]

I checked the type of qdef parameter using

MsgBox qdef.Parameters("@Explanation").Type

It displays 10 which is type Text. I also checked the Access help for type Text -

"A field data type. Text fields can contain up to 255 characters or the number of characters specified by the Size property of the Field object, whichever is less. If the Size property of the text field is set to 0, the text field can hold up to 255 characters of data."

I also tried assigning -

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

and

qdef.Parameters("@Explanation").Value = RS!fld2

where text1 is string type and fld2 is memo field.

The result is the same.

qdef.Parameters("@Explanation").Type is 10 for string size of upto 255 chars. If size is more then 255, it gives the error as mentioned by jal.

It show that Memo or String type is not supported by qdef property parameters. It supports only text type. Memo or String type gets converted to Text type if the size is up to 255. Beyond that it generates error.

This seems to apply to qdef parameters only. I have not yet seen other cases.

Yes thanks, but does this really make any sense? Why would a database have this restriction? I see no justification for it. Parameterized queries have become a staple of database programming, and one of their main uses is their ability to insert large strings without syntax errors resulting from apostrophes and embedded quotes.

 

[Banana]

FWIW, I don't think this is a "database restriction", but rather data access technology. I tried the same thing in ADO, and it worked fine using parameters, even using Jet's declaration:

As I pointed out, I can create and save an query with parameter defined as TEXT (65535) without any errors. I just can't use DAO or the default input box.

Private Sub foo()

Dim comm As New ADODB.command

With comm
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "PARAMETERS foo TEXT(255), bar TEXT(65535); INSERT INTO NCodes (Code, Explanation) VALUES ([foo], [bar]);"
    .Parameters.Append .CreateParameter("foo", adVarWChar, adParamInput, 255, CStr(Rnd()) + 1)
    .Parameters.Append .CreateParameter("bar", adVarWChar, adParamInput, 65535, "Here's 99 bottles on the wall... One bottle fell off, now there's 98 bottles of beer on the wall... one bottle fell off the wall... now there's 97 bottles on the wall... one bottle fell off the wall and now there's 96 bottles on the wall... One beer bottle fell off the wall and now there's 95 beer bottles")
    .Execute
End With

End Sub

In the google search I did earlier, people who work with Delphi said that it was Access ODBC layer that limits string to 255 characters, but I don't think that's complete story (and DAO doesn't/shouldn't pass through ODBC, especially that we're not using Delphi here).

I suppose the best thing we can do is submit feedback to MS and hope they fix it.


Aha. Now I bet I know why. Access team *just* got control over DAO, which was deprecated (but used anyway) since what? 98? 99? 00?, which then was a part of MDAC and thus between that point and prior to 2007 release, Access team couldn't fix the DAO because it wasn't their code until only recently. So, yeah, I'd definitely want to tell Access team to fix that flaw if they are going to use DAO reincarnated as ACE.

 

[jal]

Thanks Banana - I too was pretty sure that ADO parameters would allow long strings. Therefore I shouldn't have called it a "database restriction" as it is more properly called a "provider restriction." Glad to hear there is a reasonable explanation as to why this wasn't fixed a long time ago. Hope they fix it soon.