>Hello Cetin,
>
>Can you please tell me, why the following query fails:
>**************************************************************
>This is just an example:
>Insert into ocrdata(id,tiffile,ocdata,created_dt) Values(7713,'demdemorecv10','Apartment lease THIS APART',{9/9/2004})
>**************************************************************
>when i executed the above query in command window of foxpro:
>
>I get the error unrecognized command verb.
>
>and it only shows the last line which is:
>
>(7713,'demdemorecv10','Apartment lease THIS APART',{9/9/2004})
>
>What happened to my first line which has insert into ocrdata.
>******
>I am trying to do the same from Visual Basic to fox pro and this is what is happening:
>In my query all the values will come from variables those are dynamic.
>and the field ocdata contains the whole text file data.
>
>Cetin, Can you please tell me what do i need to do before executing the query to avoid this problem.
>
>Thank you very much in Advance.
Raj,
Maybe this is your problem:
In VFP any literal string cannot exceed 255 in size.
When you try to execute something like:
Insert into ocrdata(id,tiffile,ocdata,created_dt) Values(7713,'demdemorecv10','Assume this is longer than 255',{9/9/2004})
It'd error. Workaround is in a few ways:
1) Split the long string into literals that are lte 255. Like :
Insert into ocrdata(id,tiffile,ocdata,created_dt) Values(7713,'demdemorecv10','lte 255 part1'+'lte 255 part2'+..'lte PartN',{9/9/2004})
(I know no one would do it this way but is a way so I included:)
2) Instead of a literal pass a variable. A string variable is not limited to 255 (docs say limit is 16Mb but suspect in cases you can get over that limit).
lcStr = FileToStr('SomeFileName')
Insert into ocrdata(id,tiffile,ocdata,created_dt) Values(7713,'demdemorecv10',lcStr,{9/9/2004})
Now as I know already your problem is in passing this from VB how would you put that variable w/o putting quotes around (as soon as you put quotes it's passed as a literal). If you were doing it in VFP with SQLExec answer was simple :
lcStr = FileToStr('SomeFileName')
SQLExec(handle, "Insert into ocrdata(id,tiffile,ocdata,created_dt) Values "+;
"(7713,'demdemorecv10',?m.lcStr,{9/9/2004})")? makes it to be treated as a parameter. From VB you're using ADO and in ADO you can use Command object's Parameters collection. Below is a sample VB code < bg >. Remember I told you my VB is almost 0 so reading from a textfile part might be laughable for a VB coder:
Private Sub InsertButton_Click()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim oCon As New ADODB.Connection
Dim oCommand As New ADODB.Command
Dim oParm As ADODB.Parameter
Dim myMemo As Variant
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("d:\cetin\ddrive\temp\_18C0N6VC6.txt", ForReading, TriStateFalse)
myMemo = f.ReadAll
f.Close
oCon.ConnectionString = "Provider=VFPOLEDB;Data Source=d:\cetin\ddrive\temp\testdata.dbc"
oCommand.CommandText = "insert into employee (emp_id,first_name,last_name,birth_date,notes)" & _
" values ('ctest','cetin','basoz',{^1961/1/19},?)"
oCommand.CommandType = adCmdText
Set oParm = oCommand.CreateParameter("Notes", adVarChar, adParamInput, Len(myMemo), myMemo)
oCommand.Parameters.Append oParm
oCon.Open
oCon.Execute ("set null off")
oCommand.ActiveConnection = oCon
oCommand.Execute
oCon.Close
End SubCetin
