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.

Answer 1

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

Answer 2

thnaks)))

Answer 3

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

Answer 4

I need add in table moneyentries id category, not description.

Answer 5

Hi,

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

Answer 6

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

Answer 7

protected List<MoneyEntry> _entries; //my list
        public List<MoneyEntry> 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) {  ... }

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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