I have a database containing several tables with many different fields. I need to create an admin section that lets me search on one field or the combination of several. Does anyone have links to pages that offer a general overview for inhouse database search strategy and admin edits.
Thank you
I have been tasked with designing an automated process to restore production data to our testing environments on an as needed basis. The schedule would revolve around our software testing and deployment schedules. I'm looking for suggestions on best practices for this task in the form of advise / links to references / etc.. Instead of presenting all of my requirements here, I'll spare you that information :). Since part of it also needs to encompass data stored in Oracle (10g). I've done a several Google searches but would like to validate / invalidate my research against the advise of the experts here.
View 17 Replies View RelatedI have a website, where people upload tab delimited text files of their product inventories, which the site parses and inserts into a database table. Here's the catch: Instead of insisting that each user use a standardized format, each user can upload the file in whatever column order they want, they just have to let the site know through a GUI which column is in which order. And, they may upload columns that if not mapped, will be ignored. Right now, I am doing all of this in code and it runs slow, I was thinking of offloading this to either a stored procedure, ssis, or bulk upload. But, with the varying format of the uploaded text file, I am not sure how I could do that. Any suggestions? Thanks!
View 1 Replies View RelatedHello Guys:I have included the schema that i build for sql serve 2005 below, My concern is with creating views and cursors.I open for any suggestions. Please try out the scheme;
/*
Created4/5/2007
Modified4/14/2007
Project
Model
Company
Author
Version
DatabaseMS SQL 2005
*/
use master
if exists (select name from sys.databases where name='clubDataBase')
drop database clubdataBase
go
create database clubDataBase
go
use clubDatabase
Create table [Role]
(
[Role_Id] Char(3) NOT NULL,
[Role_Type] Char(30) NOT NULL,
[Description] Char(30) NOT NULL,
Primary Key ([Role_Id])
)
go
Create table [Employee]
(
[employee_id] Char(20) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[Role_Id] Char(3) NOT NULL,
[Manager_Id] Char(20) NOT NULL,
[Club_Id] Char(5) NOT NULL,
[HireDate] Datetime NOT NULL,
[PayRate] Real NOT NULL,
[Status] Varchar(30) NOT NULL,
[SSN] Char(12) NOT NULL,
[Fname] Char(30) NOT NULL,
[Lname] Char(30) NOT NULL,
[Gender] Char(1) NOT NULL,
[DOB] Datetime NOT NULL,
[LastUpdated] Datetime NOT NULL,
Primary Key ([employee_id])
)
go
Create table [Address]
(
[Add_Id] Char(10) NOT NULL,
[AddressLine1] Varchar(30) NOT NULL,
[AddressLine2] Varchar(30) NULL,
[City] Varchar(20) NOT NULL,
[State] Char(2) NOT NULL,
[ZipCode] Char(10) NOT NULL,
[Region] Char(20) NOT NULL,
Primary Key ([Add_Id])
)
go
Create table [Club]
(
[Club_Id] Char(5) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[RegionId] Char(5) NOT NULL,
[ClubName] Varchar(30) NOT NULL,
[PhoneNumber] Char(13) NOT NULL,
[FaxNumber] Char(13) NULL,
[Email] Varchar(30) NOT NULL,
[WebSite] Varchar(30) NOT NULL,
[Description] Varchar(30) NOT NULL,
Primary Key ([Club_Id])
)
go
Create table [Equip_Inven]
(
[Equip_Inven_Id] Char(5) NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Equip_Id] Char(5) NOT NULL,
[Purchase_Date] Datetime NOT NULL,
[Purchase_Cost] Money NULL,
Primary Key ([Equip_Inven_Id])
)
go
Create table [Equipment]
(
[Equip_Id] Char(5) NOT NULL, UNIQUE ([Equip_Id]),
[Equip_Name] Varchar(30) NOT NULL,
[Equip_Type] Varchar(20) NOT NULL,
[Equip_Desc] Varchar(30) NULL,
Primary Key ([Equip_Id])
)
go
Create table [CustomerClass]
(
[Club_Id] Char(5) NOT NULL,
[Class_Id] Char(5) NOT NULL,
[StartDate] Datetime NOT NULL,
[Member_Id] Char(10) NOT NULL,
[MemClassFees] Money NOT NULL,
[Description] Varchar(30) NULL,
Primary Key ([Club_Id],[Class_Id],[StartDate],[Member_Id])
)
go
Create table [Classes]
(
[Class_Id] Char(5) NOT NULL,
[ClassName] Varchar(30) NOT NULL,
[ClassDescription] Varchar(30) NOT NULL,
Primary Key ([Class_Id])
)
go
Create table [Member]
(
[Member_Id] Char(10) NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Plan_Id] Char(5) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[TotalFees] Money NOT NULL,
[MemberFname] Char(20) NOT NULL,
[MemberLname] Char(20) NOT NULL,
[Phonenumber] Char(12) NOT NULL,
[DOB] Datetime NOT NULL,
[Gender] Char(1) NOT NULL,
[Email] Char(20) NULL,
[LastUpdated] Datetime NOT NULL,
Primary Key ([Member_Id])
)
go
Create table [Health_Info]
(
[Member_Id] Char(10) NOT NULL,
[Doctor_Name] Varchar(20) NOT NULL,
[Allergy] Varchar(30) NULL,
[Diseases] Varchar(30) NULL,
[Emergency_Contact1] Char(20) NOT NULL,
[Emergency_Phone1] Char(12) NOT NULL,
[Contact_Relation1] Char(20) NOT NULL,
[Emergency_Contact2] Char(20) NULL,
[Emergency_Phone2] Char(12) NULL,
[Contact_Relation2] Char(20) NULL,
Primary Key ([Member_Id])
)
go
Create table [ClubClasses]
(
[StartDate] Datetime NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Class_Id] Char(5) NOT NULL,
[employee_id] Char(20) NOT NULL,
[ClassAgeRange] Char(10) NULL,
[ClubClassCost] Money NOT NULL,
[ClubClassSchedule] Varchar(30) NOT NULL,
Primary Key ([StartDate],[Club_Id],[Class_Id])
)
go
Create table [Plans]
(
[Plan_Id] Char(5) NOT NULL,
[SuggestedPrice] Money NOT NULL,
[PlanName] Varchar(30) NOT NULL,
[PlanDuration] Varchar(20) NOT NULL,
[Description] Varchar(30) NOT NULL,
Primary Key ([Plan_Id])
)
go
Create table [ClubPlans]
(
[Club_Id] Char(5) NOT NULL,
[Plan_Id] Char(5) NOT NULL,
[Discount] Numeric(2,2) NULL,
[Description] Varchar(30) NULL,
Primary Key ([Club_Id],[Plan_Id])
)
go
Create table [MemberClubUsage]
(
[StartDate] Datetime NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Member_Id] Char(10) NOT NULL,
[EndDate] Datetime NOT NULL,
Primary Key ([StartDate],[Club_Id],[Member_Id])
)
go
Create table [Facility]
(
[Facility_Id] Char(5) NOT NULL, UNIQUE ([Facility_Id]),
[FacilityName] Varchar(30) NOT NULL,
[FacilityType] Varchar(30) NOT NULL,
[Description] Varchar(30) NULL,
Primary Key ([Facility_Id])
)
go
Create table [FacilityUsage]
(
[StartDate] Char(1) NOT NULL,
[Member_Id] Char(10) NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Facility_Id] Char(5) NOT NULL,
[EndDate] Datetime NULL,
Primary Key ([StartDate],[Member_Id])
)
go
Create table [ClubFacilites]
(
[Club_Id] Char(5) NOT NULL,
[Facility_Id] Char(5) NOT NULL,
[OpenDate] Datetime NOT NULL,
[CloseDate] Datetime NOT NULL,
[Description] Varchar(30) NULL,
Primary Key ([Club_Id],[Facility_Id])
)
go
Create table [Equip_Maintanence]
(
[Equip_Maintanence_Id] Char(5) NOT NULL,
[Equip_Inven_Id] Char(5) NOT NULL,
[Main_Date] Datetime NULL,
[Main_Cost] Money NULL,
[Comment] Varchar(30) NULL,
Primary Key ([Equip_Maintanence_Id])
)
go
Create table [Guest]
(
[GuestId] Char(5) NOT NULL,
[Member_Id] Char(10) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[GuestName] Char(20) NOT NULL,
[GuestDOB] Datetime NOT NULL,
Primary Key ([GuestId])
)
go
Create table [RegionalOffice]
(
[RegionId] Char(5) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[RegionName] Nvarchar(30) NOT NULL,
Primary Key ([RegionId])
)
go
Alter table [Employee] add foreign key([Role_Id]) references [Role] ([Role_Id]) on update no action on delete no action
go
Alter table [Employee] add foreign key([Manager_Id]) references [Employee] ([employee_id]) on update no action on delete no action
go
Alter table [ClubClasses] add foreign key([employee_id]) references [Employee] ([employee_id]) on update no action on delete no action
go
Alter table [Employee] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [Club] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [Member] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [Guest] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [RegionalOffice] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [Equip_Inven] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [Employee] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [ClubClasses] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [ClubPlans] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [MemberClubUsage] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [ClubFacilites] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [Equip_Maintanence] add foreign key([Equip_Inven_Id]) references [Equip_Inven] ([Equip_Inven_Id]) on update no action on delete no action
go
Alter table [Equip_Inven] add foreign key([Equip_Id]) references [Equipment] ([Equip_Id]) on update no action on delete no action
go
Alter table [ClubClasses] add foreign key([Class_Id]) references [Classes] ([Class_Id]) on update no action on delete no action
go
Alter table [CustomerClass] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [MemberClubUsage] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [FacilityUsage] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [Health_Info] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [Guest] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [CustomerClass] add foreign key([StartDate],[Club_Id],[Class_Id]) references [ClubClasses] ([StartDate],[Club_Id],[Class_Id]) on update no action on delete no action
go
Alter table [ClubPlans] add foreign key([Plan_Id]) references [Plans] ([Plan_Id]) on update no action on delete no action
go
Alter table [Member] add foreign key([Club_Id],[Plan_Id]) references [ClubPlans] ([Club_Id],[Plan_Id]) on update no action on delete no action
go
Alter table [ClubFacilites] add foreign key([Facility_Id]) references [Facility] ([Facility_Id]) on update no action on delete no action
go
Alter table [FacilityUsage] add foreign key([Club_Id],[Facility_Id]) references [ClubFacilites] ([Club_Id],[Facility_Id]) on update no action on delete no action
go
Alter table [Club] add foreign key([RegionId]) references [RegionalOffice] ([RegionId]) on update no action on delete no action
go
--create procedures and views
--the procedure to randomly add members visit the club
create procedure getMemberClubUsage
as
declare @memID char(10),
@clubID char(10)
declare @begDate datetime
declare mem_club_cursor cursor
--randamly to add the member to visit clubs
for
select member_ID, c.club_ID
from member, club c
open mem_club_cursor
fetch mem_club_cursor into
@memID, @clubID
while @@fetch_status=0
begin
set @begDate=dateadd(day, rand()*-365, getdate())
insert into memberClubUsage
select @begDate, @clubID, @memID, dateadd(minute, rand()*300, @begDate)
fetch mem_club_cursor into
@memID, @clubID
end
close mem_club_cursor
deallocate mem_club_cursor
go
--the procedure randomly add the member to classes
create procedure getMemberClubClass
as
insert into customerClass (startDate, club_ID, class_ID, Member_ID, memclassFees)
select startDate, c.club_ID, class_ID, Member_ID, 150
from member m, clubClasses c
go
create view MembershipClubRegion_VW
as
select member_ID, memberFName, memberLName, C.Club_ID, C.ClubName, c.RegionID, r.RegionName
from member m join club c on m.club_ID=c.Club_ID
join regionaloffice r on c.regionID=r.regionID
go
create view VistorbyMonthClub_VW
as
select clubName,
datename(month, startdate)+'-'+datename(year, startdate) 'Month',
count(*) TotalVistors
from club c join memberclubusage mc on c.club_ID=mc.club_ID
group by clubName, datename(month, startdate)+'-'+datename(year, startdate)
go
create view classClub_View
as
select c.class_ID,
className,
clubName,
startDate
from classes c join clubClasses cc on c.class_ID=cc.class_ID
join club cl on cc.club_ID=cl.club_ID
go
--add testing data into database
--insert data to table Address
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0001', '100 Washington St', 'Salem', 'NH', '01123', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0002', '180 Porland Ave', 'Providence', 'RI', '03123', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0003', '36 Huntington Ave', 'Boston', 'MA', '02111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0004', '45 Marine St', 'August', 'ME', '31245', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0005', '400 Tel St', 'Vermont', 'VT', '11234', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0006', '100 Abc Ave', 'George Town', 'CT', '1111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('B0001', '180 Matthew Ave', 'Worchest', 'NH', '01145', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('B0002', '1000 Elm St', 'Braintree', 'MA', '02184', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0001', '24 Edwin St', 'Quincy', 'MA', '02171', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0002', '57 Sterling Street', 'Malden', 'MA', '02148', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0003', '26 Royal St', 'Quincy', 'MA', '02171', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0004', '439 Newport Ave', 'Quincy', 'MA', '02171', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0005', '41 Old Brattleboro', 'August', 'ME', '31245', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0006', '110 N Central Ave', 'Montpelier', 'VT', '11234', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0007', '67 Marjoril Rd', 'George Town', 'CT', '1111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0008', '1 Holden Row #2', 'Boston', 'MA', '02111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0009', '67 Marjoril Rd', 'Braintree', 'MA', '02421', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0010', '253 Cambridge St Apt#2', 'Boston', 'MA', '02111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0011', '30 Hill St', 'Randolph', 'MA', '02134', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0012', '87 Ocean St', 'Boston', 'MA', '02111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0013', '7A Park Terrace', 'Boston', 'MA', '02112', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0014', '8 FoxRun #12', 'Boston', 'MA', '02113', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0015', '45 Upland Road', 'Boston', 'MA', '02114', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0016', '378 Medford St', 'Boston', 'MA', '02115', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0017', '46 Wheatland St', 'Boston', 'MA', '02116', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0018', '177 North Street', 'Boston', 'MA', '02117', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0019', '280 Breadway #4', 'Boston', 'MA', '02118', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0020', '106 Chester St', 'Boston', 'MA', '02119', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0021', '45 Upland Rd', 'Boston', 'MA', '02120', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0022', '47 Malvern St', 'Boston', 'MA', '02121', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0023', '49 Dwight St #1', 'Boston', 'MA', '02122', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0024', '97 LAWN STREET', 'Boston', 'MA', '02123', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0025', '418 Revere Beach Pkwy #74', 'Boston', 'MA', '02124', 'NE')
go
--insert data to table RegionalOffice
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0001', 'C0001', 'New Hampshire')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0002', 'C0002', 'Rhode Island')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0003', 'C0003', 'Massachussetts')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0004', 'C0004', 'Marine')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0005', 'C0005', 'Vermont')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0006', 'C0006', 'Connecticut')
go
--insert data to table Club
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0001', 'C0001', 'R0001', 'NH CLub1', '8952645456', 'mail@ClubNH', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0002', 'B0001', 'R0001', 'NH CLub2', '8953566889', 'mail@ClubNH', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0003', 'C0003', 'R0003', 'MA Club1', '6174561787', 'mail@MANH', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0004', 'B0002', 'R0003', 'MA Club2', '6175688989', 'mail@MANH', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0005', 'C0002', 'R0002', 'RI Club1', '4264567898', 'mail@ClubRI', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0006', 'C0004', 'R0004', 'Marine Club 1', '8915678989', 'mail@clubME', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0007', 'C0005', 'R0005', 'Vermont Club1', '7325641234', 'mail@clubVT', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0008', 'C0006', 'R0006', 'Connecticut Club1', '7888989999', 'mail@clubCT', 'http://www.Club1NH.org', 'club')
go
--insert data to table Plans
insert into Plans ( Plan_id, SuggestedPrice, PlanName, PlanDuration, Description) values ('P0001', 156.38, 'Family Plan', '1 year', 'plan')
insert into Plans ( Plan_id, SuggestedPrice, PlanName, PlanDuration, Description) values ('P0002', 90, 'Single Plan', '1 Year', 'plan')
insert into Plans ( Plan_id, SuggestedPrice, PlanName, PlanDuration, Description) values ('P0003', 70, 'Senior Plan', '1 Year', 'plan')
insert into Plans ( Plan_id, SuggestedPrice, PlanName, PlanDuration, Description) values ('P0004', 30, 'promote plan', '1 year', 'plan')
go
--insert data to table ClubPlans
insert into ClubPlans ( Club_id, Plan_id) values ('C0001', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0002', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0003', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0004', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0005', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0006', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0007', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0008', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0001', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0002', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0003', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0004', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0005', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0006', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0007', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0008', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0003', 'P0004')
go
--insert data to table Classes
insert into Classes ( Class_id, className, classDescription) values ('A0001', 'Beginning Yoga', 'desc')
insert into Classes ( Class_id, className, classDescription) values ('A0002', 'Advance Yoga', 'desc')
insert into Classes ( Class_id, className, classDescription) values ('A0003', 'Swimming', 'desc')
insert into Classes ( Class_id, className, classDescription) values ('A0004', 'Dancing', 'desc')
insert into Classes ( Class_id, className, classDescription) values ('A0005', 'Introduction to Tennis', 'desc')
go
--insert data to table Role
insert into Role ( Role_id, Role_Type, Description) values ('R01', 'Teacher', 'Class teacher')
insert into Role ( Role_id, Role_Type, Description) values ('R02', 'Manager', 'club manager')
go
--insert data to table Employee
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0001', 'A0001', 'R01', 'E0001', 'C0001', '1/1/1999', 15, ' ', '123456789', 'John', 'Joyce', 'M', '1/3/1968', '4/18/2007')
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0002', 'A0002', 'R02', 'E0001', 'C0002', '12/1/2004', 25, ' ', '123456789', 'MacDonld', 'Harris', 'M', '10/12/1975', '4/18/2007')
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0003', 'A0003', 'R02', 'E0001', 'C0003', '3/1/2006', 20, ' ', '123456789', 'Joane', 'Li', 'F', '11/5/1960', '4/18/2007')
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0004', 'A0004', 'R02', 'E0001', 'C0004', '5/1/2006', 22, ' ', '123456789', 'Tony', 'Green', 'M', '8/12/1970', '4/18/2007')
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0005', 'A0005', 'R02', 'E0001', 'C0005', '1/1/2007', 20, ' ', '123456789', 'Lisa', 'White', 'F', '11/10/1975', '4/18/2007')
go
--insert data to table ClubClasses
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('1/1/2007', 'C0001', 'A0001', 'E0002', 1500, 'Mon, Fri 7:30PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('2/1/2007', 'C0002', 'A0002', 'E0003', 1500, 'Mon, Fri 7:30PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('3/1/2007', 'C0003', 'A0003', 'E0004', 1500, 'Tue, Th 8:00PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('4/1/2007', 'C0004', 'A0004', 'E0005', 1500, 'Tue, Th 8:00PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('5/1/2007', 'C0005', 'A0005', 'E0002', 1500, 'Wed, Fri 7:00PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('4/5/2007', 'C0006', 'A0004', 'E0004', 1500, 'Sat, 8:00AM')
go
--insert data to table Member
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0001', 'C0001', 'P0001', 'A0006', 153.38, 'John', 'He', '7812671567', '1/15/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0002', 'C0002', 'P0001', 'A0007', 153.38, 'Joane', 'Good', '5678945689', '1/16/1975', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0003', 'C0003', 'P0001', 'A0008', 153.38, 'Sharon', 'Spears', '8123456789', '1/17/1985', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0004', 'C0004', 'P0001', 'A0009', 153.38, 'Sandy', 'Green', '9874567898', '2/18/1965', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0005', 'C0005', 'P0001', 'A0010', 153.38, 'Lisa', 'White', '7185642356', '1/19/1967', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0006', 'C0006', 'P0001', 'A0011', 153.38, 'William', 'Black', '5689741235', '1/20/1973', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0007', 'C0007', 'P0001', 'A0012', 153.38, 'Ollie', 'Green', '7812671567', '1/21/1975', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0008', 'C0008', 'P0001', 'A0013', 153.38, 'Louis', 'Brien', '5678945689', '1/15/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0009', 'C0001', 'P0002', 'A0014', 90, 'Barbara', 'Stone', '8123456789', '1/16/1975', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0010', 'C0002', 'P0002', 'A0015', 90, 'Jenat', 'Jean', '9874567898', '1/17/1985', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0011', 'C0003', 'P0002', 'A0016', 90, 'Orlande', 'Moore', '7185642356', '2/18/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0012', 'C0004', 'P0002', 'A0017', 90, 'Jerry', 'Dee', '5689741235', '1/19/1967', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0013', 'C0005', 'P0002', 'A0018', 90, 'Jim', 'Smith', '7812671567', '1/20/1973', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0014', 'C0006', 'P0002', 'A0019', 90, 'Jeff', 'Smith', '5678945689', '1/21/1975', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0015', 'C0007', 'P0002', 'A0020', 90, 'Michael', 'Sam', '8123456789', '1/15/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0016', 'C0008', 'P0002', 'A0021', 90, 'Michelle', 'Lee', '9874567898', '1/16/1975', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0017', 'C0003', 'P0004', 'A0022', 30, 'Judy', 'Andy', '7185642356', '1/17/1985', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0018', 'C0001', 'P0001', 'A0023', 153.38, 'Pierre', 'Pierre', '5689741235', '2/18/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0019', 'C0002', 'P0001', 'A0024', 153.38, 'Tony', 'Kelliher', '7812671567', '1/19/1967', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0020', 'C0003', 'P0001', 'A0025', 153.38, 'Antonia', 'Sheeran', '5678945689', '1/20/1973', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0021', 'C0004', 'P0001', 'A0007', 153.38, 'Scott', 'Brown', '8123456789', '1/21/1975', 'M', '4/18/2007')
go
exec getMemberClubUsage
exec getMemberClubClass
Set quoted_identifier on
go
Set quoted_identifier off
go
Hi ng,I need some input/suggestions for a very small layout.The situation: Some groupings of thumbnails. For every picture (thumbnail)there is a "big" picture. Thats it basically :) On the front the scenario isthis:A user clicks "Autumn". The user is presented with the "Autumn" thumbnails.If he click a thumbnail, the corresponding big image is displayed. Well, uget the picture :)Hoe is this most efficiently implemented in table-layout? I mean....do Icreate a table called "images" and have a column called "is_thumb"? Or do Ibetter make 2 tables...Or better make a "relation/type" table too?--?Thanks,/Summa
View 4 Replies View RelatedDear all
I was doing research oh how to do some searches on dates that allow a bit of a freedom to the person searching. They should be able to specify the parameters.
For example how would you make a search for all events that happened on a specific day in any year,any month? example : every 18
Or how would you searh for values that happened in a specific month in any year and any day? eg. May
Or how would you search for values that happen in a specific year and any day and month? eg. 2007
Or how would you search for values that happen every 18 May?
I tried few things and can only work out the search for the year bit. The values are saved in the database as DateTime.
Any advice is apreciated.
Sincerely
Hey everybody,
First thank you for all your help thus far. Now I'm stuck again. I've been doing a lot of reading on triggers and logging information into tables but I've been trying to capture how many times someone enters an item into the search box.
So every time somebody types Gumballs into the search box I want to capture it and the name of the person who is currently logged in. Is there away to do this? Maybe this is something that I should be checking in ASP.NET forums?
Thanks in advanced guys!
I have a number of functions that require the input of parameters in order to ultimatly create a report under Reporting Services by making use of a Stored Procedure.
All the functions etc work as does the stored procedure, but it only works if I specify data that I know exists e.g.
DECLARE @return_value int
EXEC @return_value = [dbo].[spWTRalldatareportsummary]
@dt_src_date = N'04/28/2006',
@chr_div = N'NE',
@vch_portfolio_no = 3,
@vch_prop_cat = N'core'
SELECT 'Return Value' = @return_value
GO
How can I set this so that it will wild card the value. For example rather than having to specify
@chr_div = N'NE', I could specify something like
@chr_div = N *, so it would show both NE and SW values in the result set.
Anybody point me in a direction here. I have tried % but that does not seem to work, I get a
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '%'.
Thank in Advance
This is what I have:
<asp:TextBox ID="TextBoxProjectCode" runat="server"></asp:TextBox>
<asp:Button ID="ButtonSearch" runat="server" Text="Search" /> <br>
<asp:TextBox ID="TextBoxProjectDate" runat="server" ></asp:TextBox>
<asp:Button ID="ButtonSearch" runat="server" Text="Search" />
SelectCommand="SELECT CLIENT_NAME, CLIENT_CODE, PROJECT_CODE, PROJECT_NAME, PROJECT_DESCRIPTION, EMPLOYEE_ID, ENTRY_DATE, ENTRY_TIME, ENTRY_DESCRIPTION, COMBINED_CODE, TIME_ENTRY_ID, BILLABLE FROM VIEW_TIME WHERE (EMPLOYEE_ID = @SELECT_EMPLOYEE_ID) ORDER BY ENTRY_DATE DESC" FilterExpression="PROJECT_CODE = '{0}' or ENTRY_DATE = '#{0}#' "
<FilterParameters>
<asp:ControlParameter ControlID="TextBoxProjectCode" Name="PROJECT_CODE" PropertyName="Text" Type="Int32" />
<asp:ControlParameter ControlID="TextBoxProjectDate" Name="ENTRY_DATE" PropertyName="Text" Type="DateTime" /></FilterParameters>
Why do I keep getting errors. What am I missing?
BASIC QUESTION here:
Can you do JOINS with tables in a catalog?
Hi Everyone. I have created a procedure on sql to obtain information about two tables ( bookinfo and author). The procedure is :
CREATE PROCEDURE FindBookByAuthor
@Aname char
AS
select b.libcode, b.title, b.subject, a.authorname, b.instock
from bookinfo b join authorinfo a
on b.authorid = a.authorid
where a.authorname like '%' + @aname + '%'
GO
I have tested this procedure changing the variable @aname for the surname of the author. This works fine. The problem comes when I execute this procedure from web matrix. I use a textbox to obtain the data for the variable @aname. I send the data. What happens is that when I run the web page and look for a book through the author the select statement behaves differently and gives me a different output.
I would be glad to know why this happens
Thanks
Hi, I am looking for an easy and efficiant way to add a boolean search function to an already existing search i have using ASP pages and MSSQL 2000 SPROCS.
Currently I have some code such as:
CREATE PROCEDURE sp_name
@var1 int = NULL,
@var2 varchar(50) = NULL
AS
SELECT blah, blah, blah
FROM sometable INNER JOIN anothertable ON anID = anID
WHERE (table1.column2 = COALESCE(@var1,table1.column2) AND table2.column4 = COALESCE(@var2,table2.column4))
and this works like a charm, if i pass a value into @var1 or @var 2 it gives back results where those crietera match, or if I pass no values in it returns all records.... anyways, you get the idea...
Now I would like to add the ability to introduce a 3rd variable, and have it run a boolean search on a varchar(2500) column. This column is a description field for an image so I would like to be able to search the descriptions by passing in something like 'car OR bike AND travel' and return all the records that have the words 'car' or 'bike' and 'travel' somewhere in the description field.
How can i do this? I am using SQL Server Standard Edition so I don't believe I have full text search functions available, (not sure if that would even help).
Thanks all for your support and guidance!
P.S. i would also like to maintain the functinality so that if all the @var# are blank it still returns all records.
Hi,
I am writing a stored procedure to pull records from a table of personal data related to jobs applied for.
There are 3 tables involved, the jobs, the applicants and the applications.
I need to search on job title, job ref from the jobs table and on forename, surname and applicant ID from the applicants table.
There are some quirks here, if the user enters an applicant ID then we can simply scan the jobs table for that id where it also matches the job title wildcards, so that is quite easy to manage.
My own idea for the more complicated searches was to gather the unique ID's from the jobs table into a var, then do similar with the applicants and then search the applications table where both these ID's matched? I think that wouldn't work so well if using the 'WHERE IN()' clause for the main query?
So what approach would be best here to perform the second part of the SP if the client hasn't passed an ApplicantID?
Obviously the applications table has both JobID and ClientID's to relate back to the applicants table.
So can anyone help here, it seems a fairly complicated statement or set of statements would be required here.
Thanks in advance.
Here is my requirement:
The script needs to enumerate the the databases in a givin instance of sql server. Then it needs to output the account with permissions on that server. Finally it then needs to search stored procedured for reference to specific file and account names loaded from a csv file. The results should be output to a text file or displayed in an exportable window format.
What I don t understand:
What do they mean by: account with permissions on that server, which account re they talking about
and what do they mean by the SP references a specific file and account name? how can an SP reference a file or account name. i have never seen that?
Thanks for help.
For anybody that implemented fuzzy searches in SQL Server or any otherdatabase for that matter. I have implemented edit distance algorithm,specifically q-grams, in SQL Server and wanted to get some opinionsfrom anyone who used a similar algorithm in a DBMS.We are looking to implement fuzzy address search and are employingnumber of different techniques including synonyms for strings like"drive", "dr", "drv", etc... Number expansion, so 6 ave = Six Ave =6th Ave = 6... Now, we also have an implementation of edit distancealgorithm, which would cover misspellings and similar sounding words.One of the challenges is putting various techniques together. Synonymsand number expansion techniques go well together but no edit distance.Also, I am trying to figure out how to leverage edit distanceimplementation for 'contains' searches. So if a string like'Mississippi Municipal Building' would be returned if the Search is for'Misisipi Municipal'.Any thoughts?
View 1 Replies View RelatedHi All,I have the following scenario. I have a table called Invoice. Thishas around 30 columns of which i have to do a retrieval based onfilter conditions on 10 columns. These filters need to be partialsearches i.e. for e.g the Customer name could be 'Arun', 'Parthiv','Aaron', now i should be able to search the customer as 'ar' and itshould return 'Arun' and 'Parthiv'. My concern is there are 10 columnson which this like '%x%' search has to be done and there willpractically be hudreds of thousands of rows. can anybody suggest me toimprove the performance of such a query. Currently what i am thinkinof isselect Id, Memo, .. FROM Invoice where CustomerName like '%' + @Name +'%' and etc.P.S. am using ASP.Net as the front end.
View 1 Replies View RelatedHiya,
I have recently become responsible for a small database for a volunteer soccer league. I am reasonably savvy when it comes to development, but I have not had a lot of experience with administration before.
I need to do what I think must be pretty simple: set up full text indexing so I can use a CONTAINS search on a table. The table contains all of the fields the kids use, and each field has a number of divisions that typically play on that field; we use these 'favored divisions' to make scheduling a little easier. Now, one day when I have time, I will set up a proper, normalized, one-to-many relationship between the favored divisions and the playing fields, but right now it's basically like this:
fieldID (int, primary key, identity seed)
fieldName (varchar), e.g. High School Field
favored_divisions (varchar) - comma-delimited list of divisions, e.g. G10,B14,G12
I imagine it's probably database sacrilege to have a comma-delimited list like that, but we don't have the resources now to re-write that piece of the web application. My question is, in SQL Server 2005, what do I need to do to be able to do a full-text search on this field with the following query:
SELECT fieldID, fieldName
FROM playing_fields
WHERE CONTAINS(favored_divisions,'G10')
Right now the query runs and does not return an error, but does not return any results, either. IIRC, full-text indexing is enabled by default in SQL Server 2005, but I am not familiar with the procedure -- something about having to populate a catalog. Do I need to edit or set up a new index on the actual playing_fields table? What has to happen to make this work?
Thanks very much,
Sam
I have a form that loads a dataset. This dataset is composed from SQL statements using alias and unions. Basically it takes uses data from 3 tables. This dataset also has a alias column called ClientName that consists of either people's name or business name.In addition, the form also consist of a search field that allows user to enter the 'ClientName' to be searched (i.e. to search the alias column). So, my question is how can the alias column be searched (user can also enter % in the search field)Function QueryByService(ByVal searchClientNameText As String) As System.Data.DataSet
If InStr(Trim(searchClientNameText), "%")>0 Then searchStatement = "WHERE ClientName LIKE '" & searchClientNameText & "'"Else searchStatement = "WHERE ClientName = @searchClientNameText"End If
Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _"[Event].[Event_Ref]"& _"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _searchStatement + " "& _"UNION SELECT [Bus].[Organisation_Name],"& _"[Event].[NumEvents], [Event].[Event_Ref]"& _"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _searchStatement
..........End Function
i want to be able to do full text searches on a table that i have that already has data in it.. i created a new catalog, then started incremenetal population.. but nothing happened? will it only catalog the new data thats been entered into the table after i start incremental update? so since i have data in teh table already, would i need to do a full population first before i start incremenetal population?
thanks!
Hi Guys,
I have to work with a poorly designed table :(, that has columns
IDINT,
thisIDvarchar(50) null,
parentIDvarchar(50) null,
Titlevarchar(255) null,
Description varchar(8000) null,
ProductTypevarchar(255) null,
The reason it is poorly designed is the table is used to hold questions and answers, all with a 1:1 relationship. Instead of having ID, ProductType, Question, Answer they have unfortunately adopted the approach of the above i.e:
id 1
thisID 3
parentid nuLL
DESCRIPTION: this is a question
id 20
thisID 3_1
parentID 3
DESCRIPTION: this is the answer to the question above
So I am writing a sproc that does this using a temp table. I got this far:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Spencer
-- =============================================
ALTER PROCEDURE [dbo].[GetFAQs]
-- Add the parameters for the stored procedure here
@ProductType varchar(255)
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
CREATE TABLE TEMP
(
IDINT,
thisIDvarchar(50) null,
parentIDvarchar(50) null,
Titlevarchar(255) null,
DescriptionQvarchar(8000) null,
DescriptionAvarchar(8000) null,
ProductTypevarchar(255) null,
)
SELECT
ID,
thisID,
parentID,
Title,
DescriptionQ,
DescriptionA,
ProductType
FROM
A2Z
WHERE
ProductType = @ProductType AND parentID IS NULL
END
GO
This gets all my questions for that product type.
What I need to do is load the questions into my temp table and then run through the a2z table again gaining the answers to the questions (the parentid holds the question ID). The answers then will also get loaded into the temp table.
Any bright sparks out there that can help me?
Cheers
Hi,
For those implementing encrypted columns, what is the recommended approach when allowing users to also do partial searches on encrypted data? (ie email or creditcard info where the tables contain millions of rows). I understand one cannot have the encryption without performance impact, but the searches can be 10 to 20 times as long as when the info is stored in normal char(20) columns. Just looking as a way to try and lessen the impact.
Thanks
Hi all,
In the VB 2005 Express, I can get the SqlConnection and ConnectionString of a Database "shcDB" in the Object Explorer of SQL Server Management Studio Express (SSMSE) by the following set of code:
///--CallshcSpAdoNetVB2005.vb--////
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
.......................................
etc.
///////////////////////////////////////////////////////
If the Database "shcDB" and the Stored Procedure "sp_inertNewRecord" are in the Database Explorer of VB 2005 Express, I plan to use "Data Source=local" in the following code statements to get the SqlConnection and ConnectionString:
.........................
........................
Dim connectionString As String = "Data Source=local;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
........................
etc.
Is the "Data Source=local" statement right for this case? If not, what is the right code statement for my case?
Please help and advise.
Thanks,
Scott Chang
Hi, I want to create a database setup on a server. I've scripted my database from sql server express. I've tested the code in the query window and it worked. When i pasted the same code in a sqlcommand command text....the debuger threw an sql exception....saying the sintax is wrong near keywords GO, USE, some forgein keys, and so on. Here is a chunk of the script.USE [master]GO/****** Object: Database [estate_management] Script Date: 09/13/2006 09:19:32 ******/CREATE DATABASE [estate_management] ON PRIMARY ( NAME = N'estate_management', FILENAME = N'D:MSSQLestate_management.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'estate_management_log', FILENAME = N'D:MSSQLestate_management_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_ASSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[admin]') AND type in (N'U'))BEGINCREATE TABLE [dbo].[admin]( [admin_id] [int] IDENTITY(1,1) NOT NULL, [log_in_id] [varchar](20) NOT NULL, [password] [varchar](50) NOT NULL, CONSTRAINT [PK__admin__07020F21] PRIMARY KEY CLUSTERED ( [admin_id] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 1) ON [PRIMARY], CONSTRAINT [IX_admin] UNIQUE NONCLUSTERED ( [admin_id] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]END Thanks for your time.
View 1 Replies View RelatedI have a database and would like to retrieve specific data via queries. This database is also connected to an ASP .Net 2.0 application to be the front end. Ive created the query in the database. Would you recommend i use parameter names to retrieve the data via code or should i have the query within my code to retrieve the data?
Thanks
VWD 2005 Express. I need to retrieve a value from a SQL database from the code behind a page and assign it to a variable. In Microsoft Access I can do this using the DLookup function. What I need to do is get the data that results from the following query into a variable:
SELECT [SystemUserId] FROM [SystemUser] WHERE ([Username] = @Username)
The name of the data source is SqlDataSource2
Also, in Access I can create a recordset from a query and then process through the recordset. Can that be done in VB code in VWD 2005 Express?
What method would you suggest is the best way to deal with a website?
Say for example, writing SQL. Am I better using Stored Procedures and adding methods for accessing there or just writing the SQL inline. What do you think about this? Is it totally overkill to use stored procedures in a small website?
Hi Friends, Please any one help me in writing the code in thisI have 3 fileds1)empId-->textbox2)Roles-->4 radiobuttons(MN,PL,TL,CL)3)Responsibilities--->3 check boxes(profile,register,change password)I have 3 tablestable1:RoleMasterTable:roleID roleName1 MN2 PL3 TL4 CLtable2:ResponsibilityMasterTableresId resName1 profile2 register3 changepasswordtables3MasterTable:empId roleId resIdthe form conatains the empId,Roles(radiobuttons),Responsibilies(chechboxes)for example I have to enter empId=1select one radiobutton that is "PL"and I can select check boxes profile,register and manyafter submitting the button("submit) these details has to store in the database table i.e master tableI have to show the final o/p like this in the tableMasterTableempId roleId resId1 2 1,2Please any one help in this I am beginner in database c# programmingPlease its very helpful to me............Thanks & RegardsGeeta
View 7 Replies View Relatedi have a piece of code in an adp that is currently behind a form but i could put it in a module and make it public if i need to. how can I use DTS to run this piece of code? which connection and task could i use. can this even be done? i have little experiance with DTS.
thanks.
justin
Where am I going wrong? I have no idea where to start to look for any incorrect code because it all looks ok! Please help!!!
I am entering a record into 5 textboxes - I can see the new record when I open the datagrid view, but when I close the programme and reopen it the record disappears, this code looks ok to me so where is it wrong? Obviously the record is not being written to the database so that when I reopen the programme there is nothing to fill the dataset..........any ideas?
Your help is appreciated!!!
Private Sub BtnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnSave.Click
Mainform.SqlConnection1.Open()
Dim TheNewRow As DataRow
TheNewRow = Mainform.Dataset1.Tables("ChartTable").NewRow
TheNewRow("Chart Number") = TB1CN.Text
TheNewRow("Chart Title") = TB2CT.Text
TheNewRow("Edition Date") = TB3ED.Text
TheNewRow("Print Date") = TB4PD.Text
TheNewRow("Edition Number") = TB5EN.Text
Mainform.Dataset1.Tables("ChartTable").Rows.Add(TheNewRow)
Mainform.SqlDataAdapter1.Update(Mainform.Dataset1)
Mainform.SqlConnection1.Close()
Me.Visible = False
End Sub
Regards,
Steve
I have the need to detach a database from code, at the moment I am using 'SSEUTIL -d' for this in my code and it works OK but now I want to change to using an SQLCommand. Here is my code so far:
Code Snippet
Dim conDatabase As SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=MyMDFPath"; Integrated Security=True;Connect Timeout=60;User Instance=True")
Dim comDetach As New SqlCommand("EXEC master.dbo.sp_detach_db '" + MyMDFPath + "'", conDatabase)
conDatabase.Open()
comDetach.ExecuteNonQuery()
conDatabase.Close()
However, when I execute this I always get the following exception:
Code Snippet
Cannot detach the database 'MyMDFPath' because it is currently in use.
It seems obvious to me that the problem is that to do the detach I create a connection - how do i get around this?
Thanks!
Hello.
I think its silly question but i expect some solution .
Is there any way to create sql sever database by using VB.NET code ? I have to create database,tables,sps and also transfer the data from one database to newly created database.
Please, if somebody have some solution then guide me in detail.
Thanks in adv.
here is my schema...
Board of Zoning Appeals
Parcel#
BZACase#
ApplicantID
OwnerID
DateFiled
Size
Zoning
VU (Variance of Use)
BZACase#
ProposedUse
Comments
VDS (Variance of Developmental Standard)
BZACase#
OrdinanceReq
RequestedDim
ProposedUse
Comments
SE (Special Exception)
BZACase#
CurrentUse
ProposedUse
OrdinanceReq
RequestedDim
Comments
Applicant
ApplicantID
FirstName
LastName
CompanyName
Line1
Line2
City
State
Zip
PhoneNum
Owner
OwnerID
FirstName
LastName
CompanyName
Line1
Line2
City
State
Zip
PhoneNum
Now i know what im doing with the applicantID and ownerID...but the BZAcase# is a number/unique identifier that looks like this....2007-VU-000, 2007-VU-001, 2007-VU-003....so my question is
1. how do i get the last three numbers to increment each time a new application is created?
2. how do i retrieve the last record in the table???
3. Do you have any other suggestions?? i have to have the number and what type of form they applied for in the "case#"???
I am requesting suggestions to solve my problem.
Background: We are changing the way we pay commissions to our rep groups. We used to pay when the order was placed, now we want to pay when the invoice is paid.
Problem: The commision information is currently stored in the customer order, not in the invoice. These orders get deleted a couple weeks after the order was completed (shipped).
I want to create another, rather dynamic, table/structure that will store the order number and the commission percentage.
This info in this table should:
Be deleted: if the order has been deleted and the invoice either does not exist or was payed some period of time ago (maybe 6 months)
Be updated: if the customer order has been updated (i.e. the commission was changed)
Be inserted: if the order exists but the order number is not in the new table.
That is it in a nutshell.
Thanks,
Brian