Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Dorffius
Starting Member
36 Posts |
 Posted - 2002-01-17 : 13:18:33
|
| The function I am currently writing allows the user to enter a date and order number. These numbers are checked against a table in my database and if they match, all corresponding records are shifted from that table to another in the same database. I can get the data to be validated and I get no errors, but the info will not show up in the second table. I'm not really sure how to shift a group of rows over to another table. Any help would be appreciated.Here is the code I currently have.bool Warehouse::ValidateTransfer(){ szField OrderNo(szOrderNo, sizeof(szOrderNo)); szField Date(szDate, sizeof(szDate)); Statement st(pDb); st.BindParam(&OrderNo,SQL_PARAM_INPUT); st.BindParam(&Date,SQL_PARAM_INPUT); st.ExecDirect("SELECT * FROM BSMT WHERE OrderNo=? AND Date=?"); if (st.Fetch() == SQL_SUCCESS) { ("INSERT INTO OVERSTOCK (PCode, SerialNo, ScanDate, OrderNo) SELECT (PCode, SerialNo, Date, OrderNo) FROM BSMT"); SQL_UPDATE; SQL_COMMIT; strcpy(szMessage,"\n\nTransfer Completed Successfully."); return true; } else { strcpy(szMessage,"\n\nOrder # or Date invalid."); clrscr(); return false; }}Thanks in advance. |
|
|
sica
Posting Yak Master
143 Posts |
Posted - 2002-01-17 : 18:12:38
|
| This is a shot in the dark ,but maybe it helps.Replace the brackets for your select in Insert statement.("INSERT INTO OVERSTOCK (PCode, SerialNo, ScanDate, OrderNo) SELECT PCode, SerialNo, Date, OrderNo FROM BSMT");Sica |
 |
|
|
Dorffius
Starting Member
36 Posts |
Posted - 2002-01-18 : 14:05:01
|
| Just in case anyone ever needs a solution to a problem such as this I'll just throw down the answer.The code that I had was fine but was missing a few pieces. Here is the correct code, totally commented.bool Warehouse::ValidateTransfer(){ // Grabs variables szField OrderNo(szOrderNo, sizeof(szOrderNo)); szField Date(szDate, sizeof(szDate)); szField Store(szStore, sizeof(szStore)); // Declare Statement Object Statement st(pDb); // Bind Parameters for Query st.BindParam(&OrderNo,SQL_PARAM_INPUT); st.BindParam(&Date,SQL_PARAM_INPUT); // Execute search for user inputted parameters st.ExecDirect("SELECT * FROM BSMT WHERE OrderNo=? AND Date=?"); // Check for success of query and execute transfer statement if (st.Fetch() == SQL_SUCCESS) { // Close query cursor to begin new cursor for transfer statement st.CloseCursor(); // The statements to switch the records from BSMT to OVERSTOCK Statement st(pDb); // Bind new parameters st.BindParam(&OrderNo,SQL_PARAM_INPUT); st.BindParam(&Date,SQL_PARAM_INPUT); // Command to shift records st.ExecDirect("INSERT INTO OVERSTOCK (OrderNo, ScanDate, PCode, SerialNo) " " SELECT OrderNo, Date, PCode, SerialNo FROM BSMT " " WHERE OrderNo=? AND Date=?"); // Success!!! strcpy(szMessage,"\n\nTransfer Completed Successfully."); // Delete the codes copied from the BSMT table st.ExecDirect("DELETE FROM BSMT WHERE OrderNo=? AND Date=?"); // Commit the changes to the database pDb->Transact(SQL_COMMIT); // Resets Parameters st.ResetParams(); st.CloseCursor(); st.UnbindCols(); return true; } else { strcpy(szMessage,"\n\nOrder # or Date invalid."); clrscr(); return false; }} |
|
|
|
|
|
|
|
|
Visual C++ and SQL problems....