Solved

Insert SQL where some fields are blank

Posted on 2004-04-30
8
448 Views
Last Modified: 2010-04-05
We are using DB2, Delphi 7 and BDE 5.01. I use standard Query, DataSource and Update Query components.

I use the Update Editor to create Delete, Insert and Modify sql statements. I then use a Delphi For Loop to fill in the values to the each individual parameter. The problem occurs on Inserting a new record where a parameter value is blank.

The error message states the field is of an unknown type. I have tried putting quotes in the blank field, putting Null in it, putting quotes and a space and deleting the parameter and it's associated field. All fields are of the type Char.

Insert SQL Example :

insert into DB2ADMIN.CO_SCAN_ELIG
  (SCNELG_SSN, SCNELG_LAST_NAME, SCNELG_FIRST_NAME)
values
  (:SCNELG_SSN, :SCNELG_LAST_NAME, :SCNELG_FIRST_NAME)

insert into DB2ADMIN.CO_SCAN_ELIG
  (SCNELG_SSN, SCNELG_LAST_NAME, SCNELG_FIRST_NAME)
values
  ('999999999', , 'BBBBBBB' )
0
Comment
Question by:paulmartin14
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 6

Expert Comment

by:Amir Azhdari
Comment Utility
hi , i got no error! with this code :

procedure TForm1.Button1Click(Sender: TObject);
begin
query1.SQL.Text:='insert into yourtablename (SCNELG_SSN, SCNELG_LAST_NAME, SCNELG_FIRST_NAME) values  (:SCNELG_SSN, :SCNELG_LAST_NAME, :SCNELG_FIRST_NAME)';
query1.ParamByName('SCNELG_SSN').AsString:='dsfds';
query1.ParamByName('SCNELG_LAST_NAME').AsString:=''; // this parameter is blank
query1.ParamByName('SCNELG_FIRST_NAME').AsString:='fdgd';

query1.ExecSQL;
end;

did you check that if is not required field ?

0
 

Author Comment

by:paulmartin14
Comment Utility
Yes I did. The only required field is the SSN.
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
try

query1.ParamByName('SCNELG_LAST_NAME').Clear;

for blank fields

meikl ;-)
0
 

Author Comment

by:paulmartin14
Comment Utility
I tried that with no difference:

If sCurrFldData <> '' Then
   Begin
      dmScanExport.uqCOScanElig.Query[ukInsert].ParamByName(sCurrFldName).AsString:=QuotedStr(sCurrFldData);
   End
Else
  Begin
     dmScanExport.uqCOScanElig.Query[ukInsert].ParamByName(sCurrFldName).Clear;
     dmScanExport.uqCOScanElig.Query[ukInsert].ParamByName(sCurrFldName).Bound:=True;
  End;
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 27

Accepted Solution

by:
kretzschmar earned 500 total points
Comment Utility
define the datatype-property of the parameter (guessing its ftUnknown) to ftstring
0
 

Author Comment

by:paulmartin14
Comment Utility
The ftString has gotten me father along, but I now have an error of:

The value of a host variable in EXECUTE or OPEN state is too large for its corresponding use. SQLSTATE=22001.

Here is the actual statement:

insert into DB2ADMIN.CO_SCAN_ELIG
  (SCNELG_SSN, SCNELG_LAST_NAME, SCNELG_FIRST_NAME, SCNELG_MID_INITIAL,
   SCNELG_DOB, SCNELG_DL_TX, SCNELG_DL, SCNELG_TST_DT, SCNELG_SCAN_REF,
   SCNELG_REC_INTV, SCNELG_SITE_REG, SCNELG_HOME_PHONE, SCNELG_INS_EXP_DT,
   SCNELG_AVAIL_DT, SCNELG_GENDER, SCNELG_VETERAN, SCNELG_FORMER, SCNELG_RETIREE,
   SCNELG_EMP_STAT, SCNELG_NEP, SCNELG_OFF_REL, SCNELG_APPNTYP, SCNELG_SCORE_1,
   SCNELG_SCORE_2, SCNELG_SCORE_3, SCNELG_SCORE_4, SCNELG_SCORE_5, SCNELG_ADDR,
   SCNELG_CITY, SCNELG_STATE, SCNELG_ZIP_CODE, SCNELG_PREF_U_1, SCNELG_PREF_U_2,
   SCNELG_PREF_U_3, SCNELG_PREF_AREA_1, SCNELG_PREF_AREA_2, SCNELG_DR_LIC,
   SCNELG_SSC, SCNELG_ADDRESS, SCNELG_FP, SCNELG_EDUC, SCNELG_DD214, SCNELG_DISP,
   SCNELG_I9, SCNELG_CH, SCNELG_SS_REG, SCNELG_REF_REQ, SCNELG_OFF_INFO,
   SCNELG_M, SCNELG_C, SCNELG_P, SCNELG_I, SCNELG_R, SCNELG_N, SCNELG_SCORE,
   SCNELG_INT_DT, SCNELG_D1, SCNELG_D2, SCNELG_D3, SCNELG_D4, SCNELG_ITEMS,
   SCNELG_PASSED, SCNELG_OFFER, SCNELG_LT60, SCNELG_LAST12, SCNELG_IELIST)
