How to Update when dynamic Select SQL by using SQLDatasouce In .net 2.0?

In Dot net 2.0 we change using SQLDataSource to Conect with SQLDB.

Now for My case ,the Select SQL is dynamic when differnece user and parameters to the page, So if I want to Update the data input by user,then I must give Update/insert/delelte SQL to SQLDatasource's InsertCommand /UpdateCommand/DeleteCommand . 

How to Generate the Insert/update/delete command for the SQLDataSource ? as in dot net 1.1 can use SQLCommandBuilder to generate it,but SQLCommandBuilder  just support DataAdeptor not for SQLDataSource, Could any body know how to do it when the SelectCommand is dynamic and need to update data back to DB after edit?

 thanks a lot.

0
kevin_lam
4/5/2007 10:19:59 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

4 Replies
581 Views

Similar Articles

[PageSpeed] 35

Why not just write the update, insert, delete stored procedures and wrapper code directly?

Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
0
TATWORTH
4/5/2007 3:04:49 PM

The stored procedures to make stored procedures:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE sp_MakeDelete 
  (@TABLENAME VARCHAR(255)='xyzzy')
AS
-- Purpose:
--   Create delete script from table definition
-- Copyright (C) 2000, 2003, 2004, 2005 Clive Chinery
--
-- This library is free software; you can redistribute it and/or
-- modify it under the terms of the GNU Lesser General Public
-- License as published by the Free Software Foundation; either
-- version 2.1 of the License, or (at your option) any later version.
--
-- This library is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
-- Lesser General Public License for more details.
--
-- You should have received a copy of the GNU Lesser General Public
-- License along with this library; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
-- Notes:
--   1) Run from any database on this server by entering sp_MakeDelete TABLENAME
--   2) The TABLENAME is case insensitive, however the table must exist
--   3) ID and TIMESTAMP columns are specially handled
--   4) Tables with ID column will have a where clause using the ID column
--      this normally suffices.
-- Changes:
-- 01Sep2005 Clive Extend to cover more data types
-- 09Oct2000 Clive Make prefix sp_
-- 01Aug2000 Clive Documentation update
-- 31Jul2000 Clive Modified to put more of documentation header
DECLARE @ColName    VARCHAR(32)
DECLARE @ColLength  INT
DECLARE @HasIdent   INT
DECLARE @RowCount   INT
DECLARE @RowIndex   INT
DECLARE @HasTSTAMP  INT
DECLARE @PrintLine  VARCHAR(132)
DECLARE @SpName     VARCHAR(32)
DECLARE @DateWork   VARCHAR(16)
DECLARE @TN         VARCHAR(32)
DECLARE @USER       VARCHAR(32)
DECLARE @DOM_NAME   VARCHAR(50)
DECLARE @SpPrefix   VARCHAR(10)
DECLARE @SET_DB     VARCHAR(50)
DECLARE @ObPrefix   VARCHAR(10)
--  Configuration start
SELECT  @SpPrefix = 'usp_'
SELECT  @DOM_NAME = 'EDIPLC' + '\'
SELECT  @SET_DB   = 'asp_dbPermissions'
SELECT  @ObPrefix = 'x'
--  Configuration end
IF @TABLENAME ='xyzzy'
  GOTO HelpText
ELSE
  BEGIN
    IF NOT EXISTS (SELECT * FROM sysobjects WHERE UPPER(name)=UPPER(@TABLENAME) and type='U')
      GOTO HelpText
    ELSE BEGIN
      SELECT @TN = name FROM sysobjects WHERE UPPER(name)=UPPER(@TABLENAME) and type='U'
      SELECT @RowCount=0
      DECLARE xArgs CURSOR FOR
        SELECT C.name FROM syscolumns C, sysobjects O
        WHERE C.id = O.id AND O.name = @TN
        ORDER BY C.colid
      OPEN    xArgs
      FETCH   xArgs INTO @ColName
      WHILE   @@FETCH_STATUS = 0
      BEGIN
        SELECT @RowCount=@RowCount+1
        FETCH  xArgs INTO @ColName
      END
      CLOSE      xArgs
      DEALLOCATE xArgs  
      SELECT @SpName=@SpPrefix + RTRIM(@TN) + '_Delete'
      SELECT @ColLength= MAX(DATALENGTH(RTRIM(CONVERT(VARCHAR(80),C.name)))) FROM syscolumns C, sysobjects O
        WHERE C.id = O.id AND O.name = @TN AND C.status=128
      IF EXISTS(select * from sysobjects where UPPER(name) = UPPER(@SpName))
        SELECT @PrintLine='ALTER  PROCEDURE dbo.' + @SpName
      ELSE
        SELECT @PrintLine='CREATE PROCEDURE dbo.' + @SpName   
      PRINT  @PrintLine
      PRINT  '('
      SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O, systypes U
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND U.name<>'timestamp' AND C.status=128
      SELECT @HasIdent=COUNT(*) FROM syscolumns C, sysobjects O WHERE C.id = O.id AND O.name = @TN AND C.status=128
      DECLARE xArgs CURSOR FOR
      SELECT '@' + C.name, U.name, ' (' + convert(varchar,C.length)  + ')'
        FROM syscolumns C, sysobjects O, systypes U
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND U.name<>'timestamp' AND C.status=128
        ORDER BY C.colid
      SELECT  @HasTSTAMP = (SELECT COUNT(*) FROM syscolumns C, sysobjects O
        WHERE C.id = O.id AND O.name = @TN AND (C.name='UpdateCount'))
      DECLARE @ArgName   VARCHAR(36),
              @Type      VARCHAR(32),
              @Length    VARCHAR(40)
      SELECT  @RowIndex=0
      OPEN    xArgs
      FETCH   xArgs INTO @ArgName, @Type, @Length
      WHILE   @@FETCH_STATUS=0
      BEGIN
        SELECT @Type = UPPER(@Type)
        SELECT @RowIndex=@RowIndex + 1
        SELECT @ArgName = SUBSTRING(@ArgName + '                              ',1,@ColLength + 4)
        SELECT @PrintLine = '  ' + @ArgName + @Type
        IF @Type='CHAR'
          SELECT @PrintLine=@PrintLine + RTRIM(@Length)
        IF @Type='VARCHAR'
          SELECT @PrintLine=@PrintLine + RTRIM(@Length)
        IF @Type='NCHAR'
          SELECT @PrintLine=@PrintLine + RTRIM(@Length)
        IF @Type='NVARCHAR'
          SELECT @PrintLine=@PrintLine + RTRIM(@Length)
        SELECT @PrintLine=@PrintLine + ','
        PRINT @PrintLine
        FETCH xArgs INTO @ArgName, @Type, @Length
      END
      CLOSE xArgs
      DEALLOCATE xArgs
      IF @HasTSTAMP > 0
        PRINT  '  @UpdatePersonID INT,'     
      PRINT  '  @RETURN         INT OUTPUT'
      PRINT  ') AS'
      PRINT  '--  Purpose:'
      SELECT @PrintLine='--     Delete record on ' + @TN + ' table'
      PRINT  @PrintLine
      PRINT  '--  Parameters:'
      DECLARE xArgs CURSOR FOR
      SELECT C.name 
        FROM syscolumns C, sysobjects O, systypes U
       WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype
         AND U.name<>'timestamp' AND C.status=128
        ORDER BY C.colid     
      OPEN  xArgs
      FETCH xArgs INTO @ArgName
      WHILE @@FETCH_status=0
      BEGIN
        SELECT @ArgName=SUBSTRING(@ArgName + '                            ',1,@ColLength + 2)
        SELECT @PrintLine='--    ' + @ArgName + '-'
        PRINT @PrintLine
        FETCH xArgs INTO @ArgName
      END
      CLOSE  xArgs
      DEALLOCATE xArgs  
      PRINT  '--    UpdatePersonID  - ID of Person updating row'          
      PRINT  '--    RETURN          - Zero or Error Code'     
      PRINT  '--  History:   '
      SELECT @USER = system_user
      IF SUBSTRING(@USER,1,DATALENGTH(@DOM_NAME)) = @DOM_NAME BEGIN
        SELECT @USER = RTRIM(SUBSTRING(@USER,DATALENGTH(@DOM_NAME) + 1,20))
      END      
      SELECT @DateWork=CONVERT(CHAR(11),CURRENT_TIMESTAMP,106)
      SELECT @DateWork=substring(@DateWork,1,2) + SUBSTRING(@DateWork,4,3) + SUBSTRING(@DateWork,8,4)
      SELECT @PrintLine='--  ' + @DateWork + ' '
      SELECT @PrintLine=@PrintLine + @USER + ' Original coding'
      PRINT  @PrintLine
      PRINT  'SET NOCOUNT ON'
      DECLARE @SetValue   VARCHAR(80)
      SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O, systypes U
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND C.status<>128 AND U.name<>'timestamp'
      SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O WHERE C.id = O.id AND O.name = @TN
      IF @RowCount = 0 BEGIN
        SELECT @PrintLine='DELETE ' + @TN + ' WHERE '
        PRINT  @PrintLine     
        PRINT  '-- You MUST amend the following line to correctly identify the row to delete'       
        PRINT  ' SOMECOLUMN = @SOMEVALUE'
        END
      ELSE BEGIN
        SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O WHERE C.id = O.id AND O.name = @TN
          AND C.status=128     
        PRINT  'BEGIN TRANSACTION'
        IF @HasTSTAMP > 0 BEGIN
          SELECT @PrintLine='UPDATE ' + @TN 
               + ' SET UpdateCount = -1, UpdatePersonID = @UpdatePersonID WHERE '       
          PRINT  @PrintLine  
          DECLARE xArgs CURSOR FOR
            SELECT  C.name + ' = @' + C.name 
            FROM syscolumns C, sysobjects O, systypes U
            WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND C.status=128
            ORDER BY C.colid
          SELECT @RowIndex=0
          OPEN  xArgs
          FETCH xArgs INTO @SetValue
          WHILE @@FETCH_STATUS=0
          BEGIN
            SELECT @RowIndex=@RowIndex + 1
            SELECT @PrintLine='  ' + @SetValue
            IF @RowIndex <> @RowCount  
              SELECT @PrintLine=@PrintLine + ' AND'
            PRINT @PrintLine
            FETCH xArgs INTO @SetValue
          END
          CLOSE      xArgs
          DEALLOCATE xArgs 
        END                
        SELECT @PrintLine='DELETE ' + @TN + ' WHERE '
        PRINT  @PrintLine 
        DECLARE xArgs CURSOR FOR
          SELECT  C.name + ' = @' + C.name 
          FROM syscolumns C, sysobjects O, systypes U
          WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND C.status=128
          ORDER BY C.colid
        SELECT @RowIndex=0
        OPEN  xArgs
        FETCH xArgs INTO @SetValue
        WHILE @@FETCH_STATUS=0
        BEGIN
          SELECT @RowIndex=@RowIndex + 1
          SELECT @PrintLine='  ' + @SetValue
          IF @RowIndex <> @RowCount  
            SELECT @PrintLine=@PrintLine + ' AND'
          PRINT @PrintLine
          FETCH xArgs INTO @SetValue
        END
        CLOSE      xArgs
        DEALLOCATE xArgs
      END
      PRINT  'COMMIT'
      PRINT  'SELECT @RETURN = @@error'
    END
  END
  PRINT  'RETURN'
  PRINT  '-------------- this is the end ----------------' 
  PRINT  '/* Data Access code follows:'
  PRINT  '    #region " Delete "'
  PRINT  '    /// <summary> '
  PRINT  '    /// Delete record on ' + @TN + ' table '
  PRINT  '    /// </summary>'
  PRINT  '    /// <remarks>'
  PRINT  '    /// This code was autogenerated on ' + @DateWork
  PRINT  '    /// </remarks>'
  PRINT  '    /// <param name="iID">TBA</param>'
  PRINT  '    /// <param name="iUpdatePersonID">Id of user from GetUserPersonId</param>'
  PRINT  '    /// <param name="iReturn">Return code to use in switch statement</param>' 
  PRINT  '    public void Delete' + @TN + '(int iID,  int iUpdatePersonID, ref DatabaseReturnCode iReturn)'
  PRINT  '    {'
  PRINT  '      CommonData ' + @ObPrefix + 'CommonData = new CommonData();'
  PRINT  '      try'
  PRINT  '      {'
  PRINT  '        iReturn = DatabaseReturnCode.NoErrorDetected;'
  PRINT  '        string sConnect = ' + @ObPrefix + 'CommonData.GetConnection();'
  PRINT  '        SqlConnection ' + @ObPrefix + 'SqlConnection = new SqlConnection(sConnect);'
  PRINT  '        SqlCommand ' + @ObPrefix + 'SqlCommand = new SqlCommand("' + @SpName + '", ' + @ObPrefix + 'SqlConnection);'
  PRINT  '        ' + @ObPrefix + 'SqlCommand.CommandType = CommandType.StoredProcedure;'
  PRINT  '        ' + @ObPrefix + 'SqlCommand.Parameters.Add("@ID", SqlDbType.Int);'
  PRINT  '        ' + @ObPrefix + 'SqlCommand.Parameters["@ID"].Value = iID; ;'
  PRINT  '        ' + @ObPrefix + 'SqlCommand.Parameters.Add("@UpdatePersonID", SqlDbType.Int);'
  PRINT  '        ' + @ObPrefix + 'SqlCommand.Parameters["@UpdatePersonID"].Value = iUpdatePersonID;'
  PRINT  '        ' + @ObPrefix + 'SqlCommand.Parameters.Add("@RETURN", SqlDbType.Int);'
  PRINT  '        ' + @ObPrefix + 'SqlCommand.Parameters["@RETURN"].Direction = ParameterDirection.Output;'
  PRINT  '        ' + @ObPrefix + 'SqlCommand.Connection.Open();'
  PRINT  '        ' + @ObPrefix + 'SqlCommand.ExecuteNonQuery();'
  PRINT  '        ' + @ObPrefix + 'SqlCommand.Connection.Close();'
  PRINT  '        iReturn = (DatabaseReturnCode)' + @ObPrefix + 'CommonData.NullToInteger(' + @ObPrefix + 'SqlCommand.Parameters["@RETURN"].Value);'
  PRINT  '        ' + @ObPrefix + 'SqlCommand.Dispose();'
  PRINT  '        ' + @ObPrefix + 'SqlConnection.Dispose();'
  PRINT  '      }'
  PRINT  '      catch (System.InvalidOperationException ex)'
  PRINT  '      {'
  PRINT  '        string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;'
  PRINT  '        ' + @ObPrefix + 'CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");'
  PRINT  '        iReturn = DatabaseReturnCode.NetworkError;'
  PRINT  '      }'
  PRINT  '      catch (Exception ex)'
  PRINT  '      {'
  PRINT  '        string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;'
  PRINT  '        ' + @ObPrefix + 'CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");'
  PRINT  '        iReturn = DatabaseReturnCode.OtherError;'
  PRINT  '      }'
  PRINT  '    }'
  PRINT  '    #endregion'
  PRINT  '*/'
  PRINT  'GO'
  --PRINT  'asp_dbPermissions'
  PRINT  @SET_DB
  PRINT  'GO'
