• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

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

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
pranjal_ds
Asked:
pranjal_ds
  • 6
  • 5
1 Solution
 
bull_riderCommented:
Could you provide the column and table details along with some records for me to write out the trigger?
0
 
HwkrangerCommented:
You could just apply this in your DATA ACCESS LAYER.  Do the comparison there, vrs after it's being inserted.
0
 
pranjal_dsAssistant ManagerAuthor Commented:
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
bull_riderCommented:
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
 
pranjal_dsAssistant ManagerAuthor Commented:
Hi bull_rider,

Please guide me on implementing a check constraint.

Regards,
Pranjal
0
 
bull_riderCommented:
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
 
pranjal_dsAssistant ManagerAuthor Commented:
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
 
bull_riderCommented:
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
 
pranjal_dsAssistant ManagerAuthor Commented:
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
 
bull_riderCommented:
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
 
pranjal_dsAssistant ManagerAuthor Commented:
I was able to catch the error in VB.net successfully. This solved my problem.
Thanks a lot......!
0
 
bull_riderCommented:
All the Best :)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now