how i can prevent data from saving

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how i can prevent data from saving

Author

Topic

azim
Starting Member

29 Posts

Posted - 2002-02-17 : 06:50:17

i use adodc1
i want code to how i can prevent data from saving

table consistis of 4 feilds
studentId
Degree
subject
year

if the subject and studentid and year are equal existing
record prevent saving
else save data

azim

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-17 : 07:14:40

Hi

You can do this in a few ways.

One, put it all in a stored procedure and perhaps a transaction as well. Test for the ID, if it doesn't match, then do the insert, otherwise don't insert.

Two, use a trigger. A trigger will run when you try to insert data, have it check for a duplicate and rollback the insert if it is there.

Three, use some constraints so that it will throw errors of you try to insert duplicate data.

They are the basic methods, for specific detail look up the words I have put in bold in Books Online.

Hope that helps

Damian

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-17 : 08:51:10

Easiest way is probably to use the constraint (or unique index) on studentID, Subject, Year. This will throw a database error so if this is something that you expect to happen (i.e. the application doesn't try to stop it) you should probably do a check in the stored procedure as well to send back a return code.
You should probably have this constraint even if the code does prevent it.

Put the index fields in the order that will be most useful for queries.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

azim
Starting Member

29 Posts

Posted - 2002-02-19 : 06:12:55

pleas answer
i put this code
but also return error

code:--------------------------------------------------------------------------------
Private Sub cmdSave_Click()
On Error GoTo ERRORMASG
Adodc4.RecordSource = "select * from DEGREES where studentid like '" & Val(Text1.Text) & "' AND SUBJECT LIKE '" & Val(Text5.Text) & "' AND YEAR LIKE '" & Val(Text9.Text) & "'"
Adodc4.Refresh
Dim Rs As ADODB.Recordset
Set Rs = Adodc4.Recordset
Adodc4.Recordset.MoveLast

If Adodc4.Recordset.RecordCount >= 1 Then
MsgBox "this degrees already recorded"
Else
Adodc4.Recordset.UpdateBatch
Adodc4.Refresh
Adodc4.Recordset.AddNew
MsgBox "degrees recorded", vbApplicationModal + vbOKOnly
Text11.Text = ""
End If
Exit Sub
ERRORMASG:
MsgBox "check input data"
End Sub

azim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources