Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2002
    Posts
    4

    Question Unanswered: how to replace blanks with nulls

    I have several fields that are set to default to NULL. When I update a table from an ASP form page, the fields for which no input is entered, are passed with a value of "blank". When the table is updated, the "blanks" are inserted into the fields, but I need the value of the "blank" fields to be NULL. I have other queries that are dependant upon the NULL value being in the fields. What is a good solution to this?????

  2. #2
    Join Date
    Sep 2002
    Posts
    7
    To insert colum1 a null value, you can do this

    insert <table> (colum1) values (null)

  3. #3
    Join Date
    Oct 2002
    Posts
    4
    All values of the column are not blank, in some cases there is data passed to this column, the problem is when the form is submitted with no data and the blanks are then entered into the column.

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    does your ASP page insert directly into the table or via a stored procedure?

    If a stored procedure then you can test each parameter and substitute nulls for zero length strings.

    If you insert directly then you will need to periodicaly sweep the table doing global updates

    or

    create a trigger to modify the data at time of Insert or Update.


    You didn't provide to much detail on your problem so post back if you have specific questions
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Oct 2002
    Posts
    4

    More info

    I am submitting a form using ASP and inserting or updating directly into SQL Server table, I have not used a stored procedure or a trigger before. Are they more efficient or better to use? I am looking into stored procedures right now.

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    IMHO, I like the use of stored procedure because you can isolate the underlying schema from your users. Also SQL server pre-optomizes your code saving you that step on each direct call. ODBC will attempt to do this for you to some degree of success.

    In SQL server triggers have fallen out of favor for enforcing referencial integrity but are still widely used for business rules. In your case the rule could be replace zero length strings with nulls before inserting data into the db.

    If you have never messed with stored procedures and triggers I would suggest giving a stored proc a try. Triggers are written with the same syntax as stored procedures but require alot more understanding of how SQL Server operates. Triggers can bit hard if you don't get them right, debugging a stored procedure can be alot easyier!
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Oct 2002
    Posts
    4

    Cool Good stuff

    How about a sample stored procedure for my problem?

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Code:
    create table #Tmp1(Parm1 varchar(25))
    go
    create proc sample(
    @Parm1  varchar(25) = Null)
    
    as
    
    if (@Parm1 = '')
      set @Parm1 = Null
    
    Insert into #Tmp1 values(@Parm1)
    
    return 0
    go
    
    exec sample '1'
    exec sample 'A'
    exec sample ''
    exec sample ' '
    go
    
    select * from #Tmp1
    go
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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