i working of project requires me grab recent data ms access databasse data existing table in oracle.
i complete project; have 1 small problem: when compiler finished running console app, oracle table has 1 row each value null.
i have been staring @ program hours , getting nowhere. wondering if first set of eyes me through 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 connection ms access , grab recent data table
odbcconnection dbconnection = new odbcconnection(connectionstring); odbccommand dbcommand = dbconnection.createcommand(); dbconnection.open(); dbcommand.commandtext = "select datetime, s1flow, s2flow, s3flow, s4flow, s1flowtotal, s2flowtotal, s3flowtotal, s4flowtotal commonstation format(datetime, 'mm/dd/yyyy') >=(select format(max(datetime),'mm/dd/yyyy') commonstation)"; dbcommand.executenonquery(); odbcdatareader dbreader = dbcommand.executereader(); this portion outputs fieldname console window. , following console.writeline () commands sort of sanity checks me ensure gragb data looking for.
int fcount = dbreader.fieldcount; console.write(""); (int = 0; < fcount; i++) { string fname = dbreader.getname(i); console.write(fname + "\t"); } console.writeline(); this portion sends data oracle table. there again console.writeline() command here sanity check info ms access want.
try { while (dbreader.read()) { string connstring = "dsn=gas_meter_proj;uid=cm;pwd=cmdev123"; odbcconnection conn = new odbcconnection(connstring); string sqlins = @"insert 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 in case there general error in running program, have general explanation , coming from.
} catch (exception ex) { console.writeline(ex.tostring()); } { dbreader.close(); dbcommand.dispose(); dbconnection.close(); } } } } again, of information ms access, , appears getting data there row filled null. can me understand going on here?
1) why calling executenonquery , execute reader? remove executenonquery statement.
2) sanity check consuming rows , time code reaches statement while (dbreader.read()), there no more rows traverse. remove sanity check.
after making above changes, code should 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 commonstation format(datetime, 'mm/dd/yyyy') >=(select format(max(datetime),'mm/dd/yyyy') commonstation)"; //dbcommand.executenonquery(); //####this statement not required. remove odbcdatareader dbreader = dbcommand.executereader(); int fcount = dbreader.fieldcount; console.write(""); //####this loop read thru records. remove /* (int = 0; < 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 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()); } { dbreader.close(); dbcommand.dispose(); dbconnection.close(); } } } } 
Comments
Post a Comment