Command contains unrecognized phrase/keyword VB.Net  
Author Message
Komandur.Kannan





PostPosted: Visual FoxPro General, Command contains unrecognized phrase/keyword VB.Net Top

Hi

I am facing the above problem when I am trying to insert a MEMO data in to the VFP table. I am using a OLEDB connection string in VB.Net

My data present in the file is

//------------------------------

Pen

Pencil

Erasier

Sharpener

//----------------------------

Each an every line has a (Vbcrlf) that is a line feed in it I need the File as in the above format to be stored in the memo file without any change

I get the data from the file by the following procedure

----------

Dim lstrData As New String(" ", 500)

FileOpen(1, "C:\soc.txt", OpenMode.Binary)

FileGet(1, lstrData)

FileClose(1)

--------

I get the data in the lstrdata Now I use sql statment to insert into the vfp table

sql = "Insert into edifile (theirref,qtnord ) Values (' " & lstrdata & "', 'RRTo')

Public gconnMDB_I As OleDb.OleDbConnection

gconnMDB_I = New OleDb.OleDbConnection

gconnMDB_I.ConnectionString = lstrConnString

Call gconnMDB_I.Open()

Dim cmd As New OleDb.OleDbCommand

cmd = New OleDb.OleDbCommand(sql, gconnMDB_I)

cmd.ExecuteNonQuery()

Once cmd.ExecuteNonQuery is excuted the following error occurs

Command contains unrecognized phrase/keyword VB.Net



Visual FoxPro1  
 
 
Vedat ARAL





PostPosted: Visual FoxPro General, Command contains unrecognized phrase/keyword VB.Net Top

I think you are missing a duble quote at the end:

sql = "Insert into edifile (theirref,qtnord ) Values (' " & lstrdata & "', 'RRTo')

it should be:
sql = "Insert into edifile (theirref,qtnord ) Values (' " & lstrdata & "', 'RRTo') "

 
 
CetinBasoz





PostPosted: Visual FoxPro General, Command contains unrecognized phrase/keyword VB.Net Top

If you add missing quotes it would work for 'some cases' coincidentally (precisely when string length is not over 255).

Create OleDbParameter objects for your insert in the order they appear and assign your string to the corresponding parameter.

This sample was previously posted on UniversalThread ( I'm a C#er but it'd be easy for you to understand):

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;

class Test
{
 static void Main()
 {
   string strCon
"Provider=VFPOLEDB;
Data source=C:\Program Files\Microsoft Visual Studio\MSDN\2001OCT\1033\SAMPLES\VFP98\data\Testdata.dbc";

   string strSQL = "insert into employee (emp_id,first_name,last_name,notes) values ( , , , )";

   OleDbConnection cn = new OleDbConnection(strCon) ;
   cn.Open();
   // First send "set null off" command to VFP so it wouldn't error for missing fields
   // but instead fill them with empty values if default is not specified
   OleDbCommand cmdInit = new OleDbCommand("set null off",cn); 
   cmdInit.ExecuteNonQuery();

   // Prepare command and parameters
   OleDbCommand cmd = new OleDbCommand(strSQL,cn);
   OleDbParameter empID   = cmd.Parameters.Add("empID",OleDbType.Char);
   OleDbParameter firstName = cmd.Parameters.Add("firstName",OleDbType.Char);
   OleDbParameter lastName = cmd.Parameters.Add("lastName",OleDbType.Char);
   OleDbParameter notes   = cmd.Parameters.Add("notes",OleDbType.Char); 
                // might be VarChar, LongVarChar etc

   // Important
   // Note that unlike MSSQL, VFP parameters are not named but positional. 
   // Parameter name only helps to developer. It's their position that provides mapping
   // parameter to its related field. IOW:
   // (emp_id,first_name,last_name,notes) values ( , , , )
   // first parameter added (empID) is for emp_id, 2nd (firstName) is for first_Name and so on.


   empID.Value = "C#2VFP";
   firstName.Value = "TestName";
   lastName.Value = "TestLast";

   // Get an existing text file to put into VFP memo field
   string
"C:\Program Files\Microsoft Visual Studio\Vfp98\VFP6FAQ.txt";
   string memoContent;
   using (StreamReader sr = new StreamReader(file2read)) 
   {
    memoContent = sr.ReadToEnd();
   }
   notes.Value = memoContent; // Got via another var just for clarity

   // insert the record
   cmd.ExecuteNonQuery();
   cn.Close();
 }
}