How do I get the data for a auto incremented primary key from sql server to my datagridview?

Question

This is the function used to get the data to insert into the datagrid. I also have different code for a button click to view the data in datagridview. I'm using sql server 2014 as my database and I've already set the autoincrement when creating the table there. The autoincremented value is the item id.

   public:  bool AddSuppliesToTable(Supplies^ rowtosave, SqlConnection^conn, 
   SqlCommand^ cmd)
      {
    Trace::WriteLine("saving to table");
    cmd->Connection = conn;
    cmd->CommandText = "SET IDENTITY_INSERT Supplies ON";
    cmd->CommandText = "Insert Into Supplies (item_id, product_name,  
    stock_amnt,price,item_category) values(@item_id, @product_name, 
    @stock_amnt,@price,@item_category);";
    cmd->CommandText = "SET IDENTITY_INSERT Supplies OFF";
    cmd->Parameters->Add("@item_id",rowtosave->getId());
    cmd->Parameters->Add("@product_name", rowtosave->getProductNAme());
    cmd->Parameters->Add("@stock_amnt", rowtosave->getStockAmount());
    cmd->Parameters->Add("@price", rowtosave->getPrice());
    cmd->Parameters->Add("@item_category", rowtosave->getItemCategory());
    cmd->ExecuteReader();   
    return true;
}

This is the function for the datagridview

   private: System::Void loadtablebutton_Click(System::Object^  sender,  
   System::EventArgs^  e)
   {
         String^ sCon = "Data Source=Chevy; Initial Catalog=Ulti-Stocks;  
         Integrated Security = true";
         SqlConnection^ conn = gcnew SqlConnection(sCon);
         SqlCommand^ cmd = gcnew SqlCommand();
         cmd->Connection = conn;
         cmd->CommandText = "Select *from Supplies";
         try{
             SqlDataAdapter^ sda = gcnew SqlDataAdapter();
             sda->SelectCommand = cmd;
             DataTable^ dt = gcnew DataTable();
             sda->Fill(dt);
             BindingSource^ bsource = gcnew BindingSource();
             bsource->DataSource = dt;
             dataGridView1->DataSource = bsource;
             sda->Update(dt);
             MessageBox::Show("Loading Table Data was successful");
         }
         catch (Exception^ ex)
         {
             MessageBox::Show(" Error loading data ");
         }

      }

The code below is for a button to load the data in datgrid

    private: System::Void button1_Click(System::Object^  sender,  
    System::EventArgs^  e)
     {
             UltiStocks::Supplies^ supplies = gcnew UltiStocks::Supplies();


             int id, stock;
             float price;

             try{

                 supplies->setItemId(id = Convert::ToInt32(Text));
                 supplies->setProductName(itemnametextbox->Text);
                 supplies->setStockAmount(stock =  
                Convert::ToInt32(stockamnttextBox->Text));
                 supplies->setPrice(price = Convert::ToSingle(pricetextBox- 
                 >Text));
                 supplies->setItemCategory(categorytextBox->Text);
                 UltiStocks::DataBase^ database = gcnew 
                 UltiStocks::DataBase();
                 database->OpenConnection();
                 database->AddItemtable(supplies);
                 database->CloseConnection();
             }
             catch (Exception^ ex)
             {
                 MessageBox::Show(ex->Message);
             }

score 0 · Answer 1 · answered Mar 09 '15 at 06:43

0

SELECT @@IDENTITY;

After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement.

Source: https://msdn.microsoft.com/en-us/library/ms187342.aspx

answered Mar 09 '15 at 06:43

Richard Critten

2,138
3
13
16

Depending on your situation, you may want to use Scope_Identity() or Ident_Current -- details in [this SO answer](http://stackoverflow.com/q/1920558/1850797) – Edward Clements Mar 09 '15 at 06:56
I'm also getting a input string not in correct format exception message – user2127184 Mar 09 '15 at 06:58
I was trying to use scope identity but was having problems as well. The data would add but then the id would be 0 and if I tried to add a next thing in the table then I would get a duplicate primary key error – user2127184 Mar 09 '15 at 07:02
Also I'm not entirely sure which code is the problem the insert or the one for the datagrid. – user2127184 Mar 09 '15 at 07:03

How do I get the data for a auto incremented primary key from sql server to my datagridview?

1 Answers1