Delphi Pages Forums  

Go Back   Delphi Pages Forums > Delphi Forum > General

Lost Password?

Thread Tools Display Modes
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.

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);
  if not findBookmarksQuery.Eof then  //it found something
    currentBookmarkValue := findBookmarksQuery.FieldByName('FValue').AsString;
   TWLLog.LogMsg('jlog.txt', 'current bookmark = ' + currentBookmarkValue);


  //then delete bookmark so we can reinsert the new, combined value
  if currentBookmarkValue <> '' then  //only can delete if a bookmark currently exists!
    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);

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

  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);

  //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;

Reply With Quote
Old 07-02-2009, 09:27 PM
MrBaseball34 MrBaseball34 is offline
Senior Member
Join Date: Jan 2001
Posts: 7,260

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

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.

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

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.