发送到 Oracle 时使用空值代替数据
我正在从事一个项目，要求我从 MS Access 数据库中获取最新数据，然后将数据保存到 Oracle 中的现有表中。
我已经快完成这个项目了；但是我有一个小问题：当编译器完成运行控制台应用程序时，oracle 表中有一行，其中每个值现在均为空。
我已经盯着这个程序看了好几个小时了，但一无所获。我想知道第一眼是否可以帮助我解决这个问题。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using System.Data.Odbc;
namespace ConsoleApplication4
{
class Program2
{
static void Main(string[] args)
{
string connectionString = "Dsn=Gas_meter";
string col0 = "";
string col1 = "";
string col2 = "";
string col3 = "";
string col4 = "";
string col5 = "";
string col6 = "";
string col7 = "";
string col8 = "";
这将建立与 MS Access 的连接并从表中获取最新数据
OdbcConnection DbConnection = new OdbcConnection(connectionString);
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbConnection.Open();
DbCommand.CommandText = "SELECT DateTime, S1Flow, S2Flow, S3Flow, S4Flow, S1FlowTotal, S2FlowTotal, S3FlowTotal, S4FlowTotal FROM CommonStation WHERE Format(DateTime, 'mm/dd/yyyy') >=(select Format(max(DateTime),'mm/dd/yyyy') from CommonStation)";
DbCommand.ExecuteNonQuery();
OdbcDataReader DbReader = DbCommand.ExecuteReader();
。这部分将字段名称输出到控制台窗口。这个和下面的 Console.WriteLine () 命令对我来说是一种健全性检查，以确保它抓取我正在寻找的所有数据。
int fCount = DbReader.FieldCount;
Console.Write("");
for (int i = 0; i < fCount; i++)
{
String fName = DbReader.GetName(i);
Console.Write(fName + "\t");
}
Console.WriteLine();
这部分将数据发送到 Oracle 表中。这里再次有一个 Console.WriteLine() 命令，用于健全性检查来自 MS Access 的信息是否是我想要的。
try
{
while (DbReader.Read())
{
string connString = "DSN=Gas_meter_proj;Uid=cm;Pwd=cmdev123";
OdbcConnection conn = new OdbcConnection(connString);
string sqlins = @"insert into Commonstation(CommStatDate_Time, S1_Flow, S2_Flow, S3_Flow, S4_Flow, S1_Flow_Total, S2_Flow_Total, S3_Flow_Total, S4_Flow_Total ) values (to_date('" +col0+"', 'MM/DD/YYYY HH:MI:SS AM' ),to_number('" + col1 + "'), to_number('" + col2 + "'), to_number('" + col3 + "'), to_number('" + col4 + "'),to_number('" + col5 + "'),to_number('" + col6 + "'),to_number('" + col7 + "'),to_number('" + col8 + "'))";
OdbcCommand cmdnon = new OdbcCommand(sqlins, conn);
cmdnon.Parameters.Add(col0, OdbcType.DateTime);
cmdnon.Parameters.Add(col1, OdbcType.Int);
cmdnon.Parameters.Add(col2, OdbcType.Int);
cmdnon.Parameters.Add(col3, OdbcType.Int);
cmdnon.Parameters.Add(col4, OdbcType.Int);
cmdnon.Parameters.Add(col5, OdbcType.Int);
cmdnon.Parameters.Add(col6, OdbcType.Int);
cmdnon.Parameters.Add(col7, OdbcType.Int);
cmdnon.Parameters.Add(col8, OdbcType.Int);
conn.Open();
col0 = DbReader["DateTime"].ToString();
col1 = DbReader["S1Flow"].ToString();
col2 = DbReader["S2Flow"].ToString();
col3 = DbReader["S3Flow"].ToString();
col4 = DbReader["S4Flow"].ToString();
col5 = DbReader["S1FlowTotal"].ToString();
col6 = DbReader["S2FlowTotal"].ToString();
col7 = DbReader["S3FlowTotal"].ToString();
col8 = DbReader["S4FlowTotal"].ToString();
Console.Write(col0 + "\t");
Console.Write(col1 + "\t");
Console.Write(col2 + "\t");
Console.Write(col3 + "\t");
Console.Write(col4 + "\t");
Console.Write(col5 + "\t");
Console.Write(col6 + "\t");
Console.Write(col7 + "\t");
Console.Write(col8 + "\t");
int rowsAffected = cmdnon.ExecuteNonQuery();
Console.WriteLine();
conn.Close();
Console.WriteLine(rowsAffected);
}
如果在运行程序时出现一般错误，这条捕获线，我对它是什么以及它来自何处有一个一般性解释。
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
DbReader.Close();
DbCommand.Dispose();
DbConnection.Close();
}
}
}
}
再次，我从 MS Access 获取了所有信息，看起来我正在获取所有数据，但有一行填充为空。有人可以帮助我理解这里发生了什么吗？
I am working of a project that requires me to grab the most recent data from an MS Access databasse then the data to existing table in Oracle.
I am nearly complete with this project; however I have one small problem: when the compiler is finished running the console app, the oracle table has one row where each value is now null.
I have been staring at this program for hours now and I am getting nowhere. I was wondering if a first set of eyes could help me through this problem.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using System.Data.Odbc;
namespace ConsoleApplication4
{
class Program2
{
static void Main(string[] args)
{
string connectionString = "Dsn=Gas_meter";
string col0 = "";
string col1 = "";
string col2 = "";
string col3 = "";
string col4 = "";
string col5 = "";
string col6 = "";
string col7 = "";
string col8 = "";
This establishes the connection to MS Access and grab the most recent data from the table
OdbcConnection DbConnection = new OdbcConnection(connectionString);
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbConnection.Open();
DbCommand.CommandText = "SELECT DateTime, S1Flow, S2Flow, S3Flow, S4Flow, S1FlowTotal, S2FlowTotal, S3FlowTotal, S4FlowTotal FROM CommonStation WHERE Format(DateTime, 'mm/dd/yyyy') >=(select Format(max(DateTime),'mm/dd/yyyy') from CommonStation)";
DbCommand.ExecuteNonQuery();
OdbcDataReader DbReader = DbCommand.ExecuteReader();
This portion outputs fieldname to the console window. This and the following Console.WriteLine () commands are sort of sanity checks for me to ensure that it is gragb all the data that I am looking for.
int fCount = DbReader.FieldCount;
Console.Write("");
for (int i = 0; i < fCount; i++)
{
String fName = DbReader.GetName(i);
Console.Write(fName + "\t");
}
Console.WriteLine();
This portion sends the data into the Oracle table. There is again a Console.WriteLine() command here for a sanity check that the info from MS Access what I want.
try
{
while (DbReader.Read())
{
string connString = "DSN=Gas_meter_proj;Uid=cm;Pwd=cmdev123";
OdbcConnection conn = new OdbcConnection(connString);
string sqlins = @"insert into Commonstation(CommStatDate_Time, S1_Flow, S2_Flow, S3_Flow, S4_Flow, S1_Flow_Total, S2_Flow_Total, S3_Flow_Total, S4_Flow_Total ) values (to_date('" +col0+"', 'MM/DD/YYYY HH:MI:SS AM' ),to_number('" + col1 + "'), to_number('" + col2 + "'), to_number('" + col3 + "'), to_number('" + col4 + "'),to_number('" + col5 + "'),to_number('" + col6 + "'),to_number('" + col7 + "'),to_number('" + col8 + "'))";
OdbcCommand cmdnon = new OdbcCommand(sqlins, conn);
cmdnon.Parameters.Add(col0, OdbcType.DateTime);
cmdnon.Parameters.Add(col1, OdbcType.Int);
cmdnon.Parameters.Add(col2, OdbcType.Int);
cmdnon.Parameters.Add(col3, OdbcType.Int);
cmdnon.Parameters.Add(col4, OdbcType.Int);
cmdnon.Parameters.Add(col5, OdbcType.Int);
cmdnon.Parameters.Add(col6, OdbcType.Int);
cmdnon.Parameters.Add(col7, OdbcType.Int);
cmdnon.Parameters.Add(col8, OdbcType.Int);
conn.Open();
col0 = DbReader["DateTime"].ToString();
col1 = DbReader["S1Flow"].ToString();
col2 = DbReader["S2Flow"].ToString();
col3 = DbReader["S3Flow"].ToString();
col4 = DbReader["S4Flow"].ToString();
col5 = DbReader["S1FlowTotal"].ToString();
col6 = DbReader["S2FlowTotal"].ToString();
col7 = DbReader["S3FlowTotal"].ToString();
col8 = DbReader["S4FlowTotal"].ToString();
Console.Write(col0 + "\t");
Console.Write(col1 + "\t");
Console.Write(col2 + "\t");
Console.Write(col3 + "\t");
Console.Write(col4 + "\t");
Console.Write(col5 + "\t");
Console.Write(col6 + "\t");
Console.Write(col7 + "\t");
Console.Write(col8 + "\t");
int rowsAffected = cmdnon.ExecuteNonQuery();
Console.WriteLine();
conn.Close();
Console.WriteLine(rowsAffected);
}
This catch line to in case there is an general error in running the program, I have a general explanation as to what it is and were it is coming from.
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
DbReader.Close();
DbCommand.Dispose();
DbConnection.Close();
}
}
}
}
Again, I get the all of the information from MS Access, and it appears that I am getting all the data but there is row is filled with null. Can someone help me to understand what is going on here?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
1）为什么要调用 ExecuteNonQuery 然后调用 Execute reader？删除 ExecuteNonQuery 语句。
2) 您的健全性检查正在消耗所有行，并且当代码到达语句
while (DbReader.Read())时，不再需要遍历任何行。删除健全性检查。进行上述更改后，您的代码应如下所示：
1) Why are you calling ExecuteNonQuery and then Execute reader? Remove the ExecuteNonQuery statement.
2) Your sanity check is consuming all the rows and by the time code reaches the statement
while (DbReader.Read()), there are no more rows to traverse. Remove the sanity check.After making the above changes, your code should look like:
好的，我解决了问题。在将代码发送到 oracle 之前，我没有设置值，因此它最初将值设置为 null，因为这是它在代码开头设置的值。最终代码的这一部分现在可以正常工作了。
Ok, I solved the problem. I was didnt have the code set a value before sending it to oracle so it intially setting the value as the null because that is what it set as at the beginning of the code. This portion of the final code now works as it should.