Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    los angeles
    Posts
    5

    Unanswered: Trying to insert row (autogenerate key) failing

    I am trying to insert a row into a sql table; I set the identity to 'yes' and seed to 1, increment to 1 in sql manager.
    My webpage does the insert but I am receiving this error. Any help would be great. Thanks in advance!
    Dim mySQL As String = "Insert into Process_Master (ProcessName) values (@pname)"

    Dim myConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("M ATRIX_CONNECTION_STRING"))

    Dim myCmd As New SqlCommand(mySQL, myConn)

    myCmd.Parameters.Add(New SqlParameter("@pname", TextBox1.Text))


    myConn.Open()

    myCmd.ExecuteNonQuery()

    myConn.Close()

    BindData()

    Cannot insert explicit value for identity column in table 'Process_Master' when IDENTITY_INSERT is set to OFF

  2. #2
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49
    Try setting IDENTITY_INSERT to on so that the system can automatically generate the identity number.
    Cathy

  3. #3
    Join Date
    Feb 2004
    Location
    los angeles
    Posts
    5
    cathy,
    I thought that you are suppose to turn the Identity_insert to 'on' when you want to specify your own id number or 'off' to let the system generate it automatically? Is there a way so that I could set it to 'on' at the same time I send my 'insert' statement? Thanks.

  4. #4
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49
    You are correct. It does need to be on. I've used a batch script to turn it on, then run the query, then turn it back off.

    set identity_insert TBL_SAMPLE ON
    go

    insert into TBL_SAMPLE (sm_group_id, sm_dw_dwg, sm_dw_rev,
    sm_ct_counter, sm_man_code, sm_poc, sm_job_order, sm_rcv_date,
    sm_trans_date, sm_test_days_allowed, sm_test_doc_rev,
    sm_lot_nr, sm_sample_size, sm_program, sm_lot_score, sm_rpt_nr,
    sm_rpt_date, sm_engineer, sm_ar, sm_in_process_date)
    select sm_group_id, sm_dw_dwg, sm_dw_rev,
    sm_ct_counter, sm_man_code, sm_poc, sm_job_order, sm_rcv_date,
    sm_trans_date, sm_test_days_allowed, sm_test_doc_rev,
    sm_lot_nr, sm_sample_size, sm_program, sm_lot_score, sm_rpt_nr,
    sm_rpt_date, sm_engineer, sm_ar, sm_in_process_date
    from temp_TBL_SAMPLE
    go

    set identity_insert TBL_SAMPLE off
    go


    My problem now is it works for one, but not another.
    Cathy

  5. #5
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49

    Thumbs up

    Ok, this gave an error:

    set identity_insert TBL_TASKS on
    go

    insert into TBL_TASKS
    select tsk_taskid, tsk_StartDate, tsk_EndDate, tsk_score, tsk_final_score,tsk_Notes, tsk_smd_id, tsk_sc_seq_id, tsk_per_test_operator, tsk_operator2, tsk_operator3, tsk_operator4, tsk_loc_test_system, tsk_loc_test_system2, tsk_loc_test_system3, tsk_loc_test_system4, tsk_ts_number, tsk_td_counter, tsk_ts_spec_para, tsk_ts_proc, tsk_ts_test_time, tsk_test_count,
    tsk_next_test, tsk_prev_end_date, tsk_labor_hrs, tsk_eng_remarks
    from temp_TBL_TASKS
    go

    set identity_insert TBL_TASKS off
    go


    but this did not give an error:

    set identity_insert TBL_TASKS on
    go

    insert into TBL_TASKS (tsk_taskid, tsk_StartDate, tsk_EndDate, tsk_score, tsk_final_score,
    tsk_Notes, tsk_smd_id, tsk_sc_seq_id, tsk_per_test_operator,
    tsk_operator2, tsk_operator3, tsk_operator4, tsk_loc_test_system,
    tsk_loc_test_system2, tsk_loc_test_system3, tsk_loc_test_system4,
    tsk_ts_number, tsk_td_counter, tsk_ts_spec_para, tsk_ts_proc, tsk_ts_test_time, tsk_test_count, tsk_next_test, tsk_prev_end_date, tsk_labor_hrs, tsk_eng_remarks)
    select tsk_taskid, tsk_StartDate, tsk_EndDate, tsk_score, tsk_final_score, tsk_Notes, tsk_smd_id, tsk_sc_seq_id, tsk_per_test_operator, tsk_operator2, tsk_operator3, tsk_operator4, tsk_loc_test_system, tsk_loc_test_system2, tsk_loc_test_system3, tsk_loc_test_system4, tsk_ts_number, tsk_td_counter, tsk_ts_spec_para, tsk_ts_proc, tsk_ts_test_time, tsk_test_count,
    tsk_next_test, tsk_prev_end_date, tsk_labor_hrs, tsk_eng_remarks
    from temp_TBL_TASKS
    go

    set identity_insert TBL_TASKS off
    go

    The difference is that on the insert statement, I listed all of the fields to insert.
    Cathy

  6. #6
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69
    Originally posted by csbaker
    Try setting IDENTITY_INSERT to on so that the system can automatically generate the identity number.
    is ProcessName ID column in your table?

  7. #7
    Join Date
    Feb 2004
    Location
    los angeles
    Posts
    5
    Yes, processid,processname; I wanted to just do an insert with the processid automatically increment to the current max number +1. I don't see why there would be a problem if I just insert with a new processname row; my sql table is set properly also to identity_insert(1,1); I had existing rows in the table then set the identity_insert; I wonder if this is part of my problem?

Posting Permissions

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