FoxPro database connector - INSERT or UPDATE a VFP memo field using VBSCRIPT

Author

INSERT or UPDATE a VFP memo field using VBSCRIPT

webmobile

2005-10-27, 8:34 am

How can I manage to insert or update a VFP memo field from a HTML page using
VbScript?
Variables as the limit of 255 Char and I have no idea of the way to insert a
long string in a SQL clause.
Any idea or suggestion?
Thanks

Paolo

Fred Taylor

2005-10-27, 8:34 am

You must build up your string, the 255 limit is for a string constant.

--
Fred
Microsoft Visual FoxPro MVP

"webmobile" <info@guarnerio.coop> wrote in message
news:uAeJznCyFHA.2932@TK2MSFTNGP10.phx.gbl...
> How can I manage to insert or update a VFP memo field from a HTML page
> using
> VbScript?
> Variables as the limit of 255 Char and I have no idea of the way to insert
> a
> long string in a SQL clause.
> Any idea or suggestion?
> Thanks
>
> Paolo
>
>

Cindy Winegarden

2005-10-27, 8:34 am

Hi,

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
cindy_winegarden@msn
.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

"webmobile" <info@guarnerio.coop> wrote in message
news:uAeJznCyFHA.2932@TK2MSFTNGP10.phx.gbl...
> How can I manage to insert or update a VFP memo field from a HTML page
> using
> VbScript?
> Variables as the limit of 255 Char and I have no idea of the way to insert
> a
> long string in a SQL clause.
> Any idea or suggestion?
> Thanks
>
> Paolo
>
>

webmobile

2005-10-27, 8:34 am

Hi Cindy.

The code I use is this:

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=" &
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/defaul...kb;en-us;208208). I don't
like it so much 'cose I have to change all my queries.
Any different solution?

Thank you in advance

Paolo

"Cindy Winegarden" < cindy_winegarden@msn
.com> ha scritto nel messaggio
news:OexAUGEyFHA.3188@TK2MSFTNGP14.phx.gbl...
> Hi,
>
> 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
> cindy_winegarden@msn
.com www.cindywinegarden.com
> Blog: http://spaces.msn.com/members/cindywinegarden
>
>
> "webmobile" <info@guarnerio.coop> wrote in message
> news:uAeJznCyFHA.2932@TK2MSFTNGP10.phx.gbl...
insert[color=darkred
]
>
>

webmobile

2005-10-27, 8:34 am

Hi Cindy. Cleaner code than before.

The code I use is this:

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=" &
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/defaul...kb;en-us;208208). I don't
like it so much 'cose I have to change all my queries.
Any different solution?

Thank you in advance

Paolo
"Cindy Winegarden" < cindy_winegarden@msn
.com> ha scritto nel messaggio
news:OexAUGEyFHA.3188@TK2MSFTNGP14.phx.gbl...
> Hi,
>
> 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
> cindy_winegarden@msn
.com www.cindywinegarden.com
> Blog: http://spaces.msn.com/members/cindywinegarden
>
>
> "webmobile" <info@guarnerio.coop> wrote in message
> news:uAeJznCyFHA.2932@TK2MSFTNGP10.phx.gbl...
insert[color=darkred
]
>
>

Lee Mitchell

2005-10-27, 8:34 am

Hi Paolo:

I think the code in this article may help you:

http://support.microsoft.com/defaul...KB;EN-US;208208

I hope this helps.

This posting is provided "AS IS" with no warranties, and confers no rights.

Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell

*-- VFP9 HAS ARRIVED!! --*
Read about all the new features of VFP9 here:
http://msdn.microsoft.com/vfoxpro/

*--Purchase VFP 9.0 here:
http://www.microsoft.com/PRODUCTS/i...cid=54787e64-52
69-4500-8bf2-3f06689f4ab3&type=ovr

Keep an eye on the product lifecycle for Visual FoxPro here:
http://support.microsoft.com/gp/lifeselectindex
- VFP5 Mainstream Support retired June 30th, 2003
- VFP6 Mainstream Support retired Sept. 30th, 2003

> How can I manage to insert or update a VFP memo field from a HTML page
> using
> VbScript?
> Variables as the limit of 255 Char and I have no idea of the way to
insert
> a
> long string in a SQL clause.
> Any idea or suggestion?
> Thanks
>
> Paolo

