SQL Table

[shers]

Hi,

I'm creating an application that has SQL database linked to it. This is my first app that uses SQL. So a bit confused about the table structure. It goes like this.

No. | Name | Phase | Start Date | End Date | Hrs | Completed

But one No.has 7 phases. So how do I go about creating the table? Please help!

Thanks

[Consola]

Hi Shers,

So basically any "No." can have one or more "phase". In this case I would setup two tables with a "one to many" relationship. As follows:

 
CREATE TABLE [Parent]
(
   [No] INT NOT NULL,
   [Name] VARCHAR(50) NOT NULL,
   [StartDate] DATETIME NOT NULL,
   [EndDate] DATETIME NULL,
   [Hrs] INT NULL,
   [Completed] BIT NOT NULL
)
 
CREATE TABLE [Phase]
(
   [ID] INT NOT NULL,
   [ParentID] INT NOT NULL,
   [Phase] INT NOT NULL
)

Once these two tables have been setup you can then return the records by using a query like the one below:

SELECT
   T1.[No],
   T1.[Name],
   T2.[Phase],
   T1.[StartDate],
   T1.[EndDate],
   T1.[Hrs],
   T1.[Completed]
FROM [Parent] T1
INNER JOIN [Phase] T2 ON T1.[No] = T2.[ParentID]

Hope this helps

[shers]

But every phase in one No. will have a Start Date and End Date as well as the Hrs.

[shers]

I forgot to mention that this same tables is there for all departments. So how do I go about it?

[jp140768]

Hi Shers,

As Consola said you want to create a database where information is not being duplicated if possible.

Can you identify the unique characteristics of the data. You said you want to record the following pieces of data:
No. | Name | Phase | Start Date | End Date | Hrs | Completed

So, is No., Name unique? Does Phase contain Start Date, EndDate, Hrs and Completed? This will still be unique, as the start date and end date will probably be different.

If the above is true, we create two tables and give the first table a unique identifier eg NO., we then create the second table and list the Unique identifier from the first table in the second one.

You mentioned that you have departments, so in my scenario you would have a third table called Department.

So it goes something like:
Table Department has DepartmentId, DepartmentName, HeadOfDepartment.

Table Staff has fields: StaffNo, InDepartment, StaffName.

Table Phase has fields Phase No, PhaseStaffNo, StartDate, EndDate, Completed.

The field InDepartment links to DepartmentId, so that we have a staff member in a department. The field PhaseStaffNo, links back to StaffNo, so that we can link a phase back to a specific person.

The tables are created as follows (SQL Server code):

Create Table Department(
    DepartmentId               Int                 Identity(1,1)   Not Null,
    DepartmentName         VarChar(30)                         Not Null,
    HeadOfDepartment      VarChar(30)
	
Constraint Department_PK
		Primary Key NonClustered(DepartmentId)
)

Create Table Staff(
         StaffNo               Int                 Identity(1,1)   Not Null,
         InDepartment     Int                                       Not Null, 
         StaffName           VarChar(25)                        Not Null
Constraint Staff_PK
		Primary Key NonClustered(StaffNo),
Constraint Staff_InDepartment_FK
		Foreign Key(InDepartment)
			References Department(DepartmentId)
				On Update Cascade
				On Delete No Action
)

The table Phase would be created in a similar fashion.


HTH

[shers]

I've done with creating 4 tables. They are
Department Table
Phase Table
Project Table
Project Details Table
Now when the user enters data to the form, the data actually should go to the Project Details Table wherein the first 3 fields in Project Details table are the ID of the other 3 tables. So how do I go about writing the SQL for that?

Cn.Open

For iRow = 1 To 6
    P = (70 / 100) * FG.TextMatrix(iRow, 3)
    T = (30 / 100) * FG.TextMatrix(iRow, 3)
    strSQL = "INSERT INTO [RESOURCE.DBO.PROJECT DETAILS] (DeptID, RFS, PhaseID, StartDate, EndDate, TotalHrs, PHrs, THrs, PhaseComp) " & _
            "SELECT DEPTID FROM DEPARTMENT WHERE DEPTNAME=" & Frame2.Caption & ", '" & txtRFS.Text & "', SELECT PHASEID FROM " & _
            "PHASE WHERE PHASENAME='" & FG.TextMatrix(iRow, 0) & "', '" & FG.TextMatrix(iRow, 1) & "', '" & FG.TextMatrix(iRow, 2) & _
            "', " & FG.TextMatrix(iRow, 3) & ", " & P & ", " & T & ", " & FG.TextMatrix(iRow, 4)
      
    Cn.BeginTrans

    Cn.Execute strSQL
    Cn.CommitTrans
