Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Apr 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inserting null value in SQL Server column

    I have a SQL Insert statement that sends data to a SQL Server table.
    The values are picked up from a form using ASP.

    Sometimes the values will be blank or do not require and answer.
    How can I insert these values as NULL into SQL server?

    I am getting an error like the following:

    Microsoft OLE DB Provider for SQL Server error '80040e14'
    Line 1: Incorrect syntax near ','.
    end.asp, line 46

  • #2
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    can you response.write out the sql query.

    I've never had any issues inserting null value as long as your field that your inserting it to will allow this.

  • #3
    New Coder
    Join Date
    Apr 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is the statment:

    Insert Into [Score Data] (fName, lName, Ngh, Level, NumSelling, DelTimeSlot, DelStation, Ck1, Ck2, Ck3, Ck4, Ck5, Ck6, Ck7, Ck8, TotCases, wT1, wT2, wT3, startTime, endTime) Values ('Joe', 'Smith',12,2,12,2,61,1,2,3,4,5,6,7,8,36,1,2,3,'8:00','9:00')

    The problem is, the fields listed below are not required and sometimes will be blank.

    wT1
    wT2
    wT3
    startTime
    endTime

    So when the insert statement excutes and there is no value in one of these fields - it gives any error. How can I set them to null?

    Also, Allow Nulls is checked in SQL Server.

  • #4
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    ok, the sql looks fine.

    maybe just do a check on all values before you insert them, you can could this several ways.

    like:

    If strValue = "" Or IsNull(strValue) Then strValue = " "

    Or:

    If Len(strValue) = 0 Then strValue = " "

    or is the field is an Int, then

    If numValue = "" Or IsNull(strValue) Then strValue = 0


    though this still doesn't seem write, you should be able to insert null values no prob. maybe try wrapping all your Int values in single quotes aswell,
    if that doesn't work, post the sql insert back again with the values null, so we can see exactly what it looks like with null values.

    hope this helps.

  • #5
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Response.Write your query.

    The error is saying that you have a syntax error, not a Null error.
    It would seem that you have an extra comma at the end of your query when the fields are not required.

  • #6
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,065
    Thanks
    4
    Thanked 8 Times in 8 Posts
    just look to see if there is a value in the variable after you assign a value to it, if it has no value, assign it a value of 0. Then when you run your sql statement you will not have a ,, in the sql statement


  •  

    Posting Permissions

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