locked
Wrong ID returned from custom TableAdapter insert method RRS feed

  • Question

  • I have a tableadaptor for a table called "Vouchers" and added a custom insert method which is basically a simple insert followed by "SELECT SCOPE_IDENTITY()" to return the newly-inserted ID (autoincrement).

    My application collects some data from user input and then executes the insert method on a button click() event. After completion, the cleared form is re-presented for the next voucher. Clicking the button the first time correctly inserts and returns an ID = 1, as expected. Entering a new voucher and clicking the button inserts a new record (with an ID of 2), however, the value returned as the ID is still set to 1.

    I'm not experienced in C#, so I may be doing something dumb here, (like not disposing of stuff that I should). Can anyone give me some insight here?

    Thanks, James

     


    DBA/Developer
    Tuesday, August 23, 2011 6:56 AM

Answers

All replies

  • I would recommend posting your code so we can see how you are executing the statement. Use the Insert Code Block button  for formatting.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, August 23, 2011 1:27 PM
  • Thanks for repsonding Paul. To isolate the problem, I created a small app. with a single datasource attached to the Vouchers table and a form with a textbox (txtVoucher) for entry of the VoucherNo, an "Add Voucher" button which calls the InsertNewVoucher() method I created for the table adaptor and finally a text box to display back the ID returned. Couldn't be simpler.

    Executing it always returns 1 as the ID, no matter if there are already records in the table or not. Each execution correctly inserts a voucher with the next ID as expected, but ID returned is always = 1.

    Any help would be fantastic. Thanks, James

    Form image:

    The Vouchers table and the table adaptor method:

    CREATE TABLE dbo.Vouchers(
    	VoucherID int IDENTITY(1,1) NOT NULL,
    	VoucherNo int NOT NULL,
    	VoucherType nvarchar(3) NOT NULL,
    	CreateUser nchar(50) NOT NULL,
    	CreateDate datetime NOT NULL DEFAULT GETDATE(),
    	UpdateUser nchar(50) NOT NULL,
    	UpdateDate datetime NOT NULL DEFAULT GETDATE(),
     CONSTRAINT PK_Vouchers PRIMARY KEY CLUSTERED (VoucherID ASC)
     WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]);
    
    --Table Adaptor "InsertNewVoucher()" method
    INSERT INTO [dbo].[Vouchers] 
    	([VoucherNo], [VoucherType], [CreateUser], [CreateDate], 
    	[UpdateUser], [UpdateDate]) 
    	VALUES (@VoucherNo, @VoucherType, @CreateUser, @CreateDate, 
    				@UpdateUser, @UpdateDate);
    SELECT SCOPE_IDENTITY()
    

    The Code behind the form is:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    
    namespace WindowsFormsApplication1
    {
     public partial class frmVoucher : Form
     {
      public frmVoucher()
      {
       InitializeComponent();
      }
    
      private void btnAddVoucher_Click(object sender, EventArgs e)
      {
       int iID = 
          vouchersTableAdapter.InsertNewVoucher(Convert.ToInt32(txtVoucherNo.Text), 
                           "XXX", 
                           "JMM", DateTime.Now,
                           "JMM", DateTime.Now);
       txtIDReturned.Text = iID.ToString();
      }
    
      private void Form1_Load(object sender, EventArgs e)
      {
      }
     }
    }
    
    



     


    DBA/Developer
    Wednesday, August 24, 2011 4:42 AM
    • Marked as answer by clevercat Wednesday, August 24, 2011 9:16 PM
    Wednesday, August 24, 2011 12:24 PM
  • Hi Paul

    Thanks for the pointer. The execute mode of the query was set to "Non-query". I was sure that I selected "return single value" when I was building the query, but ... obviously I didn't.

    Thanks for helping stop pulling hair whilst I have some left. (At my age, thinness on top is becoming an issue :) )

    Good luck and hopefully I can return the favour sometime.

    James


    DBA/Developer
    Wednesday, August 24, 2011 9:19 PM