Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jan 2004
    Posts
    8

    Unanswered: Stored procedure help

    I am trying to use a stored procedure with an ASP page. The table I am inserting too has a column called currentemailsfaxesID which does not allow values. I want this field to be the @@IDENTITY when it inserts. I keep getting an error about not allowing NULLS. Here is the statement I have been working with. While I am familiar with SQL, @@IDENTITY is new to me. Any help would be appreciated.

    INSERT INTO arttesttable
    (customer_code, message_type, subject, start_date,
    email_address, send_from, message_text, fax_number, times_sent, last_sent,order_no)

    VALUES
    ('34588', 'TEST', 'TEST', '2004-01-01',
    '@strEmailAddress', '@strSendFrom', '@strMessageText', '@strFaxNumber', 0, '2004-01-01', 0)

    SELECT @@IDENTITY as currentemailsfaxesID

  2. #2
    Join Date
    Jan 2004
    Posts
    8

    Re: Stored procedure help

    Originally posted by wasp911
    I am trying to use a stored procedure with an ASP page. The table I am inserting too has a column called currentemailsfaxesID which does not allow values. I want this field to be the @@IDENTITY when it inserts. I keep getting an error about not allowing NULLS. Here is the statement I have been working with. While I am familiar with SQL, @@IDENTITY is new to me. Any help would be appreciated.

    INSERT INTO arttesttable
    (customer_code, message_type, subject, start_date,
    email_address, send_from, message_text, fax_number, times_sent, last_sent,order_no)

    VALUES
    ('34588', 'TEST', 'TEST', '2004-01-01',
    '@strEmailAddress', '@strSendFrom', '@strMessageText', '@strFaxNumber', 0, '2004-01-01', 0)

    SELECT @@IDENTITY as currentemailsfaxesID
    currentemailsfaxesid does not allow NULL values. sorry

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Use SELECT instead of VALUES:

    INSERT INTO arttesttable
    (customer_code, message_type, subject, start_date,
    email_address, send_from, message_text, fax_number, times_sent, last_sent,order_no)

    select '34588', 'TEST', 'TEST', '2004-01-01',
    '@strEmailAddress', '@strSendFrom', '@strMessageText', '@strFaxNumber', 0, '2004-01-01', 0,
    @@IDENTITY as currentemailsfaxesID

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    snail: Why?

    wasp: And also use SCOPE_IDENTITY(). This will guarantee that the new value is taken from YOUR last insert statement.

  5. #5
    Join Date
    Jan 2004
    Posts
    8
    Still get null errors if I use this.

    INSERT INTO arttesttable
    (currentemailsfaxesID, customer_code, message_type, subject, start_date,
    email_address, send_from, message_text, fax_number, times_sent, last_sent,order_no)

    select @@IDENTITY as currentemailsfaxesID, '34588', 'TEST', 'TEST', '2004-01-01',
    '@strEmailAddress', '@strSendFrom', '@strMessageText', '@strFaxNumber', 0, '2004-01-01', 0

    If I use the query posted by snail, I get an error that the select has more values than the insert.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Oh, and what's up with variables surrounded by apostrophes?

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by rdjabarov
    snail: Why?

    wasp: And also use SCOPE_IDENTITY(). This will guarantee that the new value is taken from YOUR last insert statement.
    SELECT is more usefull if you are in need to make some additional actions during insert - something like this:

    -- will not work
    insert tmptable(code) values((select top 1 code from tmptable))
    -- will work
    insert tmptable(code) select((select top 1 code from tmptable))

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Agree, but he's not doing it.

  9. #9
    Join Date
    Jan 2004
    Posts
    8
    Variables in '' are the variables I am going to use when the query works. Put '' around them instead of typing test text in there.

    Pure laziness basically

  10. #10
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by rdjabarov
    Agree, but he's not doing it.
    But if he do? At least it is good to know....

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Duely noted

  12. #12
    Join Date
    Jan 2004
    Posts
    8

    Re: Stored procedure help

    Originally posted by wasp911
    I am trying to use a stored procedure with an ASP page. The table I am inserting too has a column called currentemailsfaxesID which does not allow values. I want this field to be the @@IDENTITY when it inserts. I keep getting an error about not allowing NULLS. Here is the statement I have been working with. While I am familiar with SQL, @@IDENTITY is new to me. Any help would be appreciated.

    INSERT INTO arttesttable
    (customer_code, message_type, subject, start_date,
    email_address, send_from, message_text, fax_number, times_sent, last_sent,order_no)

    VALUES
    ('34588', 'TEST', 'TEST', '2004-01-01',
    '@strEmailAddress', '@strSendFrom', '@strMessageText', '@strFaxNumber', 0, '2004-01-01', 0)

    SELECT @@IDENTITY as currentemailsfaxesID
    I keep getting an error when I try this. The currentemailsfaxes column does not allow nulls and the statement keeps returning a NULL value.

  13. #13
    Join Date
    Jan 2004
    Posts
    8
    I am trying to run this in Query Analyzer. I keep getting the NULL value error. This is copied from the example in the MSDN but it won't work. No matter what method I try, it thinks the value is NULL.

    INSERT arttesttable
    (customer_code, message_type, subject, start_date,
    email_address, send_from, message_text, fax_number, times_sent, last_sent,order_no)

    VALUES
    ('34588', 'test', 'test', '2004-01-26',
    'test', 'From', 'Text', '1', 0, '2004-01-26', 1)

    SELECT @@IDENTITY AS currentemailsfaxesID

  14. #14
    Join Date
    Jan 2004
    Location
    DFW, Texas
    Posts
    1
    was the column defined as an identity column?

  15. #15
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Why don't you just set currentemailsfaxesID field as Identity (Not For Replication) in the table design. Then you don't have to pass it in.

Posting Permissions

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