Association ternaire + vb
Bonjour
voilà j'ai créée une association ternaire sous SQL server,
LOCATION est l'association de CLIENT, DATES, BIENS
voici le DDL des 4 tables
CLIENT:
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
| USE [BONVALETJ_C35]
GO
/****** Object: Table [dbo].[CLIENT] Script Date: 12/05/2009 11:24:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CLIENT](
[Numero_Client] [int] IDENTITY(1,1) NOT NULL,
[Nom] [varchar](15) NOT NULL,
[Prenom] [varchar](15) NOT NULL,
[Rue_Client] [varchar](15) NOT NULL,
[CP_Client] [varchar](15) NOT NULL,
[Ville_Client] [varchar](50) NOT NULL,
[Tel] [int] NOT NULL,
CONSTRAINT [PK_CLIENT] PRIMARY KEY CLUSTERED
(
[Numero_Client] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO |
DATES:
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| USE [BONVALETJ_C35]
GO
/****** Object: Table [dbo].[DATES] Script Date: 12/05/2009 11:24:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DATES](
[Numero] [int] IDENTITY(1,1) NOT NULL,
[Debut_Location] [date] NOT NULL,
CONSTRAINT [PK__DATES__7E532BC75D60DB10] PRIMARY KEY CLUSTERED
(
[Numero] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO |
BIENS:
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
| USE [BONVALETJ_C35]
GO
/****** Object: Table [dbo].[BIENS] Script Date: 12/05/2009 11:24:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BIENS](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Rue] [varchar](30) NOT NULL,
[CP] [nchar](5) NOT NULL,
[Ville] [nchar](15) NOT NULL,
[Superficie] [float] NOT NULL,
[Prix_Achat] [float] NULL,
[Prix_Location] [float] NULL,
[Orientation] [nchar](1) NOT NULL,
[Nb_Pieces] [nchar](2) NOT NULL,
[Loue] [int] NULL,
CONSTRAINT [PK_BIENS] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO |
LOCATION:
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
| USE [BONVALETJ_C35]
GO
/****** Object: Table [dbo].[LOCATION] Script Date: 12/05/2009 11:25:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LOCATION](
[ID] [int] NOT NULL,
[Numero_Client] [int] NOT NULL,
[Numero_Location] [int] NOT NULL,
[Mode_Paiement] [varchar](2) NOT NULL,
[Date_Fin] [date] NOT NULL,
CONSTRAINT [PK__LOCATION__0241EC7961316BF4] PRIMARY KEY CLUSTERED
(
[Numero_Client] ASC,
[Numero_Location] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[LOCATION] WITH CHECK ADD CONSTRAINT [FK_ID] FOREIGN KEY([ID])
REFERENCES [dbo].[BIENS] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[LOCATION] CHECK CONSTRAINT [FK_ID]
GO
ALTER TABLE [dbo].[LOCATION] WITH CHECK ADD CONSTRAINT [FK_Numero_Client] FOREIGN KEY([Numero_Client])
REFERENCES [dbo].[CLIENT] ([Numero_Client])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[LOCATION] CHECK CONSTRAINT [FK_Numero_Client]
GO
ALTER TABLE [dbo].[LOCATION] WITH CHECK ADD CONSTRAINT [FK_Numero_Location] FOREIGN KEY([Numero_Location])
REFERENCES [dbo].[DATES] ([Numero])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[LOCATION] CHECK CONSTRAINT [FK_Numero_Location]
GO |
j'aimerais savoir si le code est bien fait ou pas
et voici mon enregistrement sous Visual Studio 2008
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
| Me.objDATA = New Connection()
Dim ad As Integer
Me.rqSql = "INSERT INTO [BONVALETJ_C35].[dbo].[CLIENT]" & _
"(Nom,Prenom,Rue_Client,CP_Client,Ville_Client,Tel)" & _
"VALUES(" & _
"'" & Me.Nom.Text & "'" & _
",'" & Me.Prenom.Text & "'" & _
",'" & Me.Rue.Text & "'" & _
",'" & Me.CP.Text & "'" & _
",'" & Me.Ville.Text & "'" & _
",'" & Me.Tel.Text & "');" & _
"; SELECT SCOPE_IDENTITY();"
ad = Me.objDATA.actionRows(Me.rqSql)
Me.rqSql = "SELECT CLIENT.Numero_Client " & _
"FROM CLIENT "
Me.DS = Me.objDATA.getRows(Me.rqSql, "Numero_Client")
Dim enreg As Integer = DS.Tables(0).Rows.Count - 1
If DS.Tables(0).Rows(enreg).Item(0) IsNot DBNull.Value Then
index_numero = DS.Tables(0).Rows(enreg).Item(0)
End If
Dim ed As Integer
Me.rqSql = "INSERT INTO [BONVALETJ_C35].[dbo].[DATES]" & _
"(Debut_Location)" & _
"VALUES(" & _
"'" & Me.DebutLoc.Value & "');" & _
"; SELECT SCOPE_IDENTITY();"
ed = Me.objDATA.actionRows(Me.rqSql)
Me.rqSql = "SELECT DATES.Numero " & _
"FROM DATES "
Me.DS = Me.objDATA.getRows(Me.rqSql, "Numero")
Dim enreg1 As Integer = DS.Tables(0).Rows.Count - 1
If DS.Tables(0).Rows(enreg1).Item(0) IsNot DBNull.Value Then
index_date = DS.Tables(0).Rows(enreg1).Item(0)
End If
Me.rqSql = "SELECT BIENS.ID " & _
"FROM BIENS "
Me.DS = Me.objDATA.getRows(Me.rqSql, "ID")
Dim enreg2 As Integer = DS.Tables(0).Rows.Count - 1
If DS.Tables(0).Rows(enreg2).Item(0) IsNot DBNull.Value Then
id_biens = DS.Tables(0).Rows(enreg2).Item(0)
End If
Dim rd As Integer
Me.rqSql = "INSERT INTO [BONVALETJ_C35].[dbo].[LOCATION]" & _
"(Mode_Paiement,Date_Fin, Numero_Client,Numero_Location,ID)" & _
"VALUES(" & _
"'" & id_biens & "'" & _
",'" & index_numero & "'" & _
",'" & index_date & "'" & _
"'" & Me.Paiement.Text & "'" & _
",'" & Me.FinLoc.Text & "');"
'"; SELECT SCOPE_IDENTITY();"
rd = Me.objDATA.actionRows(Me.rqSql) |
mais le code ne fonctionne pas ...
quelqu'un peu m'aider ? mercii
