?
Solved

How to track a date in SQL Server 2005 & VB.net using a trigger.

Posted on 2009-05-12
12
Medium Priority
?
298 Views
Last Modified: 2013-12-17
Hi All,

I am using VB.net as frontend and SQL Server 2005 as backend for my application. Now I am having a problem that in one of the tables a date "1899-12-30 00:00:00.000" is getting inserted/updated and I am not able to track from where it is getting entered, so to check this I want to write a trigger on that table which will return some value to VB.net application and by checking this value I should be able to pop a message to the user that "Wrong date with year 1899 is getting updated".

Please give me a solution as to how I can do this.

Regards,
Pranjal

0
Comment
Question by:pranjal_ds
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 6

Expert Comment

by:bull_rider
ID: 24365872
Could you provide the column and table details along with some records for me to write out the trigger?
0
 
LVL 9

Expert Comment

by:Hwkranger
ID: 24366047
You could just apply this in your DATA ACCESS LAYER.  Do the comparison there, vrs after it's being inserted.
0
 

Author Comment

by:pranjal_ds
ID: 24366615
Hi bull_rider,

Attached alongwith is the table structure alongwith some data. I want to write the trigger on column IST_DT in table REV_INFO and database name is D2LDBSQL2.

Regards,
Pranjal
1899-Data.xls
Table-Rev-info.txt
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 6

Expert Comment

by:bull_rider
ID: 24367202
You can actually use a check constraint for that column if you want. That would not allow you to insert any value which is not associated with the check constraint. Do you want me to guide on that?
0
 

Author Comment

by:pranjal_ds
ID: 24373851
Hi bull_rider,

Please guide me on implementing a check constraint.

Regards,
Pranjal
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24376187
This is just an example. you can provide your own restrictions and alter the table. Let me know if you want more help.
CREATE TABLE Products_2
(
    ProductID int PRIMARY KEY,
    UnitPrice money CHECK(UnitPrice > 0 AND UnitPrice < 100)
)

Open in new window

0
 

Author Comment

by:pranjal_ds
ID: 24377488
But this is while creating the table, while currently my table is already created and is having thousands of row data in it, so can I use ALTER table here instead of Create table?

Regards,
Pranjal
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24377635
yes why not... but first make sure all the rows are satisfying the condition of the check constraint.

And for adding a constraint issue this command, adding your own conditions:

ALTER TABLE CheckTbl
ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 );

The above is just an example. First validate ur rows and then create the constraint
0
 

Author Comment

by:pranjal_ds
ID: 24395990
Ok ........I was able to define the check constriant on my table and it was successful. But now tell me how will I know in my VB.net application when the constraint is violated?
Will that return any status or error code to my VB.net code by which I will be able to handle it ?

Please advice.
Regards,
Pranjal
0
 
LVL 6

Accepted Solution

by:
bull_rider earned 2000 total points
ID: 24397504
you will get an error like this

Server: Msg 547, Level 16, State 1, Procedure insTest, Line 6
INSERT statement conflicted with COLUMN CHECK constraint 'CK_Test'. The conflict occurred in database 'IAPA', table 'Test', column 'Val'.

You can catch the error number through VB and then proceed.

Let me know if it answered your question.
0
 

Author Closing Comment

by:pranjal_ds
ID: 31580584
I was able to catch the error in VB.net successfully. This solved my problem.
Thanks a lot......!
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24402367
All the Best :)
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many software programs on offer that will claim to magically speed up your computer. The best advice I can give you is to avoid them like the plague, because they will often cause far more problems than they solve. Try some of these "do it…
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Video by: Tony
This video teaches viewers how to export a project from Adobe Premiere Pro and the various file types involved.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question