RETURN
HelpText:
  PRINT '--Use sp_MakeDelete TABLENAME to generate update S.P. for TABLENAME'
  PRINT '--or  sp_MakeDelete garbage   to get this help text!'
RETURN
-------------- this is the end ----------------

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE sp_MakeInsert (@TABLENAME VARCHAR(255)='xyzzy')
-- Purpose:
--   Create Insert script from table definition
-- Copyright (C) 2000, 2003, 2004, 2005 Clive Chinery
--
-- This library is free software; you can redistribute it and/or
-- modify it under the terms of the GNU Lesser General Public
-- License as published by the Free Software Foundation; either
-- version 2.1 of the License, or (at your option) any later version.
--
-- This library is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
-- Lesser General Public License for more details.
--
-- You should have received a copy of the GNU Lesser General Public
-- License along with this library; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
-- Notes:
--   1) Run from any database on this server by entering cp_MakeInsert TABLENAME
--   2) The TABLENAME is case insensitive, however the table must exist
--   3) ID and TIMESTAMP columns are bypassed
-- Changes:
-- 09Oct2005 Clive Update documentation
-- 01Sep2005 Clive Extend to cover more data types
-- 09Jun2005 Clive Make c# variant of vb.net
-- 12Feb2005 Clive MAKE CHANGES FOR AUTOGENERATION
-- 16Oct2000 Clive Produce ALTER if uap_ already exists
-- 14Oct2000 Clive Tidied output
-- 14Sep2000 Clive Add missing  'SET NOCOUNT ON' Print
-- 31Jul2000 Clive Modified to put more of documentation header
-- 17May2000 Clive rewrote
AS
SET NOCOUNT ON
DECLARE @ColName    VARCHAR(40)
DECLARE @ColLength  INT
DECLARE @ColLength1 INT
DECLARE @DateWork   VARCHAR(16)
DECLARE @HasIdent   INT          -- 1 if table has an identity column, 0 if not
DECLARE @PrintLine  VARCHAR(132)
DECLARE @RowCount   INT
DECLARE @RowIndex   INT
DECLARE @SpName     VARCHAR(32)
DECLARE @TN         VARCHAR(32)
DECLARE @USER       VARCHAR(32)
DECLARE @ArgName    VARCHAR(40)
DECLARE @Type       VARCHAR(32)
DECLARE @Length     INT
DECLARE @COL_NAME   VARCHAR(36)
DECLARE @HAS_LAST   BIT
DECLARE @colid      INT
DECLARE @xprec      INT
DECLARE @xscale     INT
DECLARE @DOM_NAME   VARCHAR(50)
DECLARE @SpPrefix   VARCHAR(10)
DECLARE @SET_DB     VARCHAR(50)
DECLARE @ObPrefix   VARCHAR(10)
--  Configuration start
SELECT  @SpPrefix = 'usp_'
SELECT  @DOM_NAME = 'EDIPLC' + '\'
SELECT  @SET_DB   = 'asp_dbPermissions'
SELECT  @ObPrefix = 'x'
--  Configuration end
IF @TABLENAME ='xyzzy'
  GOTO HelpText
ELSE
  BEGIN
    IF NOT EXISTS (SELECT * FROM sysobjects WHERE UPPER(name)= UPPER(@TABLENAME) and type = 'U') 
      GOTO HelpText
    ELSE BEGIN
      CREATE TABLE #IDCOLS (ColName VARCHAR(32) NOT NULL)
      SELECT  @TN = name FROM sysobjects WHERE UPPER(name)= UPPER(@TABLENAME) and type = 'U'
      SELECT  @RowCount=1     
      SELECT  @ColLength = MAX(DATALENGTH(RTRIM(CONVERT(VARCHAR(80),C.name))))
        FROM syscolumns C, sysobjects O
        WHERE C.id = O.id AND O.name = @TN AND C.status<>128
      SELECT  @ColLength1 = @ColLength + 1
      SELECT  @ColName = INDEX_COL(@TN,1,@RowCount)
      WHILE   @ColName IS NOT NULL BEGIN
        INSERT INTO #IDCOLS (ColName) VALUES (@ColName)
        SELECT @RowCount=@RowCount+1
        SELECT @ColName = INDEX_COL(@TN,1,@RowCount)
      END
      SELECT  @SpName = @SpPrefix + RTRIM(@TN) + '_Insert'
      IF EXISTS(select * from sysobjects where UPPER(name) = UPPER(@SpName))
        SELECT @PrintLine='ALTER  PROCEDURE dbo.' + @SpName
      ELSE
        SELECT @PrintLine='CREATE PROCEDURE dbo.' + @SpName   
      PRINT   @PrintLine
      PRINT   '('
      SELECT @HasIdent=COUNT(*) FROM syscolumns C, sysobjects O WHERE C.id = O.id AND O.name = @TN AND C.status=128
      SELECT  @HAS_LAST = 0
      SELECT  @RowIndex = 0
      SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O, systypes U
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype  AND C.status<>128 AND U.name<>'timestamp'
          AND C.name NOT IN ('CreateDate','UpdateDate','UpdatePersonID','UpdateCount')
      SELECT  @HasIdent=COUNT(*) FROM syscolumns C, sysobjects O WHERE C.id = O.id AND O.name = @TN AND C.status=128    
      DECLARE xArgs CURSOR FOR
      SELECT  C.name, U.name, C.length, C.xprec, C.xscale
        FROM syscolumns C, sysobjects O, systypes U 
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND C.status<>128 AND U.name<>'timestamp'
          AND C.name NOT IN ('CreateDate','UpdateDate','UpdatePersonID','UpdateCount')
        ORDER BY C.colid
      SELECT  @RowIndex=0
      OPEN    xArgs
      FETCH   xArgs INTO @ArgName, @Type, @Length, @xprec, @xscale
      WHILE   @@FETCH_status=0
      BEGIN -- '@' +
        SELECT @RowIndex=@RowIndex + 1
        SELECT @ArgName=SUBSTRING(@ArgName + '                            ',1,@ColLength + 2)
        SELECT @PrintLine='  @' + @ArgName + @Type
        IF @Type='char'
          SELECT @PrintLine=@PrintLine + ' (' + RTRIM(convert(varchar,@Length))  + ')'
        IF @Type='varchar'
          SELECT @PrintLine=@PrintLine + ' (' + RTRIM(convert(varchar,@Length))  + ')'
        IF @Type='nchar'
          SELECT @PrintLine=@PrintLine + ' (' + RTRIM(convert(varchar,@Length/2))  + ')'
        IF @Type='nvarchar'
          SELECT @PrintLine=@PrintLine + ' (' + RTRIM(convert(varchar,@Length/2))  + ')'
        IF (@Type='decimal') OR (@Type='numeric')
          SELECT @PrintLine=@PrintLine + ' (' + RTRIM(convert(varchar,@xprec))
                 + ','  + RTRIM(convert(varchar,@xscale)) + ')'                     
        SELECT @PrintLine=@PrintLine + ','
        PRINT @PrintLine
        FETCH xArgs INTO @ArgName, @Type, @Length, @xprec, @xscale
      END
      CLOSE  xArgs
      DEALLOCATE xArgs
      IF @HasIdent = 1 BEGIN
        PRINT   '  @RETURN         INT           OUTPUT,'
        PRINT   '  @IDENTITY       INT           OUTPUT'
        END
      ELSE BEGIN
        PRINT   '  @RETURN         INT           OUTPUT'     
        END
      PRINT   ') AS'
      PRINT   '--  Purpose:'
      SELECT  @PrintLine='--    Insert record into ' + @TN + ' table'
      PRINT   @PrintLine
      PRINT   '--  Parameters:'
      DECLARE xArgs CURSOR FOR
      SELECT CONVERT(VARCHAR(32), C.name)
        FROM syscolumns C, sysobjects O, systypes U
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND C.status<>128 AND U.name<>'timestamp'
          AND C.name NOT IN ('CreateDate','UpdateDate','UpdatePersonID','UpdateCount')     
        ORDER BY C.colid     
      OPEN  xArgs
      FETCH xArgs INTO @ArgName
      WHILE @@FETCH_status=0
      BEGIN
        SELECT @ArgName = @ArgName + '                               '
        SELECT @PrintLine='--    ' + SUBSTRING(@ArgName,1,@ColLength1) + '-'
        PRINT  @PrintLine
        FETCH  xArgs INTO @ArgName
      END
      CLOSE  xArgs
      DEALLOCATE xArgs  
      PRINT  '--    RETURN         - Zero or Error Code'
      IF @HasIdent <> 0
        PRINT  '--    IDENTITY       - Identity of inserted row'     
      PRINT  '--  History:   '
      SELECT @USER = system_user
      IF SUBSTRING(@USER,1,DATALENGTH(@DOM_NAME)) = @DOM_NAME BEGIN
        SELECT @USER = RTRIM(SUBSTRING(@USER,DATALENGTH(@DOM_NAME) + 1,20))
      END       
      SELECT @DateWork=CONVERT(CHAR(11),CURRENT_TIMESTAMP,106)
      SELECT @DateWork=substring(@DateWork,1,2) + SUBSTRING(@DateWork,4,3) + SUBSTRING(@DateWork,8,4)
      SELECT @PrintLine='--  ' + @DateWork + ' '
      SELECT @PrintLine=@PrintLine + @USER + ' Original coding'
      PRINT  @PrintLine
      PRINT  'SET NOCOUNT ON'
      SELECT @PrintLine='INSERT INTO ' + @TN  + '('
      PRINT  @PrintLine
      DECLARE @SetValue   VARCHAR(80)
      DECLARE xArgs CURSOR FOR
        SELECT  C.name
        FROM syscolumns C, sysobjects O, systypes U
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND C.status<>128 AND U.name<>'timestamp'
         AND C.name NOT IN ('CreateDate','UpdateDate','UpdatePersonID','UpdateCount')
        ORDER BY C.colid
      SELECT @RowIndex=0
      OPEN  xArgs
      FETCH xArgs INTO @SetValue
      WHILE @@FETCH_STATUS=0
      BEGIN
        SELECT @RowIndex =@RowIndex + 1
        SELECT @PrintLine='  ' + @SetValue
        ---------
        IF @RowIndex <> @RowCount  
          SELECT @PrintLine=@PrintLine + ','
        ELSE
          SELECT @PrintLine=@PrintLine + ')'
        PRINT @PrintLine
        FETCH xArgs INTO @SetValue
      END
      CLOSE      xArgs
      DEALLOCATE xArgs
      PRINT 'VALUES ('
      DECLARE xArgs CURSOR FOR
        SELECT  C.name 
        FROM syscolumns C, sysobjects O, systypes U
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND C.status<>128 AND U.name<>'timestamp'
          AND C.name NOT IN ('CreateDate','UpdateDate','UpdatePersonID','UpdateCount')
        ORDER BY C.colid
      SELECT @RowIndex=0
      OPEN   xArgs
      FETCH  xArgs INTO @SetValue
      WHILE  @@FETCH_STATUS = 0
      BEGIN
        SELECT @RowIndex=@RowIndex + 1
        SELECT @PrintLine='  @' + @SetValue
        IF @RowIndex <> @RowCount  
          SELECT @PrintLine=@PrintLine + ','
        ELSE
          SELECT @PrintLine=@PrintLine + ')'
        PRINT @PrintLine
        FETCH xArgs INTO @SetValue
      END
      CLOSE      xArgs
      DEALLOCATE xArgs
      -- PRINT  ''
      IF @HasIdent = 0
        PRINT 'SELECT @RETURN = @@error'
      ELSE
        PRINT 'SELECT @RETURN = @@error,  @IDENTITY = SCOPE_IDENTITY()  '
      DROP TABLE #IDCOLS
    END
  END
PRINT  'RETURN'
PRINT  '-------------- this is the end ----------------' 
PRINT  '/* Data Access code follows:'
DECLARE @SqlDbType VARCHAR(20)
SELECT  @RowIndex = 0
PRINT  '    #region " Insert "'
PRINT  '    /// <summary> '
PRINT  '    /// Insert record into ' + @TN + ' table '
PRINT  '    /// </summary>'
PRINT  '    /// <remarks>'
PRINT  '    /// This code was autogenerated on ' + @DateWork
PRINT  '    /// </remarks>'
DECLARE @COMMENT VARCHAR(50)
DECLARE xArgs CURSOR FOR
SELECT  COALESCE(CONVERT(VARCHAR(130),P.value), '[Undocumented in Database]') AS COMMENT,
  C.name, U.name, C.length, C.xprec, C.xscale
  FROM syscolumns C, sysobjects O, systypes U, SYSPROPERTIES P
  WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND U.name<>'timestamp'
    AND C.name NOT IN ('ID','REPLICASTAMP','UpdatePersonID','CreateDate','UpdateDate','UpdateCount')
    AND P.NAME = 'MS_Description' AND C.COLID *= P.smallid AND C.id *= P.ID
  ORDER BY C.colid
OPEN    xArgs
FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, @xprec, @xscale
WHILE @@FETCH_STATUS=0
BEGIN
  SELECT @PrintLine = '    /// <param name="'
  SELECT @SqlDbType = 'x'  -- Default assignment
  IF @Type='bigint'     SELECT @SqlDbType = 'l'
  IF @Type='bit'        SELECT @SqlDbType = 'b'
  IF @Type='int'        SELECT @SqlDbType = 'i'
  IF @Type='decimal'    SELECT @SqlDbType = 'c'
  IF @Type='smallmoney' SELECT @SqlDbType = 'c'
  IF @Type='numeric'    SELECT @SqlDbType = 'c'
  IF @Type='datetime'   SELECT @SqlDbType = 'dat'
  IF @Type='char'       SELECT @SqlDbType = 's'
  IF @Type='varchar'    SELECT @SqlDbType = 's'
  IF @Type='nchar'      SELECT @SqlDbType = 's'
  IF @Type='nvarchar'   SELECT @SqlDbType = 's'
  IF @Type='text'       SELECT @SqlDbType = 's'
  IF @Type='ntext'      SELECT @SqlDbType = 's'
  --SELECT @COMMENT = 'TBA'   
  IF @COL_NAME = 'CreatePersonID' SELECT @COMMENT = 'Id of user from GetUserPersonId'
  -- More comment signs here ?
  SELECT @PrintLine = @PrintLine + @SqlDbType + @COL_NAME + '">' + @COMMENT + '</param>'  
  PRINT @PrintLine
  FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, @xprec, @xscale
