各位大哥帮忙给看一下以下代码有什么错误
布丁88 2013-03-26 08:57:44 using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace zicai
{
public partial class zucai : Form
{
OleDbConnection objConnection;
OleDbCommand sqlcmd1,sqlcmd2;
OleDbCommand sqlInsert,sqlUpdate;
OleDbDataReader reader,reader1;
String dateTime = null;
public zucai()
{
InitializeComponent();
connAccess();
}
private void butStart_Click(object sender, EventArgs e)
{
ReadData();
}
public Boolean connAccess()
{
Boolean status = false;
string strConnection="Provider=Microsoft.Jet.OleDb.4.0;";
strConnection += @"Data Source=//127.0.0.1//access//zicai.mdb";
try
{
objConnection = new OleDbConnection(strConnection); //建立连接
objConnection.Open(); //打开连接
MessageBox.Show("连接成功");
}catch(Exception ex)
{
MessageBox.Show("Conncection:"+ex.Message);
}
return status;
}
public Boolean ReadData()
{
//String insertStr = null;
Boolean status = false;
//insertStr = @"insert into part2 values()";//sql语句
double id=0,sum=0,count=0,numb=0,num=0;
String factory=null, partNo=null;
try
{
sqlcmd1 = new OleDbCommand(@"select id,廠別,發料料號,數量分配,供貨數量 from part", objConnection);
reader = sqlcmd1.ExecuteReader(); //执行查询
while (reader.Read())
{ //这个read调用很重要!不写的话运行时将提示找不到数据
id =(int) reader.GetValue(0);
factory = reader.GetString(1);
partNo = reader.GetString(2);
sum =reader.GetDouble(3);
num = reader.GetDouble(4);
sqlcmd2 = new OleDbCommand("select id,有效數據量,採購回覆交貨日 from po where 廠別='" + factory + "' and 料號='" + partNo + "' and 有效數據量>0 and rownum=1 order by 採購回覆交貨日", objConnection);
reader1 = sqlcmd2.ExecuteReader();
if (reader1.Read())
{
numb =(int) reader1.GetInt32(0);
count = (int)reader1.GetInt32(1);
dateTime = reader1.GetString(2);
}
reader1.Close();
sqlInsert = new OleDbCommand("insert into part2 select * from part where id=" + id + " ", objConnection);
sqlInsert.ExecuteNonQuery();
if (count > sum)
{
sqlUpdate = new OleDbCommand("update part2 set 採購回覆交貨日='" + dateTime + "',供貨數量=" + sum + ",status='Y' where id=" + id + "", objConnection);
sqlUpdate.ExecuteNonQuery();
sqlUpdate = new OleDbCommand("update po set 供貨數量=" + (count - sum) + " where id=" + numb + "", objConnection);
sqlUpdate.ExecuteNonQuery();
}
else if (count == sum)
{
sqlUpdate = new OleDbCommand("update part2 set 採購回覆交貨日='" + dateTime + "',供貨數量=" + sum + ",status='Y' where id=" + id + "", objConnection);
sqlUpdate.ExecuteNonQuery();
sqlUpdate = new OleDbCommand("update po set 供貨數量=" + (count - sum) + " where id=" + numb + "", objConnection);
sqlUpdate.ExecuteNonQuery();
}
else
{
while (true)
{
sqlcmd2 = new OleDbCommand("select status from part2 where id=" + id + " and status='Y'", objConnection);
reader1 = sqlcmd2.ExecuteReader();
if (!reader1.Read())
break;
else
upDate(num, numb, count, sum, id, factory, partNo);
}
}
/*sqlInsert = new OleDbCommand(@"insert into part2 (ID,廠別,訂單機種,訂單號碼-項次,業務開單日,工廠交貨日,製件位置,發料料號,"
+ "品名規格,當時庫存,當時在驗,工單需求,需求時間,數量分配,採購回覆交貨日,供貨數量,料件供應來源,採購員代號_姓名,"
+ "供應商代號,供應商簡稱,承認,採購開單日,L/T,交貨日期,供應商確認,預計開工日,延遲,PO_EDC交期,MRP控制員_名稱,完工入庫數,"
+ "工單編號,工單製件編號,生產數量,母工單預計開工日,工作中心,工作中心說明,工單狀態,有舊料PO之舊料料號,建議改發(主)替代料)"
+" values("+reader.GetInt32(1)+",'"+reader.GetString(2)+"')", objConnection);*/
}
status = true;
objConnection.Close();
reader.Close();
}catch(Exception ex){
status = false;
MessageBox.Show("Update:"+ex.Message);
}
return status;
}
public void upDate(double num, double numb, double count, double sum, double id, String factory, String partNo)
{
num = sum - count;
try
{
sqlUpdate = new OleDbCommand("update part2 set 採購回覆交貨日='" + dateTime + "',供貨數量=" + count + ",status='Y' where id=" + id + "", objConnection);
sqlUpdate.ExecuteNonQuery();
sqlUpdate = new OleDbCommand("update po set 供貨數量=" + 0 + " where id=" + numb + "", objConnection);
sqlUpdate.ExecuteNonQuery();
sqlInsert = new OleDbCommand("insert into part2 as (select * from part where id=" + id + ") ", objConnection);
sqlInsert.ExecuteNonQuery();
sqlcmd2 = new OleDbCommand("select id,有效數據量 from po where 廠別='" + factory + "' and 料號='" + partNo + "' and 有效數據量>0 and rownum=1 order by 採購回覆交貨日", objConnection);
reader1 = sqlcmd2.ExecuteReader();
if (reader1.Read())
{
numb = reader1.GetInt32(1);
count = reader1.GetInt32(2);
dateTime = reader1.GetString(3);
}
reader1.Close();
if (numb > num)
{
sqlUpdate = new OleDbCommand("update part2 set 採購回覆交貨日='" + dateTime + "',供貨數量=" + num + ",status='Y' where id=" + id + "", objConnection);
sqlUpdate.ExecuteNonQuery();
sqlUpdate = new OleDbCommand("update po set 供貨數量=" + (numb - num) + " where id=" + numb + "", objConnection);
sqlUpdate.ExecuteNonQuery();
}
else if (numb == num)
{
sqlUpdate = new OleDbCommand("update part2 set 採購回覆交貨日='" + dateTime + "',供貨數量=" + sum + ",status='Y' where id=" + id + "", objConnection);
sqlUpdate.ExecuteNonQuery();
sqlUpdate = new OleDbCommand("update po set 供貨數量=" + (numb - num) + " where id=" + numb + "", objConnection);
sqlUpdate.ExecuteNonQuery();
}
else
{
sqlUpdate = new OleDbCommand("update part2 set 採購回覆交貨日='" + dateTime + "',供貨數量=" + numb + ",status='Y' where id=" + id + "", objConnection);
sqlUpdate.ExecuteNonQuery();
sqlUpdate = new OleDbCommand("update po set 供貨數量=" + 0 + " where id=" + numb + "", objConnection);
sqlUpdate.ExecuteNonQuery();
}
}catch(Exception ex)
{
MessageBox.Show("Data:"+ex.Message);
}
}
}
}