Next iRow

Cn.Close

[shers]

 CREATE PROCEDURE TEST
@DNAME nvarchar(50),
@PNO varchar(10),
@PRNAME varchar(50),
@PHNAME char(10),
@SDATE datetime,
@EDATE datetime,
@TLHRS int,
@PCOMP bit
AS
BEGIN
DECLARE @DID int;
SET @DID = (SELECT DEPTID 
FROM DBO.DEPARTMENT
WHERE (DEPTNAME=@DNAME));

DECLARE @PHID int;
SET @PHID = (SELECT PHASEID 
FROM DBO.PHASE 
WHERE (PHASENAME=@PHNAME));

DECLARE @PRHRS int;
SET @PRHRS = ((70 / 100) * @TLHRS)

DECLARE @TCHRS int;
SET @TCHRS = ((30 / 100) * @TLHRS)

INSERT INTO [DBO.PROJECT DETAILS] (DEPTID, RFS, PHASEID,
STARTDATE, ENDDATE, TOTALHRS, PHRS, THRS, PHASECOMP)
VALUES (@DID, @PNO, @PHID, @SDATE, @EDATE, @TLHRS,
@PRHRS, @TCHRS, @PCOMP);
END

This is stored Procedure I've created with the little knowledge I've gained about SP. Hope this is fine!
Next, how do I call this SP from VB?

[shers]

I don't understand what is going wrong. This is the Stored Procedure.

USE [Resource]
GO
/****** Object: StoredProcedure [dbo].[PDetails] Script Date: 07/05/2007 16:02:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[PDetails]
@DNAME varchar(50),
@PNO varchar(10),
@PRNAME varchar(50),
@PHNAME char(10),
@SDATE datetime,
@EDATE datetime,
@TLHRS int,
@PRHRS int,
@TCHRS int,
@PCOMP int,
@DID int,
@PHID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET @DNAME = (SELECT DEPTNAME FROM DBO.DEPARTMENT 
WHERE DEPTID = @DID)

SET @PHNAME = (SELECT PHASENAME FROM DBO.PHASE 
WHERE PHASEID = @PHID)

INSERT INTO DBO.PROJECTDETAILS (DEPTID, RFS, PHASEID, 
STARTDATE, ENDDATE, TOTALHRS, PHRS, THRS, PHASECOMP) 
VALUES (@DID, @PNO, @PHID, @SDATE, @EDATE, @TLHRS, 
@PRHRS, @TCHRS, @PCOMP) 
END

And this is my VB code.

Private Sub cmdApply_Click()
Dim Cmd As New ADODB.Command
Dim P As Integer
Dim T As Integer
Dim Comp As Integer

P = (70 / 100) * FG.TextMatrix(1, 3)
T = (30 / 100) * FG.TextMatrix(1, 3)

If FG.TextMatrix(1, 4) = "þ" Then
Comp = 1
Else
Comp = 0
End If

Cn.Open

With Cmd
.ActiveConnection = Cn
.CommandText = "dbo.PDetails"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@DNAME", adVarChar, adParamInput, 50, Frame2.Caption)
.Parameters.Append .CreateParameter("@PNO", adVarChar, adParamInput, 10, txtRFS.Text)
.Parameters.Append .CreateParameter("@PRNAME", adVarChar, adParamInput, 50, txtPName.Text)
.Parameters.Append .CreateParameter("@PHNAME", adChar, adParamInput, 10, FG.TextMatrix(1, 0))
.Parameters.Append .CreateParameter("@SDATE", adDate, adParamInput, , FG.TextMatrix(1, 1))
.Parameters.Append .CreateParameter("@EDATE", adDate, adParamInput, , FG.TextMatrix(1, 2))
.Parameters.Append .CreateParameter("@TLHRS", adInteger, adParamInput, , FG.TextMatrix(1, 3))
.Parameters.Append .CreateParameter("@PRHRS", adInteger, adParamInput, , P)
.Parameters.Append .CreateParameter("@TCHRS", adInteger, adParamInput, , T)
.Parameters.Append .CreateParameter("@PCOMP", adInteger, adParamInput, , Comp)
.Execute
End With

Set Cmd = Nothing

Cn.Close

End Sub

I get an error when I execute my code. The error is

"Could not find stored procedure '[dbo.PDetails]'.

Thanks

[shers]

Yaaahoooo! It's working! It was because the Database name was not added when calling the stored procedure in the vb code.

How do I modify the existing stored procedure to add a line to insert data into another table too?

Thanks