|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
December 22nd, 2003, 01:59 PM
|
|||
|
|||
|
Help on Delete
hi all
currently i have two table , 1st table ( tblcompetency ) Code( as primary Key) , Name 2nd table qno ( as primary key) , qdescription and Code(as foreign key of tblcompetency) both table are link as one to many relationship ( one competency can have many question) my problem is that how do i delete the entire record whereby when user Key in Code( eg cna-1001) it will delete the Code record in tblcompetency table and delete the record where = code in tblqns table i am puzzled , help ! Cheers ~
|
|
#4
December 23rd, 2003, 10:17 AM
|
||||
|
||||
|
well, you could create a stored procedure (assuming you are using Sql Server), that would take the Code as the input parameter and then run the delete statements against the 2 tables.
Code:
CREATE PROCEDURE stp_RemoveRecordsByCode
@Code varchar(20)
AS
DELETE
FROM tblCompetency
WHERE Code = Cast(@Code As int)
DELETE
FROM Table2
WHERE Code = Cast(@Code As int)
GO
and then just call it from your application passing it the Code Or Just write the code to delete it from both tables Code:
Dim Conn As SqlConnection
Dim Cmd As SqlCommand
Dim strSQL As String
Dim reader As SqlReader
Conn = New SqlConnection("server=servername;User ID=username;PWD=password;database=dbname")
strSQL = "DELETE FROM tblCompetency WHERE Code = " & Code
Cmd = New SqlCommand(strSQL, Conn)
Try
Conn.Open()
reader = Cmd.ExecuteNonQuery()
reader.Close()
strSQL = "DELETE FROM table2 WHERE Code = " & Code
Cmd = New SqlCommand(strSQL, Conn)
reader.ExecuteNonQuery()
reader.Close()
Conn.Close()
Catch ex As Exception
Response.write("Error: " & ex.Message())
End Try
|
|
| Viewing: ASP Free Forums > Database > SQL Development > Help on Delete |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
