Solved

Insert SQL where some fields are blank

Posted on 2004-04-30
8
453 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 6

Expert Comment

by:Amir Azhdari
ID: 10961276
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
ID: 10961397
Yes I did. The only required field is the SSN.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10962025
try

query1.ParamByName('SCNELG_LAST_NAME').Clear;

for blank fields

meikl ;-)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:paulmartin14
ID: 10962508
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
 
LVL 27

Accepted Solution

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

Author Comment

by:paulmartin14
ID: 10963060
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
ID: 10963816
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
ID: 10971577

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

751 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