END
CLOSE      xArgs
DEALLOCATE xArgs
PRINT  '    /// <param name="iReturn">Return code to use in switch statement</param>'
PRINT  '    /// <param name="iNewID">Id of new record</param>'
PRINT  '    public void Insert' + @TN + '('
SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O WHERE C.id = O.id AND O.name = @TN
      AND C.name NOT IN ('REPLICASTAMP','UpdatePersonID','CreateDate','UpdateDate','UpdateCount')
DECLARE xArgs CURSOR FOR
SELECT C.name, U.name, C.length, C.xprec, C.xscale
  FROM syscolumns C, sysobjects O, systypes U
  WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND U.name<>'timestamp'
    AND C.name NOT IN ('REPLICASTAMP','UpdatePersonID','CreateDate','UpdateDate','UpdateCount')
  ORDER BY C.colid
OPEN    xArgs
FETCH   xArgs INTO @COL_NAME, @Type, @Length, @xprec, @xscale
WHILE @@FETCH_STATUS=0
BEGIN
  SELECT @RowIndex = @RowIndex + 1
  IF @RowIndex > 1 BEGIN
    SELECT @PrintLine = '      '
    SELECT @SqlDbType = @Type  -- Default assignment
    IF @Type='bigint'   SELECT @SqlDbType = 'Int64'
    IF @Type='bit'      SELECT @SqlDbType = 'bool'
    IF @Type='int'      SELECT @SqlDbType = 'int'
    IF @Type='decimal'  SELECT @SqlDbType = 'decimal'
    IF @Type='numeric'  SELECT @SqlDbType = 'decimal'
    IF @Type='datetime' SELECT @SqlDbType = 'DateTime'
    IF @Type='char'     SELECT @SqlDbType = 'string'
    IF @Type='varchar'  SELECT @SqlDbType = 'string'
    IF @Type='nchar'    SELECT @SqlDbType = 'string'
    IF @Type='nvarchar' SELECT @SqlDbType = 'string'
    IF @Type='money'    SELECT @SqlDbType = 'decimal'
    IF @Type='ntext'    SELECT @SqlDbType = 'string'
    IF @Type='text'     SELECT @SqlDbType = 'string'
    IF @Type='real'     SELECT @SqlDbType = 'float'
    IF @Type='smalldatetime' SELECT @SqlDbType = 'DateTime'
    IF @Type='smallint'      SELECT @SqlDbType = 'Int16'
    IF @Type='smallmoney'    SELECT @SqlDbType = 'decimal'
    SELECT @PrintLine = @PrintLine + @SqlDbType + ' '   
    SELECT @SqlDbType = 'x'  -- Default assignment
    IF @Type='bigint'     SELECT @SqlDbType = 'l'
    IF @Type='bit'        SELECT @SqlDbType = 'b'
    IF @Type='int'        SELECT @SqlDbType = 'i'
    IF @Type='decimal'    SELECT @SqlDbType = 'c'
    IF @Type='smallmoney' SELECT @SqlDbType = 'c'
    IF @Type='numeric'    SELECT @SqlDbType = 'c'
    IF @Type='datetime'   SELECT @SqlDbType = 'dat'
    IF @Type='char'       SELECT @SqlDbType = 's'
    IF @Type='varchar'    SELECT @SqlDbType = 's'
    IF @Type='nchar'      SELECT @SqlDbType = 's'
    IF @Type='nvarchar'   SELECT @SqlDbType = 's'
    IF @Type='text'       SELECT @SqlDbType = 's'
    IF @Type='ntext'      SELECT @SqlDbType = 's'
    SELECT @PrintLine = @PrintLine + @SqlDbType + @COL_NAME
    IF @RowIndex = @RowCount
      SELECT @PrintLine = @PrintLine + ', ref DatabaseReturnCode iReturn, ref int iNewID)'
    ELSE
      SELECT @PrintLine = @PrintLine + ', '
    PRINT @PrintLine
    --PRINT '@RowIndex=' + convert(varchar(10), @RowIndex) + ' @RowCount=' + convert(varchar(10), @RowCount)
  END
  FETCH   xArgs INTO @COL_NAME, @Type, @Length, @xprec, @xscale
END
CLOSE      xArgs
DEALLOCATE xArgs
PRINT  '    {'
PRINT  '      CommonData ' + @ObPrefix + 'CommonData = new CommonData();'
PRINT  '      try'
PRINT  '      {'
PRINT  '        iReturn = DatabaseReturnCode.NoErrorDetected;'
PRINT  '        iNewID = 0;'
PRINT  '        string sConnect = ' + @ObPrefix + 'CommonData.GetConnection();'
PRINT  '        SqlConnection ' + @ObPrefix + 'SqlConnection = new SqlConnection(sConnect);'
PRINT  '        SqlCommand ' + @ObPrefix + 'SqlCommand = new SqlCommand("' + @SpName + '", ' + @ObPrefix + 'SqlConnection);'
PRINT  '        ' + @ObPrefix + 'SqlCommand.CommandType = CommandType.StoredProcedure;'
SELECT @RowIndex = 0
DECLARE xArgs CURSOR FOR
SELECT DISTINCT  C.name, U.name, C.length, C.colid, C.xprec, C.xscale
  FROM syscolumns C, sysobjects O, systypes U
  WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND U.name<>'timestamp'
    AND C.name NOT IN ('REPLICASTAMP','UpdatePersonID','CreateDate','UpdateDate','UpdateCount')
  ORDER BY C.colid, C.name, U.name, C.length
OPEN  xArgs
FETCH   xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscale
WHILE @@FETCH_STATUS=0
BEGIN
  SELECT @RowIndex=@RowIndex + 1
  IF @RowIndex > 1 BEGIN
    SELECT @PrintLine='        ' + @ObPrefix + 'SqlCommand.Parameters.Add("@' + @COL_NAME + '", SqlDbType.'
    SELECT @SqlDbType = @Type  -- Default assignment
    IF @Type='bigint'   SELECT @SqlDbType = 'BigInt'
    IF @Type='bit'      SELECT @SqlDbType = 'Bit'  
    IF @Type='int'      SELECT @SqlDbType = 'Int'
    IF @Type='decimal'  SELECT @SqlDbType = 'Decimal'
    IF @Type='numeric'  SELECT @SqlDbType = 'Decimal'
    IF @Type='datetime' SELECT @SqlDbType = 'DateTime'
    IF @Type='char'     SELECT @SqlDbType = 'Char'
    IF @Type='varchar'  SELECT @SqlDbType = 'VarChar' 
    IF @Type='nchar'    SELECT @SqlDbType = 'NChar'
    IF @Type='nvarchar' SELECT @SqlDbType = 'NVarChar'
    IF @Type='ntext'    SELECT @SqlDbType = 'NText'
    IF @Type='money'    SELECT @SqlDbType = 'Money'
    IF @Type='float'    SELECT @SqlDbType = 'Float'
    IF @Type='text'     SELECT @SqlDbType = 'Text'
    IF @Type='real'          SELECT @SqlDbType = 'Real'
    IF @Type='smalldatetime' SELECT @SqlDbType = 'SmallDateTime'
    IF @Type='smallint'         SELECT @SqlDbType = 'SmallInt'
    IF @Type='smallmoney'    SELECT @SqlDbType = 'SmallMoney'
    -- more type conversions here !
    SELECT @PrintLine=@PrintLine + @SqlDbType
    IF @Type='char'     SELECT @PrintLine=@PrintLine + ', ' + RTRIM(convert(varchar,@Length)) 
    IF @Type='varchar'  SELECT @PrintLine=@PrintLine + ', ' + RTRIM(convert(varchar,@Length))
    IF @Type='nchar'    SELECT @PrintLine=@PrintLine + ', ' + RTRIM(convert(varchar,@Length/2))
    IF @Type='nvarchar' SELECT @PrintLine=@PrintLine + ', ' + RTRIM(convert(varchar,@Length/2))
    SELECT @PrintLine=@PrintLine + ');'
    PRINT  @PrintLine   
    IF (@Type='decimal') OR (@Type='numeric') BEGIN
      SELECT @PrintLine='        ' + @ObPrefix + 'SqlCommand.Parameters["@' + @COL_NAME + '"].Precision = ' + CONVERT(VARCHAR(3), @xprec) + ';'
      PRINT @PrintLine
      SELECT @PrintLine='        ' + @ObPrefix + 'SqlCommand.Parameters["@' + @COL_NAME + '"].Scale = ' + CONVERT(VARCHAR(3), @xscale) + ';'
      PRINT @PrintLine
    END   
    SELECT @PrintLine='        ' + @ObPrefix + 'SqlCommand.Parameters["@' + @COL_NAME + '"].Value = '   
    SELECT @SqlDbType = 'x'  -- Default assignment
    IF @Type='bigint'     SELECT @SqlDbType = 'l'
    IF @Type='bit'        SELECT @SqlDbType = 'b'
    IF @Type='int'        SELECT @SqlDbType = 'i'
    IF @Type='smallint'   SELECT @SqlDbType = 'i'
    IF @Type='decimal'    SELECT @SqlDbType = 'c'
    IF @Type='numeric'    SELECT @SqlDbType = 'c'
    IF @Type='datetime'   SELECT @SqlDbType = 'dat'
    IF @Type='char'       SELECT @SqlDbType = 's'
    IF @Type='varchar'    SELECT @SqlDbType = 's'
    IF @Type='nchar'      SELECT @SqlDbType = 's'
    IF @Type='nvarchar'   SELECT @SqlDbType = 's'
    IF @Type='text'       SELECT @SqlDbType = 's'
    IF @Type='ntext'      SELECT @SqlDbType = 's'
    IF @Type='smallmoney' SELECT @SqlDbType = 'c'
    IF @SqlDbType = 'c' BEGIN
      SELECT @PrintLine = @PrintLine + '' + @ObPrefix + 'CommonData.DecimalAdjust('
      END
    SELECT @PrintLine = @PrintLine +  @SqlDbType + @COL_NAME
    IF @SqlDbType = 'c' BEGIN
      SELECT @PrintLine = @PrintLine + ', ' + RTRIM(convert(varchar(10),@xscale)) + ')'
      END
    PRINT  @PrintLine + ';'
    END
  FETCH   xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscale
END
CLOSE      xArgs
DEALLOCATE xArgs
PRINT  '        ' + @ObPrefix + 'SqlCommand.Parameters.Add("@RETURN", SqlDbType.Int);'
PRINT  '        ' + @ObPrefix + 'SqlCommand.Parameters["@RETURN"].Direction = ParameterDirection.Output;'
PRINT  '        ' + @ObPrefix + 'SqlCommand.Parameters.Add("@IDENTITY", SqlDbType.Int);'
PRINT  '        ' + @ObPrefix + 'SqlCommand.Parameters["@IDENTITY"].Direction = ParameterDirection.Output;'
PRINT  '        ' + @ObPrefix + 'SqlCommand.Connection.Open();'
PRINT  '        ' + @ObPrefix + 'SqlCommand.ExecuteNonQuery();'
PRINT  '        ' + @ObPrefix + 'SqlCommand.Connection.Close();'
PRINT  '        iReturn = (DatabaseReturnCode)' + @ObPrefix + 'CommonData.NullToInteger(' + @ObPrefix + 'SqlCommand.Parameters["@RETURN"].Value);'
PRINT  '        iNewID = ' + @ObPrefix + 'CommonData.NullToInteger(' + @ObPrefix + 'SqlCommand.Parameters["@IDENTITY"].Value);'
PRINT  '        ' + @ObPrefix + 'SqlCommand.Dispose();'
PRINT  '        ' + @ObPrefix + 'SqlConnection.Dispose();'
PRINT  '      }'
PRINT  '      catch (System.InvalidOperationException ex)'
PRINT  '      {'
PRINT  '        string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;'
PRINT  '        ' + @ObPrefix + 'CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");'
PRINT  '        iReturn = DatabaseReturnCode.NetworkError;'
PRINT  '      }'
PRINT  '      catch (Exception ex)'
PRINT  '      {'
PRINT  '        string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;'
PRINT  '        ' + @ObPrefix + 'CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");'
PRINT  '        iReturn = DatabaseReturnCode.OtherError;'
PRINT  '      }'
PRINT  '    }'
PRINT  '    #endregion'
PRINT  '*/'
PRINT  'GO'
--PRINT  'asp_dbPermissions'
PRINT  @SET_DB
PRINT  'GO'
RETURN
HelpText:
  PRINT '--Use sp_MakeInsert TABLENAME to generate insert S.P. for TABLENAME'
  PRINT '--or  sp_MakeInsert garbage   to get this help text!'
RETURN
-------------- this is the end ----------------

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE Procedure sp_MakeListAll
  (@TABLENAME VARCHAR(255)='xyzzy')
-- Purpose:
--   Create List script from table definition
-- Copyright (C) 2000, 2003, 2004 Clive Chinery
--
-- This library is free software; you can redistribute it and/or
-- modify it under the terms of the GNU Lesser General Public
-- License as published by the Free Software Foundation; either
-- version 2.1 of the License, or (at your option) any later version.
--
-- This library is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
-- Lesser General Public License for more details.
--
-- You should have received a copy of the GNU Lesser General Public
-- License along with this library; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
-- Notes:
--   1) Run from any database on this server by entering sp_MakeSelect TABLENAME
--   2) The TABLENAME is case insensitive, however the table must exist
--   3) ID and TIMESTAMP columns are specially handled
--   4) Tables with ID column will have a where clause using the ID column - this normally suffices.
-- Changes:
-- 14Oct2000 Clive - Original coding
AS
DECLARE @ColName    VARCHAR(32)
DECLARE @ColLength  INT
DECLARE @COUNT_ROW  INT
DECLARE @HasIdent   INT
DECLARE @RowCount   INT
DECLARE @RowIndex   INT
DECLARE @PrintLine  VARCHAR(132)
DECLARE @SpName     VARCHAR(32)
DECLARE @SpUpper    VARCHAR(32)
DECLARE @DateWork   VARCHAR(16)
DECLARE @TN         VARCHAR(32)
DECLARE @SetValue   VARCHAR(80)
DECLARE @USER       VARCHAR(32)
DECLARE @DOM_NAME   VARCHAR(50)
DECLARE @SpPrefix   VARCHAR(10)
DECLARE @SET_DB     VARCHAR(50)
DECLARE @ObPrefix   VARCHAR(10)
--  Configuration start
SELECT  @SpPrefix = 'usp_'
SELECT  @DOM_NAME = 'EDIPLC' + '\'
SELECT  @SET_DB   = 'asp_dbPermissions'
SELECT  @ObPrefix = 'x'
--  Configuration end
IF @TABLENAME ='xyzzy'
  GOTO HelpText
