Parameter ?_1 has no default value.

The exception "Parameter ?_1 has no default value." is generated during:
retailOutDataAdapter.Update( retailOutDataTable );

I have attempted to test solving the problem by assisning what i thought
would be a default to use in the place of nulls with:
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++) {
OleDbParameter param = new OleDbParameter("@[" +
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 spread*** 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++) {
outDataAdapter.InsertCommand.Parameters.Add( "@F" +
(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();
}



}



.