suednym


My C# application attempts to insert data into a Fp database. I have used the data adapter config wizard to create the Select and Insert commands. When I attempt the insert I get the following eror message :

Server was unable to process request. --> System.InvalidOperationException: OleDbCommand.Prepare method requires all parameters to have an explicitly set type. etc etc

An extracy of the code generated follows:

this.oleDbInsertCommand1.CommandText = resources.GetString("oleDbInsertCommand1.CommandText");

this.oleDbInsertCommand1.Parameters.AddRange(new System.Data.OleDb.OleDbParameter[] {

new System.Data.OleDb.OleDbParameter("invoice", System.Data.OleDb.OleDbType.Char, 0, "invoice"),

new System.Data.OleDb.OleDbParameter("sono", System.Data.OleDb.OleDbType.Char, 0, "sono"),

new System.Data.OleDb.OleDbParameter("csono", System.Data.OleDb.OleDbType.Char, 0, "csono"),

new System.Data.OleDb.OleDbParameter("custid", System.Data.OleDb.OleDbType.Char, 0, "custid"),

etc.......

There is an OleDbParameter for each of the fields in the insert command that is retrieved from the resources.GetString.

Are there other conditions that might generate the error.

many thanks for any assistance




Re: Insert data into FoxPro database

CetinBasoz


Data adapter config wizard doesn't generate the correct command text for VFP tables. It particularly surrounds fieldnames with square brackets. While that is a valid action for other backends like SQL server, in VFP square brackets cause them to be treated as strings (in VFP you can use single,double quotes or square brackets as string delimiters). Edit your InsertCommand, SelectCommand ... to remove the brackets.

Code Block

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

class test
{
static void Main()
{
string strConn = @"Provider=VFPOLEDB;Data source=
C:\Program Files\Microsoft Visual FoxPro 9\Samples\Data\testdata.dbc;";

OleDbConnection cn = new OleDbConnection(strConn);

// Prepare command and parameters
string strInsert = "insert into customer (cust_id,company,contact) values ( , , )";

OleDbCommand cmdStartUp = new OleDbCommand("set null off",cn);
OleDbCommand cmdSelect = new OleDbCommand("select * from customer",cn);

OleDbCommand cmdInsert = new OleDbCommand(strInsert,cn);
cmdInsert.Parameters.AddRange(
new OleDbParameter[] {
new OleDbParameter("cust_id",OleDbType.Char),
new OleDbParameter("company",OleDbType.Char),
new OleDbParameter("contact",OleDbType.Char) }
);

cn.Open();
cmdStartUp.ExecuteNonQuery();
cmdInsert.Parameters["cust_id"].Value = "X1";
cmdInsert.Parameters["company"].Value = "X1 Company";
cmdInsert.Parameters["contact"].Value = "X1 Contact";
cmdInsert.ExecuteNonQuery();

cmdInsert.Parameters["cust_id"].Value = "X2";
cmdInsert.Parameters["company"].Value = "X2 Company";
cmdInsert.Parameters["contact"].Value = "X2 Contact";
cmdInsert.ExecuteNonQuery();

OleDbDataReader rdr = cmdSelect.ExecuteReader();
while(rdr.Read())
{ Console.WriteLine("Customer: {0},{1},{2}",
rdr["cust_id"],
rdr["company"],
rdr["contact"]);
}
rdr.Close();
cn.Close();
}
}






Re: Insert data into FoxPro database

suednym

Thanks for your reply, it has left me slightly confused. If we are talking about the OleDbParameter command, then field- names haven't been surrounded by square brackets, my code extract is similar to the code in your response but with extra parameters. Could you please be a little more explicit. Thanks again.






Re: Insert data into FoxPro database

CetinBasoz

Oh I thought you were taling about the code generated by the wizard.

Would you show your insert command and parameters then.





Re: Insert data into FoxPro database

suednym

Hi,

I have since found the cause for the errors I was receiving, The insert command became a red herring.

Many thanks for your help

Regards