ELSE
  BEGIN
    IF NOT EXISTS (SELECT * FROM sysobjects WHERE UPPER(name)=UPPER(@TABLENAME) and type='U')
      GOTO HelpText
    ELSE BEGIN
      SELECT @TN = name FROM sysobjects WHERE UPPER(name)=UPPER(@TABLENAME) and type='U'
      SELECT @RowCount=0
      DECLARE xArgs CURSOR FOR
        SELECT C.name FROM syscolumns C, sysobjects O
        WHERE C.id = O.id AND O.name = @TN
        ORDER BY C.colid
      OPEN    xArgs
      FETCH   xArgs INTO @ColName
      WHILE   @@FETCH_STATUS=0
      BEGIN
        SELECT @RowCount=@RowCount+1
        FETCH  xArgs INTO @ColName
      END
      CLOSE      xArgs
      DEALLOCATE xArgs  
      SELECT @SpName = @SpPrefix + RTRIM(@TN) + '_ListAll'
      SELECT @ColLength= MAX(DATALENGTH(C.name)) FROM syscolumns C, sysobjects O
        WHERE C.id = O.id AND O.name = @TN AND C.status<>128
      SELECT @SpUpper = UPPER(@SpName)
      IF EXISTS(select * from sysobjects where (UPPER(name) = @SpUpper) AND (sysstat & 0xf = 4))
        SELECT @PrintLine='ALTER  PROCEDURE dbo.' + @SpName + ' AS'
      ELSE
        SELECT @PrintLine='CREATE PROCEDURE dbo.' + @SpName + ' AS'   
      PRINT  @PrintLine
      SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O, systypes U
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND U.name<>'timestamp' AND C.status=128 
      SELECT @HasIdent=COUNT(*) FROM syscolumns C, sysobjects O WHERE C.id = O.id AND O.name = @TN AND C.status=128
      DECLARE @ArgName   VARCHAR(36),
              @Type      VARCHAR(32),
              @Length    VARCHAR(40)
      SELECT  @RowIndex=0
      PRINT '--  Purpose:'
      SELECT @PrintLine='--    Select list of all on ' + @TN + ' table'
      PRINT  @PrintLine  
      --PRINT  '--  Resultset:'
      --PRINT  '--  '    
      PRINT  '--  History:   '
      SELECT @USER = system_user
      IF SUBSTRING(@USER,1,DATALENGTH(@DOM_NAME)) = @DOM_NAME BEGIN
        SELECT @USER = RTRIM(SUBSTRING(@USER,DATALENGTH(@DOM_NAME) + 1,20))
      END 
      SELECT @DateWork=CONVERT(CHAR(11),CURRENT_TIMESTAMP,106)
      SELECT @DateWork=substring(@DateWork,1,2) + SUBSTRING(@DateWork,4,3) + SUBSTRING(@DateWork,8,4)
      SELECT @PrintLine='--  ' + @DateWork + ' '     
      SELECT @PrintLine=@PrintLine + @USER + ' Original coding'
      PRINT  @PrintLine
      PRINT  'SET NOCOUNT ON'     
      SELECT @PrintLine='SELECT '
      PRINT  @PrintLine    
      SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O, systypes U
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND U.name<>'timestamp'
        AND C.NAME NOT IN ('ADDDATE','MODDATE','DELDATE','TSTAMP','LAST_PERSON_ID')      
      DECLARE xArgs CURSOR FOR
        SELECT  C.name FROM syscolumns C, sysobjects O, systypes U
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND U.name<>'timestamp'
        AND C.NAME NOT IN ('ADDDATE','MODDATE','DELDATE','TSTAMP','LAST_PERSON_ID')
        ORDER BY C.colid
      SELECT @RowIndex=0
      OPEN   xArgs
      FETCH  xArgs INTO @ArgName
    WHILE  @@FETCH_STATUS=0
      BEGIN
        SELECT @RowIndex=@RowIndex + 1
        SELECT @ArgName=SUBSTRING(@ArgName + '                              ',1,@ColLength+2)
        SELECT @PrintLine = '  ' + RTRIM(@ArgName)
        IF @RowIndex <> @RowCount  
          SELECT @PrintLine=@PrintLine + ','
        PRINT @PrintLine
        FETCH xArgs INTO @ArgName
      END
      CLOSE      xArgs
      DEALLOCATE xArgs
      SELECT @PrintLine='  FROM ' + @TN
      PRINT  @PrintLine       
      SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O WHERE C.id = O.id AND O.name = @TN
      IF @RowCount = 0 BEGIN
        PRINT  '-- You MUST amend the following line to correctly identify the row to order by'
        PRINT  'ORDER BY SOMECOLUMN'
        END
      ELSE BEGIN
        PRINT  'ORDER BY '
        SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O WHERE C.id = O.id AND O.name = @TN
          AND C.status=128
        DECLARE xArgs CURSOR FOR
          SELECT  C.name 
          FROM syscolumns C, sysobjects O, systypes U
          WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND C.status=128
          ORDER BY C.colid
        SELECT @RowIndex=0
        OPEN  xArgs
        FETCH xArgs INTO @SetValue
        WHILE @@FETCH_STATUS=0
        BEGIN
          SELECT @RowIndex=@RowIndex + 1
          SELECT @PrintLine='  ' + @SetValue
          IF @RowIndex <> @RowCount  
            SELECT @PrintLine=@PrintLine + ' AND'
          PRINT @PrintLine
          FETCH xArgs INTO @SetValue
        END
        CLOSE      xArgs
        DEALLOCATE xArgs
      END
    END
  END
PRINT  'RETURN'
PRINT  '----------------- this is the end --------------'
PRINT  '/* Data Access code follows:'
PRINT  '    #region " List "'
PRINT  '    /// <summary> '
PRINT  '    /// Select list of all on ' + @TN + ' table '
PRINT  '    /// </summary>'
PRINT  '    /// <remarks>'
PRINT  '    /// This code was autogenerated on ' + @DateWork
PRINT  '    /// </remarks>'
PRINT  '    /// <returns>dataset of ' + @TN + ' </returns>'
PRINT  '    public DataSet List' + @TN + 'All()'
PRINT  '    {'
PRINT  '      CommonData ' + @ObPrefix + 'CommonData = new CommonData();'
PRINT  '      return ' + @ObPrefix + 'CommonData.GetDatasetOwn("' + @SpName + '");'
PRINT  '    }'
PRINT  '    #endregion'
PRINT  '*/'
PRINT  'GO'
--PRINT  'asp_dbPermissions'
PRINT  @SET_DB
PRINT  'GO'
RETURN
HelpText:
  PRINT '--Use sp_MakeListAll TABLENAME to generate select S.P. for TABLENAME'
  PRINT '--or  sp_MakeListAll garbage   to get this help text!'
RETURN
----------------- this is the end --------------

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE Procedure sp_MakeSelect
  (@TABLENAME VARCHAR(255)='xyzzy')
-- Purpose:
--   Create Select script from table definition
-- Copyright (C) 2000, 2003, 2004, 2005 Clive Chinery
--
-- This library is free software; you can redistribute it and/or
-- modify it under the terms of the GNU Lesser General Public
-- License as published by the Free Software Foundation; either
-- version 2.1 of the License, or (at your option) any later version.
--
-- This library is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
-- Lesser General Public License for more details.
--
-- You should have received a copy of the GNU Lesser General Public
-- License along with this library; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
-- Notes:
--   1) Run from any database on this server by entering cp_MakeSelect TABLENAME
--   2) The TABLENAME is case insensitive, however the table must exist
--   3) ID and TIMESTAMP columns are specially handled
--   4) Tables with ID column will have a where clause using the ID column - this normally suffices.
-- Changes:
-- 09Oct2005 Clive Update documentation
-- 19Sep2005 Clive Extend to output XML documentation
-- 01Sep2005 Clive Extend to cover more data types
-- 09Jun2005 Clive Make c# variant of vb.net
-- 14Oct2000 Clive - Original coding
AS
DECLARE @colid      INT
DECLARE @ColName    VARCHAR(80)
DECLARE @ColLength  INT
DECLARE @COUNT_ROW  INT
DECLARE @HasIdent   INT
DECLARE @RowCount   INT
DECLARE @RowIndex   INT
DECLARE @PrintLine  VARCHAR(256)
DECLARE @SpName     VARCHAR(80)
DECLARE @HelperName VARCHAR(80)
DECLARE @SpUpper    VARCHAR(80)
DECLARE @DateWork   VARCHAR(16)
DECLARE @TN         VARCHAR(80)
DECLARE @SetValue   VARCHAR(80)
DECLARE @USER       VARCHAR(32)
DECLARE @xprec      INT
DECLARE @xscale     INT
DECLARE @DOM_NAME   VARCHAR(50)
DECLARE @SpPrefix   VARCHAR(10)
DECLARE @SET_DB     VARCHAR(50)
DECLARE @ObPrefix   VARCHAR(10)
--  Configuration start
SELECT  @SpPrefix = 'usp_'
SELECT  @DOM_NAME = 'EDIPLC' + '\'
SELECT  @SET_DB   = 'asp_dbPermissions'
SELECT  @ObPrefix = 'x'
--  Configuration end
IF @TABLENAME ='xyzzy'
  GOTO HelpText
ELSE
  BEGIN
    IF NOT EXISTS (SELECT * FROM sysobjects WHERE UPPER(name)=UPPER(@TABLENAME) and type='U')
      GOTO HelpText
    ELSE BEGIN
      SELECT @TN = name FROM sysobjects WHERE UPPER(name)=UPPER(@TABLENAME) and type='U'
      SELECT @RowCount=0
      DECLARE xArgs CURSOR FOR
        SELECT C.name FROM syscolumns C, sysobjects O
        WHERE C.id = O.id AND O.name = @TN
        ORDER BY C.colid
      OPEN    xArgs
      FETCH   xArgs INTO @ColName
      WHILE   @@FETCH_STATUS=0
      BEGIN
        SELECT @RowCount=@RowCount+1
        FETCH  xArgs INTO @ColName
      END
      CLOSE      xArgs
      DEALLOCATE xArgs  
      SELECT @SpName=@SpPrefix + RTRIM(@TN) + '_Select'
      SELECT @HelperName = RTRIM(@TN) + 'Select'
      SELECT @ColLength= MAX(DATALENGTH(CONVERT(VARCHAR(64),C.name))) FROM syscolumns C, sysobjects O
        WHERE C.id = O.id AND O.name = @TN AND C.status<>128
      SELECT @SpUpper = UPPER(@SpName)
      IF EXISTS(select * from sysobjects where (UPPER(name) = @SpUpper) AND (sysstat & 0xf = 4))
        SELECT @PrintLine='ALTER  PROCEDURE dbo.' + @SpName
      ELSE
        SELECT @PrintLine='CREATE PROCEDURE dbo.' + @SpName   
      PRINT  @PrintLine
      PRINT  '('
      SELECT @HasIdent=COUNT(*) FROM syscolumns C, sysobjects O WHERE C.id = O.id AND O.name = @TN AND C.status=128
      DECLARE @ArgName   VARCHAR(80),
              @COL_NAME  VARCHAR(80),
              @HAS_LAST  BIT,
              @Type      VARCHAR(32),
              @Length    INT
      SELECT  @HAS_LAST = 0
      SELECT  @RowIndex = 0
      --PRINT   CONVERT(VARCHAR(10),@ColLength)
      IF      @ColLength < 16 SELECT @ColLength = 16
      DECLARE xArgs CURSOR FOR
      SELECT '@' + C.name, U.name, C.length, C.xprec, C.xscale
        FROM syscolumns C, sysobjects O, systypes U
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND U.name<>'timestamp'
          AND C.name NOT IN ('REPLICASTAMP')
        ORDER BY C.colid
      OPEN    xArgs
      FETCH   xArgs INTO @COL_NAME, @Type, @Length, @xprec, @xscale
      WHILE   @@FETCH_STATUS=0
      BEGIN ----
        SELECT @RowIndex=@RowIndex + 1
        SELECT @ArgName = SUBSTRING(@COL_NAME + '                                    ',1,@ColLength + 2)
        SELECT @Type    = UPPER(@Type)
        SELECT @PrintLine = '  ' + @ArgName + @Type
        IF @Type='char'
          SELECT @PrintLine=@PrintLine + ' (' + RTRIM(convert(varchar,@Length))  + ')'
        IF @Type='varchar'
          SELECT @PrintLine=@PrintLine + ' (' + RTRIM(convert(varchar,@Length))  + ')'
        IF @Type='nchar'
          SELECT @PrintLine=@PrintLine + ' (' + RTRIM(convert(varchar,@Length/2))  + ')'
        IF @Type='nvarchar'
          SELECT @PrintLine=@PrintLine + ' (' + RTRIM(convert(varchar,@Length/2))  + ')'
        IF (@Type='decimal') OR (@Type='numeric')
          SELECT @PrintLine=@PrintLine + ' (' + RTRIM(convert(varchar,@xprec))
                 + ','  + RTRIM(convert(varchar,@xscale)) + ')'  
        IF RTRIM(LTRIM(@ArgName))='@ID'
          SELECT @PrintLine=@PrintLine + ','
        ELSE
          SELECT @PrintLine=@PrintLine + ' OUTPUT,' 
        PRINT @PrintLine
        FETCH xArgs INTO @COL_NAME, @Type, @Length, @xprec, @xscale
      END
      CLOSE xArgs
      DEALLOCATE xArgs
      PRINT   '  @RESULT           INT      OUTPUT'
      PRINT  ') AS'
      PRINT '--  Purpose:'
      SELECT @PrintLine='--    Select record on ' + @TN + ' table'
      PRINT  @PrintLine
      PRINT  '--  Parameters:'     
      DECLARE xArgs CURSOR FOR
      SELECT C.name 
        FROM syscolumns C, sysobjects O, systypes U
      WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND U.name<>'timestamp'  AND C.status=128
        ORDER BY C.colid     
      OPEN  xArgs
      FETCH xArgs INTO @ArgName
      WHILE @@FETCH_status=0
      BEGIN
        SELECT @ArgName=SUBSTRING(@ArgName + '                            ',1,@ColLength + 2)
        SELECT @PrintLine='--    ' + @ArgName + '-'
        PRINT @PrintLine
        FETCH xArgs INTO @ArgName
      END
      CLOSE  xArgs
      DEALLOCATE xArgs     
      --PRINT  '--  Resultset:'
      --PRINT  '--    RESULT        - Zero or Error Code'     
      PRINT  '--  History:   '
      SELECT @USER = system_user
      IF SUBSTRING(@USER,1,DATALENGTH(@DOM_NAME)) = @DOM_NAME BEGIN
        SELECT @USER = RTRIM(SUBSTRING(@USER,DATALENGTH(@DOM_NAME) + 1,20))
      END 
      SELECT @DateWork=CONVERT(CHAR(11),CURRENT_TIMESTAMP,106)
      SELECT @DateWork=substring(@DateWork,1,2) + SUBSTRING(@DateWork,4,3) + SUBSTRING(@DateWork,8,4)
      SELECT @PrintLine='--  ' + @DateWork + ' '     
      SELECT @PrintLine=@PrintLine + @USER + ' Original coding'
      PRINT  @PrintLine
      PRINT  'SET NOCOUNT ON'     
      SELECT @PrintLine='SELECT '
      PRINT  @PrintLine    
      SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O, systypes U
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND (U.name NOT IN ('timestamp'))
          AND C.NAME NOT IN ('ID')
      DECLARE xArgs CURSOR FOR
        SELECT  C.name FROM syscolumns C, sysobjects O, systypes U
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND (U.name NOT IN ('timestamp'))
          AND C.NAME NOT IN ('ID')       
        ORDER BY C.colid
      SELECT @RowIndex=0
      OPEN   xArgs
      FETCH  xArgs INTO @ArgName
      WHILE  @@FETCH_STATUS=0
      BEGIN
        SELECT @RowIndex=@RowIndex + 1
