Solved

Insert SQL where some fields are blank

Posted on 2004-04-30
8
449 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
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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

948 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

22 Experts available now in Live!

Get 1:1 Help Now