using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.OleDb ;
using System.Text;
using System.Threading.Tasks;
namespace 读取Excel文件
{
class Program
{
static void Main(string[] args)
{
Program MyPr = new Program();
MyPr.DSToExcel1();
Console.ReadKey();
}
//修改excel
public void DSToExcel1()
{
//实例化OleDbConnection,连接excel
string strcon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=E:\\VS\\语音点名\\学生表;" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strcon);
conn.Open();
//实例化OleDbCommand,修改excel;
OleDbCommand myComm = new OleDbCommand();
myComm.Connection = conn;
myComm.CommandText = "update [Sheet1$] set 到课次数 = 1 where 学号 = ‘123456789’";
//实例化OleDbDataAdapter,将execl送入dataset;
OleDbDataAdapter myadapter = new OleDbDataAdapter();
myadapter.SelectCommand = myComm;
//实例化dataset,提交查询
DataSet MyDS = new DataSet();
myadapter.Fill(MyDS);
conn.Close();
}
}
}
现在问题是总是提示“ myadapter.Fill(MyDS);”这个地方至少一个参数没有被指定值。还有关于sql语句 的用法似乎和在数据库中也不一样?有没有高手能帮忙解释一下,小弟卡在这里很长时间了,太痛苦了
3 个解决方案
#1
查询命令为什么初始化了一个update
#2
update是sql语句,想要用来更改excel中数据的
#3
public class OrderBLL
{
/// <summary>
/// 查询订单列表
/// </summary>
public List<Order> GetOrderList()
{
List<Order> orderList = new List<Order>();
string sql = "SELECT * FROM [Order$]";
DataSet dateSet = ExcelHelper.GetReader(sql);
orderList = Order.ToList(dateSet);
return orderList;
}
/// <summary>
/// 获取订单总数
/// </summary>
public int GetOrderCount()
{
int result = 0;
string sql = "SELECT COUNT(*) FROM [Order$]";
result = ExcelHelper.GetScalar(sql);
return result;
}
/// <summary>
/// 新增订单
/// </summary>
public int AddOrder(Order param)
{
int result = 0;
string sql = "INSERT INTO [Order$](OrderNo,ProductName,Quantity,Money,SaleDate) VALUES(@OrderNo,@ProductName,@Quantity,@Money,@SaleDate)";
OleDbParameter[] oleDbParam = new OleDbParameter[]
{
new OleDbParameter("@OrderNo", param.OrderNo),
new OleDbParameter("@ProductName", param.ProductName),
new OleDbParameter("@Quantity", param.Quantity),
new OleDbParameter("@Money",param.Money),
new OleDbParameter("@SaleDate",param.SaleDate)
};
result = ExcelHelper.ExecuteCommand(sql, oleDbParam);
return result;
}
/// <summary>
/// 修改订单
/// </summary>
public int UpdateOrder(Order param)
{
int result = 0;
if (!String.IsNullOrEmpty(param.OrderNo))
{
string sql = "UPDATE [Order$] SET ProductName=@ProductName,Quantity=@Quantity,Money=@Money WHERE OrderNo=@OrderNo";
OleDbParameter[] sqlParam = new OleDbParameter[]
{
new OleDbParameter("@OrderNo",param.OrderNo),
new OleDbParameter("@ProductName",param.ProductName),
new OleDbParameter("@Quantity", param.Quantity),
new OleDbParameter("@Money", param.Money)
};
result = ExcelHelper.ExecuteCommand(sql, sqlParam);
}
return result;
}
/// <summary>
/// 删除订单
/// </summary>
public int DeleteOrder(Order param)
{
int result = 0;
if (!String.IsNullOrEmpty(param.OrderNo))
{
string sql = "DELETE [Order$] WHERE OrderNo=@OrderNo";
OleDbParameter[] sqlParam = new OleDbParameter[]
{
new OleDbParameter("@OrderNo",param.OrderNo),
};
result = ExcelHelper.ExecuteCommand(sql, sqlParam);
}
return result;
}
}
{
/// <summary>
/// 查询订单列表
/// </summary>
public List<Order> GetOrderList()
{
List<Order> orderList = new List<Order>();
string sql = "SELECT * FROM [Order$]";
DataSet dateSet = ExcelHelper.GetReader(sql);
orderList = Order.ToList(dateSet);
return orderList;
}
/// <summary>
/// 获取订单总数
/// </summary>
public int GetOrderCount()
{
int result = 0;
string sql = "SELECT COUNT(*) FROM [Order$]";
result = ExcelHelper.GetScalar(sql);
return result;
}
/// <summary>
/// 新增订单
/// </summary>
public int AddOrder(Order param)
{
int result = 0;
string sql = "INSERT INTO [Order$](OrderNo,ProductName,Quantity,Money,SaleDate) VALUES(@OrderNo,@ProductName,@Quantity,@Money,@SaleDate)";
OleDbParameter[] oleDbParam = new OleDbParameter[]
{
new OleDbParameter("@OrderNo", param.OrderNo),
new OleDbParameter("@ProductName", param.ProductName),
new OleDbParameter("@Quantity", param.Quantity),
new OleDbParameter("@Money",param.Money),
new OleDbParameter("@SaleDate",param.SaleDate)
};
result = ExcelHelper.ExecuteCommand(sql, oleDbParam);
return result;
}
/// <summary>
/// 修改订单
/// </summary>
public int UpdateOrder(Order param)
{
int result = 0;
if (!String.IsNullOrEmpty(param.OrderNo))
{
string sql = "UPDATE [Order$] SET ProductName=@ProductName,Quantity=@Quantity,Money=@Money WHERE OrderNo=@OrderNo";
OleDbParameter[] sqlParam = new OleDbParameter[]
{
new OleDbParameter("@OrderNo",param.OrderNo),
new OleDbParameter("@ProductName",param.ProductName),
new OleDbParameter("@Quantity", param.Quantity),
new OleDbParameter("@Money", param.Money)
};
result = ExcelHelper.ExecuteCommand(sql, sqlParam);
}
return result;
}
/// <summary>
/// 删除订单
/// </summary>
public int DeleteOrder(Order param)
{
int result = 0;
if (!String.IsNullOrEmpty(param.OrderNo))
{
string sql = "DELETE [Order$] WHERE OrderNo=@OrderNo";
OleDbParameter[] sqlParam = new OleDbParameter[]
{
new OleDbParameter("@OrderNo",param.OrderNo),
};
result = ExcelHelper.ExecuteCommand(sql, sqlParam);
}
return result;
}
}
#1
查询命令为什么初始化了一个update
#2
update是sql语句,想要用来更改excel中数据的
#3
public class OrderBLL
{
/// <summary>
/// 查询订单列表
/// </summary>
public List<Order> GetOrderList()
{
List<Order> orderList = new List<Order>();
string sql = "SELECT * FROM [Order$]";
DataSet dateSet = ExcelHelper.GetReader(sql);
orderList = Order.ToList(dateSet);
return orderList;
}
/// <summary>
/// 获取订单总数
/// </summary>
public int GetOrderCount()
{
int result = 0;
string sql = "SELECT COUNT(*) FROM [Order$]";
result = ExcelHelper.GetScalar(sql);
return result;
}
/// <summary>
/// 新增订单
/// </summary>
public int AddOrder(Order param)
{
int result = 0;
string sql = "INSERT INTO [Order$](OrderNo,ProductName,Quantity,Money,SaleDate) VALUES(@OrderNo,@ProductName,@Quantity,@Money,@SaleDate)";
OleDbParameter[] oleDbParam = new OleDbParameter[]
{
new OleDbParameter("@OrderNo", param.OrderNo),
new OleDbParameter("@ProductName", param.ProductName),
new OleDbParameter("@Quantity", param.Quantity),
new OleDbParameter("@Money",param.Money),
new OleDbParameter("@SaleDate",param.SaleDate)
};
result = ExcelHelper.ExecuteCommand(sql, oleDbParam);
return result;
}
/// <summary>
/// 修改订单
/// </summary>
public int UpdateOrder(Order param)
{
int result = 0;
if (!String.IsNullOrEmpty(param.OrderNo))
{
string sql = "UPDATE [Order$] SET ProductName=@ProductName,Quantity=@Quantity,Money=@Money WHERE OrderNo=@OrderNo";
OleDbParameter[] sqlParam = new OleDbParameter[]
{
new OleDbParameter("@OrderNo",param.OrderNo),
new OleDbParameter("@ProductName",param.ProductName),
new OleDbParameter("@Quantity", param.Quantity),
new OleDbParameter("@Money", param.Money)
};
result = ExcelHelper.ExecuteCommand(sql, sqlParam);
}
return result;
}
/// <summary>
/// 删除订单
/// </summary>
public int DeleteOrder(Order param)
{
int result = 0;
if (!String.IsNullOrEmpty(param.OrderNo))
{
string sql = "DELETE [Order$] WHERE OrderNo=@OrderNo";
OleDbParameter[] sqlParam = new OleDbParameter[]
{
new OleDbParameter("@OrderNo",param.OrderNo),
};
result = ExcelHelper.ExecuteCommand(sql, sqlParam);
}
return result;
}
}
{
/// <summary>
/// 查询订单列表
/// </summary>
public List<Order> GetOrderList()
{
List<Order> orderList = new List<Order>();
string sql = "SELECT * FROM [Order$]";
DataSet dateSet = ExcelHelper.GetReader(sql);
orderList = Order.ToList(dateSet);
return orderList;
}
/// <summary>
/// 获取订单总数
/// </summary>
public int GetOrderCount()
{
int result = 0;
string sql = "SELECT COUNT(*) FROM [Order$]";
result = ExcelHelper.GetScalar(sql);
return result;
}
/// <summary>
/// 新增订单
/// </summary>
public int AddOrder(Order param)
{
int result = 0;
string sql = "INSERT INTO [Order$](OrderNo,ProductName,Quantity,Money,SaleDate) VALUES(@OrderNo,@ProductName,@Quantity,@Money,@SaleDate)";
OleDbParameter[] oleDbParam = new OleDbParameter[]
{
new OleDbParameter("@OrderNo", param.OrderNo),
new OleDbParameter("@ProductName", param.ProductName),
new OleDbParameter("@Quantity", param.Quantity),
new OleDbParameter("@Money",param.Money),
new OleDbParameter("@SaleDate",param.SaleDate)
};
result = ExcelHelper.ExecuteCommand(sql, oleDbParam);
return result;
}
/// <summary>
/// 修改订单
/// </summary>
public int UpdateOrder(Order param)
{
int result = 0;
if (!String.IsNullOrEmpty(param.OrderNo))
{
string sql = "UPDATE [Order$] SET ProductName=@ProductName,Quantity=@Quantity,Money=@Money WHERE OrderNo=@OrderNo";
OleDbParameter[] sqlParam = new OleDbParameter[]
{
new OleDbParameter("@OrderNo",param.OrderNo),
new OleDbParameter("@ProductName",param.ProductName),
new OleDbParameter("@Quantity", param.Quantity),
new OleDbParameter("@Money", param.Money)
};
result = ExcelHelper.ExecuteCommand(sql, sqlParam);
}
return result;
}
/// <summary>
/// 删除订单
/// </summary>
public int DeleteOrder(Order param)
{
int result = 0;
if (!String.IsNullOrEmpty(param.OrderNo))
{
string sql = "DELETE [Order$] WHERE OrderNo=@OrderNo";
OleDbParameter[] sqlParam = new OleDbParameter[]
{
new OleDbParameter("@OrderNo",param.OrderNo),
};
result = ExcelHelper.ExecuteCommand(sql, sqlParam);
}
return result;
}
}