SELECT @ArgName=SUBSTRING(@ArgName + '           ',1,@ColLength+2)
        SELECT @PrintLine = '  @' + @ArgName + ' = ' + @TN + '.' + RTRIM(@ArgName)
        IF @RowIndex <> @RowCount
        SELECT @PrintLine=@PrintLine + ','
        PRINT @PrintLine
        FETCH xArgs INTO @ArgName
      END
      CLOSE      xArgs
      DEALLOCATE xArgs   
      SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O WHERE C.id = O.id AND O.name = @TN
      IF @RowCount = 0 BEGIN
      PRINT  '-- You MUST amend the following line to correctly identify the row to update'
        ---------
        SELECT @PrintLine='  FROM ' + @TN + ' WHERE SOMECOLUMN = @SOMEVALUE'
        PRINT  @PrintLine       
        END
      ELSE BEGIN
        SELECT @PrintLine='  FROM ' + @TN + ' WHERE '
        PRINT  @PrintLine     
        SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O WHERE C.id = O.id AND O.name = @TN
          AND C.status=128
        IF @RowCount = 0 BEGIN
          PRINT '-- THERE IS NO IDENTITY ROW - assuming first row'
          DECLARE xArgs CURSOR FOR
          SELECT  C.name + '=@' + C.name 
          FROM syscolumns C, sysobjects O, systypes U
          WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype 
          ORDER BY C.colid
          OPEN  xArgs
          FETCH xArgs INTO @SetValue
          PRINT '  ' + @TN + '.' + @SetValue
          CLOSE      xArgs
          DEALLOCATE xArgs
          END
        ELSE BEGIN
          DECLARE xArgs CURSOR FOR
          SELECT  C.name + '=@' + C.name 
          FROM syscolumns C, sysobjects O, systypes U
          WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND C.status=128
          ORDER BY C.colid
          SELECT @RowIndex=0
          OPEN  xArgs
          FETCH xArgs INTO @SetValue
          WHILE @@FETCH_STATUS=0
          BEGIN
            SELECT @RowIndex=@RowIndex + 1
            SELECT @PrintLine='  ' + @TN + '.' + @SetValue
            IF @RowIndex <> @RowCount  
              SELECT @PrintLine=@PrintLine + ' AND'
            PRINT @PrintLine
            FETCH xArgs INTO @SetValue
          END
          CLOSE      xArgs
          DEALLOCATE xArgs
        END
      END
      PRINT  'SELECT @RESULT = @@error'
    END
  END
PRINT  'RETURN'
PRINT  '-------------- this is the end ----------------'
DECLARE @SqlDbType VARCHAR(20)
PRINT  '/* Data Access code follows:'
PRINT  '#region " Using "'
PRINT  'using System;'
PRINT  'using System.Data;'
PRINT  'using System.Data.SqlClient;'
PRINT  '#endregion'
PRINT  '    #region " Select "'
PRINT  '    /// <summary>'
PRINT  '    /// Select record on ' + @TN
PRINT  '    /// </summary>'
PRINT  '    /// <param name="iID">ID of record to fetch</param>'
PRINT  '    /// <param name="iReturn">Database return code</param>'
PRINT  '    /// <returns>' + @HelperName + ' containing data for selected row</returns>'
SELECT  @RowIndex = 0
SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O WHERE C.id = O.id AND O.name = @TN
PRINT  '    public ' + @HelperName + ' Select' + @TN + '(int iID, ref DatabaseReturnCode iReturn) '
PRINT  '    {'
PRINT  '      CommonData xCommonData = new CommonData();'
PRINT  '      ' + @HelperName + ' ' + @ObPrefix + '' + @HelperName + ' = new ' + @HelperName + '();  '
PRINT  '      string sConnect = ' + @ObPrefix + 'CommonData.GetConnection();'
PRINT  '      SqlConnection ' + @ObPrefix + 'SqlConnection = new SqlConnection(sConnect);'
PRINT  '      SqlCommand ' + @ObPrefix + 'SqlCommand = new SqlCommand("' + @SpName + '", ' + @ObPrefix + 'SqlConnection);'
PRINT  '      try'
PRINT  '      {'
PRINT  '        ' + @ObPrefix + 'SqlCommand.CommandType = CommandType.StoredProcedure;'
SELECT @RowIndex = 0
DECLARE xArgs CURSOR FOR
SELECT DISTINCT '@' + C.name, U.name, C.length, C.colid, C.xprec, C.xscale
  FROM syscolumns C, sysobjects O, systypes U
  WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND U.name<>'timestamp'
    AND C.name NOT IN ('REPLICASTAMP')
  ORDER BY C.colid, '@' + C.name, U.name, C.length
OPEN  xArgs
FETCH   xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscale
WHILE @@FETCH_STATUS=0
BEGIN
  SELECT @PrintLine='        xSqlCommand.Parameters.Add("' + @COL_NAME + '", SqlDbType.'
  SELECT @SqlDbType = @Type  -- Default assignment
  IF @Type='bigint'         SELECT @SqlDbType = 'BigInt'
  IF @Type='bit'            SELECT @SqlDbType = 'Bit'
  IF @Type='int'            SELECT @SqlDbType = 'Int'
  IF @Type='decimal'        SELECT @SqlDbType = 'Decimal'
  IF @Type='numeric'        SELECT @SqlDbType = 'Decimal'
  IF @Type='datetime'       SELECT @SqlDbType = 'DateTime'
  IF @Type='char'           SELECT @SqlDbType = 'Char'
  IF @Type='varchar'        SELECT @SqlDbType = 'VarChar'
  IF @Type='nchar'          SELECT @SqlDbType = 'NChar'
  IF @Type='nvarchar'       SELECT @SqlDbType = 'NVarChar'
  IF @Type='ntext'          SELECT @SqlDbType = 'NText'
  IF @Type='text'           SELECT @SqlDbType = 'Text'
  IF @Type='real'           SELECT @SqlDbType = 'Real'
  IF @Type='money'          SELECT @SqlDbType = 'Money'
  IF @Type='float'          SELECT @SqlDbType = 'Float'
  IF @Type='smalldatetime'  SELECT @SqlDbType = 'SmallDateTime'
  IF @Type='smallint'          SELECT @SqlDbType = 'SmallInt'
  IF @Type='smallmoney'     SELECT @SqlDbType = 'SmallMoney'
  -- more type conversions here !
  SELECT @PrintLine=@PrintLine + @SqlDbType
  IF @Type='char'     SELECT @PrintLine=@PrintLine + ', ' + RTRIM(convert(varchar,@Length)) 
  IF @Type='varchar'  SELECT @PrintLine=@PrintLine + ', ' + RTRIM(convert(varchar,@Length))
  IF @Type='nchar'    SELECT @PrintLine=@PrintLine + ', ' + RTRIM(convert(varchar,@Length/2))
  IF @Type='nvarchar' SELECT @PrintLine=@PrintLine + ', ' + RTRIM(convert(varchar,@Length/2))
  SELECT @PrintLine=@PrintLine + ');'
  PRINT @PrintLine  
  IF (@Type='decimal') OR (@Type='numeric') BEGIN
    SELECT @PrintLine='        ' + @ObPrefix + 'SqlCommand.Parameters["' + @COL_NAME + '"].Precision = ' + CONVERT(VARCHAR(3), @xprec) + ';'
    PRINT @PrintLine
    SELECT @PrintLine='        ' + @ObPrefix + 'SqlCommand.Parameters["' + @COL_NAME + '"].Scale = ' + CONVERT(VARCHAR(3), @xscale) + ';'
    PRINT @PrintLine
  END    
  SELECT @RowIndex=@RowIndex + 1
  IF @RowIndex = 1
    SELECT @PrintLine='        ' + @ObPrefix + 'SqlCommand.Parameters["' + @COL_NAME + '"].Value=iID;'
  ELSE
    SELECT @PrintLine='        ' + @ObPrefix + 'SqlCommand.Parameters["' + @COL_NAME + '"].Direction = ParameterDirection.Output;'
  PRINT @PrintLine
  FETCH   xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscale
END
CLOSE      xArgs
DEALLOCATE xArgs
PRINT  '        ' + @ObPrefix + 'SqlCommand.Parameters.Add("@RESULT", SqlDbType.Int);'
PRINT  '        ' + @ObPrefix + 'SqlCommand.Parameters["@RESULT"].Direction = ParameterDirection.Output;'
PRINT  '        ' + @ObPrefix + 'SqlCommand.Connection.Open();'
PRINT  '        ' + @ObPrefix + 'SqlCommand.ExecuteNonQuery();'
DECLARE xArgs CURSOR FOR
SELECT DISTINCT C.name, U.name, C.length, C.colid, C.xprec, C.xscale
  FROM syscolumns C, sysobjects O, systypes U
  WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND U.name<>'timestamp'
    AND C.name NOT IN ('REPLICASTAMP')
  ORDER BY C.colid, C.name, U.name, C.length
OPEN  xArgs
FETCH   xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscale
WHILE @@FETCH_STATUS=0
BEGIN
  SELECT @RowIndex = @RowIndex + 1
  IF @RowIndex > 0 BEGIN
    SELECT @PrintLine = '        ' + @ObPrefix + '' + @HelperName + '.' + @COL_NAME + ' = ' + @ObPrefix + 'CommonData.'
    SELECT @SqlDbType = 'NullToString'
    IF @Type='bit'            SELECT @SqlDbType = 'NullToBoolean'
    IF @Type='int'            SELECT @SqlDbType = 'NullToInteger'
    IF @Type='smallint'       SELECT @SqlDbType = 'NullToInteger16'
    IF @Type='bigint'         SELECT @SqlDbType = 'NullToInteger64'
    IF @Type='decimal'        SELECT @SqlDbType = 'NullToDecimal'
    IF @Type='numeric'        SELECT @SqlDbType = 'NullToDecimal'
    IF @Type='money'          SELECT @SqlDbType = 'NullToDecimal'
    IF @Type='smallmoney'     SELECT @SqlDbType = 'NullToDecimal'
    IF @Type='datetime'       SELECT @SqlDbType = 'NullToDateString'
    IF @Type='smalldatetime'  SELECT @SqlDbType = 'NullToDateString'
    IF @Type='real'           SELECT @SqlDbType = 'NullToDouble'
    IF @Type='char'           SELECT @SqlDbType = 'NullToString'
    IF @Type='varchar'        SELECT @SqlDbType = 'NullToString'
    IF @Type='nchar'          SELECT @SqlDbType = 'NullToString'
    IF @Type='nvarchar'       SELECT @SqlDbType = 'NullToString'
    IF @Type='text'           SELECT @SqlDbType = 'NullToString'
    IF @Type='ntext'          SELECT @SqlDbType = 'NullToString'
    IF @Type='float'          SELECT @SqlDbType = 'NullToDouble'
    SELECT @PrintLine = @PrintLine + @SqlDbType + '(' + @ObPrefix + 'SqlCommand.Parameters["@'
    SELECT @PrintLine = @PrintLine + @COL_NAME + '"].Value);'
    PRINT @PrintLine
    END
  FETCH   xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscale
END
CLOSE      xArgs
DEALLOCATE xArgs
PRINT  '        iReturn = DatabaseReturnCode.NoErrorDetected;'
PRINT  '      }'
PRINT  '      catch (System.InvalidOperationException ex)'
PRINT  '      {'
PRINT  '        string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name.ToString();'
PRINT  '        ' + @ObPrefix + 'CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");'
PRINT  '        iReturn = DatabaseReturnCode.NetworkError;'
PRINT  '      }'
PRINT  '      catch (Exception ex)'
PRINT  '      {'
PRINT  '        string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name.ToString();'
PRINT  '        ' + @ObPrefix + 'CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "iID=" + iID.ToString());'
PRINT  '        iReturn = DatabaseReturnCode.OtherError;'
PRINT  '      }'
PRINT  '      finally '
PRINT  '      {'
PRINT  '        if (' + @ObPrefix + 'SqlCommand.Connection != null) ' + @ObPrefix + 'SqlCommand.Connection.Close();'
PRINT  '        ' + @ObPrefix + 'SqlCommand.Dispose();'
PRINT  '        ' + @ObPrefix + 'SqlConnection.Dispose();'
PRINT  '      }'
PRINT  '      return ' + @ObPrefix + '' + @HelperName + ';'
PRINT  '    }'
PRINT  '    #endregion'
PRINT  '*/'
PRINT  '/* Helper class code follows:'
PRINT  '  #region " Helper class:"' + @HelperName
PRINT  '  /// <summary>'
PRINT  '  ///    Helper class for ' + @HelperName
PRINT  '  ///    ' + @DateWork + 'Autogenerated'
PRINT  '  /// </summary>'
PRINT  '  public class ' + @HelperName
PRINT  '  {'
DECLARE @COMMENT VARCHAR(132)
SELECT  @COMMENT = 'T.B.D.'
SELECT  @RowIndex = 0
DECLARE xArgs CURSOR FOR
SELECT COALESCE(CONVERT(VARCHAR(130),P.value), '[Undocumented in Database]') AS COMMENT,
  C.name, U.name, C.length, C.xprec, C.xscale
  FROM syscolumns C, sysobjects O, systypes U, SYSPROPERTIES P
  WHERE C.id = O.id AND O.name = @TN AND C.xusertype = U.xusertype AND U.name<>'timestamp'
    AND C.name NOT IN ('REPLICASTAMP') AND P.NAME = 'MS_Description'
    AND C.COLID *= P.smallid AND C.id *= P.ID
  ORDER BY C.colid
