Delphi Pages Forums  

Go Back   Delphi Pages Forums > Delphi Forum > General

Lost Password?

Reply
 
Thread Tools Display Modes
  #1  
Old 07-02-2009, 06:37 PM
yssirhc yssirhc is offline
Junior Member
 
Join Date: Mar 2009
Posts: 7
Default not inserting into database!

This code was working fine last week and I have no idea why it has suddenly stopped working right. It executes all the code and outputs all my debugging statements with everything looking fine, but the values are not getting inserted into the database like they were before. I don't get any error messages and it appears to work until you check the database table and see that the new entries are not there.

Does anyone have any idea why the insert statement is not working? The delete and select statements DO work. I tested those by manually putting something in the database.

Code:
isOnForm := formvar('includeOnForm', '');
category := formvar('category', '');

TWLLog.LogMsg('jlog.txt', 'In DoEditAct: category = ' + category);
TWLLog.LogMsg('jlog.txt', 'In DoEditAct: isOnForm = ' + isOnForm);

if isOnForm = 'added' then  //true - the checkbox has been selected so
begin                       // we want to store this activity as a bookmark
  sv := TDictionary.Create;
  currentBookmarkValue := '';

  bookmarkQuery := TADOCommand.Create(nil);
  findBookmarksQuery := TADODataset.Create(nil);

  bookmarkQuery.Connection := WizThreadDB.getConnection;
  findBookmarksQuery.Connection := WizThreadDB.getConnection;

  bookmarkQuery.CommandType := cmdText;
  findBookmarksQuery.CommandType := cmdText;

  //a bookmark name can only appear once in this table so only one value will be returned
  sqlSelect := 'SELECT FValue ' +
                   'FROM Student_Field ' +
                   'WHERE Project_ID = :projectID AND Fname = :bookmarkName';

  sqlInsert := 'INSERT INTO Student_Field(Project_ID, Fname, FValue) ' +
                   'VALUES (:projectID, :bookmarkName, :bookmarkValue)';

  sqlDelete := 'DELETE FROM Student_Field ' +
                   'WHERE Project_ID = :projectID AND Fname = :bookmarkName';

  if category = 'Instruction' then
     bookmark := 'instruction'
  else if category = 'Development of Adult Living Objectives' then
     bookmark := 'adultLivingObj'
  else  //category = 'Daily Living Skills' or 'Functional Vocational Evaluation'
     bookmark := 'dailyLivingFunctVoc';

  //select current bookmark values and store in variable so no tasks are lost
  findBookmarksQuery.CommandText := sqlSelect;
  findBookmarksQuery.Parameters.ParamByName('projectID').Value := pid;
            findBookmarksQuery.Parameters.ParamByName('bookmarkName').Value := bookmark;
  TWLLog.LogMsg('jlog.txt', 'select query = ' + sqlSelect);
  TWLLog.LogMsg('jlog.txt', 'bookmark var = ' + bookmark);
  findBookmarksQuery.Open;
  if not findBookmarksQuery.Eof then  //it found something
  begin
    currentBookmarkValue := findBookmarksQuery.FieldByName('FValue').AsString;
   TWLLog.LogMsg('jlog.txt', 'current bookmark = ' + currentBookmarkValue);
 end;

  findBookmarksQuery.Close;

  //then delete bookmark so we can reinsert the new, combined value
  if currentBookmarkValue <> '' then  //only can delete if a bookmark currently exists!
  begin
    TWLLog.LogMsg('jlog.txt', 'current bookmark exists!');
    bookmarkQuery.CommandText := sqlDelete;
    bookmarkQuery.Parameters.ParamByName('projectID').Value := pid;
    bookmarkQuery.Parameters.ParamByName('bookmarkName').Value := bookmark;
   TWLLog.LogMsg('jlog.txt', 'delete query = ' + sqlDelete);
   TWLLog.LogMsg('jlog.txt', 'bookmark var = ' + bookmark);
   bookmarkQuery.Execute;
 end;

 //set bookmark - #13 is a carriage return
 if bookmark = 'instruction' then
 begin
    if currentBookmarkValue = '' then
       sv['instruction'] := aAct.Name
    else
       sv['instruction'] := currentBookmarkValue + #13 + aAct.Name
 end
 else if bookmark = 'adultLivingObj' then
 begin
    if currentBookmarkValue = '' then
       sv['adultLivingObj'] := aAct.Name
    else
       sv['adultLivingObj'] := currentBookmarkValue + #13 + aAct.Name
 end
 else  //category = 'Daily Living Skills' or 'Functional Vocational Evaluation'
 begin
   if currentBookmarkValue = '' then
      sv['dailyLivingFunctVoc'] := aAct.Name
   else
      sv['dailyLivingFunctVoc'] := currentBookmarkValue + #13 + aAct.Name;
 end;

  list := TStringlist.Create;
  list.Text := sv.names;
  TWLLog.LogMsg('jlog.txt', 'In DoEditAct: list.Text = ' + list.Text);

  bookmarkQuery.CommandText := sqlInsert;
  bookmarkQuery.Parameters.ParamByName('projectID').Value := pid;
  bookmarkQuery.Parameters.ParamByName('bookmarkName').Value := list.strings[0];
  bookmarkQuery.Parameters.ParamByName('bookmarkValue').Value := sv[list.strings[0]];
  TWLLog.LogMsg('jlog.txt', 'In DoEditAct: bookmarkName = ' + list.strings[0] + ' bookmarkValue = ' + sv[list.strings[0]]);
  TWLLog.LogMsg('jlog.txt', 'insert query = ' + sqlInsert);
  bookmarkQuery.Execute;

  //add note to text of activity so user knows they've already added it to the form
  addedToFormNote := '**** Added to ' + category + ' category of transition form ****';
  aAct.Name := aAct.Name + addedToFormNote;

  findBookmarksQuery.Free;
  bookmarkQuery.Free;
  list.free;
end;
Reply With Quote
  #2  
Old 07-02-2009, 09:27 PM
MrBaseball34 MrBaseball34 is offline
Senior Member
 
Join Date: Jan 2001
Posts: 7,260
Default

You have:
Code:
bookmarkQuery.CommandText := sqlDelete;
bookmarkQuery.CommandText := sqlInsert;
instead of:

Code:
bookmarkQuery.CommandType := sqlDelete;
bookmarkQuery.CommandType := sqlInsert;
I'm suprised the delete acutally worked. Another thing, if you would have had some exception handling in there, you would have known what was going on.
__________________
MrBaseball34

Hook 'em Horns
2005 College Football National Champions
2005 College Baseball National Champions
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On

Forum Jump


All times are GMT. The time now is 08:33 AM.


Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2019, vBulletin Solutions, Inc.