Fetch and Insert Data From Axapta to Access Database

void clicked()


{



loginProperty loginProperty = new LoginProperty();

ODBCConnection odbcConnection;

Statement sql;

Statement sql2;

ResultSet result;



int _id;

str _statement = "Insert into mcsProjectTable ([Project Name],[Project Description],[Project Owner],[Project Owner Contact Name],[Project Owner Email],[Project owner Phone Number]," ;

_statement=_statement + "[Under Bidding],[Awarded],[Main Contractor],[Main Contractor Address],[Main Contractor Email],[Main Contractor Contact Person],[Main Contractor Phone Number],";

_statement=_statement + "[Main Contractor1],[Main Contractor1 Address],[Main Contractor1 Email],[Main Contractor1 Contact Person],[Main Contractor1 Phone Number],[Main Contractor2],[Main Contractor2 Address],[Main Contractor2 Email],";

_statement=_statement + "[Main Contractor2 Contact Person],[Main Contractor2 Phone Number],[Main Contractor3],[Main Contractor3 Address],[Main Contractor3 Email],[Main Contractor3 Contact Person],[Main Contractor3 Phone Number],";

_statement=_statement + "[Status Over All],[Location],[Scope1],[Scope2],[Scope3],[Scope4],[SPG INQUIRY Number],[Notes],[Region],[SalesMan],[Estimation Engineer])";

_statement=_statement + "values ("+ "'" + ProjectName.text() + "'" + "," + "'" + ProjectDescription.text() + "'" + "," + "'" + ProjectOwner.text() + "'" + "," + "'" + ProjectOwnerContactName.text() + "'" ;

_statement=_statement + "," + "'" + ProjectOwnerEmail.text() + "'" + "," + "'" + ProjectOwnerPhoneNumber.text() + "'";

_statement=_statement + "," + "'" + UnderBidding.text() + "'" + "," + "'" + Awarded.text() + "'";

_statement=_statement + "," + "'" + MainContractor.text() + "'" + "," + "'" + MainContractorAddress.text() + "'";

_statement=_statement + "," + "'" + MainContractorEmail.text() + "'" + "," + "'" + MainContractorContactPerson.text() + "'";

_statement=_statement + "," + "'" + MainContractorPhoneNumber.text() + "'" + "," + "'" + MainContractor1.text() + "'";

_statement=_statement + "," + "'" + MainContractor1Address.text() + "'" + "," + "'" + MainContractor1Email.text() + "'";

_statement=_statement + "," + "'" + MainContractor1ContactPerson.text() + "'" + "," + "'" + MainContractor1PhoneNumber.text() + "'";

_statement=_statement + "," + "'" + MainContractor2.text() + "'" + "," + "'" + MainContractor2Address.text() + "'";

_statement=_statement + "," + "'" + MainContractor2Email.text() + "'" + "," + "'" + MainContractor2ContactPerson.text() + "'";

_statement=_statement + "," + "'" + MainContractor2PhoneNumber.text() + "'" + "," + "'" + MainContractor3.text() + "'";

_statement=_statement + "," + "'" + MainContractor3Address.text() + "'" + "," + "'" + MainContractor3Email.text() + "'";

_statement=_statement + "," + "'" + MainContractor3ContactPerson.text() + "'" + "," + "'" + MainContractor3PhoneNumber.text() + "'";

_statement=_statement + "," + "'" + Status.text() + "'" + "," + "'" + Location.text() + "'";

_statement=_statement + "," + "'" + Scope1.text() + "'" + "," + "'" + Scope2.text() + "'";

_statement=_statement + "," + "'" + Scope3.text() + "'" + "," + "'" + Scope4.text() + "'";

_statement=_statement + "," + "'" + SPGInquiryNumber.text() + "'" + "," + "'" + Notes.text() + "'";

_statement=_statement + "," + "'" + Region.text() + "'" + "," + "'" + SalesMan.text() + "'";

_statement=_statement + "," + "'" + EstimationEngineer.text() + "'" + ")" ;









loginProperty.setDSN('AccessDSN');



odbcConnection = new ODBCConnection(loginProperty);

if (odbcConnection)

{

sql = odbcConnection.createStatement();

_id = sql.executeUpdate(_statement);



if(_id ==0 )

{



sql2 = odbcConnection.createStatement();

result = sql2.executeQuery("select * from mcsProjectTable");





delete_from referencedProjectFormTable ;



while (result.next())

{

// referencedProjectFormTable.ProjectID = result.getInt(1);

referencedProjectFormTable.ProjectName = result.getString(2);

referencedProjectFormTable.ProjectDescription = result.getString(3);

referencedProjectFormTable.ProjectOwner = result.getString(4);

referencedProjectFormTable.ProjectOwnerContactName = result.getString(5);

referencedProjectFormTable.ProjectOwnerEmail = result.getString(6);

referencedProjectFormTable.ProjectOwnerPhoneNumber = result.getString(7);

referencedProjectFormTable.UnderBidding = result.getString(9);

referencedProjectFormTable.Awarded = result.getString(10);

referencedProjectFormTable.MainContractor = result.getString(12);

referencedProjectFormTable.MainContractorAddress = result.getString(13);

referencedProjectFormTable.MainContractorEmail = result.getString(14);

referencedProjectFormTable.MainContractorContactPerson = result.getString(15);

referencedProjectFormTable.MainContractorPhoneNumber= result.getString(16);

referencedProjectFormTable.MainContractor1= result.getString(17);

referencedProjectFormTable.MainContractor1Address= result.getString(18);

referencedProjectFormTable.MainContractor1Email= result.getString(19);

referencedProjectFormTable.MainContractor1ContactPerson= result.getString(20);

referencedProjectFormTable.MainContractor1PhoneNumber= result.getString(21);

referencedProjectFormTable.MainContractor2= result.getString(22);

referencedProjectFormTable.MainContractor2Address= result.getString(23);

referencedProjectFormTable.MainContractor2Email= result.getString(24);

referencedProjectFormTable.MainContractor2ContactPerson= result.getString(25);

referencedProjectFormTable.MainContractor2PhoneNumber= result.getString(26);

referencedProjectFormTable.MainContractor3= result.getString(27);

referencedProjectFormTable.MainContractor3Address= result.getString(28);

referencedProjectFormTable.MainContractor3Email= result.getString(29);

referencedProjectFormTable.MainContractor3ContactPerson= result.getString(30);

referencedProjectFormTable.MainContractor3PhoneNumber= result.getString(31);

referencedProjectFormTable.StatusOverAll= result.getString(32);

referencedProjectFormTable.Location= result.getString(33);

referencedProjectFormTable.Scope1= result.getString(34);

referencedProjectFormTable.Scope2= result.getString(35);

referencedProjectFormTable.Scope3= result.getString(36);

referencedProjectFormTable.Scope4= result.getString(37);

referencedProjectFormTable.SPGInquiryNumber= result.getString(38);

referencedProjectFormTable.Notes= result.getString(39);

referencedProjectFormTable.Region= result.getString(41);

referencedProjectFormTable.SalesMan= result.getString(42);

referencedProjectFormTable.EstimationEngineer= result.getString(43);

referencedProjectFormTable.insert();

}



ProjectFormTable_ds.refresh();

odbcConnection=null;

info("Record Added");





}



}

}