OPEN    xArgs
FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, @xprec, @xscale
WHILE @@FETCH_STATUS=0
BEGIN
  SELECT @RowIndex = @RowIndex + 1
  IF @RowIndex > 0 BEGIN -- Generate iID data member
    SELECT @SqlDbType = @Type  -- Default assignment
    IF @Type='bigint'         SELECT @SqlDbType = 'Int64'
    IF @Type='smallint'       SELECT @SqlDbType = 'Int16'
    IF @Type='bit'            SELECT @SqlDbType = 'bool'
    IF @Type='int'            SELECT @SqlDbType = 'int'
    IF @Type='decimal'        SELECT @SqlDbType = 'decimal'
    IF @Type='numeric'        SELECT @SqlDbType = 'decimal'
    IF @Type='datetime'       SELECT @SqlDbType = 'string'
    IF @Type='char'           SELECT @SqlDbType = 'string'
    IF @Type='varchar'        SELECT @SqlDbType = 'string'
    IF @Type='nchar'          SELECT @SqlDbType = 'string'
    IF @Type='nvarchar'       SELECT @SqlDbType = 'string'
    IF @Type='text'           SELECT @SqlDbType = 'string'
    IF @Type='ntext'          SELECT @SqlDbType = 'string'
    IF @Type='smallmoney'     SELECT @SqlDbType = 'decimal'
    IF @Type='money'          SELECT @SqlDbType = 'decimal'
    IF @Type='smalldatetime'  SELECT @SqlDbType = 'string'
    IF @Type='real'           SELECT @SqlDbType = 'double'
    IF @Type='float'          SELECT @SqlDbType = 'double'
    SELECT @PrintLine = '    ' + @SqlDbType  
    SELECT @SqlDbType = 'x'  -- Default assignment
    IF @Type='real'           SELECT @SqlDbType = 'd'
    IF @Type='float'          SELECT @SqlDbType = 'd'
    IF @Type='smallint'       SELECT @SqlDbType = 'i'
    IF @Type='smalldatetime'  SELECT @SqlDbType = 's'
    IF @Type='bigint'         SELECT @SqlDbType = 'l'
    IF @Type='bit'            SELECT @SqlDbType = 'b'
    IF @Type='int'            SELECT @SqlDbType = 'i'
    IF @Type='decimal'        SELECT @SqlDbType = 'c'
    IF @Type='numeric'        SELECT @SqlDbType = 'c'
    IF @Type='money'          SELECT @SqlDbType = 'c'
    IF @Type='smallmoney'     SELECT @SqlDbType = 'c'
    IF @Type='datetime'       SELECT @SqlDbType = 's'
    IF @Type='char'           SELECT @SqlDbType = 's'
    IF @Type='varchar'        SELECT @SqlDbType = 's'
    IF @Type='nchar'          SELECT @SqlDbType = 's'
    IF @Type='nvarchar'       SELECT @SqlDbType = 's'
    IF @Type='text'           SELECT @SqlDbType = 's'
    IF @Type='ntext'          SELECT @SqlDbType = 's'
    SELECT @PrintLine = @PrintLine + '    ' + @SqlDbType +  @COL_NAME + ';'
    PRINT @PrintLine
  END
  FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, @xprec, @xscale
END
CLOSE   xArgs
DECLARE @PREFIX CHAR(1)
OPEN    xArgs
FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, @xprec, @xscale
WHILE @@FETCH_STATUS=0
BEGIN
  SELECT @RowIndex = @RowIndex + 1
  IF @RowIndex > 1 BEGIN
    --SELECT @PrintLine = '  Dim '
    SELECT @PREFIX = 'x'  -- Default assignment
    IF @Type='bigint'     SELECT @PREFIX = 'l'
    IF @Type='bit'        SELECT @PREFIX = 'b'
    IF @Type='int'        SELECT @PREFIX = 'i'
    IF @Type='smallint'   SELECT @PREFIX = 'i'
    IF @Type='decimal'    SELECT @PREFIX = 'c'
    IF @Type='numeric'    SELECT @PREFIX = 'c'
    IF @Type='datetime'   SELECT @PREFIX = 's'
    IF @Type='smalldatetime'  SELECT @PREFIX = 's'
    IF @Type='char'           SELECT @PREFIX = 's'
    IF @Type='varchar'    SELECT @PREFIX = 's'
    IF @Type='nchar'      SELECT @PREFIX = 's'
    IF @Type='nvarchar'   SELECT @PREFIX = 's'
    IF @Type='text'       SELECT @PREFIX = 's'
    IF @Type='ntext'      SELECT @PREFIX = 's'
    IF @Type='smallmoney' SELECT @PREFIX = 'c'
    IF @Type='money'      SELECT @PREFIX = 'c'
    IF @Type='real'       SELECT @PREFIX = 'd'
    IF @Type='float'      SELECT @PREFIX = 'd'
    SELECT @SqlDbType = 'String'  -- Default assignment
    IF @Type='real'       SELECT @SqlDbType = 'double'
    IF @Type='float'      SELECT @SqlDbType = 'double'
    IF @Type='bigint'     SELECT @SqlDbType = 'Int64'
    IF @Type='smallint'   SELECT @SqlDbType = 'Int16'
    IF @Type='bit'        SELECT @SqlDbType = 'bool'
    IF @Type='int'        SELECT @SqlDbType = 'int'
    IF @Type='decimal'    SELECT @SqlDbType = 'decimal'
    IF @Type='numeric'        SELECT @SqlDbType = 'decimal'
    IF @Type='datetime'       SELECT @SqlDbType = 'string'
    IF @Type='smalldatetime'  SELECT @SqlDbType = 'string'
    IF @Type='char'           SELECT @SqlDbType = 'string'
    IF @Type='varchar'        SELECT @SqlDbType = 'string'
    IF @Type='nchar'      SELECT @SqlDbType = 'string'
    IF @Type='nvarchar'   SELECT @SqlDbType = 'string'
    IF @Type='text'       SELECT @SqlDbType = 'string'
    IF @Type='ntext'      SELECT @SqlDbType = 'string'
    IF @Type='smallmoney' SELECT @SqlDbType = 'decimal'
    IF @Type='money'      SELECT @SqlDbType = 'decimal'
    SELECT @PrintLine = ''   
    PRINT '    /// <summary>'
    PRINT '    /// ' + @COMMENT
    PRINT '    /// </summary>'
    PRINT '    public ' + @SqlDbType + ' '  + @COL_NAME
    PRINT '    {'
  PRINT '      get { return ' + @PREFIX + @COL_NAME + ';}'
    PRINT '      set { ' + @PREFIX + @COL_NAME + ' = value; }'
    PRINT '    }'  
  END
  FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, @xprec, @xscale
END
CLOSE     xArgs
DEALLOCATE xArgs
PRINT  '  }'
PRINT  '  #endregion'
PRINT  '*/'
PRINT  'GO'
--PRINT  'asp_dbPermissions'
PRINT  @SET_DB
PRINT  'GO'
RETURN
HelpText:
  PRINT '--Use sp_MakeSelect TABLENAME to generate select S.P. for TABLENAME'
  PRINT '--or  sp_MakeSelect garbage to get this help text!'
RETURN
-------------- this is the end ----------------

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE sp_MakeUpdate (@TABLENAME VARCHAR(255)='xyzzy')
AS
-- Purpose:
--   Create update script from table definition
-- Copyright (C) 2000, 2003, 2004, 2005 Clive Chinery
--
-- This library is free software; you can redistribute it and/or
-- modify it under the terms of the GNU Lesser General Public
-- License as published by the Free Software Foundation; either
-- version 2.1 of the License, or (at your option) any later version.
--
-- This library is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
-- Lesser General Public License for more details.
--
-- You should have received a copy of the GNU Lesser General Public
-- License along with this library; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
-- Notes:
--   1) Run from any database on this server by entering cp_MakeUpdate TABLENAME
--   2) The TABLENAME is case insensitive, however the table must exist
--   3) ID and TIMESTAMP columns are specially handled
--   4) Tables with ID column will have a where clause using the ID column - this normally suffices.
-- Changes:
-- 09Oct2005 Clive Update documentation
-- 05Sep2005 Clive Changes for ENL
-- 01Sep2005 Clive Extend to cover more data types
-- 09Jun2005 Clive Make c# variant of vb.net
-- 14Feb2005 Clive Autogenerate data access code
-- 14Oct2000 Clive Tidied output
-- 09Oct2000 Clive Make prefix uap_ instead of ap_
-- 01Aug2000 Clive Documentation update
-- 31Jul2000 Clive Modified to put more of documentation header
SET NOCOUNT ON
DECLARE @ColName    VARCHAR(80)
DECLARE @COL_NAME   VARCHAR(80)
DECLARE @ColLength  INT
DECLARE @HasIdent   INT
DECLARE @HasTSTAMP  INT
DECLARE @RowCount   INT
DECLARE @RowIndex   INT
DECLARE @PrintLine  VARCHAR(256)
DECLARE @SpName     VARCHAR(80)
DECLARE @DateWork   VARCHAR(16)
DECLARE @TN         VARCHAR(80)
DECLARE @USER       VARCHAR(80)
DECLARE @colid      INT
DECLARE @xprec      INT
DECLARE @xscale     INT
DECLARE @DOM_NAME   VARCHAR(50)
DECLARE @SpPrefix   VARCHAR(10)
DECLARE @SET_DB     VARCHAR(50)
DECLARE @ObPrefix   VARCHAR(10)
--  Configuration start
SELECT  @SpPrefix = 'usp_'
SELECT  @DOM_NAME = 'EDIPLC' + '\'
SELECT  @SET_DB   = 'asp_dbPermissions'
SELECT  @ObPrefix = 'x'
--  Configuration end
IF @TABLENAME ='xyzzy'
  GOTO HelpText
ELSE
  BEGIN
    IF NOT EXISTS (SELECT * FROM sysobjects WHERE UPPER(name)=UPPER(@TABLENAME) and type='U')
      GOTO HelpText
    ELSE BEGIN
      SELECT @TN = name FROM sysobjects WHERE UPPER(name)=UPPER(@TABLENAME) and type='U'
      SELECT @HasTSTAMP = (SELECT COUNT(*) FROM syscolumns C, sysobjects O
        WHERE C.id = O.id AND O.name = @TN AND C.name = 'UpdateCount')
      SELECT @RowCount=0
      DECLARE xArgs CURSOR FOR
        SELECT C.name FROM syscolumns C, sysobjects O
        WHERE C.id = O.id AND O.name = @TN
        ORDER BY C.colid
      OPEN    xArgs
      FETCH   xArgs INTO @ColName
      WHILE   @@FETCH_STATUS=0
      BEGIN
        SELECT @RowCount=@RowCount+1
        FETCH  xArgs INTO @ColName
      END
      CLOSE      xArgs
      DEALLOCATE xArgs  
      SELECT @SpName=@SpPrefix + RTRIM(@TN) + '_Update'
      SELECT @ColLength= MAX(DATALENGTH(RTRIM(CONVERT(VARCHAR(80),C.name)))) FROM syscolumns C, sysobjects O
        WHERE C.id = O.id AND O.name = @TN AND C.status<>128
      IF EXISTS(select * from sysobjects where UPPER(name) = UPPER(@SpName))
        SELECT @PrintLine='ALTER  PROCEDURE dbo.' + @SpName
      ELSE
        SELECT @PrintLine='CREATE PROCEDURE dbo.' + @SpName   
      PRINT  @PrintLine
      PRINT  '('
      SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O, systypes U
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND U.name<>'timestamp'
          AND C.name NOT IN ('CreatePersonID', 'CreateDate','UpdateDate')
      SELECT @HasIdent=COUNT(*) FROM syscolumns C, sysobjects O WHERE C.id = O.id AND O.name = @TN AND C.status=128
      DECLARE xArgs CURSOR FOR
      SELECT '@' + C.name, U.name, C.length, C.xprec, C.xscale
        FROM syscolumns C, sysobjects O, systypes U
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND U.name<>'timestamp'
          AND C.name NOT IN ('CreatePersonID', 'CreateDate', 'UpdateDate')
        ORDER BY C.colid
      DECLARE @ArgName   VARCHAR(80),
              @Type      VARCHAR(32),
              @Length    INT
      SELECT  @RowIndex=0
      OPEN    xArgs
      FETCH   xArgs INTO @ArgName, @Type, @Length, @xprec, @xscale
      WHILE   @@FETCH_STATUS=0
      BEGIN
        SELECT @RowIndex=@RowIndex + 1
        SELECT @ArgName = SUBSTRING(@ArgName + '                              ',1,@ColLength + 4)
        SELECT @PrintLine = '  ' + @ArgName + @Type
        IF @Type='char'
          SELECT @PrintLine=@PrintLine + ' (' + RTRIM(convert(varchar(10),@Length))  + ')'
        IF @Type='varchar'
          SELECT @PrintLine=@PrintLine + ' (' + RTRIM(convert(varchar(10),@Length))  + ')'
        IF @Type='nchar'
          SELECT @PrintLine=@PrintLine + ' (' + RTRIM(convert(varchar(10),@Length/2))  + ')'
        IF @Type='nvarchar'
          SELECT @PrintLine=@PrintLine + ' (' + RTRIM(convert(varchar(10),@Length/2))  + ')'
        IF (@Type='decimal') OR (@Type='numeric')
          SELECT @PrintLine=@PrintLine + ' (' + RTRIM(convert(varchar(10),@xprec))
                 + ','  + RTRIM(convert(varchar(10),@xscale)) + ')'            
        SELECT @PrintLine=@PrintLine + ','
        PRINT @PrintLine
        FETCH xArgs INTO @ArgName, @Type, @Length, @xprec, @xscale
      END
      CLOSE xArgs
      DEALLOCATE xArgs
      PRINT   '  @RETURN           INT            OUTPUT'
      PRINT  ') AS'
      PRINT  '--  Purpose:'
      SELECT @PrintLine='--     Update record on ' + @TN + ' table'
      PRINT  @PrintLine
      PRINT  '--  Parameters:'
      DECLARE xArgs CURSOR FOR
      SELECT C.name 
        FROM syscolumns C, sysobjects O, systypes U
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND U.name<>'timestamp'
          AND C.name NOT IN ('CreatePersonID', 'CreateDate','UpdateDate') 
        ORDER BY C.colid     
      OPEN  xArgs
      FETCH xArgs INTO @ArgName
      WHILE @@FETCH_status=0
      BEGIN
        SELECT @ArgName=SUBSTRING(@ArgName + '                            ',1,@ColLength + 2)
        SELECT @PrintLine='--    ' + @ArgName + '-'
        PRINT @PrintLine
        FETCH xArgs INTO @ArgName
      END
      CLOSE  xArgs
      DEALLOCATE xArgs     
