I have a form that has 4 numeric fields. 2 are required, 2 optional. I use 
client-side validation to ensure they contain only numbers.  However, when 
I try to do an INSERT INTO an Access table, and one of the optional 
entries is blank, I get the error:
Syntax error in INSERT INTO statement.
If I supply a number, then the insert works. The field is set to 
required=no, but number/currency fields don't have the option for Allow 
Zero Length.  I don't want to enter zeros for empty fields.  Any ideas how 
I can work around this?  FYI, here's my resulting SQL statement:

INSERT INTO tblSalesList (Photog, magname, photoeditor, circ, country, 
imagesize, saledate, feeoffered, feefinal, feeresearch, feescan, webuse, 
feeweb, comments) VALUES 
('dfs', 'dsf', 'dsf', 'dsf', 'us', 'cover', '2/2/45', 1, 2, , , 'extra 
fee', , '')

Thanks,  Rory

In this case you need to use server-side validation to make sure that the fields contain numeric input... here's a function that may
help:
Function ExtractNumbers(byVal str)   If IsNull(str) Then str = ""   Dim enRegEx   Set enRegEx = New RegExp   enRegEx.Pattern =
"\D"   enRegEx.Global = True   ExtractNumbers = enRegEx.Replace(str,"")End Function
myvariable = ExtractNumbers(myvariable)
If Len(myvariable) = 0 Then
   ' Redisplay the form or whatever
Else
   'Insert the variable into the database
End If

 

`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`

Robert

robkdavis@h...

`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`
 




>From: "Rory Lysaght" 
>Reply-To: "ASP Web HowTo" 
>To: "ASP Web HowTo" 
>Subject: [asp_web_howto] Error inserting empty number field 
>Date: Sun, 16 Feb 2003 00:21:02 
> 
>I have a form that has 4 numeric fields. 2 are required, 2 optional. I use 
>client-side validation to ensure they contain only numbers. However, when 
>I try to do an INSERT INTO an Access table, and one of the optional 
>entries is blank, I get the error: 
>Syntax error in INSERT INTO statement. 
>If I supply a number, then the insert works. The field is set to 
>required=no, but number/currency fields don't have the option for Allow 
>Zero Length. I don't want to enter zeros for empty fields. Any ideas how 
>I can work around this? FYI, here's my resulting SQL statement: 
> 
>INSERT INTO tblSalesList (Photog, magname, photoeditor, circ, country, 
>imagesize, saledate, feeoffered, feefinal, feeresearch, feescan, webuse, 
>feeweb, comments) VALUES 
>('dfs', 'dsf', 'dsf', 'dsf', 'us', 'cover', '2/2/45', 1, 2, , , 'extra 
>fee', , '') 
> 
>Thanks, Rory 
>--- 
MSN 8 helps ELIMINATE E-MAIL VIRUSES.  Get 2 months FREE*.

Wow, hotmail really messes up the formatting of an HTML message. What a surprise. (Not!) Hopefully you can reformat it, if not
please reply...

>From: "Robert Davis" 
>Reply-To: "ASP Web HowTo" 
>To: "ASP Web HowTo" 
>Subject: [asp_web_howto] Re: Error inserting empty number field 
>Date: Sat, 15 Feb 2003 21:20:02 -0600 
> 
> 
>In this case you need to use server-side validation to make sure that the fields contain numeric input... here's a function that
may help: 
>Function ExtractNumbers(byVal str)   If IsNull(str) Then str = ""   Dim enRegEx   Set enRegEx = New RegExp   enRegEx.Pattern =
"\D"   enRegEx.Global = True   ExtractNumbers = enRegEx.Replace(str,"")End Function 
>myvariable = ExtractNumbers(myvariable) 
>If Len(myvariable) = 0 Then 
>   ' Redisplay the form or whatever 
>Else 
>   'Insert the variable into the database 
>End If 
> 
>  
> 
>`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°` 
> 
>Robert 
> 
>robkdavis@h... 
> 
>`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°` 
>  
> 
> 
> 
> 
> >From: "Rory Lysaght" 
> >Reply-To: "ASP Web HowTo" 
> >To: "ASP Web HowTo" 
> >Subject: [asp_web_howto] Error inserting empty number field 
> >Date: Sun, 16 Feb 2003 00:21:02 
> > 
> >I have a form that has 4 numeric fields. 2 are required, 2 optional. I use 
> >client-side validation to ensure they contain only numbers. However, when 
> >I try to do an INSERT INTO an Access table, and one of the optional 
> >entries is blank, I get the error: 
> >Syntax error in INSERT INTO statement. 
> >If I supply a number, then the insert works. The field is set to 
> >required=no, but number/currency fields don't have the option for Allow 
> >Zero Length. I don't want to enter zeros for empty fields. Any ideas how 
> >I can work around this? FYI, here's my resulting SQL statement: 
> > 
> >INSERT INTO tblSalesList (Photog, magname, photoeditor, circ, country, 
> >imagesize, saledate, feeoffered, feefinal, feeresearch, feescan, webuse, 
> >feeweb, comments) VALUES 
> >('dfs', 'dsf', 'dsf', 'dsf', 'us', 'cover', '2/2/45', 1, 2, , , 'extra 
> >fee', , '') 
> > 
> >Thanks, Rory 
> >--- 
>MSN 8 helps ELIMINATE E-MAIL VIRUSES. Get 2 months FREE*. 
> 
>--- 
MSN 8 helps ELIMINATE E-MAIL VIRUSES.  Get 2 months FREE*.

You need to insert a NULL value.

<%
intMyInteger = Request.Form("txtInteger")

If not isNumeric(intMyInteger) then
    intMyInteger = 0
End If

If Len(intMyInteger) = 0 then

    strSQL = _
        "INSERT INTO myTable (mField) VALUES (NULL)"

Else

    strSQL = _
        "INSERT INTO myTable(myField) VALUES (" & intMyInteger & ")"

End If

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Rory Lysaght" <rory@g...>
Subject: [asp_web_howto] Error inserting empty number field


: I have a form that has 4 numeric fields. 2 are required, 2 optional. I use
: client-side validation to ensure they contain only numbers.  However, when
: I try to do an INSERT INTO an Access table, and one of the optional
: entries is blank, I get the error:
: Syntax error in INSERT INTO statement.
: If I supply a number, then the insert works. The field is set to
: required=no, but number/currency fields don't have the option for Allow
: Zero Length.  I don't want to enter zeros for empty fields.  Any ideas how
: I can work around this?  FYI, here's my resulting SQL statement:
:
: INSERT INTO tblSalesList (Photog, magname, photoeditor, circ, country,
: imagesize, saledate, feeoffered, feefinal, feeresearch, feescan, webuse,
: feeweb, comments) VALUES
: ('dfs', 'dsf', 'dsf', 'dsf', 'us', 'cover', '2/2/45', 1, 2, , , 'extra
: fee', , '')

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~