getting null values in place of data when sending to Oracle

I am working of a project that r开发者_C百科equires 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?

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:

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 = "";
                        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(); //####THIS STATEMENT IS NOT REQUIRED. REMOVE IT
                        OdbcDataReader DbReader = DbCommand.ExecuteReader();
                        int fCount = DbReader.FieldCount;
                        Console.Write("");

                        //####THIS FOR LOOP WILL READ THRU ALL RECORDS. REMOVE IT
                        /*
                        for (int i = 0; i < fCount; i++)
                        {
                                String fName = DbReader.GetName(i);
                                Console.Write(fName + "\t");
                        }
                        */
                    Console.WriteLine();
                    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();
                    }
        }
    }
}

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.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
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 = "";
      string sqlins = "";
      string connString = "DSN=Gas_meter_proj;Uid=cm;Pwd=cmdev123";
      OdbcConnection conn = new OdbcConnection(connString);
      OdbcCommand cmdnon = new OdbcCommand(sqlins, conn);
      conn.Open();
      cmdnon.Parameters.Add(col0, OdbcType.DateTime);
      cmdnon.Parameters.Add(col1, OdbcType.Numeric);
      cmdnon.Parameters.Add(col2, OdbcType.Numeric);
      cmdnon.Parameters.Add(col3, OdbcType.Numeric);
      cmdnon.Parameters.Add(col4, OdbcType.Numeric);
      cmdnon.Parameters.Add(col5, OdbcType.Numeric);
      cmdnon.Parameters.Add(col6, OdbcType.Numeric);
      cmdnon.Parameters.Add(col7, OdbcType.Numeric);
      cmdnon.Parameters.Add(col8, OdbcType.Numeric);

      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)";
      OdbcDataReader DbReader = DbCommand.ExecuteReader();
      int fCount = DbReader.FieldCount;
      Console.Write("");
      /*
        for (int i = 0; i < fCount; i++)
        {
          String fName = DbReader.GetName(i);
          Console.Write(fName + "\t");
        }
      */
      Console.WriteLine();
      try
      {
        while (DbReader.Read())
        {
          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();
          cmdnon.CommandText = "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 + "'))";
          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();
          Console.WriteLine(rowsAffected);
        }
      }
      catch (Exception ex)
      {
         Console.WriteLine(ex.ToString());
      }
      finally
      {
         conn2.Close();      
         DbReader.Close();
         DbCommand.Dispose();
         DbConnection.Close();
      }          
    }
  }
}