--    PRINT  '--  Resultset:'
--      PRINT  '--    RETURN        - Zero or Error Code'     
      PRINT  '--  History:   '
      SELECT @USER = system_user
      IF SUBSTRING(@USER,1,DATALENGTH(@DOM_NAME)) = @DOM_NAME BEGIN
        SELECT @USER = RTRIM(SUBSTRING(@USER,DATALENGTH(@DOM_NAME) + 1,20))
      END     
      SELECT @DateWork=CONVERT(CHAR(11),CURRENT_TIMESTAMP,106)
      SELECT @DateWork=substring(@DateWork,1,2) + SUBSTRING(@DateWork,4,3) + SUBSTRING(@DateWork,8,4)
      SELECT @PrintLine='--  ' + @DateWork + ' '
      SELECT @PrintLine=@PrintLine + @USER + ' Original coding'
      PRINT  @PrintLine
      PRINT  'SET NOCOUNT ON'
      IF @HasIdent > 0 AND (@HasTSTAMP > 0) BEGIN
        SELECT @PrintLine='IF EXISTS(SELECT * FROM ' + @TN + ' WHERE ID = @ID AND UpdateCount = @UpdateCount) BEGIN '
        PRINT @PrintLine
      END
      SELECT @PrintLine='  UPDATE ' + @TN + ' SET'
      PRINT  @PrintLine
      DECLARE @SetValue   VARCHAR(80)
      SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O, systypes U
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND C.status<>128 AND U.name<>'timestamp'
          AND C.name NOT IN ('CreatePersonID','CreateDate')
      DECLARE xArgs CURSOR FOR
        SELECT  C.name FROM syscolumns C, sysobjects O, systypes U
        WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND C.status<>128 AND U.name<>'timestamp'
          AND C.name NOT IN ('CreatePersonID','CreateDate')       
        ORDER BY C.colid
      SELECT @RowIndex=0
      OPEN   xArgs
      FETCH  xArgs INTO @ArgName
      WHILE  @@FETCH_STATUS=0
      BEGIN
        SELECT @RowIndex=@RowIndex + 1
        SELECT @ArgName=SUBSTRING(@ArgName + '                              ',1,@ColLength+2)
        IF @ArgName = 'UpdateDate ' BEGIN
          SELECT @PrintLine = '  ' + @ArgName + '= GETUTCDATE()' 
          END
        ELSE BEGIN
          IF @ArgName = 'UpdateCount' BEGIN
            SELECT @PrintLine = '  ' + @ArgName + ' = UpdateCount + 1' 
            END       
          ELSE BEGIN
            SELECT @PrintLine = '  ' + @ArgName + ' = @' + RTRIM(@ArgName)         
          END
        END
        IF @RowIndex <> @RowCount  
          SELECT @PrintLine=@PrintLine + ','
        PRINT @PrintLine
        FETCH xArgs INTO @ArgName
      END
      CLOSE      xArgs
      DEALLOCATE xArgs
      SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O WHERE C.id = O.id AND O.name = @TN
      IF @RowCount = 0 BEGIN
        PRINT  '-- You MUST amend the following line to correctly identify the row to update'
        PRINT  'WHERE SOMECOLUMN = @SOMEVALUE'
        PRINT  ''
        PRINT  'SELECT @RETURN = @@error'
        PRINT  'RETURN' 
        END
      ELSE BEGIN
        IF (@HasIdent > 0) AND (@HasTSTAMP > 0) BEGIN
          SELECT @PrintLine='  WHERE ID = @ID AND UpdateCount = @UpdateCount '
          PRINT  @PrintLine
          PRINT  '  SELECT @RETURN = @@error'
          PRINT  '  RETURN'              
          PRINT  '  END   '  
          PRINT  'ELSE    '
          PRINT  '  SELECT @RETURN = 13' 
          PRINT  '  RETURN'    
          END
        ELSE BEGIN
          PRINT  'WHERE '
          SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O WHERE C.id = O.id AND O.name = @TN
            AND C.status=128
          DECLARE xArgs CURSOR FOR
            SELECT  C.name + ' = @' + C.name 
            FROM syscolumns C, sysobjects O, systypes U
            WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND C.status=128
            ORDER BY C.colid
          SELECT @RowIndex=0
          OPEN  xArgs
          FETCH xArgs INTO @SetValue
          WHILE @@FETCH_STATUS=0
          BEGIN
            SELECT @RowIndex=@RowIndex + 1
            SELECT @PrintLine='  ' + @SetValue
            IF @RowIndex <> @RowCount  
              SELECT @PrintLine=@PrintLine + ' AND'
            PRINT @PrintLine
            FETCH xArgs INTO @SetValue
          END
          CLOSE      xArgs
          DEALLOCATE xArgs
          IF @HasTSTAMP = 1 BEGIN
            PRINT  '  AND UpdateCount = @UpdateCount'
            PRINT  ''
          END            
          PRINT  'SELECT @RETURN = @@error'
          PRINT  'RETURN'    
        END
      END
    END
  END
PRINT  '-------------- this is the end ----------------'   
PRINT  '/* Data Access code follows:'
DECLARE @SqlDbType VARCHAR(20)
SELECT  @RowIndex = 0
PRINT  '    #region " Update "'
PRINT  '    /// <summary> '
PRINT  '    /// Update record on ' + @TN + ' table '
PRINT  '    /// </summary>'
PRINT  '    /// <remarks>'
PRINT  '    /// This code was autogenerated on ' + @DateWork
PRINT  '    /// </remarks>'
DECLARE @COMMENT VARCHAR(50)
DECLARE xArgs CURSOR FOR -- need comment fetch
SELECT COALESCE(CONVERT(VARCHAR(130),P.value), '[Undocumented in Database]') AS COMMENT,
  C.name, U.name, C.length, C.xprec, C.xscale
  FROM syscolumns C, sysobjects O, systypes U, SYSPROPERTIES P
  WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND U.name<>'timestamp'
    AND C.name NOT IN ('REPLICASTAMP','CreatePersonID','CreateDate','UpdateDate')
    AND P.NAME = 'MS_Description' AND C.COLID *= P.smallid AND C.id *= P.ID
  ORDER BY C.colid
OPEN    xArgs
FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, @xprec, @xscale
WHILE @@FETCH_STATUS=0
BEGIN
  SELECT @PrintLine = '    /// <param name="'
  SELECT @SqlDbType = 'x'  -- Default assignment
  IF @Type='bigint'         SELECT @SqlDbType = 'l'
  IF @Type='bit'            SELECT @SqlDbType = 'b'
  IF @Type='int'            SELECT @SqlDbType = 'i'
  IF @Type='decimal'        SELECT @SqlDbType = 'c'
  IF @Type='smallmoney'     SELECT @SqlDbType = 'c'
  IF @Type='numeric'        SELECT @SqlDbType = 'c'
  IF @Type='datetime'       SELECT @SqlDbType = 'dat'
  IF @Type='smalldatetime'  SELECT @SqlDbType = 'dat'
  IF @Type='char'           SELECT @SqlDbType = 's'
  IF @Type='varchar'        SELECT @SqlDbType = 's'
  IF @Type='nchar'          SELECT @SqlDbType = 's'
  IF @Type='nvarchar'       SELECT @SqlDbType = 's'
  IF @Type='text'           SELECT @SqlDbType = 's'
  IF @Type='ntext'          SELECT @SqlDbType = 's'
  --SELECT @COMMENT = 'TBA'   
  IF @COL_NAME = 'UpdatePersonID' SELECT @COMMENT = 'Id of user from GetUserPersonId'
  IF @COL_NAME = 'UpdateCount'    SELECT @COMMENT = 'Count of existing updates to row'
  -- More comment signs here ?
  SELECT @PrintLine = @PrintLine + @SqlDbType + @COL_NAME + '">' + @COMMENT + '</param>'  
  PRINT @PrintLine
  FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, @xprec, @xscale
END
CLOSE      xArgs
DEALLOCATE xArgs
PRINT  '    /// <param name="iReturn">Return code to use in switch statement</param>'
PRINT  '    public void Update' + @TN + '(int iID,'
SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O WHERE C.id = O.id AND O.name = @TN
      AND C.name NOT IN ('REPLICASTAMP','CreatePersonID','CreateDate','UpdateDate')
DECLARE xArgs CURSOR FOR
SELECT C.name, U.name, C.length, C.xprec, C.xscale
  FROM syscolumns C, sysobjects O, systypes U
  WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND U.name<>'timestamp'
    AND C.name NOT IN ('REPLICASTAMP','CreatePersonID','CreateDate','UpdateDate')
  ORDER BY C.colid
OPEN    xArgs
FETCH   xArgs INTO @COL_NAME, @Type, @Length, @xprec, @xscale
WHILE @@FETCH_STATUS=0
BEGIN
  SELECT @RowIndex = @RowIndex + 1
  IF @RowIndex > 1 BEGIN
    SELECT @PrintLine = '      '
    SELECT @SqlDbType = @Type  -- Default assignment
    IF @Type='bigint'         SELECT @SqlDbType = 'Int64'
    IF @Type='smallint'       SELECT @SqlDbType = 'Int16'
    IF @Type='bit'            SELECT @SqlDbType = 'bool'
    IF @Type='int'            SELECT @SqlDbType = 'int'
    IF @Type='decimal'        SELECT @SqlDbType = 'decimal'
    IF @Type='numeric'        SELECT @SqlDbType = 'decimal'
    IF @Type='datetime'       SELECT @SqlDbType = 'DateTime'
    IF @Type='smalldatetime'  SELECT @SqlDbType = 'DateTime'
    IF @Type='char'           SELECT @SqlDbType = 'string'
    IF @Type='varchar'        SELECT @SqlDbType = 'string'
    IF @Type='nchar'          SELECT @SqlDbType = 'string'
    IF @Type='nvarchar'       SELECT @SqlDbType = 'string'
    IF @Type='text'           SELECT @SqlDbType = 'string'
    IF @Type='ntext'          SELECT @SqlDbType = 'string'
    IF @Type='smallmoney'     SELECT @SqlDbType = 'decimal'
    IF @Type='money'          SELECT @SqlDbType = 'decimal'
    IF @Type='real'           SELECT @SqlDbType = 'double'
    IF @Type='float'          SELECT @SqlDbType = 'double'
    SELECT @PrintLine = @PrintLine + @SqlDbType + ' '   
    SELECT @SqlDbType = 'x'  -- Default assignment
    IF @Type='bigint'         SELECT @SqlDbType = 'l'
    IF @Type='bit'            SELECT @SqlDbType = 'b'
    IF @Type='int'            SELECT @SqlDbType = 'i'
    IF @Type='decimal'        SELECT @SqlDbType = 'c'
    IF @Type='numeric'        SELECT @SqlDbType = 'c'
    IF @Type='datetime'       SELECT @SqlDbType = 'dat'
    IF @Type='smalldatetime'  SELECT @SqlDbType = 'dat'
    IF @Type='char'           SELECT @SqlDbType = 's'
    IF @Type='varchar'        SELECT @SqlDbType = 's'
    IF @Type='nchar'          SELECT @SqlDbType = 's'
    IF @Type='nvarchar'       SELECT @SqlDbType = 's'
    IF @Type='text'           SELECT @SqlDbType = 's'
    IF @Type='ntext'          SELECT @SqlDbType = 's'
    IF @Type='smallmoney'     SELECT @SqlDbType = 'c'
    IF @Type='money'          SELECT @SqlDbType = 'c'
    IF @Type='real'           SELECT @SqlDbType = 'd'
    IF @Type='float'          SELECT @SqlDbType = 'd'
    SELECT @PrintLine = @PrintLine + @SqlDbType + @COL_NAME
    IF @RowIndex = @RowCount
      SELECT @PrintLine = @PrintLine + ', ref DatabaseReturnCode iReturn)'
    ELSE
      SELECT @PrintLine = @PrintLine + ', '
    PRINT @PrintLine
  END
  FETCH   xArgs INTO @COL_NAME, @Type, @Length, @xprec, @xscale
END
CLOSE      xArgs
DEALLOCATE xArgs
PRINT  '    {'
PRINT  '      CommonData ' + @ObPrefix + 'CommonData = new CommonData();'
PRINT  '      try'
PRINT  '      {'
PRINT  '        iReturn = DatabaseReturnCode.NoErrorDetected;'
PRINT  '        string sConnect = ' + @ObPrefix + 'CommonData.GetConnection();'
PRINT  '        SqlConnection ' + @ObPrefix + 'SqlConnection = new SqlConnection(sConnect);'
PRINT  '        SqlCommand ' + @ObPrefix + 'SqlCommand = new SqlCommand("' + @SpName + '", ' + @ObPrefix + 'SqlConnection);'
PRINT  '        ' + @ObPrefix + 'SqlCommand.CommandType = CommandType.StoredProcedure;'
SELECT @RowIndex = 0
DECLARE xArgs CURSOR FOR
SELECT DISTINCT  C.name, U.name, C.length, C.colid, C.xprec, C.xscale
  FROM syscolumns C, sysobjects O, systypes U
  WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype AND U.name<>'timestamp'
    AND C.name NOT IN ('REPLICASTAMP','CreatePersonID','CreateDate','UpdateDate')
  ORDER BY C.colid, C.name, U.name, C.length
