INSERT Null value problem

[Targa]

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]

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" <targa1SPAMSUCKS@alltel.net> schreef in bericht
news:%23C71lDZNEHA.2488@TK2MSFTNGP10.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!
>
>

[Bob Barrows [MVP]]

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"

[Aaron Bertrand [MVP]]

> 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]

[ben h]

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??

[Aaron Bertrand [MVP]]

> 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]

[Bob Barrows [MVP]]

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"

[Aaron Bertrand - MVP]

> 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

[Michael D. Kersey]

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 documenting 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