Problem with query.

Question

Help how to make a query? I need from the List to add the new data in table 1: category date description and category (category id). But the text of the category want to find in the other table name of Category and add in table 1 id this category. Can't properly write the query. Help please.

Zoran Horvat · Answer

In that case procedure would be: ALTER PROCEDURE AddNewAmount @amount real @entryDate date @description nvarchar(1000) @category nvarchar(1000) AS INSERT INTO MoneyEntries (Amount EntryDate Description Category) SELECT @amount @entryDate @description CategoryID FROM Category WHERE Category=@Category I have changed categoryID argument into category description. But if you do in this way then you must make sure that category with specified description exists in the database and that it is unique. Otherwise MoneyEntry class should be changed to contain category identity as well. Another scenario is to create new category if it does not exist in the database and then to use newly acquired identity to insert money entry. Zoran

Mike Jonson · Answer

thnaks)))

Zoran Horvat · Answer

Then solution from my last post should work for you only change the header of the SQL procedure and then call it with category description rather than with category id which is not available. Zoran

Mike Jonson · Answer

I need add in table moneyentries id category not description.

Jaganathan Bantheswaran · Answer

Hi I cant understand your question.. Could you explain clearly based on your last post update

Mike Jonson · Answer

Maybe so: @amount real @entryDate date @description nvarchar(1000) @categoryID int @nameCategory nvarchar(100) Insert InTo MoneyEntries (Amount EntryDate Description Category) Values (@amount @entryDate @description @CategoryID = ID) Where Select ID From Category Where Name = nameCategory But i think will not work

Mike Jonson · Answer

protected List _entries; //my list public List Entries { get { return _entries; } set { _entries = value; } } public class MoneyEntry { // ????????? ????????(????? ??? ??????) private double _amount; public double Amount { get { return _amount; } set { _amount = value; } } //???? ???? private DateTime _date; public DateTime Date { get { return _date; } set { _date = value; } } //Category private string _category; public string Category { get { return _category; } set { _category = value; } } //???????? ?????????? ???????? private string _description; public string Description { get { return _description; } set { _description = value; } } I use for add foreach(MoneyEntry myList in this._entries) { ... }

Zoran Horvat · Answer

I'd say that problem is in the way in which List has been populated - I suppose that at some point you did have both category text and ID available when list was being populated. Can you paste the piece of code which populates the list? Generally when populating the list you should show the category name but preserve the corresponding category ID values so that they can be used to call this procedure which inserts row back into the database. Zoran

Mike Jonson · Answer

I creat stored procedure ALTER PROCEDURE AddNewAmount @amount real @entryDate date @description nvarchar(1000) @categoryID int AS Insert InTo MoneyEntries (Amount EntryDate Description Category) Values (@amount @entryDate @description @CategoryID) But in my List category its string type and need found this categoryName in Table Category and than add id this category in Table MoneyEntries

Zoran Horvat · Answer

If you have particular values for Date Descrption and Category but only need to fetch category identity from other table then query would look like this: INSERT INTO Table1(Category Date Description CategoryId) SELECT 'Category descrption' '07/13/2011' 'Some description' CategoryId FROM Category WHERE Category='Category descrption' Zoran

Zoran Horvat · Answer

In which form do you code the solution? Are you creating a SQL Server procedure and what is its declaration. Otherwise please paste a piece of code which initiates the insert statement. Zoran