Solved

Insert SQL where some fields are blank

Posted on 2004-04-30
8
452 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
Technology Partners: 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

Independent Software Vendors: 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

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

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