jsp with mssql server2000
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   gimhan_priyantha
Posted On:   Saturday, January 17, 2004 09:34 PM

hello i created a database using MSSQL SERVER 2000, so need to connect this as a web application. here i attched my database( as a A SQL script) Database has 21 tables. so am i need at least 21 forms to insert data? how can i avoid entering Primary key values from User? i need automatically way to enter the pk values and their related foreign values . so i need help to create this web application here i attched my data base script. regards gimhan CREATE TABLE [dbo].[Events] ( [company] [smallint] NOT NULL , [eventID] [int] NOT NULL , [peopleID] [int] NOT NULL , [eventLinkType] [smallint] NOT NULL , [eventLinkID] [int] NOT NULL   More>>

hello
i created a database using MSSQL SERVER 2000, so need to connect this as a web application. here i attched my database( as a A SQL script)
Database has 21 tables. so am i need at least 21 forms to insert data? how can i avoid entering Primary key values from User? i need automatically way to enter the pk values and their related foreign values .

so i need help to create this web application
here i attched my data base script.


regards
gimhan





CREATE TABLE [dbo].[Events] (
[company] [smallint] NOT NULL ,
[eventID] [int] NOT NULL ,
[peopleID] [int] NOT NULL ,
[eventLinkType] [smallint] NOT NULL ,
[eventLinkID] [int] NOT NULL ,
[eventType] [smallint] NOT NULL ,
[eventDate] [datetime] NOT NULL ,
[timeStart] [datetime] NULL ,
[timeEnd] [datetime] NULL ,
[eventText] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[enteredBy] [int] NOT NULL ,
[dateIn] [datetime] NOT NULL ,
[dateChange] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Leads] (
[company] [smallint] NOT NULL ,
[locationID] [int] NOT NULL ,
[leadID] [int] NOT NULL ,
[employeeID] [int] NOT NULL ,
[orgID] [int] NOT NULL ,
[locationCode] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[peopleID] [int] NOT NULL ,
[salesCode] [smallint] NOT NULL ,
[leadDesc] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dateCloses] [datetime] NOT NULL ,
[projectedValue] [int] NOT NULL ,
[winPct] [smallint] NOT NULL ,
[leadStatus] [smallint] NOT NULL ,
[leadNotes] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dateIn] [datetime] NOT NULL ,
[dateChange] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[LeadsNotes] (
[leadID] [int] NOT NULL ,
[enteredBy] [int] NOT NULL ,
[rowText] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dateIn] [datetime] NOT NULL ,
[dateChange] [datetime] NOT NULL ,
[leadNotesID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Org] (
[company] [smallint] NOT NULL ,
[orgID] [int] NOT NULL ,
[orgName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[parentOrg] [int] NOT NULL ,
[orgScope] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[orgType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[businessCode] [smallint] NOT NULL ,
[webAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[orgDesc] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[peopleLink] [int] NOT NULL ,
[dateIn] [datetime] NOT NULL ,
[dateChange] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrgCustomers] (
[company] [smallint] NOT NULL ,
[locationID] [int] NOT NULL ,
[orgID] [int] NOT NULL ,
[shipLocationCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billLocationCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[eMailInvoice] [smallint] NOT NULL ,
[shipCode] [smallint] NOT NULL ,
[termsCode] [smallint] NOT NULL ,
[creditLimit] [int] NOT NULL ,
[currentBalance] [money] NOT NULL ,
[salesTaxStatus] [smallint] NOT NULL ,
[salesTaxDocID] [int] NOT NULL ,
[TIN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateLastInvoice] [datetime] NULL ,
[dateLastPayment] [datetime] NULL ,
[clientSA] [int] NOT NULL ,
[orderDiscount] [float] NOT NULL ,
[orderMaxDollar] [int] NOT NULL ,
[staffOrderMax] [int] NOT NULL ,
[orderMaxQty] [int] NOT NULL ,
[locationOrderMax] [int] NOT NULL ,
[deptOrderMax] [int] NOT NULL ,
[rowStatus] [smallint] NOT NULL ,
[dataIn] [datetime] NOT NULL ,
[dateChange] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrgData] (
[company] [smallint] NOT NULL ,
[ordID] [int] NOT NULL ,
[datacode] [int] NOT NULL ,
[codeText] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[codeNum] [float] NULL ,
[codeDate] [datetime] NULL ,
[dateIn] [datetime] NOT NULL ,
[dateChange] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrgDataCode] (
[company] [smallint] NOT NULL ,
[dataCode] [int] NOT NULL ,
[dataCodeName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[codeType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ValidateType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[codeValidate] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[codeComments] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dateIn] [datetime] NOT NULL ,
[dateChange] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrgInfo] (
[company] [smallint] NOT NULL ,
[orgID] [int] NOT NULL ,
[infoTypeID] [smallint] NOT NULL ,
[infoText] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dateIn] [datetime] NOT NULL ,
[datecHange] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrgLocations] (
[company] [smallint] NOT NULL ,
[orgID] [int] NOT NULL ,
[locationCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[locationName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[state] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[zipCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[countryName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[mailAddress1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mailAddress2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mailZipCode] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[locationVoice] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[locationFax] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[locationEmail] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateIn] [datetime] NOT NULL ,
[dateChange] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrgPeopleNote] (
[company] [smallint] NOT NULL ,
[orgID] [int] NOT NULL ,
[peopleID] [int] NULL ,
[noteType] [smallint] NOT NULL ,
[rowText] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[peopleLink] [int] NOT NULL ,
[dateIn] [datetime] NOT NULL ,
[dateChange] [datetime] NOT NULL ,
[orgPeopleNoteID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrgVendors] (
[company] [smallint] NOT NULL ,
[locationID] [int] NOT NULL ,
[orgID] [int] NOT NULL ,
[vendorType] [smallint] NOT NULL ,
[orgPayTo] [int] NOT NULL ,
[termsCode] [smallint] NOT NULL ,
[TIN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[accountNum] [int] NOT NULL ,
[creditLimit] [int] NOT NULL ,
[currentBalance] [money] NOT NULL ,
[dateLastInvoice] [datetime] NULL ,
[dateLastPayment] [datetime] NULL ,
[dateIn] [datetime] NOT NULL ,
[dateChange] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[People] (
[company] [smallint] NOT NULL ,
[peopleID] [int] NOT NULL ,
[salutation] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[firstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[middleName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lastName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[suffix] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fullName] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[positionType] [smallint] NOT NULL ,
[orgID] [int] NULL ,
[locationCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uerLogin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PIN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[accessLevel] [smallint] NULL ,
[gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[birthDay] [datetime] NULL ,
[userHelp] [smallint] NOT NULL ,
[userStatus] [smallint] NOT NULL ,
[profileChange] [smallint] NOT NULL ,
[peopleLink] [int] NOT NULL ,
[positionName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateIn] [datetime] NOT NULL ,
[dateChange] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PeopleAddress] (
[company] [smallint] NOT NULL ,
[peopleID] [int] NOT NULL ,
[addressType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[state] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[zipCode] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[countryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dateIn] [datetime] NOT NULL ,
[dateChange] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PeopleChanges] (
[company] [smallint] NOT NULL ,
[peopleID] [int] NOT NULL ,
[dataColumn] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[changeViewed] [smallint] NOT NULL ,
[oldData] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[newData] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateIn] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PeopleCodes] (
[company] [smallint] NOT NULL ,
[peopleID] [int] NOT NULL ,
[pCode1] [smallint] NOT NULL ,
[pCode2] [smallint] NOT NULL ,
[pCode3] [smallint] NOT NULL ,
[pCode4] [smallint] NOT NULL ,
[pCode5] [smallint] NOT NULL ,
[pCode6] [smallint] NOT NULL ,
[pCode7] [smallint] NOT NULL ,
[pCode8] [smallint] NOT NULL ,
[pCode9] [smallint] NULL ,
[pCode10] [smallint] NOT NULL ,
[dateIn] [datetime] NOT NULL ,
[dateChange] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PeopleCodesName] (
[company] [smallint] NOT NULL ,
[pCodeName1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[pCodeName2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[pCodeName3] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[pCodeName4] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[pCodeName5] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[pCodeName6] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[pCodeName7] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[pCodeName8] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[pCodeName9] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[pCodeName10] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dateIn] [datetime] NOT NULL ,
[dateChange] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PeopleData] (
[company] [smallint] NOT NULL ,
[peopleID] [int] NOT NULL ,
[dateCode] [int] NOT NULL ,
[codeText] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[codeNum] [float] NULL ,
[codeDate] [datetime] NULL ,
[dateIn] [datetime] NOT NULL ,
[dateChange] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PeopleDataCode] (
[company] [smallint] NOT NULL ,
[dataCode] [int] NOT NULL ,
[dataCodeName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[codeType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[validateType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[codeValidate] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[codeComments] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dateIn] [datetime] NOT NULL ,
[dateChnage] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PeoplePhone] (
[company] [smallint] NOT NULL ,
[peopleID] [int] NOT NULL ,
[messageCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phoneNumber] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dateIn] [datetime] NOT NULL ,
[dateChange] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Tasks] (
[company] [smallint] NOT NULL ,
[taskID] [int] NOT NULL ,
[taskTypeID] [int] NOT NULL ,
[peopleID] [int] NOT NULL ,
[projectID] [int] NOT NULL ,
[WOID] [int] NOT NULL ,
[assignedTo] [int] NOT NULL ,
[dateDue] [datetime] NOT NULL ,
[dateDone] [datetime] NULL ,
[mailAnnounce] [smallint] NOT NULL ,
[mailReminder] [smallint] NOT NULL ,
[taskPriority] [smallint] NOT NULL ,
[taskStatus] [smallint] NOT NULL ,
[taskText] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateIn] [datetime] NOT NULL ,
[dateChange] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Timesheet] (
[timeSheetID] [int] NOT NULL ,
[company] [smallint] NOT NULL ,
[employeeID] [int] NOT NULL ,
[dateWork] [datetime] NOT NULL ,
[timeStart] [datetime] NOT NULL ,
[timeEnd] [datetime] NOT NULL ,
[activityCode] [smallint] NOT NULL ,
[orgID] [int] NOT NULL ,
[projectID] [int] NOT NULL ,
[DONum] [int] NOT NULL ,
[workOrderID] [int] NOT NULL ,
[timeWorkText] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[totalTime] [float] NOT NULL ,
[laborCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[projectRate] [money] NOT NULL ,
[billType] [smallint] NOT NULL ,
[postStatus] [smallint] NOT NULL ,
[invoiceID] [int] NULL ,
[dateIn] [datetime] NOT NULL ,
[dateChange] [datetime] NOT NULL
) ON [PRIMARY]
GO

   <<Less

Re: jsp with mssql server2000

Posted By:   Norman_Hanson  
Posted On:   Tuesday, January 20, 2004 10:19 AM

MS Sql Server (and Sybase) have what is called an identity column.


use this as you PK of your parent table.


write a stored proc for the insert (or update), this lets you mange the transaction better. It either all commits or all rolls back.

);
INSERT INTO the parent table first, then select the identity column out into a @id numeric. then use this to populate the child tables.

About | Sitemap | Contact