API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 25th, 2017
65
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 16.51 KB | None | 0 0
raw download clone embed print report
  1. #include "Connection.h"
  2. #include <iostream>
  3. #include <iomanip>
  4. #include <string>
  5. #include <Windows.h>
  6.  
  7. using namespace std;
  8.  
  9. #import "C:\Program Files\Common Files\System\ado\msado15.dll" \
  10. rename("EOF", "EndOfFile")
  11.  
  12. ADODB::_ConnectionPtr ConnDB = NULL;
  13. ADODB::_RecordsetPtr EmpRec = NULL;
  14.  
  15.  
  16. Connection::Connection()
  17. {
  18. }
  19.  
  20.  
  21. Connection::~Connection()
  22. {
  23. }
  24.  
  25. //Function to display error in case database fails
  26. void Connection::error(_com_error e, char* displayErr)
  27. {
  28. sprintf_s(displayErr, 800, " Error Details: \n");
  29. sprintf_s(displayErr, 800, "%s Code Message: %s\n", displayErr, (char *)e.ErrorMessage());
  30. sprintf_s(displayErr, 800, "%s Error Source: %s\n", displayErr, (char *)e.Source());
  31. sprintf_s(displayErr, 800, "%s Error Description: %s\n", displayErr, (char *)e.Description());
  32.  
  33. return;
  34. }
  35.  
  36. void Connection::connect(char choice)
  37. {
  38. HRESULT hr;
  39. int count;
  40. char option;
  41. int option2;
  42. char ErrStr[800];
  43. char QueryStr[300];
  44. char NewQueryStr[400];
  45. string col1;
  46. string col2;
  47. string col3;
  48. string col4;
  49. string col5;
  50. string col6;
  51. string col7;
  52. string col8;
  53. string col9;
  54. string col10;
  55. string col11;
  56. _variant_t vtValue;
  57. int id;
  58. char Id[5];
  59. char FName[16];
  60. char LName[16];
  61. string add;
  62. char Address[26];
  63. string city;
  64. char City[21];
  65. char State[3];
  66. char Zip[6];
  67. char Assoc[11];
  68. char Email[26];
  69. char Phone[16];
  70. int Year;
  71. ::CoInitialize(NULL);
  72.  
  73. try
  74. {
  75.  
  76. hr = ConnDB.CreateInstance(__uuidof(ADODB::Connection));
  77. ConnDB->CursorLocation = ADODB::adUseClient;
  78. ConnDB->Open("Provider = SQLOLEDB; Server=cstnt.tstc.edu; Database = ITSE1392", "1392Stu", "1392Pass", NULL);
  79.  
  80.  
  81. //If statement to check if the choice equals 6 or exit on menu
  82. if (choice != '6')
  83. {
  84. cout << "1. Continue \n";
  85. cout << "2. Return to Main \n";
  86. cout << "Please make a selection from the menu: ";
  87. cin >> option;
  88. //
  89. if (option == '1')
  90. {
  91. system("cls");
  92.  
  93. switch (choice)
  94. {
  95. case '1':
  96. cout << "1. Search by Employee ID \n";
  97. cout << "2. Search by Member Year \n";
  98. cout << "3. Search by Association \n";
  99. cout << "4. Return \n";
  100. cout << "Please make a selection from the menu: ";
  101.  
  102. cin >> option2;
  103.  
  104. if (option2 == '1')
  105. {
  106. //Search function with parameter ID
  107. Search("Id");
  108.  
  109. //Return to the menu
  110. cout << endl;
  111. cout << " Press any key to return to the menu..." << endl;
  112. cin.get();
  113. system("cls");
  114. }
  115. else if (option2 == '2')
  116. {
  117. //Search function with parameter ID
  118. Search("Year");
  119.  
  120. //Return to the menu
  121. cout << endl;
  122. cout << " Press any key to return to the menu..." << endl;
  123. cin.get();
  124. system("cls");
  125. }
  126. else if (option2 == '3')
  127. {
  128. //Search function with parameter ID
  129. Search("Assoc");
  130.  
  131. //Return to the menu
  132. cout << endl;
  133. cout << " Press any key to return to the menu..." << endl;
  134. cin.get();
  135. system("cls");
  136. }
  137. else if (option2 == '4')
  138. {
  139. //Return to the menu
  140. cout << endl;
  141. cout << " Press any key to return to the menu..." << endl;
  142. cin.get();
  143. system("cls");
  144.  
  145. break;
  146. }
  147.  
  148. break;
  149. case '2':
  150. try
  151. {
  152. //Gets the maxID
  153. id = maxId();
  154. //cout << "DB Connected: " << endl;
  155. bool valid = false;
  156.  
  157. //Gets the User inputs
  158. cout << "Add New Employee: \n\n";
  159. cout << "First Name: ";
  160. cin >> FName;
  161. cout << "Last Name: ";
  162. cin >> LName;
  163. cout << "Address: ";
  164. cin.ignore();
  165. getline(cin, add);
  166. strncpy_s(Address, add.c_str(), sizeof(Address));
  167. Address[sizeof(Address) - 1] = 0;
  168. cout << "City: ";
  169. getline(cin, city);
  170. strncpy_s(City, city.c_str(), sizeof(City));
  171. City[sizeof(City) - 1] = 0;
  172. cout << "State: ";
  173. cin >> State;
  174. cout << "Zip: ";
  175. cin >> Zip;
  176. cout << "Association: ";
  177. cin >> Assoc;
  178. cout << "Email: ";
  179. cin >> Email;
  180. cout << "Phone(###-###-####): ";
  181. cin >> Phone;
  182. cout << "Member Year: ";
  183. cin >> Year;
  184.  
  185. //while (!valid)
  186. //{
  187. // valid = true; //Assume the cin will be an integer.
  188.  
  189. // cout << "Please enter the Member Year: " << endl;
  190. // cin >> Year;
  191.  
  192. // if (cin.fail()) //cin.fail() checks to see if the value in the cin
  193. // //stream is the correct type, if not it returns true,
  194. // //false otherwise.
  195. // {
  196. // cin.clear(); //This corrects the stream.
  197. // cin.ignore(); //This skips the left over stream data.
  198. // //cout << "Please enter an number only." << endl;
  199. // valid = false; //The cin was not an integer so try again.
  200. // }
  201.  
  202. //}
  203.  
  204. cin.ignore();
  205. //Insert Query
  206. sprintf_s(NewQueryStr, 400, "INSERT INTO Employee VALUES(%i, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', %i);", id, FName, LName, Address, City, State, Zip, Assoc, Email, Phone, Year);
  207. //Executes the query
  208. EmpRec = ConnDB->Execute(NewQueryStr, NULL, 1);
  209. }
  210. catch (exception ex)
  211. {
  212. cout << endl;
  213. cout << "Incorrect data entered, Press any key to return to the menu... " << endl;
  214. cin.get();
  215. system("cls");
  216. }
  217.  
  218. cout << "Employee Added!!!\n\n";
  219. //Return to the menu
  220. cout << endl;
  221. cout << " Press any key to return to the menu..." << endl;
  222. cin.get();
  223. system("cls");
  224.  
  225. break;
  226. case '3':
  227.  
  228.  
  229. cout << "Update Employee: \n\n";
  230. cout << "Enter the id of the employee to update: ";
  231. cin >> id;
  232. cin.ignore();
  233. cout << endl;
  234. cout << "1. First Name " << endl;
  235. cout << "2. Last Name " << endl;
  236. cout << "3. Address " << endl;
  237. cout << "4. City " << endl;
  238. cout << "5. State " << endl;
  239. cout << "6. Zip " << endl;
  240. cout << "7. Association " << endl;
  241. cout << "8. Email " << endl;
  242. cout << "9. Phone(###-###-####) " << endl;
  243. cout << "10. Member Year " << endl;
  244. cout << "11. ALL " << endl;
  245. cout << "12. Return to Main " << endl;
  246. cin >> option2;
  247.  
  248.  
  249. if (option2 == '1')
  250. {
  251.  
  252. }
  253. else if (option2 == '2')
  254. {
  255.  
  256. }
  257. else if (option2 == '3')
  258. {
  259.  
  260. }
  261. else if (option2 == '4')
  262. {
  263.  
  264. }
  265. else if (option2 == '5')
  266. {
  267.  
  268. }
  269. else if (option2 == '6')
  270. {
  271.  
  272. }
  273. else if (option2 == '7')
  274. {
  275.  
  276. }
  277. else if (option2 == '8')
  278. {
  279.  
  280. }
  281. else if (option2 == '9')
  282. {
  283.  
  284. }
  285. else if (option2 == '10')
  286. {
  287.  
  288. }
  289. else if (option2 == '11')
  290. {
  291.  
  292. }
  293. else if (option2 == 12)
  294. {
  295. cin.ignore();
  296. //Return to the menu
  297. cout << endl;
  298. cout << " Press any key to return to the menu..." << endl;
  299. cin.get();
  300. system("cls");
  301. }
  302.  
  303.  
  304. //cin.ignore();
  305. ////Insert Query
  306. //sprintf_s(NewQueryStr, 400, "INSERT INTO Employee VALUES(%i, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', %i);", id, FName, LName, Address, City, State, Zip, Assoc, Email, Phone, Year);
  307. ////Executes the query
  308. //EmpRec = ConnDB->Execute(NewQueryStr, NULL, 1);
  309.  
  310.  
  311.  
  312.  
  313. break;
  314. case '4':
  315. //cout << "DB Connected: " << endl;
  316. cout << "Delete Employee: \n\n";
  317. cout << "Enter the id of the employee to delete: ";
  318. cin >> id;
  319. cin.ignore();
  320. //Delete Query
  321. sprintf_s(NewQueryStr, 400, "DELETE FROM Employee WHERE EmployeeID = %i", id);
  322. //Executes the query
  323. EmpRec = ConnDB->Execute(NewQueryStr, NULL, 1);
  324.  
  325. cout << "Employee Deleted!!!\n\n";
  326. //Return to the menu
  327. cout << endl;
  328. cout << " Press any key to return to the menu..." << endl;
  329. cin.get();
  330. system("cls");
  331. break;
  332. case '5':
  333. cin.ignore();
  334. //cout << " DB Connected:" << endl;
  335.  
  336. //Search funtion with parameter All
  337. Search("All");
  338.  
  339. //Return to the menu
  340. cout << endl;
  341. cout << " Press any key to return to the menu..." << endl;
  342. cin.get();
  343. system("cls");
  344.  
  345. break;
  346.  
  347. }
  348. }
  349. else if (option == '2')
  350. {
  351. //if option 2 was pressed
  352. cin.get();
  353. system("cls");
  354. }
  355. else
  356. {
  357. //if an invalid option was pressed
  358. cout << "Invalid token, returning to the main menu...";
  359. cin.ignore();
  360. cin.get();
  361. system("cls");
  362.  
  363. }
  364. }
  365. else //if option 6 is chosen
  366. {
  367. //Exits the program
  368. exit;
  369. }
  370. }
  371. catch (const _com_error &e)
  372. {
  373. system("cls");
  374. system("\a");
  375. cout << " DB Connection Error: " << endl << endl;
  376. error(e, ErrStr);
  377. printf(ErrStr);
  378. cout << endl;
  379. }
  380.  
  381. CoUninitialize();
  382. }
  383.  
  384. int Connection::maxId()
  385. {
  386. _variant_t vtValue;
  387. string col1;
  388.  
  389. int scope;
  390. char QueryStr[200];
  391. sprintf_s(QueryStr, 200, "SELECT MAX(EmployeeID) as MaxCount FROM Employee");
  392. EmpRec = ConnDB->Execute(QueryStr, NULL, 1);
  393. vtValue = EmpRec->Fields->GetItem("MaxCount")->GetValue();
  394. col1 = string((char*)_bstr_t(vtValue));
  395. scope = int(atoi(col1.c_str()));
  396. //Returns the max id + 1
  397. return scope + 1;
  398. }
  399.  
  400. void Connection::Search(string type)
  401. {
  402. char QueryStr[300];
  403. string col1;
  404. string col2;
  405. string col3;
  406. string col4;
  407. string col5;
  408. string col6;
  409. string col7;
  410. _variant_t vtValue;
  411.  
  412.  
  413. if (type == "All")
  414. {
  415. //Select statement
  416. sprintf_s(QueryStr, 200, "SELECT EmployeeID, FirstName, LastName, MemberYear, Association FROM Employee");
  417. EmpRec = ConnDB->Execute(QueryStr, NULL, 1);
  418. cout << endl << left << " " << setw(6) << "ID" << setw(14) << "First Name" << setw(16) << "Last Name" << setw(8) << "Year" << setw(8) << "Association" << endl;
  419. cout << "-------------------------------------------------------------\n";
  420. //Reads the first record
  421. if (!(EmpRec->EndOfFile))
  422. {
  423. //Moves to the first record
  424. EmpRec->MoveFirst();
  425. //Reads while it is NOT the end of file/table
  426. while (!(EmpRec->EndOfFile))
  427. {
  428. vtValue = EmpRec->Fields->GetItem("EmployeeID")->GetValue();
  429. col1 = string((char*)_bstr_t(vtValue));
  430. vtValue = EmpRec->Fields->GetItem("FirstName")->GetValue();
  431. col2 = string((char*)_bstr_t(vtValue));
  432. vtValue = EmpRec->Fields->GetItem("LastName")->GetValue();
  433. col3 = string((char*)_bstr_t(vtValue));
  434. vtValue = EmpRec->Fields->GetItem("MemberYear")->GetValue();
  435. col4 = string((char*)_bstr_t(vtValue));
  436. vtValue = EmpRec->Fields->GetItem("Association")->GetValue();
  437. col5 = string((char*)_bstr_t(vtValue));
  438. cout << endl << left << " " << setw(6) << col1 << setw(14) << col2 << setw(16) << col3 << setw(8) << col4 << setw(8) << col5 << endl;
  439. //Moves to the next record
  440. EmpRec->MoveNext();
  441. }
  442. }
  443. else
  444. {
  445. cout << "\n No Records Found!!!!!" << endl;
  446. }
  447.  
  448. }
  449. else if (type == "Id")
  450. {
  451. //variable to hold search data
  452. int id;
  453.  
  454. cout << "Please enter the EmployeeID: ";
  455. cin >> id;
  456. cin.ignore();
  457.  
  458. //Select statement
  459. sprintf_s(QueryStr, 200, "SELECT EmployeeID, FirstName, LastName, Address, City, State, ZipCode FROM Employee WHERE EmployeeID = %i",id);
  460. EmpRec = ConnDB->Execute(QueryStr, NULL, 1);
  461. cout << endl << left << " " << setw(6) << "ID" << setw(14) << "First Name" << setw(16) << "Last Name" << setw(26) << "Address" << setw(8) << "City" << setw(8) << "State" << setw(8) << "Zip" << endl;
  462. cout << "-------------------------------------------------------------------------------------\n";
  463. //Reads the first record
  464. if (!(EmpRec->EndOfFile))
  465. {
  466. //Moves to the first record
  467. EmpRec->MoveFirst();
  468. //Reads while it is NOT the end of file/table
  469. while (!(EmpRec->EndOfFile))
  470. {
  471. vtValue = EmpRec->Fields->GetItem("EmployeeID")->GetValue();
  472. col1 = string((char*)_bstr_t(vtValue));
  473. vtValue = EmpRec->Fields->GetItem("FirstName")->GetValue();
  474. col2 = string((char*)_bstr_t(vtValue));
  475. vtValue = EmpRec->Fields->GetItem("LastName")->GetValue();
  476. col3 = string((char*)_bstr_t(vtValue));
  477. vtValue = EmpRec->Fields->GetItem("Address")->GetValue();
  478. col4 = string((char*)_bstr_t(vtValue));
  479. vtValue = EmpRec->Fields->GetItem("City")->GetValue();
  480. col5 = string((char*)_bstr_t(vtValue));
  481. vtValue = EmpRec->Fields->GetItem("State")->GetValue();
  482. col6 = string((char*)_bstr_t(vtValue));
  483. vtValue = EmpRec->Fields->GetItem("ZipCode")->GetValue();
  484. col7 = string((char*)_bstr_t(vtValue));
  485. cout << endl << left << " " << setw(6) << col1 << setw(14) << col2 << setw(16) << col3 << setw(26) << col4 << setw(8) << col5 << setw(8) << col6 << setw(8) << col7 << endl;
  486. //Moves to the next record
  487. EmpRec->MoveNext();
  488. }
  489. }
  490. else
  491. {
  492. cout << "\n No Records Found!!!!!" << endl;
  493. }
  494. }
  495. else if (type == "Year")
  496. {
  497. //variable to hold search data
  498. int year;
  499.  
  500. cout << "Please enter the Member Year: ";
  501. cin >> year;
  502. cin.ignore();
  503.  
  504. //Select statement
  505. sprintf_s(QueryStr, 200, "SELECT EmployeeID, FirstName, LastName, MemberYear, Association FROM Employee WHERE MemberYear = %i", year);
  506. EmpRec = ConnDB->Execute(QueryStr, NULL, 1);
  507. cout << endl << left << " " << setw(6) << "ID" << setw(14) << "First Name" << setw(16) << "Last Name" << setw(26) << "Address" << setw(8) << "City" << setw(8) << "State" << setw(8) << "Zip" << endl;
  508. cout << "-------------------------------------------------------------------------------------\n";
  509. //Reads the first record
  510. if (!(EmpRec->EndOfFile))
  511. {
  512. //Moves to the first record
  513. EmpRec->MoveFirst();
  514. //Reads while it is NOT the end of file/table
  515. while (!(EmpRec->EndOfFile))
  516. {
  517. vtValue = EmpRec->Fields->GetItem("EmployeeID")->GetValue();
  518. col1 = string((char*)_bstr_t(vtValue));
  519. vtValue = EmpRec->Fields->GetItem("FirstName")->GetValue();
  520. col2 = string((char*)_bstr_t(vtValue));
  521. vtValue = EmpRec->Fields->GetItem("LastName")->GetValue();
  522. col3 = string((char*)_bstr_t(vtValue));
  523. vtValue = EmpRec->Fields->GetItem("Address")->GetValue();
  524. col4 = string((char*)_bstr_t(vtValue));
  525. vtValue = EmpRec->Fields->GetItem("City")->GetValue();
  526. col5 = string((char*)_bstr_t(vtValue));
  527. vtValue = EmpRec->Fields->GetItem("State")->GetValue();
  528. col6 = string((char*)_bstr_t(vtValue));
  529. vtValue = EmpRec->Fields->GetItem("ZipCode")->GetValue();
  530. col7 = string((char*)_bstr_t(vtValue));
  531. cout << endl << left << " " << setw(6) << col1 << setw(14) << col2 << setw(16) << col3 << setw(26) << col4 << setw(8) << col5 << setw(8) << col6 << setw(8) << col7 << endl;
  532. //Moves to the next record
  533. EmpRec->MoveNext();
  534. }
  535. }
  536. else
  537. {
  538. cout << "\n No Records Found!!!!!" << endl;
  539. }
  540. }
  541. else if (type == "Assoc")
  542. {
  543. //variable to hold search data
  544. char ass[11];
  545.  
  546. cout << "Please enter the Association: ";
  547. cin >> ass;
  548. cin.ignore();
  549.  
  550. //Select statement
  551. sprintf_s(QueryStr, 200, "SELECT EmployeeID, FirstName, LastName, MemberYear, Association FROM Employee WHERE Association = '%s'", ass);
  552. EmpRec = ConnDB->Execute(QueryStr, NULL, 1);
  553. cout << endl << left << " " << setw(6) << "ID" << setw(14) << "First Name" << setw(16) << "Last Name" << setw(26) << "Address" << setw(8) << "City" << setw(8) << "State" << setw(8) << "Zip" << endl;
  554. cout << "-------------------------------------------------------------------------------------\n";
  555. //Reads the first record
  556. if (!(EmpRec->EndOfFile))
  557. {
  558. //Moves to the first record
  559. EmpRec->MoveFirst();
  560. //Reads while it is NOT the end of file/table
  561. while (!(EmpRec->EndOfFile))
  562. {
  563. vtValue = EmpRec->Fields->GetItem("EmployeeID")->GetValue();
  564. col1 = string((char*)_bstr_t(vtValue));
  565. vtValue = EmpRec->Fields->GetItem("FirstName")->GetValue();
  566. col2 = string((char*)_bstr_t(vtValue));
  567. vtValue = EmpRec->Fields->GetItem("LastName")->GetValue();
  568. col3 = string((char*)_bstr_t(vtValue));
  569. vtValue = EmpRec->Fields->GetItem("Address")->GetValue();
  570. col4 = string((char*)_bstr_t(vtValue));
  571. vtValue = EmpRec->Fields->GetItem("City")->GetValue();
  572. col5 = string((char*)_bstr_t(vtValue));
  573. vtValue = EmpRec->Fields->GetItem("State")->GetValue();
  574. col6 = string((char*)_bstr_t(vtValue));
  575. vtValue = EmpRec->Fields->GetItem("ZipCode")->GetValue();
  576. col7 = string((char*)_bstr_t(vtValue));
  577. cout << endl << left << " " << setw(6) << col1 << setw(14) << col2 << setw(16) << col3 << setw(26) << col4 << setw(8) << col5 << setw(8) << col6 << setw(8) << col7 << endl;
  578. //Moves to the next record
  579. EmpRec->MoveNext();
  580. }
  581. }
  582. else
  583. {
  584. cout << "\n No Records Found!!!!!" << endl;
  585. }
  586. }
  587. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!