Problem with query.

Mike Jonson

15y1.9kSQL

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.

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Zoran Horvat15y ago
Accepted 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 Jonson15y ago
thnaks)))
Zoran Horvat15y ago
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 Jonson15y ago
I need add in table moneyentries id category, not description.
Jaganathan Bantheswaran15y ago
Hi,

I cant understand your question.. Could you explain clearly based on your last post update
Mike Jonson15y ago
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 Jonson15y ago
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 Horvat15y ago
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 Jonson15y ago
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 Horvat15y ago
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 Horvat15y ago
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