Hi Paolo,
Here's some code I wrote to experiment with updating data in a FoxPro Memo
field:
Imports System.Data.OleDb
Module Module1
Sub Main()
' Assumes VFP table TestADO (Id C(10), Memo1 M)
' in database Test.dbc
Dim OleDbConnection1 = New OleDbConnection("User ID=;DSN=;" & _
"Cache Authentication=False;Data Source=""C:\MY DOCUMENTS\VISUAL FOXPRO
PROJECTS\TEST.DBC"";" & _
"Password=;Provider=""VFPOLEDB.1"";Collating Sequence=MACHINE;Mask
Password=False;Mode=Share Deny None;" & _
"Extended Properties=;Encrypt Password=False")
OleDbConnection1.Open()
Dim OleDbCommand1 As OleDbCommand = New OleDbCommand
OleDbCommand1.Connection = OleDbConnection1
OleDbCommand1.CommandType = CommandType.Text
OleDbCommand1.CommandText = _
"INSERT INTO TestADO (Id, Memo1) VALUES (?, ?)"
Dim Parm0 As OleDbParameter = New Data.OleDb.OleDbParameter
Dim Parm1 As OleDbParameter = New Data.OleDb.OleDbParameter
OleDbCommand1.Parameters.Add(Parm0)
OleDbCommand1.Parameters.Add(Parm1)
OleDbCommand1.Parameters(0).Value = "Id Here"
OleDbCommand1.Parameters(1).Value = _
"The quick brown fox jumped over the lazy dog. 50" & _
"The quick brown fox jumped over the lazy dog. 100" & _
"The quick brown fox jumped over the lazy dog. 150" & _
"The quick brown fox jumped over the lazy dog. 200" & _
"The quick brown fox jumped over the lazy dog. 250" & _
"The quick brown fox jumped over the lazy dog. 300" & _
"The quick brown fox jumped over the lazy dog. 350" & _
"The quick brown fox jumped over the lazy dog. 400" & _
"The quick brown fox jumped over the lazy dog. 450" & _
"The quick brown fox jumped over the lazy dog. 500"
Dim RowsAffected As Integer = OleDbCommand1.ExecuteNonQuery()
OleDbConnection1.Close()
End Sub
End Module
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
***@msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
Post by webmobileNo chance, the problem is the same.Still the limit of 255 char.
dim conn, comm, sql, memoTXT, parLen
set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionString="Provider=VFPOLEDB.1;Data Source=" &
server.mappath("fpdb\data2.dbc")
conn.mode=16
conn.open
set comm=Server.Createobject("ADODB.Command")
comm.CommandType = 1
comm.ActiveConnection=conn
memoTXT= " A text with more than 255 char........................"
sql="UPDATE DATA2!personefisiche SET memo =? where codice=" &
request.form("utente")
comm.CommandText=sql
parLen=Len(memoTXT)
comm.Parameters.Append
comm.CreateParameter("memoPAR",200,1,parlen,memoTxT)
comm.execute
Everything is oK but the memoTXT must be less than 255 char. No chance to
use a memo field or a long text.
Any mode suggestions beside using the AppendChunk() method?
Thanks
Paolo
Post by AndersCreate a ADO Command object with a Parameters collection. Put your long
text
Post by Andersinto variable for the parameter.
Here's an example written in VFP code that you should be able to translate
to VB script.
*******
#DEFINE adCmdText 1
LOCAL oCmd AS adodb.command
LOCAL cn AS adodb.connection
LOCAL strSQL as String, strMsgText AS String
cn = CREATEOBJECT('ADODB.Connection')
cn.Open("User ID=;Collating Sequence=MACHINE;Data ;
Source=MemoTest.dbc;Password=;Provider='VFPOLEDB.1';Cache ;
Authentication=False;Mask Password=False;Mode=Share Deny None;Extended ;
Properties=;Encrypt Password=False")
oCmd=CREATEOBJECT('ADODB.Command')
strMsgText = "This does not work because it is longer than 254
characters.
;
Post by AndersThis does not work because it is longer than 254 characters. This does not
"+ ;
"work because it is longer than 254 characters. This does not work because
it ;
is longer than 254 characters. This does not work because it is longer
than
Post by Anders254 characters."
*DEBUGOUT StrMsgText
oCmd.ActiveConnection=cn
oCmd.CommandType=adCmdText
strSQL = "INSERT INTO Memoex (memoid,memotext) VALUES (1,?)"
oCmd.CommandText=strSQL
parm1=oCmd.CreateParameter('memotext',200,1)
parm1.size=LEN(strMsgText)
parm1.Value=strMsgText
oCmd.Parameters.Append(parm1)
*DEBUGOUT strsql
oCMD.Execute
**********
-Anders
VFP MVP
Post by webmobileHi Cindy. Cleaner code than before.
set conn=Server.CreateObject("ADODB.Connection")
conn.mode=16
conn.ConnectionString="Provider=VFPOLEDB.1;Data Source=" &
server.mappath("fpdb\data2.dbc")
conn.open
sql="UPDATE DATA2!personefisiche SET memo ='" & memo & "' where
codice="
Post by AndersPost by webmobile&
request.form("utente")
conn.Execute sql, recordsaffected
conn.close
It works if memo lenght is less than 255 char.. If is more than 255 the
query is not recognized.
I'm thinking about the way to avoid the problem and the only way I
fount
is
to use ADO and the appendchunk method,
(http://support.microsoft.com/default.aspx?scid=kb;en-us;208208). I
don't
Post by AndersPost by webmobilelike it so much 'cose I have to change all my queries.
Any different solution?
Thank you in advance
Paolo
Post by Cindy WinegardenHi,
As Fred mentioned, VFP has a limit on the number of characters in a quoted
string. You can use something like
Insert Into Table (Field1) Values ("255 chars" + "255 chars" + ....)
You haven't posted the code you are using. Can you use a parameterized
update statement with the text given as a string variable?
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
Blog: http://spaces.msn.com/members/cindywinegarden
Post by webmobileHow can I manage to insert or update a VFP memo field from a HTML
page
Post by AndersPost by webmobilePost by Cindy WinegardenPost by webmobileusing
VbScript?
Variables as the limit of 255 Char and I have no idea of the way to
insert
Post by Cindy WinegardenPost by webmobilea
long string in a SQL clause.
Any idea or suggestion?
Thanks
Paolo