CODEWITHSUNDEEP
c#sql
Bradley
Bradley

Reputation: 1

Question about adding to database from text entered into textbox

I have an app that displays data from a sql database in a datagrid. I am adding a feature that will allow a user to add a new item to this datagrid. I will have an Add button in the app that when clicked, will open a new window where all the info will be added. After the user inputs the info into the text boxes and clicks save, that info gets saved to the database. This will require inserting data into two separate tables that are related. The first table is ItemDescriptor and the other table is Accessories. The id for ItemDescriptor has to be put into the Accessories table as ItemDescriptorID. I have tried to do two separate insert queries but cant figure out how to get the id from ItemDescriptor into the Accessories table programatically. Sorry, I know this might be a little confusing. Let me know if more info is needed. Thanks

_dbAccess.ExecuteNonQuery("INSERT INTO ItemDescriptor (id, Model, Brand, DeviceName, Type, Notes) VALUES ('" + System.Guid.NewGuid() + "', '" + tbModel.Text + "', '" + tbBrand.Text + "', '" + tbDeviceName.Text + '", '" + cmbType.SelectedValue + "', '" + tbNotes.Text + "')");

_dbAccess.ExecuteNonQuery("INSERT INTO Accessories (id, ItemDescriptorID, StorageRoomCount, OnHandCount, HiBayCount) VALUES ('" + System.Guid.NewGuid() + "', '" +

and thats about as far as i got...not sure how to get the ItemDescriptorID in there from the first query.

Upvotes: 0

Views: 163

Answers (4)

Bradley
Bradley

Reputation: 1

I figured out a way to make it work. I just made the guid an object and then used it as a variable and that worked. thanks for all the suggestions.

Guid guid = System.Guid.NewGuid();

if (_dbaccess != null)
{
_dbAccess.ExecuteNonQuery("INSERT INTO ItemDescriptor (id, Model, Brand, DeviceName, Type, Notes) VALUES ('" + guid + "', '" + tbModel.Text + "', '" + tbBrand.Text + "', '" + tbDeviceName.Text + '", '" + cmbType.SelectedValue + "', '" + tbNotes.Text + "')");

_dbAccess.ExecuteNonQuery("INSERT INTO Accessories (id, ItemDescriptorID, StorageRoomCount, OnHandCount, HiBayCount) VALUES ('" + System.Guid.NewGuid() + "', '" + guid + "', '" + tbStorageRoom.Text + "', '" + tbOnHand.Text + "', '" + tbHiBay.Text + "')"); 
}

Upvotes: 0

Michael Ames
Michael Ames

Reputation: 2617

I'm assuming that you're using SQL Server and that ItemDescriptor.id is an identity column; correct?

If you execute the following command immediately after your initial insert statement, it will return the value of the most recently generated identity:

var newID = _dbAccess.ExecuteScalar("SELECT Scope_Identity()");

You can use newID to construct your second insert statement.

One caveat: The SELECT Scope_Identity() statement must be executed on the same connection instance as the initial insert statement. So ensure that you're not closing and reopening your connection between statements. If this seems to be a problem, you can actually combine your insert and select statements into a single command; e.g.:

var newID = _dbAccess.ExecuteScalar("INSERT [all your code here]; SELECT Scope_Identity();");

Good luck!

Upvotes: 1

Norbert
Norbert

Reputation: 4291

Check out this site about Getting the identity of the most recently added record

Upvotes: 0

Tim
Tim

Reputation: 15237

First, please please please use parameterized queries when dealing with user-supplied input. That string concatenation stuff is just asking for a SQL injection attack.

Second of all, if you're using SQL Server, you can use something like @@IDENTITY to get the identity field after insertion. There's something comparable on all systems, so if you let us know what you're using we can point you in the right direction.

Upvotes: 2

Related Questions