Professional Web Applications Themes

INSERT Null value problem - ASP Database

Ive got a form that posts to an Access db and a few of the fields are of "number" type in the database. These fields do not require data to submit the form but if they are submitted empty, I get "Syntax error in INSERT INTO statement". Is there any way around this other than giving the fields a default value? Thanks!...

  1. #1

    Default INSERT Null value problem

    Ive got a form that posts to an Access db and a few of the fields are of
    "number" type in the database.

    These fields do not require data to submit the form but if they are
    submitted empty, I get "Syntax error in INSERT INTO statement".

    Is there any way around this other than giving the fields a default value?

    Thanks!


    Targa Guest

  2. #2

    Default Re: INSERT Null value problem


    if Trim( myNumberField) = "" then myNumberField="0"

    or you can set the default value to 0 in your MDB and only INSERT if <> ""

    if Trim( myNumberField) <> "" then sqlTxt = sqlTxt & ", " & myNumberField




    "Targa" <targa1SPAMSalltel.net> schreef in bericht
    news:%23C71lDZNEHA.2488TK2MSFTNGP10.phx.gbl...
    > Ive got a form that posts to an Access db and a few of the fields are of
    > "number" type in the database.
    >
    > These fields do not require data to submit the form but if they are
    > submitted empty, I get "Syntax error in INSERT INTO statement".
    >
    > Is there any way around this other than giving the fields a default value?
    >
    > Thanks!
    >
    >

    Maarten Guest

  3. #3

    Default Re: INSERT Null value problem

    Targa wrote:
    > Ive got a form that posts to an Access db and a few of the fields are
    > of "number" type in the database.
    >
    > These fields do not require data to submit the form but if they are
    > submitted empty, I get "Syntax error in INSERT INTO statement".
    >
    > Is there any way around this other than giving the fields a default
    > value?
    >
    > Thanks!
    Without seeing your code (or, ore importantly, your response.written sql
    statement) it's hard to see what you are doing wrong, so all I can do is

    Here is the syntax:
    INSERT INTO Tablename (<column_list>)
    SELECT <column_list> FROM some_table

    OR

    INSERT INTO Tablename (<column_list>)
    VALUES (<value_list>)

    The key is to only include the columns in the column lists into which you
    wish to insert values. For example if you have a table with 3 numeric
    columns: col1, col2 and col3, and you only wish to insert values into col1
    and col3, only include col1 and col3 in the column list in the INSERT
    clause, and only supply values only for those two columns in the other
    clause:

    INSERT INTO Tablename (col1, col3)
    SELECT colA, colB FROM some_table

    OR

    INSERT INTO Tablename (col1, col3)
    VALUES (25, 33)

    This is the most efficient way to do this. A less efficient way albeit, a
    little easier to code, involves using the Null keyword in the resulting sql
    statement:

    INSERT INTO Tablename (col1, col2, col3)
    VALUES (25, Null, 33)

    OR

    INSERT INTO Tablename ()
    VALUES (25, Null, 33)

    HTH,
    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Barrows [MVP] Guest

  4. #4

    Default Re: INSERT Null value problem

    > INSERT INTO Tablename ()
    > VALUES (25, Null, 33)
    I know you kind of implied this. But for anyone not following the hread
    explicitly, note that this only works if you have exactly three columns in
    the table; if you are ignoring an IDENTITY column, or a computed column, or
    an optional column, you will likely get errors.

    And FWIW, I usually leave out the optional INTO keyword, the empty parens
    (), and often use a SELECT rather than the VALUES keyword...

    INSERT Tablename SELECT 25, Null, 33

    When I'm constructing data population scripts, this makes them a little bit
    smaller and involves less syntax work when I'm building the script. It also
    makes it easier to translate the statement into a real data pull later, if I
    decide that one of more of the values need to come from a table, view, UDF,
    etc.

    :-)

    --
    Aaron Bertrand
    SQL Server MVP
    [url]http://www.aspfaq.com/[/url]


    Aaron Bertrand [MVP] Guest

  5. #5

    Default Re: INSERT Null value problem

    Aaron Bertrand [MVP] wrote:
    >>INSERT INTO Tablename ()
    >>VALUES (25, Null, 33)
    >
    >
    > I know you kind of implied this. But for anyone not following the hread
    > explicitly, note that this only works if you have exactly three columns in
    > the table; if you are ignoring an IDENTITY column, or a computed column, or
    > an optional column, you will likely get errors.
    >
    > And FWIW, I usually leave out the optional INTO keyword, the empty parens
    > (), and often use a SELECT rather than the VALUES keyword...
    >
    > INSERT Tablename SELECT 25, Null, 33
    >
    > When I'm constructing data population scripts, this makes them a little bit
    > smaller and involves less syntax work when I'm building the script. It also
    > makes it easier to translate the statement into a real data pull later, if I
    > decide that one of more of the values need to come from a table, view, UDF,
    > etc.
    >
    > :-)
    >
    I've got a more general question on the use of NULL vs NOT NULL in
    column definitions: I read somewhere a while ago that I should avoid
    defining columns as NULL, instead use NOT NULL and define a default of
    '' for character types, and 0 (or -1 or whatever works) for integer
    types etc... is this a good thing (TM) or not??
    ben h Guest

  6. #6

    Default Re: INSERT Null value problem

    > I've got a more general question on the use of NULL vs NOT NULL in column
    > definitions: I read somewhere a while ago that I should avoid defining
    > columns as NULL, instead use NOT NULL and define a default of > '' for
    > character types, and 0 (or -1 or whatever works) for integer types etc...
    > is this a good thing (TM) or not??
    There is no hard rule. Does it make sense for a string to be empty length?
    A lot of times people avoid NULLs so that they don't muck up the display.
    You can do this in the query easily, while still showing in the database the
    difference between an empty string and an unknown value. One thing I don't
    like about using empty strings is that you can't use this technique across
    the board. For example, how do you propose you populate a DATETIME field
    (e.g. birthdate) if you don't know the date yet? Also, 0 or -1 might not
    work. You might have an integer column that doesn't have an exclusion, or a
    token value that you could use to represent "unknown"...

    Also see [url]http://www.aspfaq.com/2073[/url] and [url]http://www.aspfaq.com/2150[/url]


    Aaron Bertrand [MVP] Guest

  7. #7

    Default Re: INSERT Null value problem

    ben h wrote:
    > I've got a more general question on the use of NULL vs NOT NULL in
    > column definitions: I read somewhere a while ago that I should avoid
    > defining columns as NULL, instead use NOT NULL and define a default of
    > '' for character types, and 0 (or -1 or whatever works) for integer
    > types etc... is this a good thing (TM) or not??
    While I agree with Aaron that there are no hard rules (I make columns
    nullable where it makes sense), you should be aware that there are experts
    out there (e.g. Joe Celko) for whom this IS a hard-and-fast rule. I will
    leave it to you to Google his writings on the subject.

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Barrows [MVP] Guest

  8. #8

    Default Re: INSERT Null value problem

    > While I agree with Aaron that there are no hard rules (I make columns
    > nullable where it makes sense), you should be aware that there are experts
    > out there (e.g. Joe Celko) for whom this IS a hard-and-fast rule. I will
    > leave it to you to Google his writings on the subject.
    Then again, if everyone followed Celko's rules, we would still be using
    SQL-92 only and never using any proprietary features... we'd be like chicken
    little expecting to migrate all of our apps to Oracle tomorrow, and then on
    to DB2 next Tuesday. And we would never name tables with a tbl prefix,
    never give columns the tablename prefix, never refer to rows as records,
    columns as fields, and databases as files. ;-)

    A


    Aaron Bertrand - MVP Guest

  9. #9

    Default Re: INSERT Null value problem

    ben h wrote:
    <snipped>
    > I've got a more general question on the use of NULL vs NOT NULL in
    > column definitions: I read somewhere a while ago that I should avoid
    > defining columns as NULL, instead use NOT NULL and define a default of
    > '' for character types, and 0 (or -1 or whatever works) for integer
    > types etc... is this a good thing (TM) or not??
    FWIW I was reading the Patriot Act the other day and it declares in
    section 43, paragraph 23.433.23, sub-paragraph aa.13 titled "NULLS
    declared HARMFUL" that the use of NULLs is a terrorist act punishable by
    imprisonment in a federal computer facility for no less than 6 years,
    which time is to be spent doenting Perl code written by Matt
    ([url]http://www.scriptarchive.com/[/url] ) while eating only pizza and drinking
    Jolt cola.

    Do what you gotta do, but don't say you weren't forewarned.

    Good Luck,
    Michael D. Kersey
    Michael D. Kersey Guest

Similar Threads

  1. 500 NULL error on DB Insert (SQL Server)
    By jgmoore in forum Coldfusion Database Access
    Replies: 3
    Last Post: May 20th, 01:15 AM
  2. odbc insert null error
    By bflophil in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: February 18th, 10:09 PM
  3. ASP SQL Insert NULL Date Value
    By Chad S in forum ASP
    Replies: 5
    Last Post: February 19th, 07:33 PM
  4. How insert null in numerics fields?
    By Vilmar Braz„o de Oliveira in forum ASP Database
    Replies: 5
    Last Post: January 8th, 06:40 PM
  5. Have trigger supply value for NOT NULL column on insert
    By Ian Boyd in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 3rd, 02:59 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139