如何解决异常没有给出一个或多个必需参数的值? [英] How to resolve the exception No value given for one or more required parameters?
本文介绍了如何解决异常没有给出一个或多个必需参数的值?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
public void impSpanJvNSEF(string FilePath,int eximID)
{
try
{
DataSet ds = o_Cls_Utility.ReadExcelToDataSet(FilePath,SHEET1 ,A6,假);
}}
public DataSet ReadExcelToDataSet( string filepath, string SheetName, string ColumnToCheckNotNull, bool renameHeader = true )
{
// 在服务器上部署后需要传递相对路径
string path = System.IO.Path.GetFullPath(filepath);
/ * 连接字符串以使用excel文件。 HDR =是 - 表示
第一行包含列名,而不是数据。 HDR =否 - 表示
相反。 IMEX = 1;告诉驱动程序始终将混合
(数字,日期,字符串等)数据列作为文本读取。
请注意,此选项可能会影响Excel工作表写入访问权限。 * /
OleDbConnection oledbConn;
oledbConn = new OleDbConnection( @ Provider = Microsoft.ACE.OLEDB.12.0; Data Source = +
path + ;扩展属性='Excel 12.0; HDR = YES; IMEX = 1;';);
oledbConn.Open();
OleDbCommand cmd = new OleDbCommand(); ;
OleDbDataAdapter oleda = new OleDbDataAdapter();
DataSet ds = new DataSet();
// 选择Slno的distict列表
cmd.Connection = oledbConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SELECT * FROM [ + SheetName + $] WHERE + ColumnToCheckNotNull + IS NOT NULL;
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(ds);
cmd = null ;
oledbConn.Close();
oledbConn = null ;
if (renameHeader == true )
{
foreach (DataColumn列 in ds.Tables [ 0 ]。列)
{
string cName = ds.Tables [ 0 ].Rows [ 0 ] [column.ColumnName] .ToString();
if (!ds.Tables [ 0 ]。Columns.Contains(cName)& & cName!= )
{
column.ColumnName = cName ;
}
}
ds.Tables [ 0 ]。行[ 0 跨度>]删除(); // 如果您不再需要该行
ds.AcceptChanges();
}
return (ds);
}
但在上面查询我的
折叠|复制代码
oleda.Fill(ds);
显示空数据并提供aboce异常
解决方案
WHERE + ColumnToCheckNotNull + IS NOT NULL;
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(ds);
cmd = null ;
oledbConn.Close();
oledbConn = null ;
if (renameHeader == true )
{
foreach (DataColumn列 in ds.Tables [ 0 ]。列)
{
string cName = ds.Tables [ 0 ]。行[ 0 ] [column.ColumnName] .ToString();
if (!ds.Tables [< span class =code-digit> 0 ]。Columns.Contains(cName)&& cName!= )
{
column.ColumnName = cName;
}
}
ds.Tables [ 0 ]。行[ 0 跨度>]删除(); // 如果您不再需要该行
ds.AcceptChanges();
}
return (ds);
}
但在上面查询我的
折叠|复制代码
oleda.Fill(ds);
显示空数据并提供aboce异常
hi,
你可以这样测试并查看天气你从Excel获取数据到数据集。
我几乎没有改变你的功能,这里查询我硬代码为了测试天气你得到了结果。
public DataSet ReadExcelToDataSet( string filepath, string SheetName, string ColumnToCheckNotNull, bool renameHeader = true )
{
// 在服务器上部署后需要传递相对路径
string path = System。 IO.Path.GetFullPath(文件路径);
/ * 连接字符串以使用excel文件。 HDR =是 - 表示
第一行包含列名,而不是数据。 HDR =否 - 表示
相反。 IMEX = 1;告诉驱动程序始终将混合
(数字,日期,字符串等)数据列作为文本读取。
请注意,此选项可能会影响Excel工作表写入访问权限。 * /
OleDbConnection oledbConn;
if (Path.GetExtension(path)== .xls)
{
oledbConn = new OleDbConnection( Provider = Microsoft.Jet.OLEDB.4.0;
Data Source = + path + ;
扩展属性= \Excel 8.0; HDR =是; IMEX = 2 \);
}
else if (Path.GetExtension(path)== 。xlsx)
{
oledbConn = new OleDbConnection( @ Provider = Microsoft.ACE.OLEDB.12.0;
Data Source = + path + ;
Extended Properties ='Excel 12.0; HDR = YES ; IMEX = 1;';跨度>);
}
oledbConn.Open();
OleDbCommand cmd = new OleDbCommand(); ;
OleDbDataAdapter oleda = new OleDbDataAdapter();
DataSet ds = new DataSet();
// 选择Slno的distict列表
cmd.Connection = oledbConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SELECT * FROM [Sheet1
跨度>;
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(ds);
cmd = null ;
oledbConn.Close();
oledbConn = null ;
if (renameHeader == true )
{
foreach (DataColumn列 in ds.Tables [ 0 ]。列)
{
string cName = ds.Tables [ 0 ].Rows [ 0 ] [column.ColumnName] .ToString();
if (!ds.Tables [ 0 ]。Columns.Contains(cName)& & cName!= )
{
column.ColumnName = cName ;
}
}
ds.Tables [ 0 ]。行[ 0 跨度>]删除(); // 如果您不再需要该行
ds.AcceptChanges();
}
return (ds);
}
如果这样可以正常使用您创建的查询检查可能是Sheetname或Column name不匹配。
public void impSpanJvNSEF(string FilePath, int eximID) { try { DataSet ds = o_Cls_Utility.ReadExcelToDataSet(FilePath, "SHEET1", "A6",false); }}
public DataSet ReadExcelToDataSet(string filepath, string SheetName, string ColumnToCheckNotNull, bool renameHeader = true)
{
// need to pass relative path after deploying on server
string path = System.IO.Path.GetFullPath(filepath);
/* connection string to work with excel file. HDR=Yes - indicates
that the first row contains columnnames, not data. HDR=No - indicates
the opposite. "IMEX=1;" tells the driver to always read "intermixed"
(numbers, dates, strings etc) data columns as text.
Note that this option might affect excel sheet write access negative. */
OleDbConnection oledbConn;
oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
oledbConn.Open();
OleDbCommand cmd = new OleDbCommand(); ;
OleDbDataAdapter oleda = new OleDbDataAdapter();
DataSet ds = new DataSet();
// selecting distict list of Slno
cmd.Connection = oledbConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM [" + SheetName + "$] WHERE " + ColumnToCheckNotNull + " IS NOT NULL";
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(ds);
cmd = null;
oledbConn.Close();
oledbConn = null;
if (renameHeader == true)
{
foreach (DataColumn column in ds.Tables[0].Columns)
{
string cName = ds.Tables[0].Rows[0][column.ColumnName].ToString();
if (!ds.Tables[0].Columns.Contains(cName) && cName != "")
{
column.ColumnName = cName;
}
}
ds.Tables[0].Rows[0].Delete(); //If you don't need that row any more
ds.AcceptChanges();
}
return (ds);
}
but in above query my
Collapse | Copy Code
oleda.Fill(ds);
shows empty data and gives aboce exception
解决方案
WHERE " + ColumnToCheckNotNull + " IS NOT NULL"; oleda = new OleDbDataAdapter(cmd); oleda.Fill(ds); cmd = null; oledbConn.Close(); oledbConn = null; if (renameHeader == true) { foreach (DataColumn column in ds.Tables[0].Columns) { string cName = ds.Tables[0].Rows[0][column.ColumnName].ToString(); if (!ds.Tables[0].Columns.Contains(cName) && cName != "") { column.ColumnName = cName; } } ds.Tables[0].Rows[0].Delete(); //If you don't need that row any more ds.AcceptChanges(); } return (ds); }
but in above query my
Collapse | Copy Code
oleda.Fill(ds);
shows empty data and gives aboce exception
hi ,
Can you test like this and check weather you get the data from Excel to dataset.
I have little changed your function and here the query i hard code it for you to test weather you are getting the result.
public DataSet ReadExcelToDataSet(string filepath, string SheetName, string ColumnToCheckNotNull, bool renameHeader = true) { // need to pass relative path after deploying on server string path = System.IO.Path.GetFullPath(filepath); /* connection string to work with excel file. HDR=Yes - indicates that the first row contains columnnames, not data. HDR=No - indicates the opposite. "IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative. */ OleDbConnection oledbConn; if (Path.GetExtension(path) == ".xls") { oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + path + "; Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""); } else if (Path.GetExtension(path) == ".xlsx") { oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + path + "; Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"); } oledbConn.Open(); OleDbCommand cmd = new OleDbCommand(); ; OleDbDataAdapter oleda = new OleDbDataAdapter(); DataSet ds = new DataSet(); // selecting distict list of Slno cmd.Connection = oledbConn; cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT * FROM [Sheet1
"; oleda = new OleDbDataAdapter(cmd); oleda.Fill(ds); cmd = null; oledbConn.Close(); oledbConn = null; if (renameHeader == true) { foreach (DataColumn column in ds.Tables[0].Columns) { string cName = ds.Tables[0].Rows[0][column.ColumnName].ToString(); if (!ds.Tables[0].Columns.Contains(cName) && cName != "") { column.ColumnName = cName; } } ds.Tables[0].Rows[0].Delete(); //If you don't need that row any more ds.AcceptChanges(); } return (ds); }
If this works fine Check with the Query you have created might be Sheetname or Column name not matching.
这篇关于如何解决异常没有给出一个或多个必需参数的值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文