Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Insert SQL where some fields are blank

Posted on 2004-04-30
8
Medium Priority
?
460 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 1500 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

688 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