Inserts and No Null values

Results 1 to 2 of 2

Thread: Inserts and No Null values

  1. #1
    Join Date
    Dec 1969

    Default Inserts and No Null values

    I&#039m adding records to a FoxPro Database using <BR>INSERT INTO tablename (col1, col2, col3) VALUES (&#039val1&#039, &#039val2&#039, &#039val3&#039).<BR>I only insert data in fields where I have a value and skip the ones that don&#039t.<BR>I get this error: Field fieldname does not accept null values.<BR>I don&#039t get this error when I update records in the same database using the UPDATE statement.<BR><BR>Any suggestions on how to get around this without having to put values in each field?<BR><BR>Thanks for your help.<BR><BR>

  2. #2
    Jason Miller Guest

    Default RE: Inserts and No Null values

    Check: Are you giving it a literal NULL or a translated NULL? Literal is "insert into x (bob) values (NULL)", translated is "insert into x (bob) values (" & NULL & ")" which looks blank to the database and is a bad thing. <BR><BR>Alternately: The database just doesn&#039t want any sort of NULL in a new record -- can&#039t blame it. Do this (you&#039ll need an isBlank function or something similar):<BR><BR> strCols = "bob, dobbs, xist"<BR> strVals = "&#039" & val1 & "&#039, &#039" & val2 & "&#039, &#039" & val3 & "&#039, "<BR> if isBlank(val1) then replace(strCols, "bob, ", "")<BR> if isBlank(val2) ... you get the picture<BR> strVals = replace(strVals, "&#039&#039, ", "") &#039 empty values<BR> strVals = left(strVals, len(strVals - 2) &#039 that last comma<BR><BR>It should be reworked to be more efficient for your use -- ie: build strVals while testing the values so you don&#039t have to try to remove &#039&#039s afterward... -- but that&#039s the general idea.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts