|
Hello Everybody
Table Name dbo.Books where I kept Books Information. At First You need to put All information on dbo.Books table. Now this book is not Active because I need to make a Copy of this book which will be stored at dbo.CopyBooks table.
dbo.Books table
ISBN BookName
1001 Sql Server 2000
1002 Sql Server 2005
Now I want to make Copy of those book
dbo.CopyBooks
ISBN Book_ID
1001 1001101
1001 1001102
1001 1001103
1002 1002101
1002 1002102
1002 1002103
1002 1002104
First four digit is ISBN number and last three digit indicate book copy number 101 is the First Copy, 102 is the Second Copy.
How will I do it?
Thanks
Sarfarj Ahmed
|
|
|
|
|
You could add another column to CopyBooks called CopyID. This would be a sequence no. uniquely identifying a copy of each book. For example:
ISBN CopyID Book_ID
1001 101 1001101
1001 102 1001102
1001 103 1001103
1002 101 1002101
1002 102 1002102
1002 103 1002103
1002 104 1002104
Book_ID could then be changed to a computed column using the formula (CONVERT([varchar],[ISBN],0)+CONVERT([varchar],[CopyID],0)).
To find the next CopyID for a particular ISBN, all you have to do is find the current highest CopyID for that ISBN and add 1.
Paul Marfleet
|
|
|
|
|
Thanks
Basically wot I would like to is....
table dbo.copyBook
AutoID ISBN CopyID BookID
1 1001 101 1001101
2 1001 102 1001102
3 1002 101 1002101
4 1002 102 1002102
On my frmAddCopy.vb
When You click btnAddCopy then only ISBN will send to database and return BookID. Also How will I incresed copyID in database. Please tell me more information. I dont know how to use function and procedure in SQL SERVER 2005 dataBase.
Is their any thing that i can do it in sql server 2005 database?
Sarfarj Ahmed
|
|
|
|
|
If you don't know how to use functions or procedures in SQL Server, you should buy a book and start learning. I don't think I can help you much if you don't have basic knowledge of the tools you are using.
If you aren't familiar with the SQL language, this[^] may be a good place to start. Look at using the MAX aggregate function to help you increment the CopyID sequence.
Paul Marfleet
|
|
|
|
|
about dbo.fine
AutoID MemberID ReturnDate ReturnOn Fine
1 S10001 23/10/2007 25/10/2007 1.00
2 S10002 21/10/2007 25/10/2007 2.00
3 S10003 21/10/2007 2.00
I wolud like create this table like this,
on dbo.fine table will automatically check ReturnDate column from dbo.borrowBook table add fine on Fine.Fine table. It will will be check CurrentDate and ReturnDate.
How will I create this Table? Any Instruction.......
Sarfarj Ahmed
|
|
|
|
|
I don't understand what your question is. Do you want help creating the table? Use Management Studio or the CREATE TABLE DDL statement.
Paul Marfleet
|
|
|
|
|
Yes I want to create table which will check coloum value from other table like dbo.fine table will check returndate from dbo.borrow table.
Like if ReturnDate is 20/12/2006 but SubmitedDate is 25/12/2006 . Per day fine 20P, so fine coloum On dbo.Fine table will automatically add £1.00
How CAN I DO IT?
Sarfarj Ahmed
|
|
|
|
|
First of all, don't shout. It's rude. This is a free site, so be grateful that people are willing to give up their time to help you.
Secondly, this is pretty basic arithmatic isn't it? Calculate the number of days difference between the ReturnDate and SubmittedDate using the DATEDIFF function and multiply the result by the daily fine to get the overall fine.
Paul Marfleet
|
|
|
|
|
we have two database A & B.i want to update field Address1,Address2,Address3 in database A with Address1,Address2,Address3 in B database.how can achived it.
Thanks
|
|
|
|
|
update a.dbo.TableName .....etc
not both databases should be in same server.,
|
|
|
|
|
Something like:
update
A.dbo.Address1 X
set
X.Address1 = Y.Address1,
X.Address2 = Y.Address2,
X.Address3 = Y.Address3
from
B.dbo.Address Y
where
X.Id = Y.Id
As stated before, both databases need to be on the same server for this to work.
Regards,
Rob Philpott.
|
|
|
|
|
i am using an oledbcommand object to perform both insert and update based on the command text supplied and i am adding the parameters for update also in the order of insert. So incase of update though, its not giving any error/exception but database is not getting updated.
Can any body pls tell me how to resolve this or any other way to implement both the functionalities using single method.
Thanks in advance.
|
|
|
|
|
The order of parameters should not be an issue. You need to first check your update stored procedures or statements. The easiest way to do this is to trying and execute them (using EXEC) directly in the database. This will enable you confirm whether they are OK.
|
|
|
|
|
I Have a table of emp with the fields of
Empno empname Empsalary
1 sam 1000
2 joe 2000
3 harry 3000
Now i want a query for a give me the result like this
Empno empname Empsal salTotal
1 sam 1000 1000
2 joe 2000 3000(sum of previous salary Total with the current sal)
3 harry 3000 6000 sum of previous salary Total with the current sal
How can be achive this result by using query
Thanks a ton in advance
Thanks and Regards
Aavesh Agarwal
|
|
|
|
|
This will give you what you are after, but will take a long time to run if your table is large, as it executes the subquery once for each line.
select EmpNo, EmpName,
(select sum(EmpSalary) from emp e2 where e2.EmpNo <= e1.EmpNo)
from emp e1
order by e1.EmpNo
Hope that helps!!
-------------------------------------------
Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Just bugger off and leave me alone!!
|
|
|
|
|
Hi
Thanks For reply.Its Working but as u said it will take long time when table is large any Other way by which our query work fine untill table is large.
Thanks a Ton
Thanks and Regards
Aavesh Agarwal
|
|
|
|
|
For something like this you might consider using cursors - things which iterate through your table like a traditional loop. They're generally best avoided although I think quite appropriate in this case.
Regards,
Rob Philpott.
|
|
|
|
|
I want to use Form Authentication for reports and that is to coordinated with my web application , whatever database i m using in my web application , only that database i have to use for formauthentication in SSRS and once user logged in , He/she didn't login again to access for report server and User can see their reports according to user role.
I was using the method which is described in Form authentication Sample in msdn but i didn't get full success.
Please help me out.
shraddha tripathi
|
|
|
|
|
i have to use the while loop
like
while(select name from table)
update table set name=name + 0
next
I know this is not a realistic , i was trying to just make u understand.
How should i implement it.
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
hi
You can use Cursor for this
regards
JOE
|
|
|
|
|
can u provide some link?
i mean how to use cursors in stored procedure?
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
google broken where you live ?
Cursors are very expensive, you should avoid them if you can.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Hi Christian...
How should i implement the following thing
i have to use the while loop
like
while(select name from table)
update table set name=name + 0
next
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
Like Chris has stated, cursors are more expensive. If you can explain us on what you are trying to do, probably you may be suggested with a better workaround.
|
|
|
|
|
Like there are four Rows
S.No Total Received Balance Delete
1 1000 100 900 Delete
2 1000 200 700 Delete
3 1000 300 400 Delete
4 1000 400 0 Delete
if i delete the first row then the effect will take place on the balance column in the 2nd, 3rd , 4th column.
I want to use the sql queries in (Stored Procedure)database and not in the form
Now i am telling u what i thought to do it
first select statement , to fetch all the records, then one by one i will subtract the received from the total and ba;lance will be updated.
This is what i want to do.
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
