Memo field add/update failure

[petejha]

I am working on a new website using asp with an Access database. I have
a problem with a Memo field when adding/updating - the SQL fails with

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
[Microsoft][ODBC Microsoft Access Driver]Invalid precision value

when I try to add or update more than 255 characters. If I remove the
extra characters, the add/update works fine.

I tried an experiment where I created a record directly in the table
with about 700 characters in the Memo field. I shipped this database to
the website and found I could retrieve and display the data with no
problem but I could not save that same data without removing the extra
characters.

I have trawled through lots of user group entries and found lots of
instances of truncating Memo fields but in every case that I've seen
the solution has been to remove GROUP BY, DISTINCT, or a similar
condition on the query. My SQL is straightforward and I am using a
preset query (stored procedure) in Access like this (there are actually
lots of fields but I have left most out for ease of reading):

connString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" &
Server.MapPath("mydb.mdb")
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connString
conn.qUpdTable, mymemo, myinput

where 'qUpdTable' is the UPDATE statement, ''mymemo' is the Memo field
and 'myinput' is the input parameter. The UPDATE statement in Access
looks like this :

UPDATE Mytable SET MyMemoFld = [mymemo]
WHERE MytableID=[myinput];

Can anyone shed any light on why this should fail and how I can resolve
the problem?

Thanks in hopeful anticipation.

PJ.

 

[giorgio rancati]

Hi PJ,

try this
----
Dim connString
Dim conn
Dim cmd

connString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" &
Server.MapPath("mydb.mdb")
Set conn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")

conn.Open connString

cmd.CommandText = "qUpdTable"
cmd.CommandType = adCmdStoredProc
Set cmd.ActiveConnection = conn
cmd.Parameters.Append cmd.CreateParameter("", adLongVarChar, adParamInput,
65535, mymemo)
cmd.Parameters.Append cmd.CreateParameter("", adInteger, adParamInput, ,
myinput)

cmd.Execute
Set cmd=Nothing

 

[petejha]

Thanks Giorgio.
I've had a go with this and thought I was getting somewhere but I keep
coming up against this error:

"Arguments are of the wrong type, are out of acceptable range, or are
in conflict with one another"

This error comes up for this line:

cmd.CommandType = adCmdStoredProc

but I have played around with the line above (cmd.CommandText =
"qUpdTable") in case it was that one which was really causing the
problem. But no luck. Have you any idea why I might get that? Is it
something to do with the way I've set up my stored procdure do you
think?

PJ.

 

[giorgio rancati]

ops..
You are working in vbs language, it has not a ADO constants.
Try this changes
----
Dim connString
Dim conn
Dim cmd

'ADO constants
Dim adCmdStoredProc
Dim adLongVarChar
Dim adInteger
Dim adParamInput

'Set ADO constants
adCmdStoredProc=4
adLongVarChar=201
adInteger=3
adParamInput=1

connString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" &
Server.MapPath("mydb.mdb")
Set conn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")

conn.Open connString

cmd.CommandText = "qUpdTable"
cmd.CommandType = adCmdStoredProc
Set cmd.ActiveConnection = conn
cmd.Parameters.Append cmd.CreateParameter("", adLongVarChar,
adParamInput,65535, mymemo)
cmd.Parameters.Append cmd.CreateParameter("", adInteger, adParamInput,
,myinput)

cmd.Execute
Set cmd=Nothing

 

[petejha]

Brilliant! That works perfectly.

Thanks Giorgio.

PJ.