Anders

2005-10-27, 8:34 am

Create a ADO Command object with a Parameters collection. Put your long text
into 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;Dat
a ;
Source=MemoTest. dbc;Password=;Provid
er='VFPOLEDB.1';Cache ;
Authentication=False
;Mask Password=False;Mode=
Share Deny None;Extended ;
Properties=;Encrypt Password=False")
oCmd=CREATEOBJECT('A
DODB.Command')
strMsgText = "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 254 characters. This does not work because
it ;
is longer than 254 characters. This does not work because it is longer than
254 characters."
*DEBUGOUT StrMsgText
oCmd.ActiveConnection=cn
oCmd. CommandType=adCmdTex
t
strSQL = "INSERT INTO Memoex (memoid,memotext) VALUES (1,?)"
oCmd.CommandText=strSQL
parm1=oCmd. CreateParameter('mem
otext',200,1)
parm1. size=LEN(strMsgText)

parm1.Value=strMsgText
oCmd.Parameters.Append(parm1)
*DEBUGOUT strsql
oCMD.Execute
**********
-Anders
VFP MVP

"webmobile" <info@guarnerio.coop> skrev i meddelandet
news:uIduEcEyFHA.664@tk2msftngp13.phx.gbl...
> Hi Cindy. Cleaner code than before.
>
> The code I use is this:
>
> 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="
> &
> 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/defaul...kb;en-us;208208). I don't
> like it so much 'cose I have to change all my queries.
> Any different solution?
>
> Thank you in advance
>
> Paolo
> "Cindy Winegarden" < cindy_winegarden@msn
.com> ha scritto nel messaggio
> news:OexAUGEyFHA.3188@TK2MSFTNGP14.phx.gbl...
> insert
>
>

webmobile

2005-10-27, 8:34 am

No chance, the problem is the same.Still the limit of 255 char.

My code:

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=con
n
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,memoTx
T)
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

"Anders" <anders@anders> ha scritto nel messaggio
news:ukaW18EyFHA.1028@TK2MSFTNGP12.phx.gbl...
> Create a ADO Command object with a Parameters collection. Put your long
text
> into 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;Dat
a ;
> Source=MemoTest. dbc;Password=;Provid
er='VFPOLEDB.1';Cache ;
> Authentication=False
;Mask Password=False;Mode=
Share Deny None;Extended ;
> Properties=;Encrypt Password=False")
> oCmd=CREATEOBJECT('A
DODB.Command')
> strMsgText = "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 254 characters. This does not work because
> it ;
> is longer than 254 characters. This does not work because it is longer
than
> 254 characters."
> *DEBUGOUT StrMsgText
> oCmd.ActiveConnection=cn
> oCmd. CommandType=adCmdTex
t
> strSQL = "INSERT INTO Memoex (memoid,memotext) VALUES (1,?)"
> oCmd.CommandText=strSQL
> parm1=oCmd. CreateParameter('mem
otext',200,1)
> parm1. size=LEN(strMsgText)

> parm1.Value=strMsgText
> oCmd.Parameters.Append(parm1)
> *DEBUGOUT strsql
> oCMD.Execute
> **********
> -Anders
> VFP MVP
>
> "webmobile" <info@guarnerio.coop> skrev i meddelandet
> news:uIduEcEyFHA.664@tk2msftngp13.phx.gbl...
codice="[color=darkred]
don't[color=darkred]

page[color=darkred]
>
>

Cindy Winegarden

2005-10-27, 8:34 am

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;Mas
k
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
cindy_winegarden@msn
.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

"webmobile" <info@guarnerio.coop> wrote in message
news:%23dmjptOyFHA.2992@TK2MSFTNGP11.phx.gbl...
> No chance, the problem is the same.Still the limit of 255 char.
>
> My code:
>
> 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=con
n
> 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,memoTx
T)
> 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
>
>
>
>
>
>
> "Anders" <anders@anders> ha scritto nel messaggio
> news:ukaW18EyFHA.1028@TK2MSFTNGP12.phx.gbl...
> text
> ;
> than
> codice="
> don't
> page
>
>

Sponsored Links