OPEN  xArgs
FETCH   xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscale
WHILE @@FETCH_STATUS=0
BEGIN
  SELECT @PrintLine='        ' + @ObPrefix + 'SqlCommand.Parameters.Add("@' + @COL_NAME + '", SqlDbType.'
  SELECT @SqlDbType = @Type  -- Default assignment
  IF @Type='bigint'        SELECT @SqlDbType = 'BigInt' 
  IF @Type='bit'           SELECT @SqlDbType = 'Bit'
  IF @Type='int'           SELECT @SqlDbType = 'Int'
  IF @Type='decimal'       SELECT @SqlDbType = 'Decimal'
  IF @Type='numeric'       SELECT @SqlDbType = 'Decimal'
  IF @Type='datetime'      SELECT @SqlDbType = 'DateTime'
  IF @Type='char'          SELECT @SqlDbType = 'Char'
  IF @Type='varchar'       SELECT @SqlDbType = 'VarChar'
  IF @Type='nchar'         SELECT @SqlDbType = 'NChar'
  IF @Type='nvarchar'      SELECT @SqlDbType = 'NVarChar'
  IF @Type='ntext'         SELECT @SqlDbType = 'NText'
  IF @Type='text'          SELECT @SqlDbType = 'Text'
  IF @Type='real'          SELECT @SqlDbType = 'Real'
  IF @Type='smalldatetime' SELECT @SqlDbType = 'SmallDateTime'
  IF @Type='smallint'         SELECT @SqlDbType = 'SmallInt'
  IF @Type='smallmoney'    SELECT @SqlDbType = 'SmallMoney'
  IF @Type='float'         SELECT @SqlDbType = 'Float'
  -- more type conversions here !
  SELECT @PrintLine=@PrintLine + @SqlDbType
  IF @Type='char'     SELECT @PrintLine=@PrintLine + ', ' + RTRIM(convert(varchar,@Length)) 
  IF @Type='varchar'  SELECT @PrintLine=@PrintLine + ', ' + RTRIM(convert(varchar,@Length))
  IF @Type='nchar'    SELECT @PrintLine=@PrintLine + ', ' + RTRIM(convert(varchar,@Length/2))
  IF @Type='nvarchar' SELECT @PrintLine=@PrintLine + ', ' + RTRIM(convert(varchar,@Length/2))
  IF @Type='text'     SELECT @PrintLine=@PrintLine + ', ' + RTRIM(convert(varchar,@Length))
  IF @Type='ntext'    SELECT @PrintLine=@PrintLine + ', ' + RTRIM(convert(varchar,@Length/2))
  SELECT @PrintLine=@PrintLine + ');'
  PRINT @PrintLine  
  IF (@Type='decimal') OR (@Type='numeric') BEGIN
    SELECT @PrintLine='        ' + @ObPrefix + 'SqlCommand.Parameters["@' + @COL_NAME + '"].Precision = ' + CONVERT(VARCHAR(3), @xprec) + ';'
    PRINT @PrintLine
    SELECT @PrintLine=' ' + @ObPrefix + 'SqlCommand.Parameters["@' + @COL_NAME + '"].Scale = ' + CONVERT(VARCHAR(3), @xscale) + ';'
    PRINT @PrintLine
    END
  SELECT @RowIndex=@RowIndex + 1
  IF @RowIndex = 1
    SELECT @PrintLine='        ' + @ObPrefix + 'SqlCommand.Parameters["@' + @COL_NAME + '"].Value=iID;'
  ELSE BEGIN
    SELECT @PrintLine='        ' + @ObPrefix + 'SqlCommand.Parameters["@' + @COL_NAME + '"].Value = '
    SELECT @SqlDbType = 'x'  -- Default assignment
    IF @Type='bigint'         SELECT @SqlDbType = 'l'
    IF @Type='bit'            SELECT @SqlDbType = 'b'
    IF @Type='int'            SELECT @SqlDbType = 'i'
    IF @Type='decimal'        SELECT @SqlDbType = 'c'
    IF @Type='numeric'        SELECT @SqlDbType = 'c'
    IF @Type='datetime'       SELECT @SqlDbType = 'dat'
    IF @Type='smalldatetime'  SELECT @SqlDbType = 'dat'
    IF @Type='char'           SELECT @SqlDbType = 's'
    IF @Type='varchar'        SELECT @SqlDbType = 's'
    IF @Type='nchar'          SELECT @SqlDbType = 's'
    IF @Type='nvarchar'       SELECT @SqlDbType = 's'
    IF @Type='text'           SELECT @SqlDbType = 's'
    IF @Type='ntext'          SELECT @SqlDbType = 's'
    IF @Type='smallmoney'     SELECT @SqlDbType = 'c'
    IF @Type='money'          SELECT @SqlDbType = 'c'
    IF @SqlDbType = 'c' BEGIN
      SELECT @PrintLine = @PrintLine + '' + @ObPrefix + 'CommonData.DecimalAdjust('
      END
    SELECT @PrintLine = @PrintLine + @SqlDbType + @COL_NAME
    IF @SqlDbType = 'c' BEGIN
      SELECT @PrintLine = @PrintLine + ', ' + RTRIM(convert(varchar(10),@xscale)) + ')'
      END
  END
  PRINT @PrintLine + ';'
  FETCH   xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscale
END
CLOSE      xArgs
DEALLOCATE xArgs
PRINT  '        ' + @ObPrefix + 'SqlCommand.Parameters.Add("@RETURN", SqlDbType.Int);'
PRINT  '        ' + @ObPrefix + 'SqlCommand.Parameters["@RETURN"].Direction = ParameterDirection.Output;'
PRINT  '        ' + @ObPrefix + 'SqlCommand.Connection.Open();'
PRINT  '        ' + @ObPrefix + 'SqlCommand.ExecuteNonQuery();'
PRINT  '        ' + @ObPrefix + 'SqlCommand.Connection.Close();'
PRINT  '        iReturn = (DatabaseReturnCode)' + @ObPrefix + 'CommonData.NullToInteger(' + @ObPrefix + 'SqlCommand.Parameters["@RETURN"].Value);'
PRINT  '        ' + @ObPrefix + 'SqlCommand.Dispose();'
PRINT  '        ' + @ObPrefix + 'SqlConnection.Dispose();'
PRINT  '      }'
PRINT  '      catch (System.InvalidOperationException ex)'
PRINT  '      {'
PRINT  '        string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;'
PRINT  '        ' + @ObPrefix + 'CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");'
PRINT  '        iReturn = DatabaseReturnCode.NetworkError;'
PRINT  '      }'
PRINT  '      catch (Exception ex)'
PRINT  '      {'
PRINT  '        string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;'
PRINT  '        ' + @ObPrefix + 'CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");'
PRINT  '        iReturn = DatabaseReturnCode.OtherError;'
PRINT  '      }'
PRINT  '    }'
PRINT  '    #endregion'
PRINT  '*/'
PRINT  'GO'
--PRINT  'asp_dbPermissions'
PRINT  @SET_DB
PRINT  'GO'
RETURN
HelpText:
  PRINT '--Use sp_MakeUpdate TABLENAME to generate update S.P. for TABLENAME'
  PRINT '--or  sp_MakeUpdate garbage   to get this help text!'
  PRINT  'GO'
RETURN
-------------- this is the end ----------------
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
0
TATWORTH
4/5/2007 3:09:26 PM

TATWORTH,

Thanks for your Store Procedure, it't helpfull ,but my select Command not select all field in the table, But the Store procedure get all field from the table,that will may cause update fail.

Also ,Is Dot net 2.0 not cater this case for SQLDataSource ? I think that's common Opperation issue at programming, and at less in .net 1.x it provide SQLCommandBuilder for Adeptor to slove the problem.And also that will generate lot of Store procedure at the DB just for the Update/delete/inert purpose ,is the Dot Net 2.0 have solution for it?

thanks a  lot.

0
kevin_lam
4/6/2007 4:01:52 AM

>> but my select Command not select all field in the table,

By using a Select class to return the data from the select, yes all the fields are returned but so what! If you require just a subset of the fields, why not copy the stored procedure, the select method and select return class to a new name just brings back the required subset.

>> may cause update to fail

Again if your form only updates a subset of the fields, copy the stored procedure and update method and create a subset function. In each case, it is immensely easier to take a stored procedure that does all the columns and cut it down to a required subset.

Some people get great mileage out of the SQLCommandBuilder, I prefer to build my own as I can get it to do things like update record locking and maintain audit information.


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
0
TATWORTH
4/6/2007 9:02:01 AM
Reply:

Similar Artilces:

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

need code for updating gridview in .NET 2.0 without using sql data source
hi,this is satish,I have faced problem to update the gridview with template columns in .NET 2.0 without using sql datasource.So please send me the code with events for updating gridview with teplate columns including one dropdownlist inatead of textbox without using sql data source.i hope you will understand my question, please send the answer ASAP.Waiting for reply,regards You will need to deal with it as if it was a traditional GridView in ASP.NET 1.x. Hope this helps! Hammoudeh AhmadWE never know how high we are ... Till we are called to rise And then, if we are true to...

ORA-1722 using PB5.0.03 and SQL*NET 2.3.3.0.0
Hi there! Does anyone know how we can update a numeric datatype field from a datawindow using PB5.0.03, Oracle 7.3, SQL*Net 2.3.3.0.0, Windows 95 on the client side and Windows NT 4.1 on the server side? When we try to change the value of a numeric field, for example from 13 to 12, we get an ORA-1722 when we save the changes back to the database. We have tried the connection parameter disablebind=0 and it works fine, we don't get the error, but we need that parameter set to 1 because we are using stored procedures as data sources for some datawindows in our application. ...

.Net 2.0 Web application using Vb.net is unable to create object of another dll writen in C# .net 2.0
Hi, I habe created one ASP.net web application using Vb.net which is adding reference of dlls written in C# and .net 2.0. But whenever trying to create object of referencing dll, it is throwing error :Object reference does not set to an object. But, locally it is working fine,. In the server i have deployed the .aspx files and dll files in the bin. There were already an web.config in the server which is of .net 1.1. But checked it is retrieving value for that web.config correctly. Should i have to deply any other files and if not what can be the solution for this? Please help. Thanks, So...

Differences between .net 1, .net 1.1, .net 2.0 and .net 3.0 #2
Hi, This seems to be a common question, but i havent got an answer yet:(Can, any one please explain me the differences between these versions.If you keep your feet firmly on the ground, you'll have trouble putting on your pants! There are too many differences for one email - - from 1.0 to 1.1 (not a whole lot of real change, other than fixes, at least compared to 1.1 to 2.0) With 2.0, there were many new declarative controls, with many new ideas added in With 3.0, it's a superset of 2.0 - instead of replacing the installation completely, it just 'added on' new functionality - I would...

How to get datasource name dynamically from .txt / .xml file using C#.net 2.0 with Mysql 5.0
I am using C#.Net 2.0 With MySql 5.0. I created a project and it works fine in my machine. But problem occurs when i try to run the project other than my machine. I wrote datasource=mysystem name in connection string. Is it possible to get datasource name dynamically from a text file (*.txt) or from .xml file for MySql.Regards,Tamizhselvi  Add an App.Config file to your project and you can drop a connection string in there like so:  <connectionStrings>    <add name="PICK A NAME" connectionString="YOUR CONNECTION STRING"/>...

use dw .net 2,0 with SQL SERVER
I want to use dw .net 2,0 with SQL SERVER but I have problems in when use the method settransaction (), I am using oledb. Gracias rudolph124@hotmail.com Please provide a more detailed description of the problem and a code sample if possible. Regards, Dave Fish Sybase On 31 Jul 2006 13:30:52 -0700, bladimir calcina wrote: >I want to use dw .net 2,0 with SQL SERVER but I have >problems in when use the method settransaction (), I am >using oledb. Gracias rudolph124@hotmail.com I want to use dw .net 2.0 with sqlserver and this is totally new for my, was proving...

Differences between .net 1, .net 1.1, .net 2.0 and .net 3.0
Hi, This seems to be a common question, but i havent got an answer yet:(Can, any one please explain me the differences between these versions.If you keep your feet firmly on the ground, you'll have trouble putting on your pants! Have a look into this links http://en.wikipedia.org/wiki/.NET_Framework http://blogs.msdn.com/mohammadakif/archive/2006/12/03/net-3-0-different-versions-of-the-net-framework.aspx http://www.codeproject.com/aspnet/ComparisonASP1xASP20.aspAshok Rajawww.iGold.inDon't forget to click "Mark as Answer" on the post that helped you. This credits that m...

Error with using .Net 3.0 instead of .Net 2.0
Hi,  I have recently made a website in microsoft visual web developer 2005.  When I have uploaded my site to my host I am having trouble.  when I visit the page www.amcnewzealand.co.nz I get an error message.  My web hosting company does not support .net 3.0 but instead use .net 2.0.  My website is made with file extensions .aspx.  I am usure how to tell what version of .net I am using and wether this is the only cause.  At first there was a 403 error occuring. Help would be much appreciated as I have been struggling with this for some time. The version...

SQL query that works in SQL Server Management Studio, but doesn't on .NET 2.0 page
SELECT favorites.FID, favorites.filename, favorites.username, files.status, files.private, files.views, files.title FROM favorites INNER JOIN files ON favorites.filename = files.filename WHERE (favorites.username = @username) AND (files.status IS NULL) AND (files.private = @private)@private is manually set to 'no'@username is set to profile.usernamewhen I run the above query in microsoft sql server mgmt studio express on the database, it presents all the information i'm asking for very easily. however when i try to implement this query on a aspx .net2.0 page with teh sqldatasourc...

.NET 2.0 Reporting Module using SQL Server Reporting Services
Hello Everyone,I have created a generic module using ASP.NET 2.0, MS SQL Server 2005 and SQL Server 2005 Reporting Services. This module is intended to provide a generic user interface for selecting and generating reports. You can configure its respective table for your fixed or flexible parameters for each report. It also supports end user access level security for each report and report group which is generally required for any basic reporting module.You can view reports in two different styles:-- Drop down--Tree View And You can view your report in 3 supported formats.  This is an in...

PB 10.2.0 Connect SQL Server Using ADO.NET Provider
I have a problem with connection to SQL Server database using ADO.NET after upgrading to PB10.2.0 build 8011 runtime on the client machine. But it worked fine in version 10.0.1 build 6044 . Anyone can help me to figure ot that problem? TIA. John Ho. Can you try to provide details of the problem? <John Ho> wrote in message news:427aeaa1.7f95.1681692777@sybase.com... >I have a problem with connection to SQL Server database > using ADO.NET after upgrading to PB10.2.0 build 8011 runtime > on the client machine. But it worked fine in version 10.0.1 > build 604...

Using .Net 2.0 and System.Web.UI.WebControls.Treeview with SQL Reporting Services 2005 for navigation and display.
I spent several hours sifting through article after article about how to build tree navigations dynamically.  They assumed that the order of the data coming in was 'orderly' and that you were usually using the IE Web Control toolkit which is a .Net 1.0 package.  In our environment 2.0+, that would not work for us and we needed to ensure browser compatibility.  Thanks to a colleagues suggestion of recursion, I came up with the following code below.  It's length is due to all the detailed commenting.  Feel free to comment on it, any positives or...

7.3 Driver for Pb6.0 and SQL Net 2.2
Hi We are trying to migrate to Pb 6.0 from Pb 5.0. During our 5.0 development we have been using 7.3 driver for backend procedures and such. In migrating to 6.0 we realized that we need to upgrade to SQL Net 2.3 but it is not possible at this time. Does anyone know if there is Pb 6.0 /7.3 driver that will work with SQL Net 2.2? Thanks in advance. PB6 ships with the O72 driver. You can also load the Oracle Required Support Files for 7.3 and then use the O73 driver with sqlnet 2.2 -- Terry Dykstra [TeamPS] Canadian Forest Oil Ltd. ishtern <ishtern@kineticgroup.com> wr...