|
I have three table A, B and C. Table A contain list of books borrow everyday. Table B contain the list of Books available in library. Table C suppose to have list of book not borrowed per day. I need a sample code to do this. I am using MS Access as my Backend database.
My Code is as follows:
"Insert into TableC (BookID,[Name], Author) (Select * From Table B where BookID) Not in (Select * from Table C where [Date] between ' " & TextBox1.Text & "' and ' " & TextBox2.Text & "'"
Thank you.
Thayhor
|
|
|
|
|
|
Paddy
I want to be able to get those books available in the School Library (Table A) and not yet borrowed (All the books borrowed entered into Table B) and copy the information to Table C every day. My query is given me "Syntax error in Insert Statement". I don't know how to go about this automatically.
NB: Table A,B and C have the following field in common 'BookID' 'Name' 'Author'
Thank you
|
|
|
|
|
It's a bit difficult to say what's the solution without seeing the table definitions. However, if I'm reading correctly, your problem is in syntax (I take it this gives an error). Perhaps something like following:
Insert into TableC (BookID,[Name], Author)
(Select BookID,[Name], Author
From TableB
where BookID Not in (Select bookID
from TableC
where [Date] between '" & TextBox1.Text & "' and '" & TextBox2.Text & "'" ))Also instead of using text concatenation for parameters, it's better to use bind variables (for example to prevent sql injection).
Hope this helps,
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thank you very much, but how can I use bind variable. A sample code will be appreciated.
Thayhor
|
|
|
|
|
The bind variable or parameter usage depends on how you are connecting to the database. If you're using ODBC, have a look at: OdbcParameter Class[^].
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Sounds like your database design is broken.
Instead of having three tables "A - Borrowed", "B - All", "C - Not Borrowed". Consider having just "Books" with a column representing their status, ie: "Status: Borrowed / Not Borrowed / On Fire".
Then you might want to look at the "Relational" part of "Relational Database", and have "Books", "Borrowed Book", and "Client".
Also I'm not convinced that Access is an appropriate choice to run a loan desk....
|
|
|
|
|
How can I execute a function and then a stored procedure? The function gets the latest recordID and the the insert statement should execute and insert the recordID and value. My code looks like this:
CREATE PROCEDURE dbo.InsertCountry
(
@Country NVARCHAR(200)
)
AS
BEGIN
DECLARE @LastID = BIGINT
SET @LastID = (SELECT MAX(Country_ID) FROM Countries
IF @LastID IS NULL SET @LastID = '1'
ELSE SET @LastID = (@LastID) + 1
RETURN @LastID
END
BEGIN
INSERT INTO Countries
(
Country_ID,
Country
)
VALUES
(
@LastID,
@Country
)
END
The above code does not compile but when I take the first Begin and End block and put that in a function it works. I try to execute the function by using EXEC GetLastID but that does not work.
Illegal Operation
|
|
|
|
|
I have an UGLY suspicion that you are doing this completely wrong. CountryID should be an Identity field, you should not be attempting to populate the ID like this.
However try this
<br />
DECLARE @LastID = BIGINT<br />
SET @LastID = IsNull((SELECT MAX(Country_ID) FROM Countries),0)<br />
SET @LastID = (@LastID) + 1<br />
<br />
BEGIN<br />
INSERT INTO Countries<br />
(<br />
Country_ID,<br />
Country<br />
)<br />
VALUES<br />
(<br />
@LastID,<br />
@Country<br />
)<br />
END<br />
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Well, if it doesn't compile then the error message would have been useful, but it's never going to be reliable anyway.
Try this - and yes, there may be syntax errors for you to fix 
CREATE PROCEDURE dbo.InsertCountry (
@Country NVARCHAR(200) )
AS
BEGIN
begin tran
INSERT INTO Countries
(Country_ID,Country)
select isnull(max(Country_ID),0) + 1, @Country
FROM Countries
commit
END
The reason it would never be reliable is because you could end up with 2 users executing it at the same time and getting the same id. By putting a transaction around it will prevent that.
BTW, your original code error was probably caused by RETURN @LastID, it would skip the rest of the code and exit the proc.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Here is my delema. A company row can have multiple representations in the security table, based on the priusa or prican values in the company table matching the iid values int he security table, or the values in company table being null, in which case i just want to match the cue of the first iid listed for the key in the security table.
The If section works if I change out the copmany.key references for a literal value, so as far as i can tell its having an issue with the assignment of the value to security.iid = (IF...).
Any ideas?
SELECT company.key, company.name, security.cue
FROM company LEFT OUTER JOIN
security ON company.key= security.key AND security.iid = (IF (select priusa FROM company WHERE key = company.key) is NOT NULL
BEGIN
select priusa FROM company WHERE key= company.key
END
ELSE
IF (select prican FROM company WHERE key= company.key) is NOT NULL
BEGIN
select prican FROM company WHERE key= company.key
END
ELSE
BEGIN
select TOP 1 iid FROM security WHERE key= company.key
ORDER BY security.iid
END)
WHERE (company.name like @name)
|
|
|
|
|
The reason is that you cannot have correlation in inline view when used in from section.
Perhaps something like this would be a simpler approach:
SELECT company.key,
company.name,
(SELECT security.cue
FROM security
WHERE company.key = security.key
AND security.iid = ISNULL(company.priusa,
ISNULL(company.prican,
(select MAX(iid) FROM security WHERE key= company.key))))
FROM company
WHERE (company.name like @name)
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thanks much
That works wonderfully for this case. However, what if i wasn't skipping null values. What if the default value was 0 and not null?
|
|
|
|
|
if you want to use more complicated conditions, use COALESCE.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Perhaps the COALESCE function would be better?
|
|
|
|
|
Yep, that would also work out fine.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thanks.
I'm just getting started and coundn't find anything without knowing the keywords to look for.
|
|
|
|
|
No problem.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
refer to the title, thanks....
|
|
|
|
|
I would guess that you are referring to the time the database is in active use:
- 24 x 7: 24 hours per day 7 days a week
- 8 x 5: 8 hours per day 5 days in a week (working days and working time)
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
hours you work in a week VS hours you breathe in a week, could be worst! 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi, im from Germany sorry for my (bad?) english.
Actually i have a really stupid problem.
Ill try to explain it in an abstract example:
1. Imagine you have to log your cd sells, every cd has a different price
2. The price Changes every 6th months and is used for the next 6 month.
3. When i sell the CD, i want to save the date and the current price.
Now my problem:
How do i save the price logging. Do i have to save the current Price in the
CD-Table and save this price for each sell in the selling-table?
Or
Do i have to create a CD/Price/Date table wich contains the prices of a CD of
a period. And i have to look for this price manually.
Or
????
Oh man i hope you understand my problem and know a solution.
Best regards!
|
|
|
|
|
The "proper" way would be to have a price table with an id and a to and from date and log the cd id, price id (and sale date) to a sales table.
eg
Prices Table
ID Price From To
1 14.99 1 jan 2008 30 jun 2008
2 15.99 1 jul 2008 null (indicates current price)
CD Table
CD_ID Title
102 The best of...
103 The Worst of..
Sales Table
CD_ID Price_ID Sale Date
102 1 14 may 2008
102 1 17 Jun 2008
103 1 10 Jan 2008
102 2 14 aug 2008
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Very similar to my solution below, except you have not stored a reference to the item in the price table. This, i guess, allows you a bit more flexibility in re-using prices across items.
|
|
|
|
|
Great minds... 
I would proably (in real life) have a poc coe against the cd and in the price table to allow for different prices depending on the cd, but that wasn't specified 
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
