Re: Problems with ADO Interop and dBASE file
- From: "Cor Ligthert[MVP]" <notmyfirstname@xxxxxxxxx>
- Date: Fri, 16 May 2008 05:55:19 +0200
Jeff,
For the best answers try one of FoxPro newsgroup for this, they can as well tell you what you can use the best to get good performance.
Cor
"Jeff Johnson" <i.get@xxxxxxxxxxx> schreef in bericht news:fYednUwgqLqfMLHVnZ2dnUVZ_ovinZ2d@xxxxxxxxxxxxxxxxx
I am in Interop Hell.
I'm writing a C# program that needs to write to a dBASE III file. ADO.NET's performance with ISAMs is abysmal, a fact that I have not only read online but have experience firsthand. The recommend solution is to use plain old ADO, which is the direction I went. However, I'm getting the following error: "Parameter ?_1 has no default value," and it's a nasty one. All the hits I found on this error relate to people using ADO.NET, not ADO, so hopefully someone can help me. If this is the wrong place to ask, please point me in the right direction. Below is the code I am currently using, with some redundant stuff stripped out for size reasons:
**************CODE*************
ConnectionClass cnn = new ConnectionClass();
cnn.Open(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\TEMP;Extended Properties=dBASE III;", null, null, (int)ConnectOptionEnum.adConnectUnspecified);
object recs;
// There's earlier code that deletes the DBF if it exists, so this statement never fails
cnn.Execute("CREATE TABLE [TESTIMP] ([PREFIX] VARCHAR(10), [FIRSTNAME] VARCHAR(23), [MIDDLENAME] VARCHAR(1), [LASTNAME] VARCHAR(35), [SUFFIX] VARCHAR(10), [FULLNAME] VARCHAR(60), <and a bunch of other fields>)", out recs, (int)ExecuteOptionEnum.adExecuteNoRecords);
CommandClass cmd = new CommandClass();
cmd.ActiveConnection = cnn;
// YEAR must be escaped because apparently it's a reserved word in dBASE. Without
// the escape you get a "Syntax error in INSERT command" error.
cmd.CommandText = "INSERT INTO TESTIMP (FIRSTNAME, LASTNAME, ADDRESS1, CITY, STATE, ZIP, PLUS4, JOBNUMBER,BATCH, PHONE, PHONE2, [YEAR], MAKE, MODEL, VIN, MILEAGE, DEALERNAME, DLRNMBR, DISTANCE, SALEDATE, RODATE, FCSDLRNMBR, FILENAME) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?, ?, ?)";
cmd.CommandType = CommandTypeEnum.adCmdText;
Parameter prm = cmd.CreateParameter("FIRSTNAME", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 23, string.Empty);
cmd.Parameters.Append(prm);
prm = cmd.CreateParameter("LASTNAME", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, string.Empty);
cmd.Parameters.Append(prm);
// [...] Several more of the same basic parameters, all using VarChar and all being
// initialized to string.Empty
object dummyParams = null;
// Next I read from an SqlDataReader that I opened right before the code above and I start filling the command with values from the reader. (In other words, I'm exporting from SQL Server to dBASE.)
while (reader.Read())
{
cmd.Parameters["FIRSTNAME"].Value = (reader.IsDBNull(7) ? (object)string.Empty : (object)reader.GetString(7));
cmd.Parameters["LASTNAME"].Value = (reader.IsDBNull(8) ? (object)string.Empty : (object)reader.GetString(8));
// [...] Again, more the same basic code which sets all the other parameters, all using string.Empty if the column is NULL
//cmd.Execute(out recs, ref dummyParams, (int)ExecuteOptionEnum.adExecuteNoRecords);
cmd.Execute(out recs, ref dummyParams, -1); // ERROR HERE
}
**************END CODE*************
As indicated, the error occurs on the Execute method call, whether I call the one that's commented out or the other one. If I replace all the string.Empty's above with DBNull.Value I get the same error. If I replace them with a dummy value, like "#", I get the same error.
I took this exact code and put it in a VB6 program (converting C# to VB, of course). The only difference is that I took out the datareader part and just set the values of the parameters to a constant value. It worked fine, i.e., one record was written to the DBF. To be sure I was comparing apples and oranges, I did the same thing in my C# program, i.e., I commented out the datareader and just set the values of the parameters directly. I got the same $%^&^%*^% error.
Any suggestions? Is there any way to "profile" OLEDB calls? In SQL Server, I'd use Profiler to see the exact command that was being passed and diagnose problems from there.
Oh, something else: when I was using ADO.NET before this (and experienced the insanely awful performance), I got the same error and was able to get around it because the OleDbParameter object exposed an IsNullable property. I set that to true for all the params and the error went away. But ADO doesn't have that property, at least not directly on the Parameter object. I thought for a moment that maybe it's something that could be added to the Properties collection, but I don't know where to find a list of valid properties for parameters. I examined the collection in debug mode but it had 0 entries, so that was no help.
.
- References:
- Problems with ADO Interop and dBASE file
- From: Jeff Johnson
- Problems with ADO Interop and dBASE file
- Prev by Date: RE: TableAdapter.Update fails with missing parameter
- Next by Date: Re: transaction scope
- Previous by thread: Re: Problems with ADO Interop and dBASE file
- Next by thread: Re: Problems with ADO Interop and dBASE file
- Index(es):
Relevant Pages
|
