Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Location
    Memphis, TN, USA
    Posts
    61

    Red face Unanswered: misearble problem

    Hi there,

    I have a wee problem with the INSERT statement. The problem is very unusual problem. I would like yours help in the same. The problem is.... Mine files are running very well on my IIS but when i posted on the net, it prompts me error messages. The error which it prompt is...
    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [Microsoft][ODBC SQL Server Driver][SQL Server]Insert Error: Column name or number of supplied values does not match table definition.

    /minorities/postrec.asp, line 34

    In brief, i would like to put some lights on the code and database. It's a very simple program in which user fill up their events details in the form and the vaules of that form is going to store in the database.The database contain one "AUTO-INCREMENT" fileds which is increment by 1 every times a new records is added. I am using SQL 2000 and ASP.

    The snapshot of Insert statement is :

    strsql="insert into recruitment values('"& Request("title") & "','"& Request("cname") & "' , '" _

    & request("cdesc") &"' , '"& request("country") &"','"& request("state") &"' , '"& request("ind") &"' , '" _

    & request("indother") &"' , '"& request("level") &"','"& request("jobdesig") &"' , '"& request("jobfun") &"', '" _

    & request("jobdesc") &"' , '"& request("currency") &"','"& request("lsalary") &"' , '"& request("hsalary") &"', '" _

    & request("chkperks") &"' , '"& request("rdohousing") &"','"& request("numvac") &"' , '"& request("degree") &"', '" _

    & request("workexp") &"','"& request("add_info") &"','"& request("email") &"', '"& request("sms") &"', '" _

    & request("address1") &"', '"& request("address2") &"', '"& request("pincode") &"','"& request("contry") &"', '" _

    & request("constate") &"' , '"& request("city") &"','"& request("teloff") &"','"& request("telhome") &"','"& request("postaddress") &"')"

    set recordset = connection.execute(strsql)

    I am very well aware what the error message means. But, both tables and files are same. One run fines on my IIS but other show the above mentions error.

    What am I doing wrong? I suspect that it might be related to the autoincrement field. Or it might be that I just plain can't do this?

    I am attaching the table details with this mail.

    Any answers or suggestion( for debugging) are most welcome.

    Thanks for your anticipations.
    shyam
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can write an INSERT statement with a column list,

    insert into
    thetable (col1, col2, ... )
    values ( val1, val2, ...)

    or else you can write it without a column list,

    insert into
    thetable
    values ( val1, val2, ...)

    but when you do the latter, the first value goes into the first column of the table, which is your bigint identity field

    you will have to list the columns


    rudy

  3. #3
    Join Date
    Mar 2003
    Location
    Memphis, TN, USA
    Posts
    61
    Originally posted by r937

    you can write an INSERT statement with a column list,

    insert into
    thetable (col1, col2, ... )
    values ( val1, val2, ...)

    or else you can write it without a column list,

    insert into
    thetable
    values ( val1, val2, ...)

    but when you do the latter, the first value goes into the first column of the table, which is your bigint identity field

    you will have to list the columns

    rudy

    Rudy,
    Well, i did the things which you taught me. But, now i am getting this error...

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'recnum', table recruitment; column does not allow nulls. INSERT fails.

    /minorities/postrec.asp, line 35

    Looking forward for your reply
    sqlboy

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i am sorry, i should have explained that you should not list the identity column

    in other words, if col1 is the identity, then you would say

    insert into
    thetable (col2, col3, ... )
    values ( val2, val3, ...)

    by not entering a value for the identity, it gets incremented

    i guess when you tried to insert a null, that's where the error happened


    rudy

  5. #5
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    With identity:
    -----------------
    Server: Msg 544, Level 16, State 1, Line 1
    Cannot insert explicit value for identity column in table 'recruitment' when IDENTITY_INSERT is set to OFF.

    Without identity or with (SET IDENTITY_INSERT recruitment ON):
    -----------------
    Server: Msg 515, Level 16, State 2, Line 1
    Cannot insert the value NULL into column 'recnum', table 'recruitment'; column does not allow nulls. INSERT fails.
    The statement has been terminated.

  6. #6
    Join Date
    Mar 2003
    Location
    Memphis, TN, USA
    Posts
    61
    Originally posted by r937
    i am sorry, i should have explained that you should not list the identity column

    in other words, if col1 is the identity, then you would say

    insert into
    thetable (col2, col3, ... )
    values ( val2, val3, ...)

    by not entering a value for the identity, it gets incremented

    i guess when you tried to insert a null, that's where the error happened


    rudy
    Thanks Mark,
    Yes !! exactly. So, what is the bottom line for me.? How i can tackle out this problem ?

    sqlboy

  7. #7
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    The error, you got, means that your table doesn't have any identity column
    or ODBC automatically runs "SET IDENTITY_INSERT recruitment ON"
    when it is inserting a value into the identity column.

    You should receive Msg 544 and not Msg 515. Are you sure your column 'recnum' is an identity column ?

Posting Permissions

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