if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CodeGen_Geographic]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CodeGen_Geographic]
GO
CREATE TABLE [dbo].[CodeGen_Geographic] (
[GeographicCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
INSERT INTO CodeGen_Geographic(GeographicCode)
SELECT 'BranchCode'
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CodeGen_DataTypes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CodeGen_DataTypes]
GO
CREATE TABLE [dbo].[CodeGen_DataTypes] (
[SqlType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VbType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VbTypeDim] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
INSERT INTO CodeGen_DataTypes(SqlType, VbType, VbTypeDim)
SELECT 'bigint','DataTypeEnum.adBigInt','Long' UNION
SELECT 'binary','DataTypeEnum.adBinary','' UNION
SELECT 'bit','DataTypeEnum.adBoolean','Boolean' UNION
SELECT 'char','DataTypeEnum.adChar','String' UNION
SELECT 'datetime','DataTypeEnum.adDBTimeStamp','Date' UNION
SELECT 'decimal','DataTypeEnum.adNumeric','Variant '' Decimal' UNION
SELECT 'float','DataTypeEnum.adDouble','Double' UNION
SELECT 'image','DataTypeEnum.adLongVarBinary','' UNION
SELECT 'int','DataTypeEnum.adInteger','Long' UNION
SELECT 'money','DataTypeEnum.adCurrency','Currency' UNION
SELECT 'nchar','DataTypeEnum.adWChar','String' UNION
SELECT 'ntext','DataTypeEnum.adLongVarWChar','String' UNION
SELECT 'numeric','DataTypeEnum.adNumeric','Variant '' Numeric' UNION
SELECT 'nvarchar','DataTypeEnum.adVarWChar','String' UNION
SELECT 'real','DataTypeEnum.adSingle','Single' UNION
SELECT 'smalldatetime','DataTypeEnum.adDBTimeStamp','Date' UNION
SELECT 'smallint','DataTypeEnum.adUnsignedSmallInt','Integer' UNION
SELECT 'smallmoney','DataTypeEnum.adCurrency','' UNION
SELECT 'sql_variant','','' UNION
SELECT 'sysname','','' UNION
SELECT 'text','DataTypeEnum.adLongVarChar','String' UNION
SELECT 'timestamp','DataTypeEnum.adBinary','' UNION
SELECT 'tinyint','DataTypeEnum.adUnsignedTinyInt','Byte' UNION
SELECT 'uniqueidentifier','DataTypeEnum.adGUID','String' UNION
SELECT 'varbinary','DataTypeEnum.adVarBinary','' UNION
SELECT 'varchar','DataTypeEnum.adVarChar','String'
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CodeGenFe_OneTableNoAutoId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CodeGenFe_OneTableNoAutoId]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CodeGenFe_TwoTableNoAutoId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CodeGenFe_TwoTableNoAutoId]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CodeGenMt_OneTableNoAutoId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CodeGenMt_OneTableNoAutoId]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CodeGenMt_TwoTableNoAutoId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CodeGenMt_TwoTableNoAutoId]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE CodeGenFe_OneTableNoAutoId
@TableTransactName VARCHAR(100),
@TableName VARCHAR(100)
AS
IF object_id(@TableName) IS NULL BEGIN
PRINT 'Table ' + @TableTransactName + ' does not exists'
RETURN
END
DECLARE @Tab VARCHAR(4)
SET @Tab = SPACE(4)
DECLARE @TryBlockBegin VARCHAR(8000)
SET @TryBlockBegin = @Tab + 'On Error GoTo goCatchError'
+ CHAR(13)
+ @Tab + '''Try{' + CHAR(13) + CHAR(13)
DECLARE @TryBlockEnd VARCHAR(8000)
SET @TryBlockEnd = CHAR(13) + @Tab + 'GoTo goFinally' + CHAR(13) + @Tab + '''}' + CHAR(13)
DECLARE @CatchBlockBegin VARCHAR(8000)
SET @CatchBlockBegin= @Tab + '''CatchError{' + CHAR(13) + 'goCatchError:' + CHAR(13) + CHAR(13) + char(13)
+ @Tab + 'Screen.MousePointer = vbDefault'
DECLARE @FinallyBlockBegin VARCHAR(8000)
SET @FinallyBlockBegin = @Tab + '''Finally{' + CHAR(13) + 'goFinally:' + CHAR(13) + CHAR(13)
DECLARE @FinallyBlockEnd VARCHAR(8000)
SET @FinallyBlockEnd = @Tab + '''}'
DECLARE @PkDeclare VARCHAR(8000)
set @PkDeclare = ''
select
@PkDeclare = @PkDeclare + CHAR(13) + @Tab + 'Dim ' + c.Name + ' As String'
+ CHAR(13) + @Tab + C.Name + ' = txt' + C.Name + '.Value'
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
-----------------------------------------------------
print 'Option Explicit'
print 'Option Compare Text'
print 'Option Base 0'
print ''
PRINT '''Passed parameters...'
print 'Public ParentForm As Object'
PRINT '''...passed parameters'
print ''
print 'Private m_IsDataExisting as Boolean'
print ''
PRINT 'Private Sub Form_Load()'
PRINT ''
PRINT @Tab + 'Set Me.Icon = LoadPicture("")'
PRINT ''
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub Form_Activate()'
PRINT ''
PRINT @Tab + 'ParentForm.MessageToUser "' + @TableTransactName + ' ready."'
PRINT ''
PRINT 'End Sub'
PRINT ''
PRINT ''
DECLARE @c CURSOR
DECLARE @C_ReferencedTableName VARCHAR(100)
SET @C = CURSOR FOR
SELECT SC.Name, SC.XType, dt.VbType, ReferencedTableName = rt.name, sc.length
FROM syscolumns AS SC
INNER JOIN systypes AS ST ON sc.xtype = st.xtype
INNER JOIN CodeGen_DataTypes AS dt ON dt.sqltype = st.name
INNER JOIN sysforeignkeys AS sfk ON sfk.fkeyid = sc.id AND sfk.fkey = sc.colid
INNER JOIN sysobjects AS rt ON rt.id = sfk.rkeyid
WHERE sc.id = object_id(@TableName)
AND SC.iscomputed = 0
AND CHARINDEX('_', SC.Name) = 0
ORDER BY SC.colid
DECLARE @S VARCHAR(8000)
DECLARE @PkFieldsMtParameter VARCHAR(8000)
set @PkFieldsMtParameter = NULL
select
@PkFieldsMtParameter = coalesce(
@PkFieldsMtParameter + ', ' + c.Name
, c.Name )
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
DECLARE @PkFieldsOpenParameter VARCHAR(8000)
set @PkFieldsOpenParameter = NULL
select
@PkFieldsOpenParameter = coalesce(
@PkFieldsOpenParameter + ', Rs.Fields("' + c.Name + '").Value'
, 'Rs.Fields("' + c.Name + '").Value')
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
DECLARE @PkFieldsFirstKey VARCHAR(8000)
set @PkFieldsFirstKey = NULL
select
TOP 1 @PkFieldsFirstKey = C.Name
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
declare @PkFieldsFeParameter varchar(8000)
set @PkFieldsFeParameter = NULL
select
@PkFieldsFeParameter = coalesce(
@PkFieldsFeParameter + ', ByVal ' + c.Name + ' As String'
,'ByVal ' + c.Name + ' As String')
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
PRINT '''User initiated events...'
print 'Private Sub cmdDelete_Click()'
PRINT @Tab + 'DoDelete'
PRINT 'End Sub'
PRINT 'Private Sub cmdSave_Click()'
PRINT @Tab + 'DoSave'
PRINT 'End Sub'
PRINT 'Private Sub cmdNew_Click()'
PRINT @Tab + 'DoNew'
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub txt' + @PkFieldsFirstKey + '_DropDown(UseDefault As Boolean, RS As ADODB.Recordset, RecordsetPositioner As String)'
PRINT @TryBlockBegin
PRINT @Tab + 'Dim E As String'
PRINT @Tab + 'Set Rs = Services.' + @TableTransactName + '_Browse(E,"")'
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT dbo.CodeGenFe_CatchBlockEnd('txt' + @PkFieldsFirstKey + '_Dropdown')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub txt' + @PkFieldsFirstKey + '_GotoRecord(RS As ADODB.Recordset)'
PRINT @TryBlockBegin
PRINT @Tab + 'DoOpen ' + @PkFieldsOpenParameter
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT dbo.CodeGenFe_CatchBlockEnd('txt' + @PkFieldsFirstKey + '_GotoRecord')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Sub'
PRINT ''
PRINT '''...User initiated events'
DECLARE @C_Name AS VARCHAR(100)
DECLARE @C_XType AS INT
DECLARE @C_VbType VARCHAR(100)
DECLARE @C_Length INT
---------------------
-- generate lookup...
PRINT ''
PRINT '''Lookups...'
OPEN @C
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_ReferencedTableName, @C_length
WHILE @@FETCH_STATUS = 0 BEGIN
IF EXISTS(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
)
BEGIN
GOTO goContinueLookup
END
SET @S = @C_Name
PRINT ''
PRINT 'Private Sub txt' + @C_Name + '_DropDown(UseDefault As Boolean, RS As ADODB.Recordset, RecordsetPositioner As String)'
PRINT @TryBlockBegin
PRINT @Tab + 'Dim E As String'
PRINT @Tab + 'Set Rs = Services.' + @C_ReferencedTableName + '_Lookup(E,"")'
PRINT @Tab + 'RaiseIfErr E'
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT dbo.CodeGenFe_CatchBlockEnd('txt' + @C_Name + '_DropDown')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub txt' + @C_Name + '_GotoRecord(Rs As ADODB.Recordset)'
PRINT @Tab + 'txt' + @C_Name + '.Value = Rs.Fields("' + @C_Name + '").Value'
PRINT 'End Sub'
PRINT ''
goContinueLookup:
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_ReferencedTableName, @C_length
END
CLOSE @C
PRINT '''...Lookups'
PRINT ''
-- ..generate lookup
---------------------
PRINT ''
PRINT '''Transactions...'
PRINT ''
SET @C = CURSOR FOR
SELECT SC.Name, SC.XType, dt.VbType, sc.length
FROM syscolumns AS SC
INNER JOIN systypes AS ST ON sc.xtype = st.xtype
INNER JOIN CodeGen_DataTypes AS dt ON dt.sqltype = st.name
WHERE sc.id = object_id(@TableName)
AND SC.iscomputed = 0
AND CHARINDEX('_', SC.Name) = 0
ORDER BY SC.colid
PRINT 'Private Sub DoNew()'
OPEN @C
DECLARE @Value AS VARCHAR(100)
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN
IF EXISTS(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
)
BEGIN
PRINT + @Tab + 'txt' + @C_Name + '.IsEditable = True'
END
SET @S = @C_Name
IF @C_XType IN (127,106,62,56,60,108,59,52,122,48) BEGIN
SET @Value = '0'
END
ELSE IF @C_XType = 104 BEGIN
SET @Value = 'False'
END
ELSE BEGIN
SET @Value = '""'
END
PRINT @Tab + 'txt' + @S + '.Value = ' + @Value
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END
PRINT @Tab + 'm_IsDataExisting = False'
PRINT @Tab + 'ParentForm.MessageToUser "New ' + @TableName + ' ready."'
PRINT 'End Sub'
PRINT ''
CLOSE @C
PRINT 'Public Sub DoOpen(' + @PkFieldsFeParameter + ')'
PRINT @TryBlockBegin
PRINT @Tab + 'Dim Rs As ADODB.Recordset'
PRINT ''
PRINT @Tab + 'Dim E As String'
PRINT @Tab + 'Services.' + @TableName + '_Open E, "", ' + @PkFieldsMtParameter + ', Rs'
PRINT @Tab + 'RaiseIfErr E'
PRINT @Tab + 'If Rs.RecordCount = 1 Then'
OPEN @C
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN
IF EXISTS(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and c.name = @C_Name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
)
BEGIN
PRINT + @Tab + @Tab + 'txt' + @C_Name + '.IsEditable = False'
END
SET @S = @C_Name
PRINT @Tab + @Tab + 'txt' + @C_Name + '.Value = Rs.Fields("' + @C_Name + '").Value'
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END
PRINT @Tab + @Tab + 'm_IsDataExisting = True'
PRINT @Tab + @Tab + 'ParentForm.MessageToUser "Existing ' + @TableName + ' ready."'
CLOSE @C
PRINT @Tab + 'End If'
PRINT @TryBlockEnd
PRINT ''
PRINT @CatchBlockBegin
PRINT dbo.CodeGenFe_CatchBlockEnd('DoOpen')
PRINT ''
PRINT @FinallyBlockBegin
PRINT ''
PRINT @FinallyBlockEnd
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub DoDelete()'
PRINT @TryBlockBegin
print @pkdeclare
PRINT ''
declare @PkDisplay varchar(8000)
set @PkDisplay = NULL
select
@PkDisplay = coalesce(
@PkDisplay + ' & " " & ' + c.Name
,c.Name )
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
PRINT @Tab + 'Dim E As String'
PRINT @Tab + 'If m_IsDataExisting Then'
PRINT @Tab + @Tab + 'If MsgBox("Are you sure you do want to delete " & ' + @PkDisplay + ', vbQuestion + vbYesNo + vbDefaultButton2, "Deleting Record") = vbYes Then'
PRINT ''
PRINT @Tab + @Tab + @Tab + 'Services.' + @TableTransactName + '_Delete E, "", ' + @PkFieldsMtParameter
PRINT @Tab + @Tab + @Tab + 'RaiseIfErr E'
PRINT @Tab + @Tab + @Tab + 'DoNew'
PRINT ''
PRINT @Tab + @Tab + 'End If'
PRINT @Tab + 'End If'
PRINT ''
PRINT @TryBlockEnd
PRINT ''
PRINT @CatchBlockBegin
PRINT dbo.CodeGenFe_CatchBlockEnd('DoDelete')
PRINT ''
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Sub'
PRINT ''
PRINT 'Private Function IsInputValid() As Boolean'
print @Tab + 'IsInputValid = False'
OPEN @C
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN
-- numbers
IF @C_XType IN (127,106,62,56,60,108,59,52,122,48) BEGIN
print ''
END
ELSE IF @C_XType = 104 BEGIN -- boolean
PRINT ''
END
-- date
ELSE IF @C_XType IN (61, 58) BEGIN
PRINT @Tab + 'If Not txt' + @C_Name + '.ReadOnly And IsNull(txt' + @C_Name + '.Value) Then'
PRINT @Tab + @Tab + 'ParentForm.MessageToUser "' + @C_Name + ' can''t be left blank"'
PRINT @Tab + @Tab + 'txt' + @C_Name + '.SetFocus'
PRINT @Tab + @Tab + 'Exit Function'
PRINT @Tab + 'End If'
END
ELSE BEGIN
PRINT @Tab + 'If Not txt' + @C_Name + '.ReadOnly And txt' + @C_Name + '.Value = "" Then'
PRINT @Tab + @Tab + 'ParentForm.MessageToUser "' + @C_Name + ' can''t be left blank"'
PRINT @Tab + @Tab + 'txt' + @C_Name + '.SetFocus'
PRINT @Tab + @Tab + 'Exit Function'
PRINT @Tab + 'End If'
END
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END
CLOSE @C
PRINT @Tab + '''Put other validations here...'
PRINT @Tab + '''...Put other validations here'
PRINT @Tab + 'IsInputValid = True'
PRINT 'End Function'
PRINT ''
PRINT 'Private Sub DoSave()'
PRINT @Tab + 'If Not IsInputValid() Then'
PRINT @Tab + @Tab + 'Exit Sub'
PRINT @Tab + 'End If'
PRINT @PkDeclare
PRINT ''
PRINT @TryBlockBegin
PRINT @Tab + 'Dim IsExists As Boolean'
PRINT @Tab + 'Dim E As String'
PRINT @Tab + 'IsExists = Services.' + @TableName + '_IsExisting(E, "", ' + @PkFieldsMtParameter + ')'
PRINT @Tab + 'RaiseIfErr E'
PRINT ''
PRINT @Tab + 'If m_IsDataExisting Then '
PRINT @Tab + @Tab + 'If Not IsExists Then'
PRINT @Tab + @Tab + @Tab + 'MsgBox ' + @PkDisplay + ' & " is already deleted before you have even saved", vbInformation'
PRINT @Tab + @Tab + @Tab + 'GoTo goFinally'
PRINT @Tab + @Tab + 'End If'
PRINT @Tab + 'Else'
PRINT @Tab + @Tab + 'If IsExists Then'
PRINT @Tab + @Tab + @Tab + 'MsgBox ' + @PkDisplay + ' & " already in the database", vbInformation'
PRINT @Tab + @Tab + @Tab + 'GoTo goFinally'
PRINT @Tab + @Tab + 'End If'
PRINT @Tab + 'End If'
------------------------
PRINT ''
PRINT @Tab + 'Dim RsSrc As ADODB.Recordset'
PRINT @Tab + 'Set RsSrc = New ADODB.Recordset'
OPEN @C
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN
IF EXISTS(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
)
BEGIN
GOTO goContinueType
END
SET @S = @C_Name
IF @C_XType IN (127,106,62,56,60,108,59,52,122,48) BEGIN
PRINT @Tab + 'RsSrc.Fields.Append "' + @C_Name + '", ' + @C_VbType
END
ELSE IF @C_XType = 104 BEGIN -- boolean
PRINT @Tab + 'RsSrc.Fields.Append "' + @C_Name + '", ' + @C_VbType
END
ELSE BEGIN
PRINT @Tab + 'RsSrc.Fields.Append "' + @C_Name + '", ' + @C_VbType + ', ' + CONVERT(VARCHAR,@C_Length)
END
goContinueType:
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END
CLOSE @C
PRINT @Tab + 'RsSrc.Open'
PRINT ''
------------------------
PRINT @Tab + 'RsSrc.AddNew'
OPEN @C
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN
IF EXISTS(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
)
BEGIN
GOTO goContinue
END
SET @S = @C_Name
PRINT @Tab + 'RsSrc.Fields("' + @S + '").Value = txt' + @S + '.Value'
goContinue:
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END
CLOSE @C
PRINT @Tab + 'RsSrc.Update'
PRINT ''
PRINT @Tab + 'ParentForm.MessageToUser "' + @TableName + ' saving..."'
PRINT @Tab + 'Services.' + @TableName + '_Save E, "", m_IsDataExisting, ' + @PkFieldsMtParameter + ', RsSrc'
PRINT @Tab + 'RaiseIfErr E'
PRINT ''
DECLARE @PkEditable VARCHAR(8000)
set @PkEditable = ''
select
@PkEditable =
@PkEditable + @Tab + 'txt' + c.Name + '.IsEditable = False' + CHAR(13)
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
PRINT @PkEditable
PRINT @Tab + 'm_IsDataExisting = True'
PRINT @Tab + 'ParentForm.MessageToUser "' + @TableName + ' saved."'
PRINT @Tab + 'MsgBox "' + @TableName + ' saved.", vbInformation'
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT @Tab + 'ParentForm.MessageToUser ""'
PRINT dbo.CodeGenFe_CatchBlockEnd('DoSave')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Sub'
PRINT ''
PRINT '''...Transactions'
----------------------
DEALLOCATE @C
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE CodeGenFe_TwoTableNoAutoId
@TransactName VARCHAR(100),
@TableHeaderName VARCHAR(100),
@TableDetailName VARCHAR(100)
AS
IF object_id(@TableHeaderName) IS NULL BEGIN
PRINT 'Table ' + @TableHeaderName + ' does not exists'
RETURN
END
IF object_id(@TableDetailName) IS NULL BEGIN
PRINT 'Table ' + @TableDetailName + ' does not exists'
RETURN
END
DECLARE @Tab VARCHAR(4)
SET @Tab = SPACE(4)
DECLARE @TryBlockBegin VARCHAR(8000)
SET @TryBlockBegin = @Tab + 'On Error GoTo goCatchError'
+ CHAR(13)
+ @Tab + '''Try{' + CHAR(13) + CHAR(13)
DECLARE @TryBlockEnd VARCHAR(8000)
SET @TryBlockEnd = CHAR(13) + @Tab + 'GoTo goFinally' + CHAR(13) + @Tab + '''}' + CHAR(13)
DECLARE @CatchBlockBegin VARCHAR(8000)
SET @CatchBlockBegin= @Tab + '''CatchError{' + CHAR(13) + 'goCatchError:' + CHAR(13) + CHAR(13) + char(13)
+ @Tab + 'Screen.MousePointer = vbDefault'
DECLARE @FinallyBlockBegin VARCHAR(8000)
SET @FinallyBlockBegin = @Tab + '''Finally{' + CHAR(13) + 'goFinally:' + CHAR(13) + CHAR(13)
DECLARE @FinallyBlockEnd VARCHAR(8000)
SET @FinallyBlockEnd = @Tab + '''}'
DECLARE @PkDeclare VARCHAR(8000)
set @PkDeclare = ''
select
@PkDeclare = @PkDeclare + CHAR(13) + @Tab + 'Dim ' + c.Name + ' As String'
+ CHAR(13) + @Tab + C.Name + ' = txt' + C.Name + '.Value'
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and c.name NOT IN (SELECT GeographicCode FROM CodeGen_Geographic)
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
-----------------------------------------------------
print 'Option Explicit'
print 'Option Compare Text'
print 'Option Base 0'
PRINT ''
PRINT '''Passed parameters...'
print 'Public ParentForm As Object'
PRINT '''...passed parameters'
print ''
PRINT 'Private Const m_grdGotFocusColor = &HFF0000'
PRINT 'Private Const m_grdLostFocusColor = 0'
print ''
print 'Private m_IsDataExisting as Boolean'
PRINT 'Private m_RsDeleted As ADODB.Recordset'
print ''
PRINT 'Private Sub Form_Load()'
PRINT ''
PRINT @Tab + 'Set Me.Icon = LoadPicture("")'
PRINT ''
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub Form_Activate()'
PRINT ''
PRINT @Tab + 'ParentForm.ShowMessage "' + @TransactName + ' ready."'
PRINT ''
PRINT 'End Sub'
PRINT ''
PRINT ''
DECLARE @c CURSOR
DECLARE @C_ReferencedTableName VARCHAR(100)
SET @C = CURSOR FOR
SELECT SC.Name, SC.XType, dt.VbType, ReferencedTableName = rt.name, sc.length
FROM syscolumns AS SC
INNER JOIN systypes AS ST ON sc.xtype = st.xtype
INNER JOIN CodeGen_DataTypes AS dt ON dt.sqltype = st.name
INNER JOIN sysforeignkeys AS sfk ON sfk.fkeyid = sc.id AND sfk.fkey = sc.colid
INNER JOIN sysobjects AS rt ON rt.id = sfk.rkeyid
WHERE sc.id = object_id(@TableHeaderName)
AND SC.iscomputed = 0
AND CHARINDEX('_', SC.Name) = 0
ORDER BY SC.colid
DECLARE @S VARCHAR(8000)
DECLARE @PkFieldsMtParameter VARCHAR(8000)
set @PkFieldsMtParameter = NULL
select
@PkFieldsMtParameter = coalesce(
@PkFieldsMtParameter + ', ' + c.Name
, c.Name )
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
DECLARE @PkFieldsOpenParameter VARCHAR(8000)
set @PkFieldsOpenParameter = NULL
select
@PkFieldsOpenParameter = coalesce(
@PkFieldsOpenParameter + ', Rs.Fields("' + c.Name + '").Value'
, 'Rs.Fields("' + c.Name + '").Value')
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
DECLARE @PkFieldsFirstKey VARCHAR(8000)
set @PkFieldsFirstKey = NULL
select
TOP 1 @PkFieldsFirstKey = C.Name
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
declare @PkFieldsFeParameter varchar(8000)
set @PkFieldsFeParameter = NULL
select
@PkFieldsFeParameter = coalesce(
@PkFieldsFeParameter + ', ByVal ' + c.Name + ' As String'
,'ByVal ' + c.Name + ' As String')
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
PRINT '''User initiated events...'
print 'Private Sub cmdDelete_Click()'
PRINT @Tab + 'DoDelete'
PRINT 'End Sub'
PRINT 'Private Sub cmdSave_Click()'
PRINT @Tab + 'DoSave'
PRINT 'End Sub'
PRINT 'Private Sub cmdNew_Click()'
PRINT @Tab + 'DoNew'
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub txt' + @PkFieldsFirstKey + '_DropDown(UseDefault As Boolean, RS As ADODB.Recordset, RecordsetPositioner As String)'
PRINT @TryBlockBegin
PRINT @Tab + 'Dim E As String'
PRINT @Tab + 'Set Rs = Services.' + @TransactName + '_Browse(E,"")'
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT dbo.CodeGenFe_CatchBlockEnd('txt' + @PkFieldsFirstKey + '_Dropdown')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub txt' + @PkFieldsFirstKey + '_GotoRecord(RS As ADODB.Recordset)'
PRINT @TryBlockBegin
PRINT @Tab + 'DoOpen ' + @PkFieldsOpenParameter
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT dbo.CodeGenFe_CatchBlockEnd('txt' + @PkFieldsFirstKey + '_GotoRecord')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub cmdClone_Click()'
PRINT @Tab + 'DoClone'
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub DoClone()'
PRINT @Tab + 'm_IsDataExisting = False'
declare @PkClonePrimaryKeys varchar(8000)
set @PkClonePrimaryKeys = ''
select
@PkClonePrimaryKeys =
@PkClonePrimaryKeys + CHAR(13) +
CHAR(13) + @Tab + 'txt' + c.Name + '.Value = ""' +
CHAR(13) + @Tab + 'txt' + c.Name + '.IsEditable = True'
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
PRINT @PkClonePrimaryKeys
PRINT ''
PRINT @Tab + 'SetGridIsChangedStatus grd, True'
PRINT 'End Sub'
PRINT '''...User initiated events'
DECLARE @C_Name AS VARCHAR(100)
DECLARE @C_XType AS INT
DECLARE @C_VbType VARCHAR(100)
DECLARE @C_Length INT
---------------------
-- generate lookup...
PRINT ''
PRINT '''Lookups...'
OPEN @C
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_ReferencedTableName, @C_length
WHILE @@FETCH_STATUS = 0 BEGIN
IF EXISTS(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
)
BEGIN
GOTO goContinueLookup
END
SET @S = @C_Name
PRINT ''
PRINT 'Private Sub txt' + @C_Name + '_DropDown(UseDefault As Boolean, RS As ADODB.Recordset, RecordsetPositioner As String)'
PRINT @TryBlockBegin
PRINT @Tab + 'Dim E As String'
PRINT @Tab + 'Set Rs = Services.' + @C_ReferencedTableName + '_Lookup(E,"")'
PRINT @Tab + 'RaiseIfErr E'
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT dbo.CodeGenFe_CatchBlockEnd('txt' + @C_Name + '_DropDown')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub txt' + @C_Name + '_GotoRecord(Rs As ADODB.Recordset)'
PRINT @Tab + 'txt' + @C_Name + '.Value = Rs.Fields("' + @C_Name + '").Value'
PRINT 'End Sub'
PRINT ''
goContinueLookup:
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_ReferencedTableName, @C_length
END
CLOSE @C
PRINT '''...Lookups'
PRINT ''
-- ..generate lookup
---------------------
PRINT ''
PRINT '''Transactions...'
PRINT ''
SET @C = CURSOR FOR
SELECT SC.Name, SC.XType, dt.VbType, sc.length
FROM syscolumns AS SC
INNER JOIN systypes AS ST ON sc.xtype = st.xtype
INNER JOIN CodeGen_DataTypes AS dt ON dt.sqltype = st.name
WHERE sc.id = object_id(@TableHeaderName)
AND SC.iscomputed = 0
AND
(
PATINDEX('%_QTY', SC.Name) = 0
AND PATINDEX('%_COST', SC.Name) = 0
AND PATINDEX('%_PRICE', SC.Name) = 0
)
ORDER BY SC.colid
PRINT 'Private Sub DoNew()'
OPEN @C
DECLARE @Value AS VARCHAR(100)
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN
IF EXISTS(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
)
BEGIN
PRINT + @Tab + 'txt' + @C_Name + '.IsEditable = True'
END
SET @S = @C_Name
IF @C_XType IN (127,106,62,56,60,108,59,52,122,148) BEGIN
SET @Value = '0'
END
ELSE IF @C_XType = 104 BEGIN
SET @Value = 'False'
END
ELSE BEGIN
SET @Value = '""'
END
PRINT @Tab + 'txt' + @S + '.Value = ' + @Value
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END
PRINT @Tab + 'grd.RemoveAll'
PRINT @Tab + 'm_IsDataExisting = False'
PRINT @Tab + 'ParentForm.ShowMessage "New ' + @TransactName + ' ready."'
PRINT 'End Sub'
PRINT ''
CLOSE @C
PRINT 'Public Sub DoOpen(' + @PkFieldsFeParameter + ')'
PRINT @TryBlockBegin
PRINT @Tab + 'Dim RsHeader As ADODB.Recordset'
PRINT @Tab + 'Dim RsDetail AS ADODB.Recordset'
PRINT ''
PRINT @Tab + 'Dim E As String'
PRINT @Tab + 'Services.' + @TransactName + '_Open E, "", ' + @PkFieldsMtParameter + ', RsHeader'
PRINT @Tab + 'RaiseIfErr E'
PRINT @Tab + 'If RsHeader.RecordCount = 1 Then'
OPEN @C
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN
IF EXISTS(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and c.name = @C_Name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
)
BEGIN
PRINT + @Tab + @Tab + 'txt' + @C_Name + '.IsEditable = False'
END
SET @S = @C_Name
PRINT @Tab + @Tab + 'txt' + @C_Name + '.Value = RsHeader.Fields("' + @C_Name + '").Value'
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END
PRINT @Tab + @Tab + 'MycFunc4Ctrls.ScatterRecordsetToGrid grd, RsDetail, False'
PRINT @Tab + @Tab + 'm_IsDataExisting = True'
PRINT @Tab + @Tab + 'ParentForm.ShowMessage "Existing ' + @TransactName + ' ready."'
CLOSE @C
PRINT @Tab + 'End If'
PRINT @TryBlockEnd
PRINT ''
PRINT @CatchBlockBegin
PRINT dbo.CodeGenFe_CatchBlockEnd('DoOpen')
PRINT ''
PRINT @FinallyBlockBegin
PRINT ''
PRINT @FinallyBlockEnd
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub DoDelete()'
PRINT @TryBlockBegin
print @pkdeclare
PRINT ''
declare @PkDisplay varchar(8000)
set @PkDisplay = NULL
select
@PkDisplay = coalesce(
@PkDisplay + ' & " " & ' + c.Name
,c.Name )
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
PRINT @Tab + 'Dim E As String'
PRINT @Tab + 'If m_IsDataExisting Then'
PRINT @Tab + @Tab + 'If MsgBox("Are you sure you do want to delete " & ' + @PkDisplay + ', vbQuestion + vbYesNo + vbDefaultButton2, "Deleting Record") = vbYes Then'
PRINT ''
PRINT @Tab + @Tab + @Tab + 'Services.' + @TransactName + '_Delete E, "", ' + @PkFieldsMtParameter
PRINT @Tab + @Tab + @Tab + 'RaiseIfErr E'
PRINT @Tab + @Tab + @Tab + 'DoNew'
PRINT ''
PRINT @Tab + @Tab + 'End If'
PRINT @Tab + 'End If'
PRINT ''
PRINT @TryBlockEnd
PRINT ''
PRINT @CatchBlockBegin
PRINT dbo.CodeGenFe_CatchBlockEnd('DoDelete')
PRINT ''
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Sub'
PRINT ''
PRINT 'Private Function IsInputValid() As Boolean'
print @Tab + 'IsInputValid = False'
OPEN @C
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN
-- numbers
IF @C_XType IN (127,106,62,56,60,108,59,52,122,148) BEGIN
print ''
END
ELSE IF @C_XType = 104 BEGIN -- boolean
PRINT ''
END
-- date
ELSE IF @C_XType IN (61, 58) BEGIN
PRINT @Tab + 'If Not txt' + @C_Name + '.ReadOnly And IsNull(txt' + @C_Name + '.Value) Then'
PRINT @Tab + @Tab + 'ParentForm.ShowMessage "' + @C_Name + ' can''t be left blank"'
PRINT @Tab + @Tab + 'txt' + @C_Name + '.SetFocus'
PRINT @Tab + @Tab + 'Exit Function'
PRINT @Tab + 'End If'
END
ELSE BEGIN
PRINT @Tab + 'If Not txt' + @C_Name + '.ReadOnly And txt' + @C_Name + '.Value = "" Then'
PRINT @Tab + @Tab + 'ParentForm.ShowMessage "' + @C_Name + ' can''t be left blank"'
PRINT @Tab + @Tab + 'txt' + @C_Name + '.SetFocus'
PRINT @Tab + @Tab + 'Exit Function'
PRINT @Tab + 'End If'
END
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END
CLOSE @C
PRINT @Tab + '''Put other validations here...'
PRINT @Tab + '''...Put other validations here'
PRINT @Tab + 'IsInputValid = True'
PRINT 'End Function'
PRINT ''
PRINT 'Private Sub DoSave()'
PRINT @Tab + 'If Not IsInputValid() Then'
PRINT @Tab + @Tab + 'Exit Sub'
PRINT @Tab + 'End If'
PRINT @PkDeclare
PRINT ''
PRINT @TryBlockBegin
PRINT @Tab + 'Dim IsExists As Boolean'
PRINT @Tab + 'Dim E As String'
PRINT @Tab + 'IsExists = Services.' + @TransactName + '_IsExisting(E, "", ' + @PkFieldsMtParameter + ')'
PRINT @Tab + 'RaiseIfErr E'
PRINT ''
PRINT @Tab + 'If m_IsDataExisting Then '
PRINT @Tab + @Tab + 'If Not IsExists Then'
PRINT @Tab + @Tab + @Tab + 'MsgBox ' + @PkDisplay + ' & " is already deleted before you have even saved", vbInformation'
PRINT @Tab + @Tab + @Tab + 'GoTo goFinally'
PRINT @Tab + @Tab + 'End If'
PRINT @Tab + 'Else'
PRINT @Tab + @Tab + 'If IsExists Then'
PRINT @Tab + @Tab + @Tab + 'MsgBox ' + @PkDisplay + ' & " already in the database", vbInformation'
PRINT @Tab + @Tab + @Tab + 'GoTo goFinally'
PRINT @Tab + @Tab + 'End If'
PRINT @Tab + 'End If'
------------------------
PRINT ''
PRINT @Tab + 'Dim RsHeaderSrc As ADODB.Recordset'
PRINT @Tab + 'Set RsHeaderSrc = New ADODB.Recordset'
OPEN @C
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN
IF EXISTS(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
)
BEGIN
GOTO goContinueType
END
SET @S = @C_Name
IF @C_XType IN (127,106,62,56,60,108,59,52,122,148) BEGIN
PRINT @Tab + 'RsHeaderSrc.Fields.Append "' + @C_Name + '", ' + @C_VbType
END
ELSE IF @C_XType = 104 BEGIN -- boolean
PRINT @Tab + 'RsHeaderSrc.Fields.Append "' + @C_Name + '", ' + @C_VbType
END
ELSE BEGIN
PRINT @Tab + 'RsHeaderSrc.Fields.Append "' + @C_Name + '", ' + @C_VbType + ', ' + CONVERT(VARCHAR,@C_Length)
END
goContinueType:
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END
CLOSE @C
PRINT @Tab + 'RsHeaderSrc.Open'
PRINT ''
------------------------
PRINT @Tab + 'RsHeaderSrc.AddNew'
OPEN @C
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN
IF EXISTS(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
)
BEGIN
GOTO goContinue
END
SET @S = @C_Name
PRINT @Tab + 'RsHeaderSrc.Fields("' + @S + '").Value = txt' + @S + '.Value'
goContinue:
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END
CLOSE @C
PRINT @Tab + 'RsHeaderSrc.Update'
PRINT ''
PRINT @Tab + 'Dim RsDetailSrc As ADODB.Recordset'
PRINT @Tab + 'Set RsDetailSrc = MycFunc4Ctrls.GatherGridToRecordset(grd)'
PRINT @Tab + 'ParentForm.ShowMessage "' + @TransactName + ' saving..."'
PRINT @Tab + 'Services.' + @TransactName + '_Save E, "", m_IsDataExisting, ' + @PkFieldsMtParameter + ', RsHeaderSrc, RsDetailSrc, m_rsDeleted'
PRINT @Tab + 'RaiseIfErr E'
PRINT ''
DECLARE @PkEditable VARCHAR(8000)
set @PkEditable = ''
select
@PkEditable =
@PkEditable + @Tab + 'txt' + c.Name + '.IsEditable = False' + CHAR(13)
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
PRINT @PkEditable
PRINT @Tab + 'm_IsDataExisting = True'
PRINT @Tab + 'ParentForm.ShowMessage "' + @TransactName + ' saved."'
PRINT @Tab + 'MsgBox "' + @TransactName + ' saved.", vbInformation'
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT @Tab + 'ParentForm.ShowMessage ""'
PRINT dbo.CodeGenFe_CatchBlockEnd('DoSave')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Sub'
PRINT ''
PRINT '''...Transactions'
----------------------
DEALLOCATE @C
PRINT ''
PRINT ''' Details...'
PRINT ''
PRINT 'Private Sub ComputeAllRows()'
PRINT ''
DECLARE @DetailAggregates AS VARCHAR(8000)
SET @DetailAggregates = ''
SELECT @DetailAggregates =
@DetailAggregates + CHAR(13)
+ @Tab + 'Dim Sum' + c.Name + ' As ' + dt.VbTypeDim + CHAR(13)
+ @Tab + 'Sum' + c.Name + ' = 0'
FROM sysobjects as o
INNER JOIN syscolumns as c ON c.id = o.id
INNER JOIN systypes AS ST ON c.xtype = st.xtype
INNER JOIN CodeGen_DataTypes AS dt ON dt.sqltype = st.name
WHERE o.id = object_id(@TableDetailName)
AND c.iscomputed = 0
AND st.xtype IN (127,106,62,56,60,108,59,52,122,48)
AND CHARINDEX('_', c.Name) = 0
ORDER BY colid
PRINT @DetailAggregates
PRINT ''
PRINT @Tab + 'Dim R As Long'
PRINT @Tab + 'For R = 0 to grd.Rows - 1'
PRINT @Tab + @Tab + 'Dim Bm'
PRINT @Tab + @Tab + 'Bm = grd.AddItemBookmark(R)'
SET @DetailAggregates = ''
SELECT @DetailAggregates =
@DetailAggregates + CHAR(13)
+ @Tab + @Tab + 'Sum' + c.Name + ' = Sum' + C.Name + ' + grd.Columns("' + C.Name + '").CellValue(BM)'
FROM sysobjects as o
INNER JOIN syscolumns as c ON c.id = o.id
INNER JOIN systypes AS ST ON c.xtype = st.xtype
INNER JOIN CodeGen_DataTypes AS dt ON dt.sqltype = st.name
WHERE o.id = object_id(@TableDetailName)
AND c.iscomputed = 0
AND st.xtype IN (127,106,62,56,60,108,59,52,122,48)
AND CHARINDEX('_', c.Name) = 0
PRINT @DetailAggregates
PRINT @Tab + 'Next'
SET @DetailAggregates = ''
SELECT @DetailAggregates =
@DetailAggregates + CHAR(13)
+ @Tab + 'txtSum' + c.Name + '.Value = Sum' + C.Name
FROM sysobjects as o
INNER JOIN syscolumns as c ON c.id = o.id
INNER JOIN systypes AS ST ON c.xtype = st.xtype
INNER JOIN CodeGen_DataTypes AS dt ON dt.sqltype = st.name
WHERE o.id = object_id(@TableDetailName)
AND c.iscomputed = 0
AND st.xtype IN (127,106,62,56,60,108,59,52,122,48)
AND CHARINDEX('_', c.Name) = 0
PRINT @DetailAggregates
PRINT ''
PRINT ''
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub ComputeRow()'
PRINT @Tab + 'grd.Columns("IsChanged").Value = True'
PRINT @Tab + ''' example: grd.Columns("Amount").Value = grd.Columns("Qty").Value * grd.Columns("UnitPrice").ValuE'
PRINT @Tab + 'grd.Update'
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub CreateRsDeleteStructure()'
PRINT @Tab + 'If Not (m_rsDeleted Is Nothing) Then'
PRINT @Tab + @Tab + 'm_rsDeleted.Close'
PRINT @Tab + @Tab + 'Set m_rsDeleted = Nothing'
PRINT @Tab + 'End If'
PRINT @Tab + 'Set m_rsDeleted = New ADODB.Recordset'
PRINT @Tab + 'm_rsDeleted.CursorLocation = adUseClient'
PRINT @Tab + 'm_rsDeleted.Fields.Append "RecId", adGUID'
PRINT @Tab + 'm_rsDeleted.Open'
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub grd_KeyDown(KeyCode As Integer, Shift As Integer)'
PRINT ''
PRINT @Tab + '''grdEnhancement... don''t delete this comment'
PRINT @Tab + 'If KeyCode = vbKeyReturn Then'
PRINT @Tab + @Tab + 'If grd.Col + 1 >= grd.Columns.Count Then'
PRINT @Tab + @Tab + @Tab + ''' wrap to next row and first column'
PRINT ''
PRINT @Tab + @Tab + @Tab + 'grd.Bookmark = grd.GetBookmark(1)'
PRINT @Tab + @Tab + @Tab + 'Dim C As SSDataWidgets_B_OLEDB.Column'
PRINT @Tab + @Tab + @Tab + 'Dim I As Integer'
PRINT @Tab + @Tab + @Tab + 'For I = 0 To grd.Columns.Count - 1'
PRINT @Tab + @Tab + @Tab + @Tab + 'If grd.Columns(I).Visible And Not grd.Columns(I).Locked Then'
PRINT @Tab + @Tab + @Tab + @Tab + @Tab + 'grd.Col = I'
PRINT @Tab + @Tab + @Tab + @Tab + @Tab + 'Exit For'
PRINT @Tab + @Tab + @Tab + @Tab + 'End If'
PRINT @Tab + @Tab + @Tab + 'Next'
PRINT @Tab + @Tab + 'Else'
PRINT @Tab + @Tab + @Tab + '''go to next column'
PRINT @Tab + @Tab + @Tab + 'grd.Col = grd.Col + 1'
PRINT @Tab + @Tab + 'End If'
PRINT @Tab + 'End If'
PRINT @Tab + '''...grdEnhancement'
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub grd_KeyUp(KeyCode As Integer, Shift As Integer)'
PRINT @Tab + 'If KeyCode = vbKeyInsert Or KeyCode = vbKeyF7 Then'
PRINT @Tab + @Tab + 'grd.Update'
PRINT @Tab + @Tab + 'grd.AddItem NewGuid'
PRINT @Tab + @Tab + 'grd.MoveLast'
DECLARE @Lookups VARCHAR(8000)
set @Lookups = ''
select
TOP 1 @Lookups = 'txt' + c.name + '.DoClickButton grd'
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableDetailName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableDetailName, i.indid, 1) or
c.name = index_col (@TableDetailName, i.indid, 2) or
c.name = index_col (@TableDetailName, i.indid, 3) or
c.name = index_col (@TableDetailName, i.indid, 4) or
c.name = index_col (@TableDetailName, i.indid, 5) or
c.name = index_col (@TableDetailName, i.indid, 6) or
c.name = index_col (@TableDetailName, i.indid, 7) or
c.name = index_col (@TableDetailName, i.indid, 8) or
c.name = index_col (@TableDetailName, i.indid, 9) or
c.name = index_col (@TableDetailName, i.indid, 10) or
c.name = index_col (@TableDetailName, i.indid, 11) or
c.name = index_col (@TableDetailName, i.indid, 12) or
c.name = index_col (@TableDetailName, i.indid, 13) or
c.name = index_col (@TableDetailName, i.indid, 14) or
c.name = index_col (@TableDetailName, i.indid, 15) or
c.name = index_col (@TableDetailName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
AND C.Name NOT IN
(
select
C.Name
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
) --and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
)
order by c.colid
PRINT @Tab + @Tab + @Lookups
PRINT @Tab + 'ElseIf KeyCode = vbKeyDelete And Shift = vbAltMask Then'
PRINT @Tab + @Tab + '''grdEnhancement... don''t delete this comment'
PRINT @Tab + @Tab + 'Dim PrevCol As Integer'
PRINT @Tab + @Tab + 'PrevCol = grd.Col'
PRINT @Tab + @Tab + '''...grdEnhancement'
PRINT ''
PRINT @Tab + @Tab + 'Dim BM'
PRINT @Tab + @Tab + 'If grd.SelBookmarks.Count > 0 Then'
PRINT @Tab + @Tab + @Tab + 'For Each BM In grd.SelBookmarks'
PRINT @Tab + @Tab + @Tab + @Tab + 'If grd.Columns("RecID").CellValue(BM) <> "" Then'
PRINT @Tab + @Tab + @Tab + @Tab + @Tab + 'm_rsDeleted.AddNew'
PRINT @Tab + @Tab + @Tab + @Tab + @Tab + 'm_rsDeleted.Fields("RecId").Value = grd.Columns("RecID").CellValue(BM)'
PRINT @Tab + @Tab + @Tab + @Tab + @Tab + 'm_rsDeleted.Update'
PRINT @Tab + @Tab + @Tab + @Tab + 'End If'
PRINT @Tab + @Tab + @Tab + 'Next BM'
PRINT @Tab + @Tab + 'Else'
PRINT @Tab + @Tab + @Tab + 'If grd.Columns("RecID").CellValue(BM) <> "" Then'
PRINT @Tab + @Tab + @Tab + @Tab + 'm_rsDeleted.AddNew'
PRINT @Tab + @Tab + @Tab + @Tab + 'm_rsDeleted.Fields("RecId").Value = grd.Columns("RecID").CellValue(grd.Bookmark)'
PRINT @Tab + @Tab + @Tab + @Tab + 'm_rsDeleted.Update'
PRINT @Tab + @Tab + @Tab + 'End If'
PRINT @Tab + @Tab + @Tab + 'grd.SelBookmarks.Add grd.AddItemRowIndex(grd.Bookmark)'
PRINT @Tab + @Tab + 'End If'
PRINT @Tab + @Tab + 'grd.DeleteSelected'
PRINT ''
PRINT @Tab + @Tab + '''grdEnhancement... don''t delete this comment'
PRINT @Tab + @Tab + 'grd.Col = PrevCol'
PRINT @Tab + @Tab + '''...grdEnhancement'
PRINT @Tab + 'End If'
PRINT ''
PRINT @Tab + '''grdEnhancement... don''t delete this comment'
PRINT @Tab + 'If KeyCode = vbKeyReturn Then'
PRINT @Tab + @Tab + 'grd.Update'
PRINT @Tab + 'End If'
PRINT @Tab + '''...grdEnhancement'
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub grd_RowColChange(ByVal LastRow As Variant, ByVal LastCol As Integer)'
PRINT ''
PRINT @Tab + '''grdEnhancement... don''t delete this comment'
PRINT @Tab + 'If grd.Col < 0 And LastCol >= 0 Then'
PRINT @Tab + @Tab + 'grd.Col = LastCol'
PRINT @Tab + 'End If'
PRINT @Tab + 'grd.ActiveCell.SelLength = Len(grd.ActiveCell.Text)'
PRINT @Tab + '''...grdEnhancement'
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub grd_UpdateError(ByVal ColIndex As Integer, Text As String, ErrCode As Integer, ErrString As String, Cancel As Integer)'
PRINT @Tab + '''grdEnhancement... don''t delete this comment'
PRINT @Tab + 'Cancel = 1'
PRINT @Tab + '''...grdEnhancement'
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub grd_GotFocus()'
PRINT @Tab + '''grdEnhancement... don''t delete this comment'
PRINT @Tab + 'grd.ForeColor = m_grdGotFocusColor'
PRINT @Tab + '''...grdEnhancement'
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub grd_LostFocus()'
PRINT @Tab + '''grdEnhancement... don''t delete this comment'
PRINT @Tab + 'grd.ForeColor = m_grdGotFocusColor'
PRINT @Tab + '''...grdEnhancement'
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub grd_AfterDelete(RtnDispErrMsg As Integer)'
PRINT @Tab + 'If grd.AddItemRowIndex(grd.Bookmark) >= grd.Rows Then'
PRINT @Tab + @Tab + 'grd.Bookmark = grd.AddItemBookmark(grd.Rows - 1)'
PRINT @Tab + 'End If'
PRINT 'End Sub'
PRINT ''
PRINT '''grid Lookups...'
-- pick all primary keys found in details, but whose field is not in header
SET @C = CURSOR FOR
SELECT C.Name, C.XType, dt.VbType, ReferencedTableName = rt.name, c.length
FROM syscolumns AS C
INNER JOIN systypes AS ST ON C.xtype = st.xtype
INNER JOIN CodeGen_DataTypes AS dt ON dt.sqltype = st.name
INNER JOIN sysforeignkeys AS sfk ON sfk.fkeyid = c.id AND sfk.fkey = c.colid
INNER JOIN sysobjects AS rt ON rt.id = sfk.rkeyid
INNER JOIN sysindexes AS i ON i.id = c.id
WHERE c.id = object_id(@TableDetailName)
AND C.iscomputed = 0
AND CHARINDEX('_', c.Name) = 0
AND C.Name NOT IN (SELECT GeographicCode FROM CodeGen_Geographic)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
AND C.Name NOT IN
(
select
C.Name
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
) --and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
)
ORDER BY c.colid
OPEN @C
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_ReferencedTableName, @C_length
WHILE @@FETCH_STATUS = 0 BEGIN
-- PRINT @C_ReferencedTableName + ':' + @C_Name
PRINT 'Private Sub txt' + @C_Name + '_Dropdown(UseDefault As Boolean, RS As ADODB.Recordset, RecordsetPositioner As String)'
PRINT @TryBlockBegin
PRINT @Tab + 'Dim E As String'
PRINT @Tab + 'Set RS = Services.' + @C_ReferencedTableName + '_Lookup(E, "")'
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT dbo.CodeGenFe_CatchBlockEnd('txt' + @C_Name + '_Dropdown')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Sub'
PRINT ''
PRINT 'Private Sub txt' + @C_Name + '_GotoRecord(RS As ADODB.Recordset)'
PRINT @TryBlockBegin
PRINT @Tab + 'grd.Columns("' + @C_Name + '").Value = RS.Fields("' + @C_Name + '").Value'
PRINT @Tab + 'grd.Update'
PRINT ''
print @Tab + '''grdEnhancement... don''t delete this comment'
PRINT @Tab + 'grd.Col = grd.Columns("' + @C_Name + '").Position + 1'
PRINT @Tab + '''...grdEnhancement'
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT dbo.CodeGenFe_CatchBlockEnd('txt' + @C_Name + '_GotoRecord')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Sub'
PRINT ''
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_ReferencedTableName, @C_length
END
CLOSE @C
DEALLOCATE @C
PRINT '''...grid Lookups'
PRINT ''
PRINT '''...Details'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE CodeGenMt_OneTableNoAutoId
@TransactName VARCHAR(100),
@TableName VARCHAR(100)
AS
IF object_id(@TableName) IS NULL BEGIN
PRINT 'Table ' + @TableName + ' does not exists'
RETURN
END
PRINT '''' + @TableName + ' add/edit/delete...'
PRINT ''
DECLARE @Tab VARCHAR(4)
SET @Tab = SPACE(4)
DECLARE @TryBlockBegin VARCHAR(8000)
SET @TryBlockBegin = @Tab + '''Try{' + CHAR(13) + @Tab + 'On Error GoTo goCatchError' + CHAR(13)
DECLARE @TryBlockEnd VARCHAR(8000)
SET @TryBlockEnd = CHAR(13) + @Tab + 'GoTo goFinally' + CHAR(13) + @Tab + '''}' + CHAR(13)
DECLARE @CatchBlockBegin VARCHAR(8000)
SET @CatchBlockBegin= @Tab + '''CatchError{' + CHAR(13) + 'goCatchError:' + CHAR(13) + CHAR(13) + char(13)
+ @Tab + 'Screen.MousePointer = vbDefault'
DECLARE @FinallyBlockBegin VARCHAR(8000)
SET @FinallyBlockBegin = @Tab + '''Finally{' + CHAR(13) + 'goFinally:' + CHAR(13) + CHAR(13)
DECLARE @FinallyBlockEnd VARCHAR(8000)
SET @FinallyBlockEnd = @Tab + '''}'
DECLARE @PkFields VARCHAR(8000)
SET @PkFields = NULL
select
@PkFields = coalesce(
@PkFields + ', ByVal ' + c.Name + ' As String'
,'ByVal ' + C.Name + ' As String' )
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
declare @PkCondition varchar(8000)
set @PkCondition = NULL
select
@PkCondition = coalesce(
@PkCondition + ' AND ' + c.Name + ' = ''" & Enquote(' + c.Name + ') & "''"'
,c.Name + ' = ''" & Enquote(' + c.Name + ') & "''"')
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
---------------------------
DECLARE @S VARCHAR(8000)
SET @S =
'Public Sub ' + @TransactName + '_Save(ByRef ReturnError As String, '
+ 'ByVal AccessKey As String, '
+ 'ByVal IsExisting As Boolean, '
SET @S = @S + @PkFields + ', ByVal RsSrc AS ADODB.Recordset)'
PRINT @S
/*
' Ver 1
If IsOldRecord Then
Set RsItem = m_Database.NewRsOpen("SELECT * FROM Item WHERE ItemCode='" & Enquote(Rs.Fields("ItemCode").Value) & "'", adOpenStatic, adLockOptimistic)
Else
Set RsItem = m_Database.NewRsOpen("SELECT * FROM Item WHERE 1=0", adOpenStatic, adLockOptimistic)
RsItem.AddNew
RsItem.Fields("ItemCode").Value = Rs.Fields("ItemCode").Value
End If
'Ver 2
Set RsItem = m_Database.NewRsOpen("SELECT * FROM Item WHERE ItemCode='" & Enquote(Rs.Fields("ItemCode").Value) & "'", adOpenStatic, adLockOptimistic)
If IsOldRecord Then
If RsItem.RecordCount = 0 Then
Err.Raise 777,"", "Record already deleted by other user before you have even saved this record"
End If
Else
If RsItem.RecordCount > 0 Then
err.raise 777, "", "Item code already exist, please change itemcode"
Else
RsItem.AddNew
RsItem.Fields("ItemCode").Value = Rs.Fields("ItemCode").Value
End If
End If
*/
PRINT @TryBlockBegin
PRINT @Tab + 'm_Database.BeginTrans'
PRINT ''
PRINT @Tab + 'Dim RsDst AS ADODB.Recordset'
PRINT ''
DECLARE @FieldComma VARCHAR(8000)
SET @FieldComma = NULL
SELECT @FieldComma = COALESCE(@FieldComma + ',' + Name, Name) FROM syscolumns WHERE id = object_id(@TableName) AND
iscomputed = 0
AND
(
PATINDEX('%_QTY', Name) = 0
AND PATINDEX('%_COST', Name) = 0
AND PATINDEX('%_PRICE', Name) = 0)
ORDER BY colid
SET @s = @Tab + 'Set RsDst = m_Database.NewRsOpen("SELECT ' + @FieldComma + ' FROM ' + @TableName + ' WHERE ' + @PkCondition + ', adOpenStatic, adLockOptimistic)'
print @s
print ''
PRINT @Tab + 'If IsExisting Then'
PRINT @Tab + @Tab + 'If RsDst.RecordCount = 0 Then'
PRINT @Tab + @Tab + @Tab + 'Err.Raise 777, "", "' + @TransactName + ' already deleted by other user before you have even saved"'
PRINT @Tab + @Tab + 'End If'
PRINT @Tab + 'Else'
PRINT @Tab + @Tab + 'If RsDst.RecordCount > 0 Then'
PRINT @Tab + @Tab + @Tab + 'Err.Raise 777, "", "' + @TransactName + ' already exist"'
PRINT @Tab + @Tab + 'Else'
PRINT @Tab + @Tab + @Tab + 'RsDst.AddNew'
SET @S = NULL
select
@S = coalesce(
@S + CHAR(13) + @Tab + @Tab + @Tab + 'RsDst.Fields("' + c.Name + '").Value = ' + c.Name
, @Tab + @Tab + @Tab + 'RsDst.Fields("' + c.Name + '").Value = ' + c.Name )
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
PRINT @S
PRINT @Tab + @Tab + 'End If'
PRINT @Tab + 'End If'
PRINT ''
DECLARE @c CURSOR
SET @C = CURSOR FOR SELECT Name FROM syscolumns WHERE id = object_id(@TableName) AND
iscomputed = 0
AND
(
PATINDEX('%_QTY', Name) = 0
AND PATINDEX('%_COST', Name) = 0
AND PATINDEX('%_PRICE', Name) = 0)
ORDER BY colid
OPEN @C
DECLARE @C_Name AS VARCHAR(100)
FETCH NEXT FROM @C INTO @C_Name
WHILE @@FETCH_STATUS = 0 BEGIN
IF EXISTS(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
)
BEGIN
GOTO goContinue
END
SET @S = @C_Name
PRINT @Tab + 'RsDst.Fields("' + @S + '").Value = RsSrc.Fields("' + @S + '").Value'
goContinue:
FETCH NEXT FROM @C INTO @C_Name
END
CLOSE @C
DEALLOCATE @C
PRINT @Tab + 'RsDst.Update'
PRINT ''
PRINT @Tab + 'm_Database.CommitTrans'
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT @Tab + 'm_Database.RollbackTrans'
PRINT dbo.CodeGenMt_CatchBlockEnd (@TransactName + '_Save')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Sub'
PRINT ''
-------------------------------
PRINT 'Public Function ' + @TransactName + '_IsExisting(ByRef ReturnError As String, ByVal AccessKey As String, ' + @PkFields + ') As Boolean'
PRINT @TryBlockBegin
SET @S = @Tab + @TransactName + '_IsExisting = m_Database.NewRsOpen("SELECT COUNT(*) As Cnt FROM ' + @TableName + ' WHERE ' + @PkCondition + ').Fields("Cnt").Value = 1'
PRINT @S
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT dbo.CodeGenMt_CatchBlockEnd (@TransactName + '_IsExisting')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Function'
--------------------------------------------
PRINT ''
PRINT 'Public Sub ' + @TransactName + '_Open(ByRef ReturnError As String, ByVal AccessKey As String, ' + @PkFields + ', ByRef RsReturn As ADODB.Recordset)'
PRINT @TryBlockBegin
PRINT ''
SET @s = @Tab + 'Set RsReturn = m_Database.NewRsOpen("SELECT ' + @FieldComma + ' FROM ' + @TableName + ' WHERE ' + @PkCondition + ')'
print @s
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT dbo.CodeGenMt_CatchBlockEnd (@TransactName + '_Open')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Sub'
-------------------------------------------------------------------------
PRINT ''
PRINT 'Public Function ' + @TransactName + '_Browse(ByRef ReturnError As String, ByVal AccessKey As String) As ADODB.Recordset'
PRINT @TryBlockBegin
SET @S = NULL
SELECT @S = COALESCE(@S + ',' + Name, Name) FROM syscolumns WHERE id = object_id(@TableName) AND
iscomputed = 0
AND
(
PATINDEX('%_QTY', Name) = 0
AND PATINDEX('%_COST', Name) = 0
AND PATINDEX('%_PRICE', Name) = 0)
AND colid BETWEEN 1 AND 3
ORDER BY colid
SET @s = @Tab + 'Set ' + @TransactName + '_Browse = m_Database.NewRsOpen("SELECT ' + @s + ' FROM ' + @TableName + '")'
PRINT ''
print @s
PRINT ''
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT dbo.CodeGenMt_CatchBlockEnd (@TransactName + '_Browse')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Function'
-------------------------------------------------------------------------
-------------------------------------------------------------------------
PRINT ''
PRINT 'Public Function ' + @TransactName + '_Lookup(ByRef ReturnError As String, ByVal AccessKey As String) As ADODB.Recordset'
PRINT @TryBlockBegin
SET @S = NULL
SELECT @S = COALESCE(@S + ',' + Name, Name) FROM syscolumns WHERE id = object_id(@TableName) AND
iscomputed = 0
AND
(
PATINDEX('%_QTY', Name) = 0
AND PATINDEX('%_COST', Name) = 0
AND PATINDEX('%_PRICE', Name) = 0)
AND colid BETWEEN 1 AND 3
ORDER BY colid
SET @s = @Tab + 'Set ' + @TransactName + '_Lookup = m_Database.NewRsOpen("SELECT ' + @s + ' FROM ' + @TableName + '")'
PRINT ''
print @s
PRINT ''
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT dbo.CodeGenMt_CatchBlockEnd (@TransactName + '_Lookup')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Function'
-------------------------------------------------------------------------
PRINT ''
PRINT 'Public Sub ' + @TransactName + '_Delete(ByRef ReturnError As String, ByVal AccessKey As String, ' + @PkFields + ')'
PRINT @TryBlockBegin
PRINT @Tab + 'm_Database.BeginTrans'
print ''
SET @s = @Tab + 'Call m_Database.NewRsOpen("DELETE FROM ' + @TableName + ' WHERE ' + @PkCondition + ')'
PRINT @s
PRINT @Tab + 'm_Database.CommitTrans'
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT @Tab + 'm_Database.RollbackTrans'
PRINT dbo.CodeGenMt_CatchBlockEnd (@TransactName + '_Delete')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Sub'
PRINT ''
PRINT '''...' + @TransactName + ' add/edit/delete'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE CodeGenMt_TwoTableNoAutoId
@TableTransactName VARCHAR(100),
@TableHeaderName VARCHAR(100),
@TableDetailName VARCHAR(100)
AS
IF object_id(@TableHeaderName) IS NULL BEGIN
PRINT 'Table ' + @TableHeaderName + ' does not exists'
RETURN
END
IF object_id(@TableDetailName) IS NULL BEGIN
PRINT 'Table ' + @TableDetailName + ' does not exists'
RETURN
END
PRINT '''' + @TableTransactName + ' add/edit/delete...'
PRINT ''
DECLARE @Tab VARCHAR(4)
SET @Tab = SPACE(4)
DECLARE @TryBlockBegin VARCHAR(8000)
SET @TryBlockBegin = @Tab + '''Try{' + CHAR(13) + @Tab + 'On Error GoTo goCatchError' + CHAR(13)
DECLARE @TryBlockEnd VARCHAR(8000)
SET @TryBlockEnd = CHAR(13) + @Tab + 'GoTo goFinally' + CHAR(13) + @Tab + '''}' + CHAR(13)
DECLARE @CatchBlockBegin VARCHAR(8000)
SET @CatchBlockBegin= @Tab + '''CatchError{' + CHAR(13) + 'goCatchError:' + CHAR(13) + CHAR(13) + char(13)
+ @Tab + 'Screen.MousePointer = vbDefault'
DECLARE @FinallyBlockBegin VARCHAR(8000)
SET @FinallyBlockBegin = @Tab + '''Finally{' + CHAR(13) + 'goFinally:' + CHAR(13) + CHAR(13)
DECLARE @FinallyBlockEnd VARCHAR(8000)
SET @FinallyBlockEnd = @Tab + '''}'
DECLARE @PkFields VARCHAR(8000)
SET @PkFields = NULL
select
@PkFields = coalesce(
@PkFields + ', ByVal ' + c.Name + ' As String'
,'ByVal ' + C.Name + ' As String' )
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
declare @PkCondition varchar(8000)
set @PkCondition = NULL
select
@PkCondition = coalesce(
@PkCondition + ' AND ' + c.Name + ' = ''" & Enquote(' + c.Name + ') & "''"'
,c.Name + ' = ''" & Enquote(' + c.Name + ') & "''"')
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
---------------------------
DECLARE @S VARCHAR(8000)
SET @S =
'Public Sub ' + @TableTransactName + '_Save(ByRef ReturnError As String, '
+ 'ByVal AccessKey As String, '
+ 'ByVal IsExisting As Boolean, '
SET @S = @S + @PkFields
+ ', ByVal RsHeaderSrc AS ADODB.Recordset ' +
+ ', ByVal RsDetailSrc As ADODB.Recordset ' +
+ ', ByVal RsDetailDel As ADODB.Recordset)'
PRINT @S
/*
' Ver 1
If IsOldRecord Then
Set RsItem = m_Database.NewRsOpen("SELECT * FROM Item WHERE ItemCode='" & Enquote(Rs.Fields("ItemCode").Value) & "'", adOpenStatic, adLockOptimistic)
Else
Set RsItem = m_Database.NewRsOpen("SELECT * FROM Item WHERE 1=0", adOpenStatic, adLockOptimistic)
RsItem.AddNew
RsItem.Fields("ItemCode").Value = Rs.Fields("ItemCode").Value
End If
'Ver 2
Set RsItem = m_Database.NewRsOpen("SELECT * FROM Item WHERE ItemCode='" & Enquote(Rs.Fields("ItemCode").Value) & "'", adOpenStatic, adLockOptimistic)
If IsOldRecord Then
If RsItem.RecordCount = 0 Then
Err.Raise 777,"", "Record already deleted by other user before you have even saved this record"
End If
Else
If RsItem.RecordCount > 0 Then
err.raise 777, "", "Item code already exist, please change itemcode"
Else
RsItem.AddNew
RsItem.Fields("ItemCode").Value = Rs.Fields("ItemCode").Value
End If
End If
*/
PRINT @TryBlockBegin
PRINT @Tab + 'm_Database.BeginTrans'
PRINT ''
PRINT @Tab + 'Dim RsHeaderDst AS ADODB.Recordset'
PRINT ''
DECLARE @FieldComma VARCHAR(8000)
SET @FieldComma = NULL
SELECT @FieldComma = COALESCE(@FieldComma + ',' + Name, Name) FROM syscolumns WHERE id = object_id(@TableHeaderName) AND
iscomputed = 0
AND
(
CHARINDEX('_', Name) = 0
)
ORDER BY colid
DECLARE @FieldCommaDetail VARCHAR(8000)
SET @FieldCommaDetail = NULL
SELECT @FieldCommaDetail = COALESCE(@FieldCommaDetail + ',' + Name, Name) FROM syscolumns as detailcol WHERE id = object_id(@TableDetailName) AND
iscomputed = 0
AND
(
CHARINDEX('_', Name) = 0
)
AND NOT EXISTS
(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and c.name = detailcol.name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
)
ORDER BY colid
SET @s = @Tab + 'Set RsHeaderDst = m_Database.NewRsOpen("SELECT ' + @FieldComma + ' FROM ' + @TableHeaderName + ' WHERE ' + @PkCondition + ', adOpenStatic, adLockOptimistic)'
print @s
print ''
PRINT @Tab + 'If IsExisting Then'
PRINT @Tab + @Tab + 'If RsHeaderDst.RecordCount = 0 Then'
PRINT @Tab + @Tab + @Tab + 'Err.Raise 777, "", "' + @TableTransactName + ' already deleted by other user before you have even saved"'
PRINT @Tab + @Tab + 'End If'
PRINT @Tab + 'Else'
PRINT @Tab + @Tab + 'If RsHeaderDst.RecordCount > 0 Then'
PRINT @Tab + @Tab + @Tab + 'Err.Raise 777, "", "' + @TableTransactName + ' already exist"'
PRINT @Tab + @Tab + 'Else'
PRINT @Tab + @Tab + @Tab + 'RsHeaderDst.AddNew'
SET @S = NULL
select
@S = coalesce(
@S + CHAR(13) + @Tab + @Tab + @Tab + 'RsHeaderDst.Fields("' + c.Name + '").Value = ' + c.Name
, @Tab + @Tab + @Tab + 'RsHeaderDst.Fields("' + c.Name + '").Value = ' + c.Name )
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
PRINT @S
PRINT @Tab + @Tab + 'End If'
PRINT @Tab + 'End If'
PRINT ''
DECLARE @c CURSOR
SET @C = CURSOR FOR SELECT Name FROM syscolumns WHERE id = object_id(@TableHeaderName) AND
iscomputed = 0
AND
(
CHARINDEX('_', Name) = 0
)
ORDER BY colid
OPEN @C
DECLARE @C_Name AS VARCHAR(100)
FETCH NEXT FROM @C INTO @C_Name
WHILE @@FETCH_STATUS = 0 BEGIN
IF EXISTS(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
)
BEGIN
GOTO goContinue
END
SET @S = @C_Name
PRINT @Tab + 'RsHeaderDst.Fields("' + @S + '").Value = RsHeaderSrc.Fields("' + @S + '").Value'
goContinue:
FETCH NEXT FROM @C INTO @C_Name
END
CLOSE @C
DEALLOCATE @C
PRINT @Tab + 'RsHeaderDst.Update'
--------------
-- detail here...
PRINT ''
PRINT ''
PRINT @Tab + 'If RsDetailDel.RecordCount > 0 Then'
PRINT @Tab + @Tab + 'RsDetailDel.MoveFirst'
PRINT @Tab + @Tab + 'Do Until RsDetailDel.EOF'
PRINT @Tab + @Tab + @Tab + 'Call m_Database.NewRsOpen("DELETE FROM ' + @TableDetailName + ' WHERE RecID = ''" & Enquote(RsDetailDel.Fields("RecID").Value) & "''")'
PRINT @Tab + @Tab + @Tab + 'RsDetailDel.MoveNext'
PRINT @Tab + @Tab + 'Loop'
PRINT @Tab + 'End If'
PRINT ''
PRINT @Tab + 'Dim RsDetailDst AS ADODB.Recordset'
PRINT ''
DECLARE @CDetail CURSOR
SET @CDetail = CURSOR FOR SELECT Name FROM syscolumns WHERE id = object_id(@TableDetailName) AND
iscomputed = 0
AND
(
CHARINDEX('_', Name) = 0
)
ORDER BY colid
OPEN @CDetail
DECLARE @CD_Name AS VARCHAR(100)
SET @FieldCommaDetail = NULL
SELECT @FieldCommaDetail = COALESCE(@FieldCommaDetail + ',' + Name, Name) FROM syscolumns as detailcol WHERE id = object_id(@TableDetailName) AND
iscomputed = 0
AND
(
CHARINDEX('_', Name) = 0
)
ORDER BY colid
PRINT @Tab + 'If RsDetailSrc.RecordCount > 0 Then'
PRINT @Tab + @Tab + 'RsDetailSrc.MoveFirst'
PRINT @Tab + @Tab + 'Do Until RsDetailSrc.EOF'
PRINT @Tab + @Tab + @Tab + 'Set RsDetailDst = m_Database.NewRsOpen("SELECT ' + @FieldCommaDetail + ' FROM ' + @TableDetailName + ' WHERE RecID=''" & Enquote(RsDetailSrc.Fields("RecId").Value) & "''", adOpenStatic, adLockOptimistic)'
PRINT @Tab + @Tab + @Tab + 'If RsDetailDst.EOF Then'
PRINT @Tab + @Tab + @Tab + @Tab + 'RsDetailDst.AddNew'
PRINT @Tab + @Tab + @Tab + @Tab + 'RsDetailDst.Fields("RecID").Value = RsDetailSrc.Fields("RecID").Value'
SET @S = NULL
select
@S = coalesce(
@S + CHAR(13) + @Tab + @Tab + @Tab + @Tab + 'RsDetailDst.Fields("' + c.Name + '").Value = ' + c.Name
, @Tab + @Tab + @Tab + @Tab + 'RsDetailDst.Fields("' + c.Name + '").Value = ' + c.Name )
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid
PRINT @S
PRINT @Tab + @Tab + @Tab + 'End If'
PRINT ''
FETCH NEXT FROM @CDetail INTO @CD_Name
WHILE @@FETCH_STATUS = 0 BEGIN
IF EXISTS(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
AND C.name = @CD_Name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
)
BEGIN
GOTO goContinueSaveDetail
END
IF (@CD_Name = 'RecId') BEGIN
GOTO goContinueSaveDetail
END
PRINT @Tab + @Tab + @Tab + 'RsDetailDst.Fields("' + @CD_Name + '").Value = RsDetailSrc.Fields("' + @CD_Name + '").Value'
goContinueSaveDetail:
FETCH NEXT FROM @CDetail INTO @CD_Name
END
PRINT @Tab + @Tab + @Tab + 'RsDetailDst.Update'
PRINT @Tab + @Tab + @Tab + 'RsDetailSrc.MoveNext'
PRINT @Tab + @Tab + 'Loop'
PRINT @Tab + 'End If'
CLOSE @CDetail
-- ...detail here
---------------------
PRINT ''
PRINT @Tab + 'm_Database.CommitTrans'
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT @Tab + 'm_Database.RollbackTrans'
PRINT dbo.CodeGenMt_CatchBlockEnd (@TableTransactName + '_Save')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Sub'
PRINT ''
-------------------------------
PRINT 'Public Function ' + @TableTransactName + '_IsExisting(ByRef ReturnError As String, ByVal AccessKey As String, ' + @PkFields + ') As Boolean'
PRINT @TryBlockBegin
SET @S = @Tab + @TableTransactName + '_IsExisting = m_Database.NewRsOpen("SELECT COUNT(*) As Cnt FROM ' + @TableHeaderName + ' WHERE ' + @PkCondition + ').Fields("Cnt").Value = 1'
PRINT @S
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT dbo.CodeGenMt_CatchBlockEnd (@TableTransactName + '_IsExisting')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Function'
--------------------------------------------
PRINT ''
PRINT 'Public Sub ' + @TableTransactName + '_Open(ByRef ReturnError As String, ByVal AccessKey As String, ' + @PkFields + ', ByRef RsHeaderReturn As ADODB.Recordset, ByRef RsDetailReturn As ADODB.Recordset)'
PRINT @TryBlockBegin
PRINT ''
SET @s = @Tab + 'Set RsHeaderReturn = m_Database.NewRsOpen("SELECT ' + @FieldComma + ' FROM ' + @TableHeaderName + ' WHERE ' + @PkCondition + ')'
print @s
SET @S = @Tab + 'Set RsDetailReturn = m_Database.NewRsOpen("SELECT ' + @FieldCommaDetail + ' FROM ' + @TableDetailName + ' WHERE ' + @PkCondition + ')'
print @s
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT dbo.CodeGenMt_CatchBlockEnd (@TableTransactName + '_Open')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Sub'
-------------------------------------------------------------------------
PRINT ''
PRINT 'Public Function ' + @TableTransactName + '_Browse(ByRef ReturnError As String, ByVal AccessKey As String) As ADODB.Recordset'
PRINT @TryBlockBegin
SET @S = NULL
SELECT @S = COALESCE(@S + ',' + Name, Name) FROM syscolumns WHERE id = object_id(@TableHeaderName) AND
iscomputed = 0
AND
(
CHARINDEX('_', Name) = 0
)
AND colid BETWEEN 1 AND 3
ORDER BY colid
SET @s = @Tab + 'Set ' + @TableTransactName + '_Browse = m_Database.NewRsOpen("SELECT ' + @s + ' FROM ' + @TableHeaderName + '")'
PRINT ''
print @s
PRINT ''
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT dbo.CodeGenMt_CatchBlockEnd (@TableTransactName + '_Browse')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Function'
-------------------------------------------------------------------------
-------------------------------------------------------------------------
PRINT ''
PRINT 'Public Function ' + @TableTransactName + '_Lookup(ByRef ReturnError As String, ByVal AccessKey As String) As ADODB.Recordset'
PRINT @TryBlockBegin
SET @S = NULL
SELECT @S = COALESCE(@S + ',' + Name, Name) FROM syscolumns WHERE id = object_id(@TableHeaderName) AND
iscomputed = 0
AND
(
CHARINDEX('_', Name) = 0
)
AND colid BETWEEN 1 AND 3
ORDER BY colid
SET @s = @Tab + 'Set ' + @TableTransactName + '_Lookup = m_Database.NewRsOpen("SELECT ' + @s + ' FROM ' + @TableHeaderName + '")'
PRINT ''
print @s
PRINT ''
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT dbo.CodeGenMt_CatchBlockEnd (@TableTransactName + '_Lookup')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Function'
-------------------------------------------------------------------------
PRINT ''
PRINT 'Public Sub ' + @TableTransactName + '_Delete(ByRef ReturnError As String, ByVal AccessKey As String, ' + @PkFields + ')'
PRINT @TryBlockBegin
PRINT @Tab + 'm_Database.BeginTrans'
print ''
SET @s = @Tab + 'Call m_Database.NewRsOpen("DELETE FROM ' + @TableDetailName + ' WHERE ' + @PkCondition + ')'
PRINT @s
SET @s = @Tab + 'Call m_Database.NewRsOpen("DELETE FROM ' + @TableHeaderName + ' WHERE ' + @PkCondition + ')'
PRINT @s
PRINT @Tab + 'm_Database.CommitTrans'
PRINT @TryBlockEnd
PRINT @CatchBlockBegin
PRINT @Tab + 'm_Database.RollbackTrans'
PRINT dbo.CodeGenMt_CatchBlockEnd (@TableTransactName + '_Delete')
PRINT @FinallyBlockBegin
PRINT @FinallyBlockEnd
PRINT 'End Sub'
PRINT ''
PRINT '''...' + @TableHeaderName + ' add/edit/delete'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
