Parameter ?_1 has no default value.

T

Trees

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.ColumnName + "]", OleDbType.Char, 255, "[" +
retailOutDataTable.Columns.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 = inDataRow;
}

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++) {
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.ToString();
}

outDataTable.Rows.Add( row );

}

outDataAdapter.Update( outDataTable );

} finally {
outConnection.Close();
}

} finally {
inConnection.Close();
}



}
 
B

Bart Mermuys

Hi,

Trees said:
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.ColumnName + "]", OleDbType.Char, 255, "[" +
retailOutDataTable.Columns.ColumnName + "]");
Click to expand...


- You shouldn't put SourceColumn (3th parameter) between brackets, it needs
to match the ColumnName:

OleDbParameter param = new OleDbParameter("@[" +
retailOutDataTable.Columns.ColumnName + "]", OleDbType.Char, 255,
retailOutDataTable.Columns.ColumnName );


- You can also make the commandtext building a little bit more readable:

string[] colnames = new string[retailOutDataTable.Columns.Count];
string[] params = new string[retailOutDataTable.Columns.Count];

for ( int j=0; j < retailOutDataTable.Columns.Count; ++j )
{
colnames[j] = "[" + retailOutDataTable.Columns[j].ColumnName + "]";
params[j] = "?";
}

retailOutDataAdapter.InsertCommand = new OleDbCommand( "INSERT INTO
[Sheet1$] (" + string.Join(",", colnames)+ ") values (" + string.Join(",",
params) + ")", retailOutConnection );


HTH,
Greetings

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 = inDataRow;
}

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++) {
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.ToString();
}

outDataTable.Rows.Add( row );

}

outDataAdapter.Update( outDataTable );

} finally {
outConnection.Close();
}

} finally {
inConnection.Close();
}



}
Click to expand...
 
T

Trees

Awesome Bart, I owe you a beer!

Fixing the param to the OleDBParameter constructor worked straight away.

Thanks for the tips on simplifying the code too, I'll take a look at this.

The questions up on expert-exchange too if you're into accumulating expert
points. Just reply here with the id to accept the solution for if so.

Bart Mermuys said:
Hi,

Trees said:
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.ColumnName + "]", OleDbType.Char, 255, "["
+
retailOutDataTable.Columns.ColumnName + "]");
Click to expand...


- You shouldn't put SourceColumn (3th parameter) between brackets, it
needs to match the ColumnName:

OleDbParameter param = new OleDbParameter("@[" +
retailOutDataTable.Columns.ColumnName + "]", OleDbType.Char, 255,
retailOutDataTable.Columns.ColumnName );


- You can also make the commandtext building a little bit more readable:

string[] colnames = new string[retailOutDataTable.Columns.Count];
string[] params = new string[retailOutDataTable.Columns.Count];

for ( int j=0; j < retailOutDataTable.Columns.Count; ++j )
{
colnames[j] = "[" + retailOutDataTable.Columns[j].ColumnName + "]";
params[j] = "?";
}

retailOutDataAdapter.InsertCommand = new OleDbCommand( "INSERT INTO
[Sheet1$] (" + string.Join(",", colnames)+ ") values (" + string.Join(",",
params) + ")", retailOutConnection );


HTH,
Greetings

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 = inDataRow;
}

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++) {
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.ToString();
}

outDataTable.Rows.Add( row );

}

outDataAdapter.Update( outDataTable );

} finally {
outConnection.Close();
}

} finally {
inConnection.Close();
}



}
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Parameter has no default value 4
OleDbDataAdapter issue 4
Incredibly slow writing by CSV driver 8
DateTime parse 2
Parameter ?_1 has no default value 1
Excel and ADO.NET 5
No value given for one or more required parameters on relation update 7
System.FormatException: The string was not recognized as a valid DateTime. 6

Top