values
  ('999999999', 'AAAA', 'BBBB', 'C', '041346', '', '12142655', '041304', '0220',
   '', '10', '9368900771', '', '050104', 'M', 'N', 'N', 'N', 'F', 'N', 'N', 'T', '017',
   '016', '020', '017', '019',
   'DDDDDDDDDDD', 'EEEEEEE', 'FF', 'GGGGG', 'B1',
   '', '', '20', '',
   '', '', '', '', '',
   '', '', '', '', '',
   '', '', '3', '3', '33',
   '3', '33', '3', '0089', '', '',
   '', '', '', 'CDCACADCCCBADADDABCBDDDDBDCBDACADDCBBCCBDAECDAECAED', '', '',
   'F', 'F', 'F')

I have all the DB2 field lengths against the data and I have the position of the fields in relation to the parameters. It all checks out!
0
 
LVL 6

Expert Comment

by:Amir Azhdari
Comment Utility
paulmartin14
i have no DB2 engine installed on my pc but i did test the statements with paradox 7
and the record successfully inserted without error!

procedure TForm1.Button1Click(Sender: TObject);
begin

// create
    query1.SQL.text:=(
        'create table ''d:\test5.db''  (SCNELG_SSN char(60) , SCNELG_LAST_NA' +
        'ME char(60), SCNELG_FIRST_NAME char(60), SCNELG_MID_INITIAL char' +
        '(60), '+
        'SCNELG_DOB char(60), SCNELG_DL_TX char(60), SCNELG_DL char(60), ' +
        'SCNELG_TST_DT char(60), SCNELG_SCAN_REF char(60), '+
        'SCNELG_REC_INTV char(60), SCNELG_SITE_REG char(60), SCNELG_HOME_' +
        'PHONE char(60), SCNELG_INS_EXP_DT char(60), '+
        'SCNELG_AVAIL_DT char(60), SCNELG_GENDER char(60), SCNELG_VETERAN' +
        ' char(60), SCNELG_FORMER char(60), SCNELG_RETIREE char(60), '    +
        'SCNELG_EMP_STAT char(60), SCNELG_NEP char(60), SCNELG_OFF_REL ch' +
        'ar(60), SCNELG_APPNTYP char(60), SCNELG_SCORE_1 char(60), '      +
        'SCNELG_SCORE_2 char(60), SCNELG_SCORE_3 char(60), SCNELG_SCORE_4' +
        ' char(60), SCNELG_SCORE_5 char(60), SCNELG_ADDR char(60), '      +
        'SCNELG_CITY char(60), SCNELG_STATE char(60), SCNELG_ZIP_CODE cha' +
        'r(60), SCNELG_PREF_U_1 char(60), SCNELG_PREF_U_2 char(60), '     +
        'SCNELG_PREF_U_3 char(60), SCNELG_PREF_AREA_1 char(60), SCNELG_PR' +
        'EF_AREA_2 char(60), SCNELG_DR_LIC char(60), '+
        'SCNELG_SSC char(60), SCNELG_ADDRESS char(60), SCNELG_FP char(60)' +
        ', SCNELG_EDUC char(60), SCNELG_DD214 char(60), SCNELG_DISP char(' +
        '60), '+
        'SCNELG_I9 char(60), SCNELG_CH char(60), SCNELG_SS_REG char(60), ' +
        'SCNELG_REF_REQ char(60), SCNELG_OFF_INFO char(60), '+
        'SCNELG_M char(60), SCNELG_C char(60), SCNELG_P char(60), SCNELG_' +
        'I char(60), SCNELG_R char(60), SCNELG_N char(60), SCNELG_SCORE c' +
        'har(60), '+
        'SCNELG_INT_DT char(60), SCNELG_D1 char(60), SCNELG_D2 char(60), ' +
        'SCNELG_D3 char(60), SCNELG_D4 char(60), SCNELG_ITEMS char(60), ' +
        'SCNELG_PASSED char(60), SCNELG_OFFER char(60), SCNELG_LT60 char(' +
        '60), SCNELG_LAST12 char(60), SCNELG_IELIST char(60) )');

