Parameter ?_1 has no default value.

git

2007-01-02 15:08:03 UTC

Post by Trees
cross post from microsoft.public.data.ado
any assistance greatly appreciated from the excel folks

retailOutDataAdapter.Update( retailOutDataTable );
I have attempted to test solving the problem by assisning what i thought
param.Value = "x";
but no joy.
private void OpenRetailPriceList() {
retailOutConnection = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + OutputFolder +
"\\Retail Price List.xls;Extended Properties=\"Excel 8.0;HDR=YES\"" );
retailOutConnection.Open();
retailOutDataAdapter = new OleDbDataAdapter( "SELECT * FROM
[Sheet1$A1:N1]", retailOutConnection );
retailOutDataTable = new DataTable();
retailOutDataAdapter.FillSchema( retailOutDataTable,
SchemaType.Source );
retailOutDataAdapter.Fill( retailOutDataTable );
string columns = "([" +
retailOutDataTable.Columns[0].ColumnName + "],";
string parameters = "(?,";
int j = 0;
for (j = 1; j < retailOutDataTable.Columns.Count - 1; j++) {
columns += "[" + retailOutDataTable.Columns[j].ColumnName +
"],";
parameters += "?,";
}
columns += "[" + retailOutDataTable.Columns[j].ColumnName +
"])";
parameters += "?)";
retailOutDataAdapter.InsertCommand = new OleDbCommand( "INSERT
INTO [Sheet1$] " + columns + " values " + parameters,
retailOutConnection );
for (int i = 0; i < retailOutDataTable.Columns.Count; i++) {
retailOutDataTable.Columns[i].ColumnName + "]", OleDbType.Char, 255, "[" +
retailOutDataTable.Columns[i].ColumnName + "]");
param.Value = "x";
retailOutDataAdapter.InsertCommand.Parameters.Add( param );
}
}
private void MergeRetailPriceList( string mergePriceList ) {
OleDbConnection inConnection = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + OutputFolder + "\\" +
mergePriceList + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=YES\"" );
try {
inConnection.Open();
// init in data objects
OleDbDataAdapter inDataAdapter = new OleDbDataAdapter(
"select * from [Sheet1$]", inConnection );
DataTable inDataTable = new DataTable();
inDataAdapter.FillSchema( inDataTable, SchemaType.Mapped );
inDataAdapter.Fill( inDataTable );
// process rows from in to out
foreach (DataRow inDataRow in inDataTable.Rows) {
DataRow row = retailOutDataTable.NewRow();
for (int i = 0; i < retailOutDataTable.Columns.Count; i++) {
row[i] = inDataRow[i];
}
retailOutDataTable.Rows.Add( row );
}
retailOutDataAdapter.Update( retailOutDataTable );
} finally {
inConnection.Close();
}
}
what is odd, is this is not a lot disimilar to code I wrote earlier which
works. The only difference that I can determine is the working code uses
CREATE TABLE to produce the result data. The broken code outputs data
instead to an existing spreadsheet that has just the column headings.
This is the code that does work if it's of interest.
private void GeneratePriceList( string source, string tab, string
dest ) {
// create output file named DEST filled with first 3 columns of
data from SOURCE
// init in and out connections
OleDbConnection inConnection = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + InputFolder + "\\" +
source + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"" );
OleDbConnection outConnection = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + OutputFolder + "\\" +
dest + ";Extended Properties=\"Excel 8.0;HDR=NO\"" );
try {
inConnection.Open();
try {
outConnection.Open();
// init in data objects
OleDbDataAdapter inDataAdapter = new
OleDbDataAdapter( "select * from [" + tab + "$]", inConnection );
DataTable inDataTable = new DataTable();
inDataAdapter.FillSchema( inDataTable,
SchemaType.Source );
inDataAdapter.Fill( inDataTable );
// init out data objects
OleDbCommand createCommand = new OleDbCommand( "CREATE
TABLE Sheet1 (F1 char(255)," +
"F2 char(255)," +
"F3 char(255)," +
"F4 char(255)," +
"F5 char(255)," +
"F6 char(255)," +
"F7 char(255)," +
"F8 char(255)," +
"F9 char(255)," +
"F10 char(255)," +
"F11 char(255)," +
"F12 char(255)," +
"F13 char(255)," +
"F14 char(255)," +
"F15 char(255)," +
"F16 char(255)," +
"F17 char(255)," +
"F18 char(255)," +
"F19 char(255)," +
"F20 char(255))", outConnection );
createCommand.ExecuteNonQuery();
OleDbDataAdapter outDataAdapter = new
OleDbDataAdapter( "SELECT * FROM [Sheet1$A1:T10]", outConnection );
string columns = "(F1,";
string parameters = "(?,";
int j = 0;
for (j = 1; j < inDataTable.Columns.Count - 1; j++) {
columns += "F" + (j+1) + ",";
parameters += "?,";
}
columns += "F" + (j+1) + ")";
parameters += "?)";
outDataAdapter.InsertCommand = new OleDbCommand(
"INSERT INTO [Sheet1$] " + columns + " values " + parameters,
outConnection );
// Odd how F1, etc is the default column naming
convention if I use SELECT *. If I use SELECT A, B, C I get no value given
for one or more parameters (?,?,...) matching
for (int i = 0; i < inDataTable.Columns.Count; i++) {
(i+1), OleDbType.Char, 255, "F" + (i+1) );
}
DataTable outDataTable = new DataTable();
outDataAdapter.FillSchema( outDataTable,
SchemaType.Mapped );
outDataAdapter.Fill( outDataTable );
// process rows from in to out
foreach (DataRow inDataRow in inDataTable.Rows) {
DataRow row = outDataTable.NewRow();
for (int i = 0; i < inDataTable.Columns.Count; i++) {
row["F" + (i+1)] = inDataRow[i].ToString();
}
outDataTable.Rows.Add( row );
}
outDataAdapter.Update( outDataTable );
} finally {
outConnection.Close();
}
} finally {
inConnection.Close();
}
}

I am not very familiar with some of the objects you are using here,
however I can see that you are enumerating the columns from 1 in the first
loop and from 0 in the second. Surely this cannot be correct?

AJ

--
Cubical Land:
www.cubicalland.com
Nerds-Central:
nerds-central.blogspot.com