 |
|
ผมต้องการ insert ข้อมูลลง Oracle แต่ทำไม่ได้ แสดง ORA-00936: missing expression ตลอด แต่หาเท่าไหร่ก็หาไม่เจอว่าผิดตรงไหน เพราะลองนำข้อมูลไป insert ด้วย Toad แล้วก็ insert ได้ปกติ ช่วยดูหน่อยครับ ขอบคุณครับ
ปล.ใช้ DBClass ของคุณ pheak https://www.thaicreate.com/dotnet/forum/055716.html
DBClass.cs บางส่วน
01.
02.#region Oracle
03.public DataSet OracleGet(string sql, string tblName)
04.{
05. OracleConnection conn = new ConnectDB().OracleStrCon(BillConnStr);
06. OracleDataAdapter da = new OracleDataAdapter(sql, conn);
07. DataSet ds = new DataSet();
08. da.Fill(ds, tblName);
09. return ds;
10.}
11.public DataSet OracleGet(string sql, string tblName, OracleParameterCollection parameters)
12.{
13. OracleConnection conn = new ConnectDB().OracleStrCon(BillConnStr);
14. OracleDataAdapter da = new OracleDataAdapter(sql, conn);
15. DataSet ds = new DataSet();
16. foreach (OracleParameter param in parameters)
17. {
18. da.SelectCommand.Parameters.Add(param.ParameterName, param.OracleDbType).Value = param.Value;
19. }
20. da.Fill(ds, tblName);
21. return ds;
22.}
23.public int OracleExecute(string sql)
24.{
25. int i;
26. OracleConnection conn = new ConnectDB().OracleStrCon(BillConnStr);
27. OracleCommand cmd = new OracleCommand(sql, conn);
28. conn.Open();
29. i = cmd.ExecuteNonQuery();
30. conn.Close();
31. return i;
32.}
33.public int OracleExcute(string sql, OracleParameterCollection parameters)
34.{
35. int i;
36. OracleConnection conn = new ConnectDB().OracleStrCon(BillConnStr);
37. OracleCommand cmd = new OracleCommand(sql, conn);
38. foreach (OracleParameter param in parameters)
39. {
40. cmd.Parameters.Add(param.ParameterName, param.OracleDbType).Value = param.Value;
41. }
42. conn.Open();
43. i = cmd.ExecuteNonQuery();
44. conn.Close();
45. return i;
46.}
47.#endregion
Code (C#)
01.for (int r = 0; r < dtUNIT_MASTER_ToBill.Rows.Count; r++)
02.{
03. sql = "insert into UNIT_MASTER (USER_SEQ,BILL_PERIOD,USERNAME,USERADDR,USERTYPE,METER_TYPE,CALC_CODE,PRESSURE_CODE,SERIAL_NO,PEA_NO,INSTALL_DATE,PHASE_CODE,MULTI_UNIT,CURR_UNIT1) ";
04. sql += "values(@user_seq,@bill_period,@username,@useraddress,@usertype,@meter_type,@calc_code,@pressure_code,@serialnumber,@peanumber,to_date(@install_date,'DD/MM/YYYY HH24:MI:SS'),@phase_code,@multi_unit,@curr_unit1); ";
05. OracleParameterCollection param = new OracleCommand().Parameters;
06. param.Add("user_seq", OracleDbType.Char).Value = dtUNIT_MASTER_ToBill.Rows[r]["USER_SEQ"].ToString();
07. param.Add("bill_period", OracleDbType.Char).Value = dtUNIT_MASTER_ToBill.Rows[r]["BILL_PERIOD"].ToString();
08. param.Add("username", OracleDbType.Varchar2).Value = dtUNIT_MASTER_ToBill.Rows[r]["USERNAME"].ToString();
09. param.Add("useraddress", OracleDbType.Varchar2).Value = dtUNIT_MASTER_ToBill.Rows[r]["USERADDR"].ToString();
10. param.Add("usertype", OracleDbType.Char).Value = dtUNIT_MASTER_ToBill.Rows[r]["USERTYPE"].ToString();
11. param.Add("meter_type", OracleDbType.Char).Value = dtUNIT_MASTER_ToBill.Rows[r]["METER_TYPE"].ToString();
12. param.Add("calc_code", OracleDbType.Char).Value = dtUNIT_MASTER_ToBill.Rows[r]["CALC_CODE"].ToString();
13. param.Add("pressure_code", OracleDbType.Char).Value = dtUNIT_MASTER_ToBill.Rows[r]["PRESSURE_CODE"].ToString();
14. param.Add("serialnumber", OracleDbType.Char).Value = dtUNIT_MASTER_ToBill.Rows[r]["SERIAL_NO"].ToString();
15. param.Add("peanumber", OracleDbType.Char).Value = dtUNIT_MASTER_ToBill.Rows[r]["PEA_NO"].ToString();
16. param.Add("install_date", OracleDbType.Date).Value = Convert.ToDateTime(dtUNIT_MASTER_ToBill.Rows[r]["INSTALL_DATE"]);
17. param.Add("phase_code", OracleDbType.Char).Value = dtUNIT_MASTER_ToBill.Rows[r]["PHASE_CODE"].ToString();
18. param.Add("multi_unit", OracleDbType.Double).Value = Convert.ToDouble(dtUNIT_MASTER_ToBill.Rows[r]["MULTI_UNIT"]);
19. param.Add("curr_unit1", OracleDbType.Double).Value = Convert.ToDouble(dtUNIT_MASTER_ToBill.Rows[r]["CURR_UNIT1"]);
20. int i = new DBClass().OracleExcute(sql, param);
21.}
DataTable dtUNIT_MASTER_ToBill
000001,255705,การไฟฟ้า,ที่อยู่1,00,1,1,4,211339448,211339448,30/3/2557 0:00:00,3,1,4786120.14610851
0 000001,255709,การไฟฟ้า,ที่อยู่1,00,1,1,4,211339448,211339448,30/3/2557 0:00:00,3,1,5792757.12572683
0 000004,255705,การไฟฟ้า,ที่อยู่1,00,1,1,4,212501419,212501419,30/3/2557 0:00:00,3,1,1441221
0 000004,255709,การไฟฟ้า,ที่อยู่1,00,1,1,4,212501419,212501419,30/3/2557 0:00:00,3,1,2689658
0 000004,255710,การไฟฟ้า,ที่อยู่1,00,1,1,4,212501419,212501419,30/3/2557 0:00:00,3,1,3002378
0 000005,255705,การไฟฟ้า,ที่อยู่1,00,1,1,4,212501454,212501454,30/3/2557 0:00:00,3,1,702718
0 000005,255709,การไฟฟ้า,ที่อยู่1,00,1,1,4,212501454,212501454,30/3/2557 0:00:00,3,1,1428022
0 000005,255710,การไฟฟ้า,ที่อยู่1,00,1,1,4,212501454,212501454,30/3/2557 0:00:00,3,1,1611240
Tag : .NET, Oracle, Win (Windows App), C#
|
ประวัติการแก้ไข
2014-10-06 16:17:58
2014-10-06 16:19:05
2014-10-06 16:20:35
2014-10-06 16:23:18
|
 |
 |
 |
 |
| Date :
2014-10-06 16:14:08 |
By :
snowbell |
View :
2065 |
Reply :
2 |
|
 |
 |
 |
 |
|
|
|
|
 |