query1.ExecSQL;


//--------------------------------------------------------------------
// insert data

with query1.sql do
begin
Text:='insert into ''d:\test5.db'' (SCNELG_SSN, SCNELG_LAST_NAME, SCNELG_FIRST_NAME, SCNELG_MID_INITIAL,';
Add('SCNELG_DOB, SCNELG_DL_TX, SCNELG_DL, SCNELG_TST_DT, SCNELG_SCAN_REF,');
Add('SCNELG_REC_INTV, SCNELG_SITE_REG, SCNELG_HOME_PHONE, SCNELG_INS_EXP_DT,');
Add('SCNELG_AVAIL_DT, SCNELG_GENDER, SCNELG_VETERAN, SCNELG_FORMER, SCNELG_RETIREE,');
Add('SCNELG_EMP_STAT, SCNELG_NEP, SCNELG_OFF_REL, SCNELG_APPNTYP, SCNELG_SCORE_1,');
Add('SCNELG_SCORE_2, SCNELG_SCORE_3, SCNELG_SCORE_4, SCNELG_SCORE_5, SCNELG_ADDR,');
Add('SCNELG_CITY, SCNELG_STATE, SCNELG_ZIP_CODE, SCNELG_PREF_U_1, SCNELG_PREF_U_2,');
Add('SCNELG_PREF_U_3, SCNELG_PREF_AREA_1, SCNELG_PREF_AREA_2, SCNELG_DR_LIC,');
Add('SCNELG_SSC, SCNELG_ADDRESS, SCNELG_FP, SCNELG_EDUC, SCNELG_DD214, SCNELG_DISP,');
Add('SCNELG_I9, SCNELG_CH, SCNELG_SS_REG, SCNELG_REF_REQ, SCNELG_OFF_INFO,');
Add('SCNELG_M, SCNELG_C, SCNELG_P, SCNELG_I, SCNELG_R, SCNELG_N, SCNELG_SCORE,');
Add('SCNELG_INT_DT, SCNELG_D1, SCNELG_D2, SCNELG_D3, SCNELG_D4, SCNELG_ITEMS,');
Add('SCNELG_PASSED, SCNELG_OFFER, SCNELG_LT60, SCNELG_LAST12, SCNELG_IELIST)');
Add('values');
Add('(''999999999'', ''AAAA'', ''BBBB'', ''C'', ''041346'', '''', ''12142655'', ''041304'', ''0220'',''');
Add(''', ''10'', ''9368900771'', '''', ''050104'', ''M'', ''N'', ''N'', ''N'', ''F'', ''N'', ''N'', ''T'', ''017'',');
Add('''016'', ''020'', ''017'', ''019'',');
Add('''DDDDDDDDDDD'', ''EEEEEEE'', ''FF'', ''GGGGG'', ''B1'',');
Add(''''', '''', ''20'', '''',');
Add(''''', '''', '''', '''', '''',');
Add(''''', '''', '''', '''', '''',');
Add(''''', '''', ''3'', ''3'', ''33'',');
Add('''3'', ''33'', ''3'', ''0089'', '''', '''',');
Add(''''', '''', '''', ''CDCACADCCCBADADDABCBDDDDBDCBDACADDCBBCCBDAECDAECAED'', '''', '''',');
Add('''F'', ''F'', ''F'')');
end;

query1.ExecSQL

end;
0
 
LVL 12

Expert Comment

by:Ivanov_G
Comment Utility

   dmScanExport.uqCOScanElig.Query[ukInsert].ParamByName(sCurrFldName).Value := null;
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now