|
Hello Everybody
I am new in SQL SERVER 2005 and ASP.NET 2.0 , Im doing a Online Library Management Project, I know you are expert and please help me.
On Borrower Table
MemberID, ItemID, DateTaken, Duedate, SubmitDate, Delay
On Penalty Table
MemberID, ItemID, Fine, Paid, DatePaid
(B as Borrower and P as Penalty) and a Member borrow a Item and Borrower below
B.MemberID = SCOM1001
B.ItemID=EBEG100302
B.DateTaken = 14/10/2007
B.DueDate= 16/10/2007
B.Submitdate= NULL
B.Delay=NULL
right now CurrentDate is 18/10/2007 and M.MemberID = SCOM1001 did not return ItemID = EBEG100302 .
Per day penalty is 1.500 and Member already delayed 2 days and Tomorrow it will be 3 days then 4 days then 5 days............
Just think Penalty Table is Empty, If you delay 1 day then Penalty Table will automatic add MemberID, ItemID, Fine
P.MemberID=SCOM1001
P.ItemID = EBEG100302
P.Fine=1.500
P.Paid=NO
P.PaidDate=NULL
Dealy day 2
Penalty Table will automati update P.Fine, Value will be 3.000
Delay day 3, day4....................... UpdateP.Fine
My Question is it possible to write some code inside my SQL SERVER datbase, Which will automatic check the B.DateDue column and (add or update Penaly Table where necessary)
I can write some function inside ASP.NET which will create a report for my needs but It will be taken more time.
I want my DataBase will check column status and put value to other table or same table where necessary.
Please tell me how can I do it? Any instruction will be really helpful.
Thanks
Sarfaraj Ahmed
Sarfarj Ahmed
|
|
|
|
|
Didn't you ask the same question last week? I gave you some advice then.
Use an INSERT...SELECT to select the records from the Borrower table where SubmitDate is greater than DueDate and insert corresponding entries into the Penalty table. Calculate the fine based on the logic I explained in my previous reply.
Paul Marfleet
|
|
|
|
|
Thanks For Your Reply
I followed your instruction last time.
I put that query inside ASP.NET and I have to call every single time for updating information.
I want this code inside my database which will be executed automatically and put that value on the correct column.
Could Please Help me about this.
Thanks
Sarfarj Ahmed
|
|
|
|
|
Post what you have written so far. I'm not going to write your code for you, but I'll look at what you've currently done try and help you improve it.
Paul Marfleet
|
|
|
|
|
hi i m ajay
can any one of u tell me that for javascript problem where i should write my problem...
actually i want to make a fuction that can compare two dates....the date are in mm/dd/yyyy formate..
i want a alert that comes in that condition when second date will less thn first...
and i want this onclick of button
thxxxx
Ajay Rathi
software engineer
NOIDA(UP),INDIA
|
|
|
|
|
Your problem is that you posted your question in the wrong forum.
Paul Marfleet
|
|
|
|
|
hi
how to display files of week in a month based on date.plz send query for displaying the files for a week in a month using sqlserver 2000
send query asap.
regards
venkat
|
|
|
|
|
venky456 wrote: files of week in a month based on dat
Please be clear your query first
best regard
pathan
please don't forget to vote on the post that helped you.
|
|
|
|
|
Hi Guys,
I need your help.
How to use Encryption and Decryption Logic in Sql server or sql 2005.
Function in SQL
Please let me know suggestions
Subash
|
|
|
|
|
Hi All,
Can somebody tell me the easiest way to import data from and Excel sheet to a table in Sql Server 2005 database.
An immediate response would be appreciable.
Thanx in advance.
Mujtaba
"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."
|
|
|
|
|
Get it
SSK.
Anyone who says sunshine brings happiness has never danced in the rain.
|
|
|
|
|
Thnx buddy that was useful.
Mujtaba
"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."
|
|
|
|
|
Hi All,
I have a Student table which has 3 fileds:-
ID Name PartNo
----------------------
1 Smith 3
1 Adam 1
1 Samuel 3
2 Gary 1
2 Johnson 2
3 Immanuel 4
3 Ryan 2
3 Irene 1
3 Pete 5
My problem is i need to group students based on IDs and the Name need to be a concatenation of names based on the PartNo in ascending order. The O/p need to be like:-
ID Name
---------------------------
1 Adam Smith
2 Gary Johnson
3 Irene Ryan Immanuel Pete
I am working with SQL 2000. Hope somebody can help me sort this out.
Thanks In Advance.
Payal
-- modified at 22:57 Wednesday 17th October, 2007
|
|
|
|
|
Any particular reason you didn't go with the more traditional FirstName, MiddleNames, Surname format?
|
|
|
|
|
Hi Colin,
I was being given this table(Built by somebodyelse) to get the specified result.
Thanks.
Payal
|
|
|
|
|
Your database design is really weird. Having separate forename & surname fields would make your query a lot easier.
Paul Marfleet
|
|
|
|
|
I have one solution but dont know the syntax. So telling the logic only.
execute the below tsql stmt
select * from codeproj order by id,partno asc
Result will be like this:
1 Adam 1
1 Samuel 2
1 Smith 3
2 John 1
2 king 2
Now create a cursor, fetch the data and concatinate in a variable also check for the condition ID.
You need to write stored procedure for all these.
|
|
|
|
|
Thanks for the reply.
I will try it out.
|
|
|
|
|
hi all ,
How is it possible to Install SSL for Sql Server in Workgroup environment?
Thanks in advance .
|
|
|
|
|
If you look up SSL in the index in SQL Server Books Online, one of the results is the topic "How to: Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager)" (online here[^]).
If you don't want to use a public certificate (i.e. one that you have to pay a trusted root authority to produce for you), you can set up your own stand-alone root authority to generate certificates. See here[^] for how to install certificate services. In order for the server to use the certificate you generate, and for the client to trust that certificate, the root certificate (just the certificate, not the private key associated with it - you do not distribute this) needs to be installed as a trusted root on both server and client.
You can then generate a Server Authentication certificate by going to the CA's certsrv website, clicking Request a certificate, advanced certificate request, create and submit a request to this CA. Fill in the identifying information, change Type of Certicate Needed to Server Authentication Certificate, check the Mark Private Keys As Exportable box (if you're doing this on a computer other than the target server), check Store certificate in the local computer certificate store if you're doing this on the SQL Server computer itself, [UPDATE: this is wrong, the certificate needs to go into the Personal store for the account that SQL Server is running under - use the Local Computer store only if using LocalSystem, which isn't recommended] and enter the fully-qualified domain name of the SQL Server computer in the Friendly Name field. Then click Submit.
Stand-alone CAs do not automatically approve requests. In the Certification Authority console, go to Pending Requests, right-click the request and select Issue. You can now go back to the website (don't hit Refresh, this generates another request) and click View Status. Click the request, then click Install This Certificate.
Now you can go through SQL Server Configuration Manager as described in the article and select the certificate you just generated.
You should be careful to keep your Certification Authority computer isolated. I have ours as a virtual machine on our network, and I only start it up when needing to create a new certificate.
-- modified at 12:34 Wednesday 17th October, 2007
DoEvents : Generating unexpected recursion since 1991
|
|
|
|
|
Thanks , If I generate the Certificate , Is it possible to deploy it with my .Net app setup project and Import it during setup and set SQL Server certificate to this?How?
Thanks again ,
|
|
|
|
|
Hi,
I need to check if a Value exists in a Table. I want to create 1 SP for al datachecks for different fields and tables.
I created an SP:
CREATE PROCEDURE DBO.SP_CHECK_BASEVALUE
@CodeInGrid Varchar(255),
@FieldInTable Varchar(255),
@Table Varchar(255)
AS
DECLARE @sql nvarchar(400)
SET @sql = 'SELECT COUNT(*) FROM ' + @Table + ' WHERE '+ @FieldInTable + ' = ' + @CodeInGrid
EXEC sp_executesql @sql
GO
Problem is @FieldInTable. Is says as Error that Column 'Column' does not exist.
How to create an SP that sees @FieldInTable as ColummName as stead of a varchar value.
|
|
|
|
|
You are using dynamic SQL where you are concatenating a string together to make a full statement. You have not sanitised the values @Table, @FieldInTable and @CodeInGrid. This means your application is susceptable to a SQL Injection Attack. Please read this[^]
All column and table names are nvarchar(128)
If you must do this then please check that @Table and @FieldInTable (actually, they are called columns in SQL Server) are valid first
This might be a starting point for the checking that the table and column name are valid:
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @Table
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = @fieldInTable AND TABLE_NAME = @Table
Also, table and column names should be wrapped in square brackets in case they contain obscure characters or whitespace.
@CodeInGrid I presume is an integer as you don't wrap it in apostrophes in your dynamic SQL. If so pass it as an integer and NOT as any sort of string type. This can easily be cast to a string for concatenation with the rest of the satement.
|
|
|
|
|
Hi,
MY SP is now:
CREATE PROCEDURE DBO.SP_CHECK_BASEVALUE
@CodeInGrid Varchar(255),
@FieldInTable Varchar(128),
@Table Varchar(128)
AS
IF EXISTS(
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @Table
)
BEGIN
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = @FieldInTable AND TABLE_NAME = @Table)
BEGIN
DECLARE @sql nvarchar(400)
SET @sql = 'SELECT COUNT(*) FROM ' + @Table + ' WHERE '+ @FieldInTable + ' = ' + @CodeInGrid
EXEC sp_executesql @sql
END
END
GO
still the same error. How to convert?
AND @CodeInGrid is a String Value (CountryISOCode, CurrencyISOCODE, etc)
|
|
|
|
|
spooky manus wrote: still the same error. How to convert?
AND @CodeInGrid is a String Value (CountryISOCode, CurrencyISOCODE, etc)
Then it is being interpreted as a column name because you've not enclosed it in apostrophes. Print out @sql and see if it actually makes sense. You'll see that it doesn't because it is missing the apostrophes around @CodeInGrid
And you still haven't sanitised @CodeInGrid. Does @CodeInGrid really need 255 characters for an ISO code of a few characters?
@FieldInTable and @Table are still the wrong type. They should be NVARCHAR(128).
|
|
|
|