|
Hi
I am currently developing hostel management software for a local hostel. I am trying to find a way of identifying those residents who have outstanding rent payments.
The hotel/ youth hostel requires that its guests pay their rentals at the beginning of the month. The residents often stay at the hostel for periods that are often over 5 months.I need to find a way of finding out which residents have paid their renatals, and which ones have not paid their rents. This is further complicated by the fact that some residents will make one large payment at the beginning of their session and this is meant to cover them for the duration of their stay.
Each session has one account, and the total payments made are stored in the acount. I have also included the rate charged per day and the startdate of the session into the table, such that it is possible to calculate the amount that is owed and this figure can be some how be compared to the amount that has been paid.
Given these circumstances, how can I find the residents who have outstanding rents.
I havce included the table defs below.
Please do feel free to help me.
create table sessions
( sessionNumber int Identity Not null Primary Key,
startdate smalldatetime not null,
enddate smalldatetime not null,
sessionType char(15) not null,
duration int not null,
roomNumber char(5) not null references Rooms (roomNumber),
idNumber char(15) Not null references guestinfo (idnumber),
)
create table accounts
(
receiptNumber int Identity Not Null primary key,
amountpaid money not Null,
datepaid smalldatetime not null,
roomNumber char(5) not null references rooms(roomNumber),
sessionNumber int Not null references sessions(sessionNumber),
paymentType char(10),
userName char(15) Not Null references users (username),
)
---create table accpayments---
( accountNumber int identity Not Null primary key,
sessionNumber int not null references sessions(sessionNumber),
totalpaymentsmade money not null,
amountowing money not null,
rate money not null
)
twsted f8
|
|
|
|
|
You would need to write a query that returns:
i) the total rent incurred thus far by each guest. This would presumably be the the number of days elapsed since the guest began their stay, multiplied by their daily rate
ii) the total amount paid by the guest thus far
Subtracting ii) from i) should give you the total amount currently owed by each guest.
Regards
Paul
|
|
|
|
|
Hi,
I have 2 tables with same fields like event, venue, date and cost. Table 1 has 5 records and Table 2 has 100 records. How can i match a record from Table 1 with Table 2 to get the exact match record of Table 1 record. Event Venue fields are string datatypes which will not have same records. I need the nearest match .Please help me out. Thanks in advance.
Jebin
|
|
|
|
|
SELECT t1.venue
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.[ID] = t2.[ID]
WHERE t2.venue IS NOT NULL
|
|
|
|
|
I have several hundred tables that I need to do stuff with... problem is I do not have anything even close to a direct connection to the database. They have two layers of "stuff" between the app. and the datasource.
So, I'm wondering, is it possible to write SQL like commands against a DataSet without having a connection? All the examples I've looked at so far do something allong the lines of:
SqlConnection conn = new SqlConnection("blah, blah”);
conn.Open();
SqlDataAdapter dataadapter = new SqlDataAdapter("SELECT * FROM whatever", conn);
Is there a way around the connection thing?
Any recommended readings?
Thank you.
|
|
|
|
|
You want to populate a dataset without a connection?
You can certainly create a dataset and manually code rows and tables. If you have an already populated dataset, you can manipulate the data inside it to your hearts content, but you do need a DB connection to persist the data to the database.
|
|
|
|
|
Yeah, this is strictly for presentational purposes... they have Load and Post proxies I can use to update.
What I want to do is create a “Display Table” from 2 or 3 existing tables without writing an excessive amount of code. There are a minimal amount of common columns in the existing tables, so I need to take like 4 or 5 columns from one, and 4 or 5 from another, based on 1 primary in a 3rd.
I'm really new to C# and the whole .NET thing, but this seams like it should be a no-brainer of a task.
|
|
|
|
|
I'm guessing you have a number of datasets each containing a single table?
You might be best to copy all these tables to within a single dataset, and you can then set up relationships between these tables (within the dataset). You can then set about coding what you want to see relatively easily.
|
|
|
|
|
DataTable has a Select method. You can use DataRelations between DataTables to navigate between parent and child tables (using the ParentRelations and ChildRelations properties). Unfortunately there's no built-in Join implementation, and no indexing other than by primary key.
.NET Framework 2.0 offers a DataTableReader class which provides the IDBDataReader interface over a DataTable. This might be handy if you have code that can work with either live data coming direct from the database or cached data in a DataSet or DataTable.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
i try to create trigger on table to insert values in another table but
the is syntax error
(Msg 1046, Level 15, State 1, Procedure MoveStudentInfo, Line 20
Subqueries are not allowed in this context. Only scalar expressions are allowed.)
here is the trigger script
create trigger MoveStudentInfo on Temp_Code
after insert
as
declare @Stu_ID bigint ,@Stu_Code nvarchar(50)
select @Stu_ID=Stu_ID from inserted
select @Stu_Code=Stu_Code from inserted
insert into Student_Info (Stu_Code,Stu_Name,Sex,Nationality,Relagion,Address
,ZipCode,Phone,Mobile,Email,District,BirthPlace,BirthDate,Certificate_Type
,Certification,Department,Grad_Date,Total,Grade_Total,School_name,Stu_Image
,School_SeatNO,National_ID,Region,First_Choice,Second_Choice,Third_Choice
,First_Lang,Second_Lang)
values(@Stu_Code,(select Stu_Name,Sex,Nationality,Relagion
,Address,ZipCode,PhoneNO,Mobile,email,District,BirthPlace,BirthDate,Certificate_Type
,Certification,Department,Grad_Date,Total,Grade_Total,school_name,Stu_Image
,School_SeatNO,National_ID,Region,First_Choice,Second_Choice,Third_Choice
,First_Lang,Second_Lang from Student_Registry where Student_ID=@Stu_ID))
thnx for help
MD_NADA
|
|
|
|
|
You must use the inserted table for your insert so your code should be something like:
Forum SQL / ADO / ADO.NET
Subject: Re: problem with create trigger
Sender: md_nada
Date: 8:28 24 Sep '07
i try to create trigger on table to insert values in another table but
the is syntax error
(Msg 1046, Level 15, State 1, Procedure MoveStudentInfo, Line 20
Subqueries are not allowed in this context. Only scalar expressions are allowed.)
here is the trigger script
create trigger MoveStudentInfo on Temp_Code
after insert
as
insert into Student_Info (Stu_Code,Stu_Name,Sex,Nationality,Relagion,Address
,ZipCode,Phone,Mobile,Email,District,BirthPlace,BirthDate,Certificate_Type
,Certification,Department,Grad_Date,Total,Grade_Total,School_name,Stu_Image
,School_SeatNO,National_ID,Region,First_Choice,Second_Choice,Third_Choice
,First_Lang,Second_Lang)
select i.Stu_Code, Stu_Name,Sex,Nationality,Relagion
,Address,ZipCode,PhoneNO,Mobile,email,District,BirthPlace,BirthDate,Certificate_Type
,Certification,Department,Grad_Date,Total,Grade_Total,school_name,Stu_Image
,School_SeatNO,National_ID,Region,First_Choice,Second_Choice,Third_Choice
,First_Lang,Second_Lang from inserted i
join Student_Registry sr on sr.Student_ID= i.Stu_ID
Ben
|
|
|
|
|
i dont get the data from the table i add the trigger for him
i get the data from another table
so i cant use inserted
MD_NADA
|
|
|
|
|
Did you even look at the code I posted? I use inserted to join to the table that has the data you need. Your code used inserted to get the value and then use that variable to select the data. Take a look at the code I posted again.
Ben
|
|
|
|
|
hi
please change the insert statement as follows....
insert into Student_Info (Stu_Code,Stu_Name,Sex,Nationality,Relagion,Address
,ZipCode,Phone,Mobile,Email,District,BirthPlace,BirthDate,Certificate_Type
,Certification,Department,Grad_Date,Total,Grade_Total,School_name,Stu_Image
,School_SeatNO,National_ID,Region,First_Choice,Second_Choice,Third_Choice
,First_Lang,Second_Lang)
select @Stu_Code,Stu_Name,Sex,Nationality,Relagion
,Address,ZipCode,PhoneNO,Mobile,email,District,BirthPlace,BirthDate,Certificate_Type
,Certification,Department,Grad_Date,Total,Grade_Total,school_name,Stu_Image
,School_SeatNO,National_ID,Region,First_Choice,Second_Choice,Third_Choice
,First_Lang,Second_Lang from Student_Registry where Student_ID=@Stu_ID
|
|
|
|
|
hai
if anyone known the difference between the delete and truncate
and please give me an example
thanks in advance
Ganesh Mohan
|
|
|
|
|
Delete and Truncate performs Identical tasks. but there are differences...
Delete is a DML command used to delete a particular record or some set of Records based on the WHERE-Clause. And It can also Delete all the Records when there is no WHERE-Clause specified. The Delete operation performed using Delete Command can be Rolled Back.
Truncate is a DDL Command used to delete all the Reocrds from the Table and also it Reinitializes the Table. Primary Key Identity values will be regenerated from the initial value for the subsequent entries after deleting all reocrds. WHERE-Clause can't be used in Truncate Command. The Delete operation performed using Truncate Command can't be Rolled Back.
Truncate is much faster than Delete
Note : Truncate = Delete + Commit
Regards
J O H N ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
"Even eagles need a push." David McNally
|
|
|
|
|
Good answer. Get's a 5 for its clarity and completeness.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks Pete.
Regards
J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
You're welcome. It's always a pleasure to read a detailed and accurate answer.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I think one other difference is that Delete is logged and truncate is not. Thus the ability to roll back etc. That is also part of the reason that truncate is faster.
Ben
|
|
|
|
|
Ok so I guess part of it is logged. I had to look it up.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
Ben
|
|
|
|
|
|
One other difference is that the user doing the TRUNCATE must have greater permissions than doing a DELETE. DELETE permissions can be granted to a user or role on a per-table basis; on SQL Server 2000, TRUNCATE permissions are only granted to the owner of the table, members of the sysadmin server role, and members of the db_owner or db_ddladmin fixed server roles.
SQL Server 2005's documentation is a little unclear, it says that the user must have ALTER permissions on the table but then repeats the sentence from SQL Server 2000 Books Online.
Obviously if you're connecting to SQL Server using sa or a trusted connection (Windows authentication) using an administrator account, you have those permissions. However, this is not a good practice as those accounts have full control over the entire database server, and as such, a mistake in your application logic could lead to accidental, or even deliberate, damage. In my view, you should firewall what an application can do by setting up less-privileged server logins and database users.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Delete - delete deletes the records from table it can be rollbacked also you can give the where condiition to it.
Truncate - delete all records from table There is no rollback it always commit without givening the commit.
SSK.
Anyone who says sunshine brings happiness has never danced in the rain.
|
|
|
|
|
Hi,
In Our Organisation we have a CRm which is used by 100 pcs at a time, we are regularly facing the problem of connection timeout bcoz of SQL. Now we plan to increase the seats to 200...
Is there any configuration setting that we need to look in to SQL Server 2000.
Plz suggest....
Regards,
Bala
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
