Re: empty values in INSERT INTO statement

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

Of course I want to hear more about parameters, if you don't mind.

And yes, SQL is one the more commonly words spoken by me. I find it a very
interesting dicipline which properly harnessed with other (for me) pending
issues like Getrows() gives fantastic results.

TB

"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:OvNsup5RFHA.3088@xxxxxxxxxxxxxxxxxxxxxxx
> TB wrote:
>> 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
>>
> As usual, your problem stems from using dynamic sql (do I sound like a
> broken record rere? <grin>)
>
>
> If you want to stick with dynamic sql, you will need to substitute the
> word "null" for your request variables when they are empty. That way, the
> resulting sql looks like this:
>
> ... VALUES ('John', 'Doe', null, null )
>
>
> With parameters, the problem is simpler. Post back if you wish to hear
> more.
>
> 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"
>


.

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)
  • empty values in INSERT INTO statement
    ... execute statement. ... So with complete data the above lines generate the following SQL statement: ... INSERT INTO mytable (Firstname, Lastname, Department, Extension) VALUES ... If the texts fields are empty then the statement would be: ...
    (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)