Next Chapter 69 SQL Module
PROJECT SOURCE CODE
SPONSORED LINKS
SQL Module
Dim SQLSTATEMENT As String
Dim SQL As String
Dim RstOld As New ADODB.Recordset
Dim Rstnew As New ADODB.Recordset
Public j As Integer
Public Function GETSQL(ByVal i As Integer) As String
Select Case i
Case 0
SQL = " CREATE TABLE [dbo].[ACMAST] (" & _
" [MODIFY] [nvarchar] (1) NULL , " & _
" [ACCODE] [nvarchar] (6) NULL , " & _
" [ACDESC] [nvarchar] (50) NULL , " & _
" [ACALIAS] [nvarchar] (50) NULL , " & _
" [RECTYPE] [nvarchar] (1) NULL , " & _
" [CASH] [nvarchar] (1) NULL , " & _
" [GRCODE] [nvarchar] (6) NULL , " & _
" [ACTYPE] [nvarchar] (1) NULL , " & _
" [LEVEL] [float] NULL , " & _
" [BSHEET] [Bit] NOT NULL " & _
" ) ON [PRIMARY] "
Case 1
SQL = " CREATE TABLE [dbo].[AcTrans] ( " & _
" [Accode] [nvarchar] (6) NULL , " & _
" [CLOSING] [numeric](18, 3) NULL , " & _
" [YOBDR] [numeric](18, 3) NULL , " & _
" [YOBCR] [numeric](18, 3) NULL , " & _
" [Cr_Dr] [nvarchar] (1) NULL , " & _
" [BALANCE] [float] NULL " & _
" ) ON [PRIMARY] "
Case 2
SQL = " CREATE TABLE [dbo].[Addmission] ( " & _
" [refno] [int] NULL , " & _
" [name] [nvarchar] (50) NULL , " & _
" [catname] [nvarchar] (50) NULL , " & _
" [bedno] [int] NULL , " & _
" [doa] [datetime] NULL , " & _
" [dod] [datetime] NULL , " & _
" [status] [nvarchar] (50) NULL , " & _
" [docincharge] [nvarchar] (250) NULL , " & _
" [diagnosis] [nvarchar] (250) NULL , " & _
" [aot] [datetime] NULL , " & _
" [dot] [datetime] NULL " & _
" ) ON [PRIMARY] "
Case 3
SQL = " CREATE TABLE [dbo].[Addmission1] ( " & _
" [refno] [int] NULL , " & _
" [name] [nvarchar] (50) NULL , " & _
" [catname] [nvarchar] (50) NULL , " & _
" [bedno] [int] NULL , " & _
" [doa] [datetime] NULL , " & _
" [dod] [datetime] NULL , " & _
" [status] [nvarchar] (50) NULL , " & _
" [docincharge] [nvarchar] (250) NULL , " & _
" [diagnosis] [nvarchar] (250) NULL , " & _
" [aot] [datetime] NULL , " & _
" [dot] [datetime] NULL " & _
" ) ON [PRIMARY] "
Case 4
SQL = " CREATE TABLE [dbo].[ADDRESS] ( " & _
" [ACCODE] [nvarchar] (6) NULL , " & _
" [CONTACT] [nvarchar] (50) NULL , " & _
" [ADDRESS] [nvarchar] (255) NULL , " & _
" [CSTNO] [nvarchar] (15) NULL , " & _
" [CSTDT] [datetime] NULL , " & _
" [LSTNO] [nvarchar] (15) NULL , " & _
" [LSTDT] [datetime] NULL , " & _
" [PH_O] [nvarchar] (25) NULL , " & _
" [PH_R] [nvarchar] (25) NULL , " & _
" [FAX] [nvarchar] (25) NULL , " & _
" [EMAIL] [nvarchar] (100) NULL " & _
" ) ON [PRIMARY] "
Case 5
SQL = " CREATE TABLE [dbo].[Anaes] ( " & _
" [id] [int] NULL , " & _
" [title] [nvarchar] (50) NULL , " & _
" [fname] [nvarchar] (100) NULL , " & _
" [mname] [nvarchar] (100) NULL , " & _
" [lname] [nvarchar] (100) NULL , " & _
" [iden] [nvarchar] (250) NULL , " & _
" [speciality] [nvarchar] (250) NULL , " & _
" [address] [nvarchar] (400) NULL , " & _
" [city] [nvarchar] (200) NULL , " & _
" [pincode] [nvarchar] (100) NULL , " & _
" [phoneoff] [nvarchar] (100) NULL , " & _
" [phoneres] [nvarchar] (100) NULL , " & _
" [mobile] [nvarchar] (100) NULL , " & _
" [notes] [nvarchar] (400) NULL , " & _
" [hospital] [nvarchar] (400) NULL , " & _
" [AccountNO] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 6
SQL = " CREATE TABLE [dbo].[Apnt] ( " & _
" [refno] [int] NULL , " & _
" [aptno] [int] NULL , " & _
" [name] [nvarchar] (100) NULL , " & _
" [doap] [datetime] NULL , " & _
" [time1] [datetime] NULL , " & _
" [surgeon] [nvarchar] (100) NULL , " & _
" [nameofsur] [nvarchar] (100) NULL , " & _
" [placeofsur] [nvarchar] (100) NULL , " & _
" [anas] [nvarchar] (100) NULL , " & _
" [instopat] [nvarchar] (250) NULL , " & _
" [notes] [nvarchar] (250) NULL " & _
" ) ON [PRIMARY] "
Case 7
SQL = " CREATE TABLE [dbo].[BANKBOOK_TEMP] ( " & _
" [SNO] [int] NULL , " & _
" [VDATE] [datetime] NULL , " & _
" [CRCode] [nvarchar] (6) NULL , " & _
" [CRDESC] [nvarchar] (55) NULL , " & _
" [CrAmt] [float] NULL , " & _
" [CrNarr] [nvarchar] (50) NULL , " & _
" [DRCode] [nvarchar] (6) NULL , " & _
" [DRDESC] [nvarchar] (55) NULL , " & _
" [DrAmt] [float] NULL , " & _
" [DrNarr] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 8
SQL = " CREATE TABLE [dbo].[BedDetail] ( " & _
" [catcode] [int] NULL , " & _
" [bedno] [nvarchar] (50) NULL , " & _
" [detail] [nvarchar] (50) NULL , " & _
" [status] [nvarchar] (50) NULL , " & _
" [refno] [int] NULL , " & _
" [rate] [int] NULL " & _
" ) ON [PRIMARY] "
Case 9
SQL = " CREATE TABLE [dbo].[Bill] ( " & _
" [BillNo] [int] NULL , " & _
" [billdate] [datetime] NULL , " & _
" [SerialNo] [int] NULL , " & _
" [Estimate_Amount] [int] NULL , " & _
" [discount] [int] NULL , " & _
" [Advance] [int] NULL , " & _
" [Total_Due] [int] NULL , " & _
" [Amount_paid] [int] NULL , " & _
" [Balance] [int] NULL , " & _
" [RegistrationNo] [int] NULL , " & _
" [paidby] [nvarchar] (50) NULL , " & _
" [status] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 10
SQL = " CREATE TABLE [dbo].[BillAdd] ( " & _
" [BillNo] [int] NULL , " & _
" [SerialNo] [int] NULL , " & _
" [Discription] [nvarchar] (50) NULL , " & _
" [Amount] [int] NULL , " & _
" [id] [int] NULL " & _
" ) ON [PRIMARY] "
Case 11
SQL = " CREATE TABLE [dbo].[billdetail] ( " & _
" [BillNo] [int] NULL , " & _
" [SerialNo] [int] NULL , " & _
" [Discription] [nvarchar] (50) NULL , " & _
" [Amount] [int] NULL , " & _
" [id] [int] NULL " & _
" ) ON [PRIMARY] "
Case 12
SQL = " CREATE TABLE [dbo].[BillMast] ( " & _
" [BillCode] [int] NULL , " & _
" [Discription] [nvarchar] (50) NULL , " & _
" [Amount] [float] NULL " & _
" ) ON [PRIMARY] "
Case 13
SQL = " CREATE TABLE [dbo].[BSHEET_TEMP] ( " & _
" [SNO] [int] NULL , " & _
" [CRGroupCode] [nvarchar] (6) NULL , " & _
" [CRGroupDESC] [nvarchar] (55) NULL , " & _
" [CRCode] [nvarchar] (6) NULL , " & _
" [CRDESC] [nvarchar] (60) NULL , " & _
" [CrAmt] [float] NULL , " & _
" [DRGroupCode] [nvarchar] (6) NULL , " & _
" [DRGroupDESC] [nvarchar] (55) NULL , " & _
" [DRCode] [nvarchar] (6) NULL , " & _
" [DRDESC] [nvarchar] (60) NULL , " & _
" [DrAmt] [float] NULL " & _
" ) ON [PRIMARY] "
Case 14
SQL = " CREATE TABLE [dbo].[CASHBOOK_TEMP] ( " & _
" [SNO] [int] NULL , " & _
" [VDATE] [datetime] NULL , " & _
" [CRCode] [nvarchar] (6) NULL , " & _
" [CRDESC] [nvarchar] (55) NULL , " & _
" [CrAmt] [float] NULL , " & _
" [CrNarr] [nvarchar] (50) NULL , " & _
" [DRCode] [nvarchar] (6) NULL , " & _
" [DRDESC] [nvarchar] (55) NULL , " & _
" [DrAmt] [float] NULL , " & _
" [DrNarr] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 15
SQL = " CREATE TABLE [dbo].[CompMast] ( " & _
" [compcode] [int] NULL ," & _
" [compname] [nvarchar] (250) NULL " & _
" ) ON [PRIMARY] "
Case 16
SQL = " CREATE TABLE [dbo].[CrPL_TEMP] ( " & _
" [ACCODE] [nvarchar] (6) NULL , " & _
" [ACDESC] [nvarchar] (50) NULL , " & _
" [GRCODE] [nvarchar] (6) NULL , " & _
" [GROUPDESC] [nvarchar] (50) NULL , " & _
" [RECTYPE] [nvarchar] (1) NULL , " & _
" [CASH] [nvarchar] (1) NULL , " & _
" [ACTYPE] [nvarchar] (1) NULL , " & _
" [BALANCE] [float] NULL " & _
" ) ON [PRIMARY] "
Case 17
SQL = " CREATE TABLE [dbo].[DailyMast] ( " & _
" [BillCode] [int] NULL , " & _
" [Discription] [nvarchar] (50) NULL , " & _
" [Amount] [float] NULL " & _
" ) ON [PRIMARY] "
Case 18
SQL = " CREATE TABLE [dbo].[DateWiseInvest] ( " & _
" [Regno] [int] NULL , " & _
" [invest_date] [datetime] NULL , " & _
" [XRAY] [nvarchar] (50) NULL , " & _
" [EDD] [nvarchar] (50) NULL , " & _
" [HIV] [nvarchar] (50) NULL , " & _
" [HBs] [nvarchar] (50) NULL , " & _
" [GPRH] [nvarchar] (50) NULL , " & _
" [VDRL] [nvarchar] (50) NULL , " & _
" [Gravindex_Text] [nvarchar] (50) NULL , " & _
" [PPBS] [nvarchar] (50) NULL , " & _
" [FBS] [nvarchar] (50) NULL , " & _
" [MH] [nvarchar] (50) NULL , " & _
" [HB] [nvarchar] (50) NULL , " & _
" [OBST] [nvarchar] (50) NULL , " & _
" [URINE] [nvarchar] (50) NULL , " & _
" [USG] [nvarchar] (50) NULL , " & _
" [DIABETES] [nvarchar] (50) NULL , " & _
" [HYPER] [nvarchar] (50) NULL , " & _
" [GENERALcase] [nvarchar] (50) NULL , " & _
" [pCASE] [nvarchar] (400) NULL " & _
" ) ON [PRIMARY] "
Case 19
SQL = " CREATE TABLE [dbo].[DAYBOOK_TEMP] ( " & _
" [SNO] [int] NULL , " & _
" [VDATE] [datetime] NULL , " & _
" [CRCode] [nvarchar] (6) NULL , " & _
" [CRDESC] [nvarchar] (55) NULL , " & _
" [CrAmt] [float] NULL , " & _
" [CrNarr] [nvarchar] (50) NULL , " & _
" [DRCode] [nvarchar] (6) NULL , " & _
" [DRDESC] [nvarchar] (55) NULL , " & _
" [DrAmt] [float] NULL , " & _
" [DrNarr] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 20
SQL = " CREATE TABLE [dbo].[Department] ( " & _
" [DCode] [nvarchar] (4) NULL , " & _
" [DepartmentName] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 21
SQL = " CREATE TABLE [dbo].[DiMast] ( " & _
" [dicode] [int] NULL , " & _
" [diname] [nvarchar] (250) NULL " & _
" ) ON [PRIMARY] "
Case 22
SQL = " CREATE TABLE [dbo].[DischargeTicket] ( " & _
" [regno] [int] NULL , " & _
" [name] [nvarchar] (50) NULL , " & _
" [age] [int] NULL , " & _
" [sex] [nvarchar] (50) NULL , " & _
" [dname] [nvarchar] (50) NULL , " & _
" [doa] [datetime] NULL , " & _
" [bno] [int] NULL , " & _
" [bcateg] [nvarchar] (50) NULL , " & _
" [dod] [datetime] NULL , " & _
" [diag] [nvarchar] (50) NULL , " & _
" [invest] [nvarchar] (500) NULL , " & _
" [treat] [nvarchar] (500) NULL , " & _
" [advise] [nvarchar] (400) NULL , " & _
" [toa] [datetime] NULL , " & _
" [tod] [datetime] NULL " & _
" ) ON [PRIMARY] "
Case 23
SQL = " CREATE TABLE [dbo].[DnCnLedger] ( " & _
" [ID] [numeric](18, 0) NULL , " & _
" [VTYPE] [nvarchar] (2) NULL , " & _
" [VNO] [nvarchar] (6) NULL , " & _
" [NARR] [nvarchar] (100) NULL , " & _
" [AMOUNT] [int] NULL " & _
" ) ON [PRIMARY] "
Case 24
SQL = " CREATE TABLE [dbo].[DocMast] ( " & _
" [id] [int] NULL , " & _
" [title] [nvarchar] (50) NULL , " & _
" [fname] [nvarchar] (100) NULL , " & _
" [mname] [nvarchar] (100) NULL , " & _
" [lname] [nvarchar] (100) NULL , " & _
" [iden] [nvarchar] (250) NULL , " & _
" [speciality] [nvarchar] (250) NULL , " & _
" [address] [nvarchar] (400) NULL , " & _
" [city] [nvarchar] (200) NULL , " & _
" [pincode] [nvarchar] (100) NULL , " & _
" [phoneoff] [nvarchar] (100) NULL , " & _
" [phoneres] [nvarchar] (100) NULL , " & _
" [mobile] [nvarchar] (100) NULL , " & _
" [notes] [nvarchar] (400) NULL , " & _
" [hospital] [nvarchar] (400) NULL , " & _
" [AccountNo] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 25
SQL = " CREATE TABLE [dbo].[DocTemp] ( " & _
" [regno] [int] NULL , " & _
" [patname] [nvarchar] (50) NULL , " & _
" [type] [nvarchar] (50) NULL , " & _
" [fees] [int] NULL , " & _
" [vdate] [datetime] NULL , " & _
" [dname] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 26
SQL = " CREATE TABLE [dbo].[DrAnas] ( " & _
" [dateofsur] [datetime] NULL , " & _
" [amount] [int] NULL , " & _
" [optno] [int] NULL , " & _
" [regno] [int] NULL , " & _
" [pname] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 27
SQL = " CREATE TABLE [dbo].[DrPL_TEMP] ( " & _
" [ACCODE] [nvarchar] (6) NULL , " & _
" [ACDESC] [nvarchar] (50) NULL , " & _
" [GRCODE] [nvarchar] (6) NULL , " & _
" [GROUPDESC] [nvarchar] (50) NULL , " & _
" [RECTYPE] [nvarchar] (1) NULL , " & _
" [CASH] [nvarchar] (1) NULL , " & _
" [ACTYPE] [nvarchar] (1) NULL , " & _
" [BALANCE] [float] NULL " & _
" ) ON [PRIMARY] "
Case 28
SQL = " CREATE TABLE [dbo].[FIN_LEDGER_TEMP] ( " & _
" [SNO] [int] NULL , " & _
" [ACCODE] [nvarchar] (6) NULL , " & _
" [ACCONTRA] [nvarchar] (6) NULL , " & _
" [VDATE] [datetime] NULL , " & _
" [DEBIT] [int] NULL , " & _
" [CREDIT] [int] NULL , " & _
" [BALANCE] [nvarchar] (50) NULL , " & _
" [NARR] [nvarchar] (100) NULL " & _
" ) ON [PRIMARY] "
Case 29
SQL = " CREATE TABLE [dbo].[Hospital] ([Registration] [int] NULL ,[registrationfees] [int] NULL , " & _
" [dateOfMarriage] [datetime] NULL ,[registration_date] [datetime] NULL , " & _
" [company] [nvarchar] (50) NULL , " & _
" [Title] [nvarchar] (50) NULL , " & _
" [First_Name] [nvarchar] (50) NULL , " & _
" [Middle_Name] [nvarchar] (50) NULL , " & _
" [Last_Name] [nvarchar] (50) NULL , " & _
" [Date_of_birth] [datetime] NULL ,[Sex] [nvarchar] (50) NULL , " & _
" [Age] [int] NULL ,[Marital_Status] [nvarchar] (50) NULL , " & _
" [Blood_Group] [nvarchar] (50) NULL , " & _
" [Occupation] [nvarchar] (50) NULL , " & _
" [Religion] [nvarchar] (50) NULL , " & _
" [father_name] [nvarchar] (50) NULL , " & _
" [Ref_Doctor_Name] [nvarchar] (50) NULL , " & _
" [Ref_Tel] [nvarchar] (50) NULL , " & _
" [Family_Doctor_Name] [nvarchar] (50) NULL , " & _
" [Fem_Tel] [nvarchar] (50) NULL , " & _
" [Notes] [nvarchar] (50) NULL , " & _
" [Purpose_For_Visit] [nvarchar] (50) NULL , " & _
" [Doctor_Incharge] [nvarchar] (50) NULL , " & _
" [Street] [nvarchar] (50) NULL ,[City] [nvarchar] (50) NULL , " & _
" [State] [nvarchar] (50) NULL ,[Country] [nvarchar] (50) NULL , " & _
" [Zip] [nvarchar] (50) NULL ,[Phone_R] [nvarchar] (50) NULL , " & _
" [Phone_O] [nvarchar] (50) NULL ,[E_mail] [nvarchar] (50) NULL , " & _
" [cStreet] [nvarchar] (50) NULL ,[cCity] [nvarchar] (50) NULL , "
SQL = SQL + " [cState] [nvarchar] (50) NULL, " & _
" [cCountry] [nvarchar] (50) NULL , " & _
" [cZip] [nvarchar] (50) NULL , " & _
" [cPhone_R] [nvarchar] (50) NULL , " & _
" [cPhone_O] [nvarchar] (50) NULL , " & _
" [cE_mail] [nvarchar] (50) NULL , " & _
" [Gp_RH] [nvarchar] (50) NULL , " & _
" [VDRL] [nvarchar] (50) NULL , " & _
" [HIV] [nvarchar] (50) NULL , " & _
" [HBs] [nvarchar] (50) NULL , " & _
" [LMP] [nvarchar] (50) NULL , " & _
" [EDD] [nvarchar] (50) NULL , " & _
" [Gravindex_Text] [nvarchar] (50) NULL , " & _
" [FBS] [nvarchar] (50) NULL , " & _
" [PPBS] [nvarchar] (50) NULL , " & _
" [PERSONAL_HISTORY] [nvarchar] (400) NULL , " & _
" [HB] [nvarchar] (50) NULL , " & _
" [MH] [nvarchar] (50) NULL , " & _
" [URINE_EXAM] [nvarchar] (50) NULL , " & _
" [OBST_HISTORY] [nvarchar] (50) NULL , " & _
" [DIABETES] [nvarchar] (50) NULL , " & _
" [USG] [nvarchar] (50) NULL , " & _
" [GENERAL_EXAM] [nvarchar] (50) NULL , " & _
" [HYPERTENSION] [nvarchar] (50) NULL ,[registration_time] [datetime] NULL, " & _
" [OpdID] [nvarchar] (50) NULL , [AccountNO] [nvarchar] (50) NULL) ON [PRIMARY] "
Case 30
SQL = " CREATE TABLE [dbo].[ITEM] ( " & _
" [ItemCode] [nvarchar] (5) NULL , " & _
" [Item_Desc] [nvarchar] (50) NULL , " & _
" [IGCode] [nvarchar] (4) NULL , " & _
" [ManufCode] [nvarchar] (4) NULL , " & _
" [Packing] [nvarchar] (50) NULL , " & _
" [PharmasistName] [nvarchar] (50) NULL , " & _
" [ReOrderLevel] [int] NULL ,[OverFlowLevel] [int] NULL ,[Sale_Rate] [float] NULL , " & _
" [Purch_Rate] [float] NULL ,[Remark] [int] NULL) ON [PRIMARY] "
Case 31
SQL = " CREATE TABLE [dbo].[ITEMGROUP] ( " & _
" [IGCode] [nvarchar] (4) NULL , " & _
" [IGDesc] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 32
SQL = " CREATE TABLE [dbo].[ITEMSIZE] ( " & _
" [ICODE] [nvarchar] (5) NULL , " & _
" [ISIZE] [real] NULL , " & _
" [STOCK] [numeric](18, 0) NULL , " & _
" [OPPRCHRATE] [float] NULL , " & _
" [PRCHRATE] [float] NULL " & _
" ) ON [PRIMARY] "
Case 33
SQL = " CREATE TABLE [dbo].[Ledger] ( " & _
" [ID] [nvarchar] (6) NOT NULL , " & _
" [VTYPE] [nvarchar] (2) NULL , " & _
" [VNO] [nvarchar] (6) NOT NULL , " & _
" [VDATE] [datetime] NULL , " & _
" [ICODE] [nvarchar] (5) NULL , " & _
" [ISIZE] [real] NULL , " & _
" [QTY] [numeric](18, 0) NULL , " & _
" [Rate] [float] NULL , " & _
" [Free] [int] NULL " & _
" ) ON [PRIMARY] "
Case 34
SQL = " CREATE TABLE [dbo].[MANUFACTURER] ( " & _
" [ManufCode] [nvarchar] (4) NULL , " & _
" [Manuf_Desc] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 35
SQL = " CREATE TABLE [dbo].[opd] ( " & _
" [regno] [int] NULL , " & _
" [dor] [datetime] NULL , " & _
" [regfee] [int] NULL , " & _
" [tim] [datetime] NULL , " & _
" [sex] [nvarchar] (50) NULL , " & _
" [age] [nvarchar] (50) NULL , " & _
" [docincharge] [nvarchar] (100) NULL , " & _
" [address] [nvarchar] (250) NULL , " & _
" [company] [nvarchar] (50) NULL , " & _
" [Title] [nvarchar] (50) NULL , " & _
" [FName] [nvarchar] (50) NULL , " & _
" [MName] [nvarchar] (50) NULL , " & _
" [LName] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 36
SQL = " CREATE TABLE [dbo].[OPDbill] ( " & _
" [BillNo] [int] NULL , " & _
" [billdate] [datetime] NULL , " & _
" [SerialNo] [int] NULL , " & _
" [Estimate_Amount] [int] NULL , " & _
" [discount] [int] NULL , " & _
" [Advance] [int] NULL , " & _
" [Total_Due] [int] NULL , " & _
" [Amount_paid] [int] NULL , " & _
" [Balance] [int] NULL , " & _
" [RegistrationNo] [int] NULL , " & _
" [paidby] [nvarchar] (50) NULL , " & _
" [status] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 37
SQL = " CREATE TABLE [dbo].[OPDBillDetail] ( " & _
" [BillNo] [int] NULL , " & _
" [SerialNo] [int] NULL , " & _
" [Discription] [nvarchar] (50) NULL , " & _
" [Amount] [int] NULL , " & _
" [id] [int] NULL " & _
" ) ON [PRIMARY] "
Case 38
SQL = " CREATE TABLE [dbo].[OPDPres] (" & _
" [RefNo] [int] NULL ," & _
" [cDate] [datetime] NULL ," & _
" [Age] [int] NULL ," & _
" [Sex] [nvarchar] (50) NULL , " & _
" [weight] [nvarchar] (50) NULL , " & _
" [bp] [nvarchar] (50) NULL , " & _
" [dateofmarriage] [datetime] NULL ," & _
" [Name] [nvarchar] (50) NULL , " & _
" [DoctorName] [nvarchar] (50) NULL , " & _
" [DiseaseName] [nvarchar] (50) NULL , " & _
" [Title_Head] [nvarchar] (50) NULL , " & _
" [Prescription] [nvarchar] (400) NULL " & _
" ) ON [PRIMARY] "
Case 39
SQL = " CREATE TABLE [dbo].[OPDUltraSound] ( " & _
" [ultradate] [datetime] NULL , " & _
" [id] [int] NULL , " & _
" [name] [nvarchar] (100) NULL , " & _
" [lmp] [nvarchar] (50) NULL , " & _
" [edd] [nvarchar] (50) NULL , " & _
" [dgaweek] [nvarchar] (50) NULL , " & _
" [dgadays] [nvarchar] (50) NULL , " & _
" [Foetus] [nvarchar] (50) NULL , " & _
" [bpd] [nvarchar] (50) NULL , " & _
" [bpdweek] [nvarchar] (50) NULL , " & _
" [bpddays] [nvarchar] (50) NULL , " & _
" [fl] [nvarchar] (50) NULL , " & _
" [flweek] [nvarchar] (50) NULL , " & _
" [fldays] [nvarchar] (50) NULL , " & _
" [ac] [nvarchar] (50) NULL , " & _
" [acweek] [nvarchar] (50) NULL , " & _
" [acdays] [nvarchar] (50) NULL , " & _
" [fweight] [nvarchar] (50) NULL , " & _
" [ImpressGA] [nvarchar] (50) NULL , " & _
" [liquor] [nvarchar] (50) NULL , " & _
" [ImpressW] [nvarchar] (50) NULL , " & _
" [Placenta] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 40
SQL = " CREATE TABLE [dbo].[OptNote] (" & _
" [optno] [int] NULL ," & _
" [refno] [int] NULL ," & _
" [name] [nvarchar] (250) NULL ," & _
" [surgeon] [nvarchar] (250) NULL ," & _
" [dateofsur] [datetime] NULL ," & _
" [assis1] [nvarchar] (200) NULL ," & _
" [assis2] [nvarchar] (200) NULL ," & _
" [anes] [nvarchar] (100) NULL ," & _
" [sthr] [datetime] NULL ," & _
" [cohr] [datetime] NULL ," & _
" [finding] [nvarchar] (400) NULL ," & _
" [pro] [nvarchar] (400) NULL ," & _
" [outcome] [nvarchar] (400) NULL ," & _
" [dramount] [int] NULL , " & _
" [anamount] [int] NULL" & _
" ) ON [PRIMARY] "
Case 41
SQL = " CREATE TABLE [dbo].[PL_TEMP] ( " & _
" [SNO] [int] NULL , " & _
" [RTYPE] [nvarchar] (1) NULL , " & _
" [CRGroupCode] [nvarchar] (6) NULL , " & _
" [CRGroupDESC] [nvarchar] (55) NULL , " & _
" [CRCode] [nvarchar] (6) NULL , " & _
" [CRDESC] [nvarchar] (50) NULL , " & _
" [CrAmt] [float] NULL , " & _
" [CrLine] [nvarchar] (20) NULL , " & _
" [DRGroupCode] [nvarchar] (6) NULL , " & _
" [DRGroupDESC] [nvarchar] (55) NULL , " & _
" [DRCode] [nvarchar] (6) NULL , " & _
" [DRDESC] [nvarchar] (50) NULL , " & _
" [DrAmt] [float] NULL , " & _
" [DrLine] [nvarchar] (20) NULL " & _
" ) ON [PRIMARY] "
Case 42
SQL = " CREATE TABLE [dbo].[UltraSound1] ( " & _
" [ultradate] [datetime] NULL , " & _
" [id] [int] NULL , " & _
" [name] [nvarchar] (100) NULL , " & _
" [lmp] [nvarchar] (50) NULL , " & _
" [edd] [nvarchar] (50) NULL , " & _
" [dgaweek] [nvarchar] (50) NULL , " & _
" [dgadays] [nvarchar] (50) NULL , " & _
" [Foetus] [nvarchar] (50) NULL , " & _
" [bpd] [nvarchar] (50) NULL , " & _
" [bpdweek] [nvarchar] (50) NULL , " & _
" [bpddays] [nvarchar] (50) NULL , " & _
" [fl] [nvarchar] (50) NULL , " & _
" [flweek] [nvarchar] (50) NULL , " & _
" [fldays] [nvarchar] (50) NULL , " & _
" [ac] [nvarchar] (50) NULL , " & _
" [acweek] [nvarchar] (50) NULL , " & _
" [acdays] [nvarchar] (50) NULL , " & _
" [fweight] [nvarchar] (50) NULL , " & _
" [ImpressGA] [nvarchar] (50) NULL , " & _
" [liquor] [nvarchar] (50) NULL , " & _
" [ImpressW] [nvarchar] (50) NULL , " & _
" [Placenta] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 43
SQL = " CREATE TABLE [dbo].[Prescription] (" & _
" [ID] [int] NULL ," & _
" [RefNo] [int] NULL ," & _
" [cDate] [datetime] NULL ," & _
" [Age] [int] NULL ," & _
" [Sex] [nvarchar] (50) NULL , " & _
" [weight] [nvarchar] (50) NULL , " & _
" [bp] [nvarchar] (50) NULL , " & _
" [dateofmarriage] [datetime] NULL ," & _
" [Name] [nvarchar] (50) NULL , " & _
" [DoctorName] [nvarchar] (50) NULL , " & _
" [DiseaseName] [nvarchar] (50) NULL , " & _
" [Title_Head] [nvarchar] (50) NULL , " & _
" [Prescription] [nvarchar] (400) NULL " & _
" ) ON [PRIMARY] "
Case 44
SQL = " CREATE TABLE [dbo].[Purchase] ( " & _
" [ID] [nvarchar] (6) NOT NULL , " & _
" [VTYPE] [nvarchar] (2) NULL , " & _
" [VNO] [nvarchar] (6) NULL , " & _
" [VDATE] [datetime] NULL , " & _
" [ACCODE] [nvarchar] (6) NULL , " & _
" [ACDESC] [nvarchar] (100) NULL , " & _
" [SUPP_INV_NO] [nvarchar] (10) NULL , " & _
" [SUPP_INV_DATE] [datetime] NULL , " & _
" [PROD_VALUE] [numeric](25, 0) NULL , " & _
" [NET_PAYABLE] [numeric](25, 0) NULL , " & _
" [REMARK] [nvarchar] (100) NULL , " & _
" [CARTAGE] [real] NULL , " & _
" [TAX] [real] NULL " & _
" ) ON [PRIMARY] "
Case 45
SQL = " CREATE TABLE [dbo].[PurchaseDetail] ( " & _
" [VNo] [nvarchar] (50) NULL , " & _
" [VType] [nvarchar] (50) NULL , " & _
" [ProductCode] [nvarchar] (50) NULL , " & _
" [ExpDate] [datetime] NULL , " & _
" [MFGDate] [datetime] NULL , " & _
" [Qty] [int] NULL , " & _
" [BatchNo] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 46
SQL = " CREATE TABLE [dbo].[Remark] ( " & _
" [sno] [int] NULL , " & _
" [remark1] [nvarchar] (250) NULL " & _
" ) ON [PRIMARY] "
Case 47
SQL = " CREATE TABLE [dbo].[Sale] ( " & _
" [ID] [nvarchar] (6) NOT NULL , " & _
" [VTYPE] [nvarchar] (2) NULL , " & _
" [VNO] [nvarchar] (6) NULL , " & _
" [VDATE] [datetime] NULL , " & _
" [ACCODE] [nvarchar] (6) NULL , " & _
" [ACDESC] [nvarchar] (100) NULL , " & _
" [SALESMANCODE] [nvarchar] (6) NULL , " & _
" [PROD_VALUE] [numeric](18, 2) NULL , " & _
" [DISCPER] [numeric](18, 2) NULL , " & _
" [DISCOUNT] [numeric](10, 2) NULL , " & _
" [TAXPER] [int] NULL , " & _
" [TAXAMT] [float] NULL , " & _
" [NET_PAYABLE] [numeric](18, 2) NULL , " & _
" [REMARK] [nvarchar] (100) NULL " & _
" ) ON [PRIMARY] "
Case 48
SQL = " CREATE TABLE [dbo].[Stock_Ledger_Temp] ( " & _
" [ICODE] [nvarchar] (6) NULL , " & _
" [ITEM_DESC] [nvarchar] (100) NULL , " & _
" [VTYPE] [nvarchar] (6) NULL , " & _
" [VNO] [nvarchar] (50) NULL , " & _
" [VDATE] [datetime] NULL , " & _
" [Desc1] [nvarchar] (100) NULL , " & _
" [Desc2] [nvarchar] (100) NULL , " & _
" [OPENING] [float] NULL , " & _
" [Purchase] [float] NULL , " & _
" [PurchaseRtrn] [float] NULL , " & _
" [Sale] [float] NULL , " & _
" [SaleRtrn] [float] NULL , " & _
" [Balance] [float] NULL , " & _
" [RATE] [float] NULL " & _
" ) ON [PRIMARY] "
Case 49
SQL = " CREATE TABLE [dbo].[SUPMAST] ( " & _
" [ID] [int] NULL , " & _
" [COMPNAME] [nvarchar] (250) NULL , " & _
" [NAME] [nvarchar] (250) NULL , " & _
" [STREET1] [nvarchar] (250) NULL , " & _
" [STREET2] [nvarchar] (250) NULL , " & _
" [CITY] [nvarchar] (250) NULL , " & _
" [PINCODE] [nvarchar] (250) NULL , " & _
" [PHONE1] [nvarchar] (250) NULL , " & _
" [PHONE2] [nvarchar] (250) NULL , " & _
" [FAX] [nvarchar] (250) NULL , " & _
" [MOBILE] [nvarchar] (250) NULL , " & _
" [WEB] [nvarchar] (250) NULL , " & _
" [NOTES] [nvarchar] (400) NULL , " & _
" [opbal] [int] NULL , " & _
" [type] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 50
SQL = " CREATE TABLE [dbo].[tblTempStatement] ( " & _
" [ICODE] [nvarchar] (5) NULL , " & _
" [ISIZE] [real] NULL , " & _
" [Item_Desc] [nvarchar] (50) NULL , " & _
" [OpeningQty] [float] NULL , " & _
" [OpeningValue] [float] NULL , " & _
" [PurchaseQty] [float] NULL , " & _
" [PurchaseValue] [float] NULL , " & _
" [SaleQty] [float] NULL , " & _
" [SaleValue] [float] NULL " & _
" ) ON [PRIMARY] "
Case 51
SQL = " CREATE TABLE [dbo].[TempDwc] ( " & _
" [recno] [nvarchar] (50) NULL , " & _
" [recdate] [datetime] NULL , " & _
" [regno] [int] NULL , " & _
" [pname] [nvarchar] (50) NULL , " & _
" [against] [nvarchar] (50) NULL , " & _
" [amt] [int] NULL , " & _
" [mode] [nvarchar] (50) NULL , " & _
" [type] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 52
SQL = " CREATE TABLE [dbo].[TempPatpay] ( " & _
" [regno] [int] NULL , " & _
" [date1] [datetime] NULL , " & _
" [name1] [nvarchar] (50) NULL , " & _
" [txtpaid] [int] NULL , " & _
" [txtest] [int] NULL , " & _
" [txtdis] [int] NULL , " & _
" [txtpay] [int] NULL , " & _
" [sno] [int] NULL " & _
" ) ON [PRIMARY] "
Case 53
SQL = " CREATE TABLE [dbo].[TempregFee] ( " & _
" [date1] [datetime] NULL , " & _
" [regno] [int] NULL , " & _
" [pname] [nvarchar] (50) NULL , " & _
" [amt] [int] NULL , " & _
" [type] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 54
SQL = " CREATE TABLE [dbo].[VISIT] ( " & _
" [RegNo] [int] NULL , " & _
" [sno] [int] NULL , " & _
" [dat] [nvarchar] (50) NULL , " & _
" [Compl] [nvarchar] (150) NULL , " & _
" [Temp] [int] NULL , " & _
" [BP] [int] NULL , " & _
" [bp2] [int] NULL , " & _
" [Weight] [nvarchar] (50) NULL , " & _
" [gencond] [nvarchar] (50) NULL , " & _
" [pulse] [int] NULL , " & _
" [rr] [int] NULL , " & _
" [pa] [nvarchar] (50) NULL , " & _
" [neuropupil] [nvarchar] (50) NULL , " & _
" [neuroplanter] [nvarchar] (50) NULL , " & _
" [vtime] [nvarchar] (50) NULL , " & _
" [neuropupil1] [nvarchar] (50) NULL , " & _
" [neuropupil2] [nvarchar] (50) NULL , " & _
" [neuropupil3] [nvarchar] (50) NULL , " & _
" [neuroplanter1] [nvarchar] (50) NULL " & _
" ) ON [PRIMARY] "
Case 55
SQL = " CREATE TABLE [dbo].[Voucher] ( " & _
" [SNO] [nvarchar] (6) NOT NULL , " & _
" [VTYPE] [nvarchar] (2) NULL , " & _
" [VNO] [nvarchar] (6) NULL , " & _
" [VDATE] [datetime] NULL , " & _
" [ACCODE] [nvarchar] (6) NULL , " & _
" [ACCONTRA] [nvarchar] (6) NULL , " & _
" [DRAMT] [float] NULL , " & _
" [CRAMT] [float] NULL , " & _
" [DRCR] [nvarchar] (1) NULL , " & _
" [NARR] [nvarchar] (300) NULL " & _
" ) ON [PRIMARY] "
Case 56
SQL = " CREATE TABLE [dbo].[Workdone] ( " & _
" [serialno] [int] NULL , " & _
" [regno] [int] NULL , " & _
" [visitdate] [smalldatetime] NULL , " & _
" [patientName] [nvarchar] (50) NULL , " & _
" [AttendingDoc] [nvarchar] (50) NULL , " & _
" [workdone] [nvarchar] (50) NULL , " & _
" [amount] [money] NULL , " & _
" [remark] [nvarchar] (400) NULL " & _
" ) ON [PRIMARY] "
Case 57
SQL = " CREATE TABLE [dbo].[WorkMast] ( " & _
" [workcode] [int] NULL , " & _
" [workname] [nvarchar] (250) NULL " & _
" ) ON [PRIMARY] "
Case 58
SQL = " CREATE VIEW dbo.Accounts " & _
" AS " & _
" SELECT TOP 100 PERCENT A.MODIFY, A.ACCODE, A.ACDESC, A.GRCODE, G.ACDESC AS GROUPDESC, A.ACALIAS, A.RECTYPE, A.CASH, A.ACTYPE, A.[LEVEL],A.BSHEET " & _
" FROM dbo.ACMAST A LEFT OUTER JOIN dbo.ACMAST G ON A.GRCODE = G.ACCODE ORDER BY A.ACCODE "
Case 59
SQL = " CREATE VIEW dbo.CashCollection " & _
" AS " & _
" SELECT dbo.Voucher.SNO AS [Receipt No], dbo.Voucher.VDATE AS [Receipt Date], dbo.Voucher.ACCODE, dbo.Hospital.Registration, dbo.Voucher.ACCONTRA, " & _
" dbo.Voucher.DRAMT, dbo.Voucher.CRAMT, dbo.Voucher.DRCR, dbo.ACMAST.ACDESC AS [Patient Name] " & _
" FROM dbo.Voucher INNER JOIN " & _
" dbo.Hospital ON dbo.Voucher.ACCODE = dbo.Hospital.AccountNO INNER JOIN " & _
" dbo.ACMAST ON dbo.Hospital.AccountNO = dbo.ACMAST.ACCODE " & _
" WHERE (dbo.Voucher.VTYPE = 'RT') "
Case 60
SQL = " CREATE VIEW dbo.DoctorPayment " & _
" AS " & _
" SELECT dbo.Voucher.SNO AS [Payment No], dbo.Voucher.VDATE AS [Payment Date], dbo.Voucher.ACCODE, dbo.Voucher.ACCONTRA, dbo.Voucher.DRAMT, " & _
" dbo.Voucher.CRAMT, dbo.Voucher.DRCR, dbo.ACMAST.ACDESC AS [Doctor Name] " & _
" FROM dbo.Voucher INNER JOIN " & _
" dbo.ACMAST ON dbo.Voucher.ACCODE = dbo.ACMAST.ACCODE " & _
" WHERE (dbo.Voucher.VTYPE = 'PT') "
Case 61
SQL = " CREATE VIEW dbo.ItemDetails " & _
" AS " & _
" SELECT TOP 100 PERCENT I.ItemCode AS ItemCode, I.Item_Desc AS [Item Name], IG.IGCode, IG.IGDesc AS [Group], M.ManufCode, " & _
" M.Manuf_Desc AS Manufacturer, I.PharmasistName AS [Pharmasist Name], I.Packing, I.ReOrderLevel AS [Re Order Level], " & _
" I.OverFlowLevel AS [Over Flow Level], I.Purch_Rate AS [Purchase Rate], I.Sale_Rate AS [Sale Rate], I.Remark AS Description " & _
" FROM dbo.MANUFACTURER M RIGHT OUTER JOIN " & _
" dbo.ITEMGROUP IG RIGHT OUTER JOIN " & _
" dbo.ITEM I ON IG.IGCode = I.IGCode ON M.ManufCode = I.ManufCode " & _
" ORDER BY I.Item_Desc "
Case 62
SQL = " CREATE VIEW dbo.SaleLedger " & _
" AS " & _
" SELECT TOP 100 PERCENT I.Item_Desc AS [Item Name], IG.IGDesc AS [Group], dbo.Ledger.VDATE, dbo.Sale.VTYPE, dbo.Ledger.ISIZE AS Packing, " & _
" dbo.Ledger.QTY AS Quantity, dbo.Sale.ACCODE AS AccountNo " & _
" FROM dbo.ITEMGROUP IG INNER JOIN " & _
" dbo.ITEM I ON IG.IGCode = I.IGCode INNER JOIN " & _
" dbo.Sale INNER JOIN " & _
" dbo.Ledger ON dbo.Sale.VNO = dbo.Ledger.VNO AND dbo.Sale.VTYPE = dbo.Ledger.VTYPE ON I.ItemCode = dbo.Ledger.ICODE " & _
" ORDER BY I.Item_Desc "
Case 63
SQL = " CREATE VIEW dbo.WorkQuery " & _
" AS " & _
" SELECT DISTINCT TOP 100 PERCENT regno, visitdate " & _
" From dbo.Workdone " & _
" ORDER BY regno, visitdate "
End Select
GETSQL = SQL
End Function
Public Sub TransferData(TableName As String, OldConn As ADODB.Connection, NewConn As ADODB.Connection, DataType1 As Boolean)
j = 0
Set RstOld = New ADODB.Recordset
Set Rstnew = New ADODB.Recordset
RstOld.Open TableName, OldConn, adOpenDynamic, adLockOptimistic, adCmdTable
Rstnew.Open TableName, NewConn, adOpenDynamic, adLockOptimistic, adCmdTable
While Not RstOld.EOF
Rstnew.AddNew
For j = 0 To RstOld.Fields.Count - 1
Rstnew.Fields(j) = RstOld.Fields(j)
Next
Rstnew.Update
RstOld.MoveNext
Wend
End Sub
Public Sub GetDatabaseList(ByRef Combo As Object)
Dim gsqlserver As New SQLDMO.SQLServer
gsqlserver.connect ServerName, "Samee", "Samee"
Dim oDB As SQLDMO.Database
Combo.Clear
For Each oDB In gsqlserver.Databases
If oDB.SystemObject = False Then
Combo.AddItem oDB.Name
Combo.ListIndex = 0
End If
Next oDB
End Sub
Public Sub UserID(ByRef Combo As Object)
Dim Previous1 As String
Combo.Text = Previous1
Combo.Clear
Dim rst As New ADODB.Recordset
rst.Open "select Name from sysusers where isLogin=1 And isSQlRole=0 And isSqlUser=1", Conn, adOpenDynamic, adLockBatchOptimistic
While Not rst.EOF
Combo.AddItem rst.Fields("Name")
Combo.ListIndex = 0
rst.MoveNext
Wend
Combo.Text = Previous1
End Sub