empty values in INSERT INTO statement

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I have this ASP page that inserts new records into an Access database via an
execute statement.

Taking the data from a form, I build my SQL statement like this:
<%
strSQL = "INSERT INTO mytable (Firstname, Lastname, Department, Extension)
_
& " VALUES ('" & CStr(Replace(Request.Form("Firstname"), "'", "''")) _
& "', '" & CStr(Replace(Request.Form("Lastname"), "'", "''")) _
& "', " & CInt(Replace(Request.Form("Department"), "'", "''")) _
& ", " & CInt(Replace(Request.Form("Extension"), "'", "''")) & ")"
%>

That works ok as long as ALL the fields in the form contain data. If one of
the number fields (Department and Extension) are empty, and error is
returned.

I guess it has something to do with the fact that text values are surrounded
by delimiting apostrophies while number fields are not.

So with complete data the above lines generate the following SQL statement:

INSERT INTO mytable (Firstname, Lastname, Department, Extension) VALUES
('John', 'Doe', 3, 55)

If the texts fields are empty then the statement would be:

INSERT INTO mytable (Firstname, Lastname, Department, Extension) VALUES ('',
'', 3, 55)

which is also OK.

But if the number fields are emtpy the statement become:

INSERT INTO mytable (Firstname, Lastname, Department, Extension) VALUES
('John', 'Doe', , )

and that produces an error.

How can I change the above SQL building code so as to avoid this kind of
problem? For example if I could make the word NULL appear instead of
(nothing) then SQL statement would work

Any suggestions would be highly appreciated.

Thanks

TB


.

Relevant Pages

  • How to add new extensions "correctly"
    ... How should one get SQL FTS to index files' content ... populated the "extension mapping" column with the original file's ... So far as I can see, via the Citeknet IFilter Explorer, for SQL ... (somehow, e.g. directly in registry). ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Searching for text within files
    ... It seems to ignore files with a .SQL extension when searching, ... In the Advanced options, check 'Search Subfolders' ... contain that string. ...
    (microsoft.public.windowsxp.general)
  • Re: empty values in INSERT INTO statement
    ... > Extension) _ ... > So with complete data the above lines generate the following SQL ... > If the texts fields are empty then the statement would be: ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.db)
  • Re: sliced find in Linq
    ... > Of course, this is the same as the Customers collection, so you ... you want to use the Skip extension ... so I wouldn't assume that Linq to SQL knows about it. ... certainly avoids returning everything to the client. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Fulltext and text type fields
    ... file system and the ifilter is in the os and SQL is configured to load it), ... and varbinary(with the document type column containing the extension ... Director of Text Mining and Database Strategy ...
    (microsoft.public.sqlserver.fulltext)