filling the textbox,dropdownlist from the database?? : The Official Microsoft ASP.NET Forums

Reply

TATWORTH

All-Star

72415 Points

14017 Posts

MVP

Re: filling the textbox,dropdownlist from the database??

Mar 08, 2008 11:36 AM|LINK

The stored procedure that generated it was:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[csp_MakeInsert]
(
@TABLENAME VARCHAR(255)= 'xyzzy',
@LANG      CHAR(2) = '' -- VB or C# (default set in configration section)
) AS
-- Purpose:
--   Create Insert script from table definition
-- Copyright (C) 2000, 2003, 2004, 2005, 2006, 2007, 2008 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 development server
--      csp_MakeInsert TABLENAME XX
--      where XX is either C# or VB
--   2) The TABLENAME is case insensitive, however the table must exist
--   3) Id and TIMESTAMP columns are bypassed
-- Changes:
-- 11Nov2007 Clive Convert to SQL2005 Operation (n.b. this cannot reside in Master as a result)
-- 24Jun2006 Clive Make VB/C# switchable
-- 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
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)
DECLARE @VarPrefix VARCHAR(10)
DECLARE @DefLanguage CHAR(2)
DECLARE @Separator2 VARCHAR(1)
DECLARE @COMMENT    VARCHAR(128)
DECLARE @ALLOWED    BIT
SELECT @ALLOWED = 0
SELECT @LANG = RTRIM(UPPER(LTRIM(@LANG)))
IF @LANG = 'VB' SELECT @ALLOWED = 1
IF @LANG = 'C#' SELECT @ALLOWED = 1
-- Configuration start
SELECT @SpPrefix = 'usp_'          -- Set to desired prefix for stored procedures
SELECT @DOM_NAME = 'DALETH' + '\' -- Set to your domain name
-- Set @SET_DB to name of SP to set permissions or to blank
SELECT @SET_DB   = 'asp_dbPermissions'
SELECT @ObPrefix = 'x'             -- Prefix for object variables
SELECT @DefLanguage = 'VB'         -- Default language C# or VB
SELECT @Separator2 = ''           -- Separator between Tablename and action
-- Configuration end
IF @ALLOWED = 0 SET @LANG = @DefLanguage
IF @TABLENAME ='xyzzy'
OR (NOT EXISTS (SELECT * FROM sysobjects
      WHERE UPPER(name)= UPPER(@TABLENAME) and type = 'U'))
BEGIN
PRINT '--Use csp_MakeInsert TABLENAME (XX) to generate insert S.P. for TABLENAME'
PRINT '--or csp_MakeInsert garbage   to get this help text!'
PRINT ' and XX is the language for the Wrapper VB or C#'
RETURN
END
SELECT @TN = name FROM sysobjects
WHERE UPPER(name)= UPPER(@TABLENAME) and type = 'U'
DECLARE @DocTable TABLE
(
Comment VARCHAR(128),
Colname VARCHAR(128),
Coltype VARCHAR(128),
Cstatus INT,
ColId   INT,
Cid     INT,
length INT,
xprec   INT,
xscale INT
)
INSERT INTO @DocTable (Comment, Colname, Coltype, Cstatus,
ColId, Cid, length, xprec, xscale)
SELECT COALESCE(CONVERT(VARCHAR(130),P.value),
'[Undocumented in Database]') AS Comment,
C.name AS Colname, UPPER(U.name) AS Coltype, C.status AS Cstatus,
C.ColId, C.Id AS Cid, C.length, C.xprec, C.xscale
FROM syscolumns C
JOIN sys.tables O ON C.id = O.object_id
JOIN systypes   U ON C.xusertype = U.xusertype
LEFT OUTER JOIN SYS.extended_properties P ON P.minor_id = C.COLID AND C.id = P.major_id
WHERE O.name = @TN AND U.name <> 'timestamp'
ORDER BY C.colid
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 @SpName = @SpPrefix + RTRIM(@TN) + @Separator2 + '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   '('
--PRINT '144'
SELECT @HAS_LAST = 0
SELECT @RowIndex = 0
SELECT @RowCount=COUNT(*) FROM @DocTable
WHERE Cstatus<>128 AND Colname NOT IN
    ('CreateDate','UpdateDate','UpdatePersonId','UpdateCount')
SELECT @HasIdent=COUNT(*) FROM @DocTable WHERE Cstatus=128
DECLARE xArgs CURSOR FOR
SELECT Colname, Coltype, length, xprec, xscale, Comment
FROM @DocTable
WHERE Cstatus<>128 AND Colname NOT IN
    ('CreateDate','UpdateDate','UpdatePersonId','UpdateCount')
ORDER BY ColId
--PRINT '162'
SELECT @RowIndex=0
OPEN    xArgs
FETCH   xArgs INTO @ArgName, @Type, @Length, @xprec, @xscale, @Comment
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 + ', -- ' + @Comment
PRINT @PrintLine
FETCH xArgs INTO @ArgName, @Type, @Length, @xprec, @xscale, @Comment
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
--SELECT * FROM @DocTable
PRINT   '-- Parameters:'
DECLARE xArgs CURSOR FOR
SELECT Colname, Comment, length
FROM @DocTable
WHERE Cstatus <> 128 AND Colname NOT IN
    ('CreateDate','UpdateDate','UpdatePersonId','UpdateCount')
ORDER BY ColId
OPEN xArgs
FETCH xArgs INTO @ArgName, @Comment, @ColLength1
WHILE @@FETCH_status=0
BEGIN
SELECT @ArgName = @ArgName + '                               '
SELECT @PrintLine = '--    ' + SUBSTRING(@ArgName,1,@ColLength1) + '-'
PRINT @PrintLine
FETCH xArgs INTO @ArgName, @Comment, @ColLength1
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() '
PRINT 'RETURN'
PRINT '-------------- this is the end ----------------'
PRINT '/* Data Access code follows:'
DECLARE @SqlDbType VARCHAR(20)
SELECT @RowIndex = 0
IF @LANG = 'VB' BEGIN
PRINT '    #Region " Insert "'
PRINT '    '''''' <summary> '
PRINT '    '''''' Insert record into ' + @TN + ' table '
PRINT '    '''''' </summary>'
PRINT '    '''''' <remarks>'
PRINT '    '''''' This code was autogenerated on ' + @DateWork
PRINT '    '''''' </remarks>'
END
ELSE BEGIN
PRINT '    #region " Insert "'
PRINT '    /// <summary> '
PRINT '    /// Insert record into ' + @TN + ' table '
PRINT '    /// </summary>'
PRINT '    /// <remarks>'
PRINT '    /// This code was autogenerated on ' + @DateWork
PRINT '    /// </remarks>'
END
DECLARE xArgs CURSOR FOR
SELECT COALESCE(CONVERT(VARCHAR(130),P.value),
'[Undocumented in Database]') AS COMMENT, -- C.COLID,C.id,
C.name, U.name, C.length, C.xprec, C.xscale
FROM syscolumns C
JOIN sys.tables O ON C.id = O.object_id
JOIN systypes   U ON C.xusertype=U.xusertype
LEFT OUTER JOIN SYS.extended_properties P ON P.minor_id = C.COLID AND C.id = P.major_id
WHERE O.name = @TN
    AND U.name<>'timestamp' AND C.name NOT IN
    ('Id','REPLICASTAMP','CreatePersonId','CreateDate','UpdateDate','UpdateCount')
ORDER BY C.colid
OPEN    xArgs
FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, @xprec, @xscale
WHILE @@FETCH_STATUS=0
BEGIN
IF @LANG = 'VB'
SELECT @PrintLine = '    '''''' <param name="'
ELSE
    SELECT @PrintLine = '    /// <param name="'
SELECT @SqlDbType = 'x' -- Default assignment
IF @Type='bigint'     SELECT @VarPrefix = 'l'
IF @Type='bit'        SELECT @VarPrefix = 'b'
IF @Type='int'        SELECT @VarPrefix = 'i'
IF @Type='decimal'    SELECT @VarPrefix = 'c'
IF @Type='smallmoney' SELECT @VarPrefix = 'c'
IF @Type='numeric'    SELECT @VarPrefix = 'c'
IF @Type='datetime'   SELECT @VarPrefix = 'dat'
IF @Type='char'       SELECT @VarPrefix = 's'
IF @Type='varchar'    SELECT @VarPrefix = 's'
IF @Type='nchar'      SELECT @VarPrefix = 's'
IF @Type='nvarchar'   SELECT @VarPrefix = 's'
IF @Type='text'       SELECT @VarPrefix = 's'
IF @Type='ntext'      SELECT @VarPrefix = 's'
--SELECT @COMMENT = 'TBA'
--IF @COL_NAME = 'CreatePersonId' SELECT @COMMENT = 'Id of user from GetUserPersonId'
-- More comment signs here ?
SELECT @PrintLine = @PrintLine + @VarPrefix + @COL_NAME + '">' + @COMMENT + '</param>'
PRINT @PrintLine
FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, @xprec, @xscale
END
CLOSE      xArgs
DEALLOCATE xArgs
IF @LANG = 'VB' BEGIN
PRINT '    '''''' <param name="iReturn">Return code to use in switch statement</param>'
PRINT '    '''''' <param name="iNewId">Id of new record</param>'
PRINT '    Public Sub Insert' + @TN + '( _'
END
ELSE BEGIN
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 + '('
END
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 @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 @VarPrefix = 'x' -- Default assignment
    IF @Type='bigint'     SELECT @VarPrefix = 'l'
    IF @Type='bit'        SELECT @VarPrefix = 'b'
    IF @Type='int'        SELECT @VarPrefix = 'i'
    IF @Type='decimal'    SELECT @VarPrefix = 'c'
    IF @Type='smallmoney' SELECT @VarPrefix = 'c'
    IF @Type='numeric'    SELECT @VarPrefix = 'c'
    IF @Type='datetime'   SELECT @VarPrefix = 'dat'
    IF @Type='char'       SELECT @VarPrefix = 's'
    IF @Type='varchar'    SELECT @VarPrefix = 's'
    IF @Type='nchar'      SELECT @VarPrefix = 's'
    IF @Type='nvarchar'   SELECT @VarPrefix = 's'
    IF @Type='text'       SELECT @VarPrefix = 's'
    IF @Type='ntext'      SELECT @VarPrefix = 's'
    SELECT @PrintLine = '      '
    IF @LANG = 'VB' BEGIN
      If @SqlDbType = 'bit'     SELECT @SqlDbType = 'Boolean'
      If @SqlDbType = 'decimal' SELECT @SqlDbType = 'Decimal'
      If @SqlDbType = 'int'     SELECT @SqlDbType = 'Integer'
      If @SqlDbType = 'string' SELECT @SqlDbType = 'String'
      SELECT @PrintLine = @PrintLine + @VarPrefix + @COL_NAME
      SELECT @PrintLine = @PrintLine + ' As ' + @SqlDbType
      IF @RowIndex = @RowCount
        SELECT @PrintLine = @PrintLine + ', ByRef iReturn As DatabaseReturnCode, ByRef iNewId As Integer)'
      ELSE
        SELECT @PrintLine = @PrintLine + ', _ '
      END
    ELSE BEGIN
      SELECT @PrintLine = @PrintLine + @SqlDbType + ' '
      SELECT @PrintLine = @PrintLine + @VarPrefix + @COL_NAME
      IF @RowIndex = @RowCount
        SELECT @PrintLine = @PrintLine + ', ref DatabaseReturnCode iReturn, ref int iNewId)'
      ELSE
        SELECT @PrintLine = @PrintLine + ', '
      END
    PRINT @PrintLine
END
FETCH   xArgs INTO @COL_NAME, @Type, @Length, @xprec, @xscale
END
CLOSE      xArgs
DEALLOCATE xArgs
IF @LANG = 'VB' BEGIN
PRINT '      iReturn = DatabaseReturnCode.NoErrorDetected'
PRINT '      iNewId = 0'
PRINT '      Dim sConnect As String = CommonData.GetConnect()'
PRINT '      Dim ' + @ObPrefix + 'SqlConnection As SqlConnection = new SqlConnection(sConnect)'
PRINT '      Dim ' + @ObPrefix + 'SqlCommand As SqlCommand = new SqlCommand("'
                    + @SpName + '", ' + @ObPrefix + 'SqlConnection)'
PRINT '        ' + @ObPrefix + 'SqlCommand.CommandType = CommandType.StoredProcedure'
PRINT '      Try'
END
ELSE BEGIN
PRINT '    {'
PRINT '      iReturn = DatabaseReturnCode.NoErrorDetected;'
PRINT '      iNewId = 0;'
PRINT '      string sConnect = CommonData.GetConnect();'
PRINT '      SqlConnection ' + @ObPrefix + 'SqlConnection = new SqlConnection(sConnect);'
PRINT '      SqlCommand ' + @ObPrefix + 'SqlCommand = new SqlCommand("'
                    + @SpName + '", ' + @ObPrefix + 'SqlConnection);'
PRINT '      ' + @ObPrefix + 'SqlCommand.CommandType = CommandType.StoredProcedure;'
PRINT '      try'
PRINT '      {'
END
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 + ')'
    IF @LANG = 'C#' SELECT @PrintLine = @PrintLine + ';'
    PRINT @PrintLine
    IF (@Type='decimal') OR (@Type='numeric') BEGIN
      IF @LANG = 'VB' 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
      ELSE 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
    END
    IF @LANG = 'VB'
      SELECT @PrintLine='        ' + @ObPrefix + 'SqlCommand.Parameters("@'
          + @COL_NAME + '").Value = '
    ELSE
      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
   IF @LANG = 'C#' SELECT @PrintLine = @PrintLine + ';'
   PRINT @PrintLine
END
FETCH   xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscale
END
CLOSE      xArgs
DEALLOCATE xArgs
IF @LANG = 'VB' BEGIN
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 = CType('
      + 'CommonData.NullToInteger(' + @ObPrefix + 'SqlCommand.Parameters("@Return").Value), DatabaseReturnCode)'
PRINT '        iNewId = CommonData.NullToInteger('
   + @ObPrefix + 'SqlCommand.Parameters("@Identity").Value)'
--PRINT '        ' + @ObPrefix + 'SqlCommand.Dispose()'
--PRINT '        ' + @ObPrefix + 'SqlConnection.Dispose()'
PRINT '      Catch ex As System.InvalidOperationException'
PRINT '        Dim sMethod As String = System.Reflection.MethodInfo.GetCurrentMethod().Name'
PRINT '        CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "")'
PRINT '        iReturn = DatabaseReturnCode.NetworkError'
PRINT '      Catch ex As Exception'
PRINT '        Dim sMethod As String = System.Reflection.MethodInfo.GetCurrentMethod().Name'
PRINT '        CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "")'
PRINT '        iReturn = DatabaseReturnCode.OtherError'
PRINT '      Finally '
--PRINT '        ' + @ObPrefix + 'SqlCommand.Connection.Close()'
PRINT '        ' + @ObPrefix + 'SqlCommand.Dispose()'
PRINT '        ' + @ObPrefix + 'SqlConnection.Dispose()'
PRINT '      End Try'
PRINT '    End Sub'
PRINT '    #End Region'
END
ELSE BEGIN
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)'
      + 'CommonData.NullToInteger(' + @ObPrefix + 'SqlCommand.Parameters["@Return"].Value);'
PRINT '        iNewId = 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 '        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 '        CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");'
PRINT '        iReturn = DatabaseReturnCode.OtherError;'
PRINT '      }'
PRINT '      finally'
PRINT '      {'
--PRINT '        ' + @ObPrefix + 'SqlCommand.Connection.Close();'
PRINT '        ' + @ObPrefix + 'SqlCommand.Dispose();'
PRINT '        ' + @ObPrefix + 'SqlConnection.Dispose();'
PRINT '      }'
PRINT '    }'
PRINT '    #endregion'
END
PRINT '*/'
PRINT 'GO'
PRINT @SET_DB --
PRINT 'GO'
RETURN
-------------- this is the end ----------------
GO

Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239

Edited by TATWORTH on Mar 08, 2008 02:08 PM

Reply

TATWORTH

All-Star

72415 Points

14017 Posts

MVP

Re: filling the textbox,dropdownlist from the database??

Mar 08, 2008 11:41 AM|LINK

Now using the following

csp_MakeUpdate 'Details', 'C#'

I generated

CREATE PROCEDURE dbo.usp_detailsUpdate
(
@id               INT,
@Title            VARCHAR (50),
@Name             VARCHAR (50),
@Email            VARCHAR (50),
@teamemtable_id   INT,
@Return           INT            OUTPUT
) AS
-- Purpose:
--    Update record on details table
-- Parameters:
--    id              - [Undocumented in Database]
--    Title           - [Undocumented in Database]
--    Name            - [Undocumented in Database]
--    Email           - [Undocumented in Database]
--    teamemtable_id - [Undocumented in Database]
--    Return          - Return value
-- History:
-- 08Mar2008 ACERXP\Clive Original coding
SET NOCOUNT ON
UPDATE details SET
Title            = @Title,
Name             = @Name,
Email            = @Email,
teamemtable_id   = @teamemtable_id
WHERE
id = @id
SELECT @Return = @@error
RETURN
-------------- this is the end ----------------
GO
asp_dbPermissions
GO

Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239

Reply

TATWORTH

All-Star

72415 Points

14017 Posts

MVP

Re: filling the textbox,dropdownlist from the database??

Mar 08, 2008 11:42 AM|LINK

The generated C# wrapper code was

#region " Update "
/// <summary>
/// Update record on details table
/// </summary>
/// <remarks>
/// This code was autogenerated on 08Mar2008
/// </remarks>
/// <param name="iid">[Undocumented in Database]</param>
/// <param name="sTitle">[Undocumented in Database]</param>
/// <param name="sName">[Undocumented in Database]</param>
/// <param name="sEmail">[Undocumented in Database]</param>
/// <param name="iteamemtable_id">[Undocumented in Database]</param>
/// <param name="iReturn">Return code to use in switch statement</param>
public void Updatedetails(int iId,
    string sTitle,
    string sName,
    string sEmail,
    int iteamemtable_id, ref DatabaseReturnCode iReturn)
{
    iReturn = DatabaseReturnCode.NoErrorDetected;
    string sConnect = CommonData.GetConnect();
    SqlConnection xSqlConnection = new SqlConnection(sConnect);
    SqlCommand xSqlCommand = new SqlCommand("usp_detailsUpdate", xSqlConnection);
    xSqlCommand.CommandType = CommandType.StoredProcedure;
    try
    {
      xSqlCommand.Parameters.Add("@id", SqlDbType.Int);
      xSqlCommand.Parameters["@id"].Value = iId; ;
      xSqlCommand.Parameters.Add("@Title", SqlDbType.VarChar, 50);
      xSqlCommand.Parameters["@Title"].Value = sTitle;
      xSqlCommand.Parameters.Add("@Name", SqlDbType.VarChar, 50);
      xSqlCommand.Parameters["@Name"].Value = sName;
      xSqlCommand.Parameters.Add("@Email", SqlDbType.VarChar, 50);
      xSqlCommand.Parameters["@Email"].Value = sEmail;
      xSqlCommand.Parameters.Add("@teamemtable_id", SqlDbType.Int);
      xSqlCommand.Parameters["@teamemtable_id"].Value = iteamemtable_id;
      xSqlCommand.Parameters.Add("@Return", SqlDbType.Int);
      xSqlCommand.Parameters["@Return"].Direction = ParameterDirection.Output;
      xSqlCommand.Connection.Open();
      xSqlCommand.ExecuteNonQuery();
      xSqlCommand.Connection.Close();
      iReturn = (DatabaseReturnCode)CommonData.NullToInteger(xSqlCommand.Parameters["@Return"].Value);
      xSqlCommand.Dispose();
      xSqlConnection.Dispose();
    }
    catch (System.InvalidOperationException ex)
    {
      string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;
      CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");
      iReturn = DatabaseReturnCode.NetworkError;
    }
    catch (Exception ex)
    {
      string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;
      CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");
      iReturn = DatabaseReturnCode.OtherError;
    }
    finally
    {
      if (xSqlCommand.Connection != null) xSqlCommand.Connection.Close();
      xSqlCommand.Dispose();
      xSqlConnection.Dispose();
    }
}
#endregion

Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239

Reply

TATWORTH

All-Star

72415 Points

14017 Posts

MVP

Re: filling the textbox,dropdownlist from the database??

Mar 08, 2008 11:44 AM|LINK

The stored procedure that generated it was:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[csp_MakeUpdate]
(
@TABLENAME VARCHAR(255)= 'xyzzy',
@LANG      CHAR(2) = '' -- VB or C# (default set in configration section)
) 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 the database under development
--      csp_MakeUpdate 'TABLENAME', 'XX'
--      where XX is C# or VB
--   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:
-- 11Nov2007 Clive Convert to SQL2005 Operation (n.b. this cannot reside in Master as a result)
-- 24Jun2006 Clive Make VB/C# switchable
-- 09Oct2005 Clive Update documentation
-- 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
-- 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)
DECLARE @DefLanguage CHAR(2)
DECLARE @Separator2 VARCHAR(1)
DECLARE @SqlDbType VARCHAR(20)
DECLARE @COMMENT    VARCHAR(128)
DECLARE @ALLOWED    BIT
SELECT @ALLOWED = 0
SELECT @LANG = RTRIM(UPPER(LTRIM(@LANG)))
IF @LANG = 'VB' SELECT @ALLOWED = 1
IF @LANG = 'C#' SELECT @ALLOWED = 1
-- Configuration start
SELECT @SpPrefix = 'usp_'          -- Set to desired prefix for stored procedures
SELECT @DOM_NAME = 'DALETH' + '\' -- Set to your domain name
-- Set @SET_DB to name of SP to set permissions or to blank
SELECT @SET_DB   = 'asp_dbPermissions'
SELECT @ObPrefix = 'x'             -- Prefix for object variables
SELECT @DefLanguage = 'VB'         -- Default language C# or VB
SELECT @Separator2 = ''           -- Separator between Tablename and action
-- Configuration end
IF @ALLOWED = 0 SET @LANG = @DefLanguage
IF @TABLENAME ='xyzzy'
OR (NOT EXISTS (SELECT * FROM sysobjects
    WHERE UPPER(name)=UPPER(@TABLENAME) and type='U'))
BEGIN
PRINT '--Use csp_MakeUpdate TABLENAME (XX) to generate update '
PRINT 'S.P. for TABLENAME and XX is language for Wrapper VB or C#'
PRINT '--or csp_MakeUpdate garbage   to get this help text!'
PRINT 'GO'
RETURN
END
SELECT @TN = name FROM sysobjects
WHERE UPPER(name)=UPPER(@TABLENAME) and type='U'
DECLARE @DocTable TABLE
(
Comment VARCHAR(128),
Colname VARCHAR(128),
Coltype VARCHAR(128),
Cstatus INT,
ColId   INT,
Cid     INT,
length INT,
xprec   INT,
xscale INT
)
INSERT INTO @DocTable (Comment, Colname, Coltype, Cstatus,
ColId, Cid, length, xprec, xscale)
SELECT COALESCE(CONVERT(VARCHAR(130),P.value),
'[Undocumented in Database]') AS Comment,
C.name AS Colname, UPPER(U.name) AS Coltype, C.status AS Cstatus,
C.ColId, C.Id AS Cid, C.length, C.xprec, C.xscale
FROM syscolumns C
JOIN sys.tables O ON C.id = O.object_id
JOIN systypes   U ON C.xusertype = U.xusertype
LEFT OUTER JOIN SYS.extended_properties P ON P.minor_id = C.COLID AND C.id = P.major_id
WHERE O.name = @TN AND U.name <> 'timestamp'
ORDER BY C.colid
SELECT @HasTSTAMP = (SELECT COUNT(*) FROM @DocTable WHERE Colname =
-- Begin updateable
'UpdateCount')
-- End Updateable
SELECT @SpName = @SpPrefix + RTRIM(@TN) + @Separator2 + 'Update'
SELECT @ColLength = MAX(DATALENGTH(RTRIM(CONVERT(VARCHAR(80),Colname))))
FROM @DocTable
WHERE Cstatus <> 128
IF @ColLength < 14 SET @ColLength = 14
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 @DocTable
WHERE Coltype <> 'timestamp' AND Colname NOT IN
-- Begin updateable
    ('CreatePersonId', 'CreateDate','UpdateDate')
-- End Updateable
SELECT @HasIdent=COUNT(*) FROM @DocTable WHERE Cstatus=128
DECLARE xArgs CURSOR FOR
SELECT '@' + Colname, Coltype, length, xprec, xscale
FROM @DocTable
WHERE Coltype <> 'timestamp' AND Colname NOT IN
-- Begin updateable
       ('CreatePersonId', 'CreateDate', 'UpdateDate')
-- End Updateable
ORDER BY 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 Colname, Comment FROM @DocTable
WHERE Colname NOT IN
-- Begin updateable
    ('CreatePersonId', 'CreateDate','UpdateDate')
-- End Updateable
ORDER BY colid
OPEN xArgs
FETCH xArgs INTO @ArgName, @COMMENT
WHILE @@FETCH_status=0
BEGIN
SELECT @ArgName=SUBSTRING(@ArgName
       + '                            ',1,@ColLength + 2)
SELECT @PrintLine='--    ' + @ArgName + '- ' + @COMMENT
PRINT @PrintLine
FETCH xArgs INTO @ArgName, @COMMENT
END
CLOSE xArgs
DEALLOCATE xArgs
PRINT '--    Return          - Return value'
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 @DocTable
WHERE Colname NOT IN ('CreatePersonId','CreateDate')
    AND Cstatus <> 128
DECLARE xArgs CURSOR FOR
SELECT Colname FROM @DocTable
WHERE Colname NOT IN ('CreatePersonId','CreateDate')
    AND Cstatus <> 128
ORDER BY 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 @DocTable
-- There is a missing where clause here!
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 @DocTable
     WHERE Cstatus=128
    DECLARE xArgs CURSOR FOR
      SELECT Colname + ' = @' + Colname
      FROM @DocTable
      WHERE Cstatus=128
      ORDER BY 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
PRINT '-------------- this is the end ----------------'
PRINT '/* Data Access code follows:'
SELECT @RowIndex = 0
IF @LANG = 'VB' BEGIN
PRINT '    #Region " Update "'
PRINT '    '''''' <summary> '
PRINT '    '''''' Update record on ' + @TN + ' table '
PRINT '    '''''' </summary>'
PRINT '    '''''' <remarks>'
PRINT '    '''''' This code was autogenerated on ' + @DateWork
PRINT '    '''''' </remarks>'
END
ELSE BEGIN
PRINT '    #region " Update "'
PRINT '    /// <summary> '
PRINT '    /// Update record on ' + @TN + ' table '
PRINT '    /// </summary>'
PRINT '    /// <remarks>'
PRINT '    /// This code was autogenerated on ' + @DateWork
PRINT '    /// </remarks>'
END
DECLARE xArgs CURSOR FOR -- need comment fetch
SELECT Comment, Colname, Coltype, length, xprec, xscale
FROM @DocTable
WHERE Colname NOT IN
-- Begin updateable
    ('CreatePersonId','CreateDate','UpdateDate')
-- End Updateable
ORDER BY ColId
OPEN    xArgs
FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, @xprec, @xscale
WHILE @@FETCH_STATUS=0
BEGIN
IF @LANG = 'VB'
    SELECT @PrintLine = '    '''''' <param name="'
ELSE
    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'
--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
IF @LANG = 'VB' BEGIN
PRINT '    '''''' <param name="iReturn">Return code to '
      + 'use in switch statement</param>'
PRINT '    Public Sub Update' + @TN + '(iId As Integer, _'
END
ELSE BEGIN
PRINT '    /// <param name="iReturn">Return code to use'
      + ' in switch statement</param>'
PRINT '    public void Update' + @TN + '(int iId,'
END
SELECT @RowCount=COUNT(*) FROM @DocTable
WHERE Colname NOT IN
-- Begin updateable
     ('CreatePersonId','CreateDate','UpdateDate')
-- End Updateable
DECLARE @PREFIX VARCHAR(10)
DECLARE xArgs CURSOR FOR
SELECT Colname, Coltype, length, xprec, xscale
FROM @DocTable
WHERE Colname NOT IN
-- Begin updateable
      ('CreatePersonId','CreateDate','UpdateDate')
-- End Updateable
ORDER BY 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 @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 @PREFIX = 'x' -- Default assignment
    IF @Type='bigint'         SELECT @PREFIX    = 'l'
    IF @Type='bit'            SELECT @PREFIX    = 'b'
    IF @Type='int'            SELECT @PREFIX    = 'i'
    IF @Type='decimal'        SELECT @PREFIX    = 'c'
    IF @Type='numeric'        SELECT @PREFIX    = 'c'
    IF @Type='datetime'       SELECT @PREFIX    = 'dat'
    IF @Type='smalldatetime' SELECT @PREFIX    = 'dat'
    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 @PrintLine = '      '
    IF @LANG = 'VB' BEGIN
      IF @SqlDbType = 'int'     SELECT @SqlDbType = 'Integer'
      IF @SqlDbType = 'string' SELECT @SqlDbType = 'String'
      IF @SqlDbType = 'decimal' SELECT @SqlDbType = 'Decimal'
      IF @SqlDbType = 'bit'     SELECT @SqlDbType = 'Boolean'
      IF @SqlDbType = 'bool'    SELECT @SqlDbType = 'Boolean'
      SELECT @PrintLine = @PrintLine
           + @PREFIX     + @COL_NAME + ' As '
      SELECT @PrintLine = @PrintLine + @SqlDbType -- + ' '
      END
    ELSE BEGIN
      SELECT @PrintLine = @PrintLine + @SqlDbType + ' '
      SELECT @PrintLine = @PrintLine + @PREFIX     + @COL_NAME
      END
    IF @RowIndex = @RowCount
      IF @LANG = 'VB'
        SELECT @PrintLine = @PrintLine
             + ', ByRef iReturn As DatabaseReturnCode)'
      ELSE
        SELECT @PrintLine = @PrintLine
             + ', ref DatabaseReturnCode iReturn)'
    ELSE BEGIN
      SELECT @PrintLine = @PrintLine + ', '
      IF @LANG = 'VB' SELECT @PrintLine = @PrintLine + ' _ '
      END
    PRINT @PrintLine
END
FETCH   xArgs INTO @COL_NAME, @Type, @Length, @xprec, @xscale
END
CLOSE      xArgs
DEALLOCATE xArgs
IF @LANG = 'VB' BEGIN
PRINT '      iReturn = DatabaseReturnCode.NoErrorDetected'
PRINT '      Dim sConnect As String = CommonData.GetConnect()'
PRINT '      Dim ' + @ObPrefix
      + 'SqlConnection As SqlConnection = new SqlConnection(sConnect)'

PRINT '      Dim ' + @ObPrefix
      + 'SqlCommand As SqlCommand = New SqlCommand("'
      + @SpName + '", ' + @ObPrefix + 'SqlConnection)'
PRINT '      ' + @ObPrefix
      + 'SqlCommand.CommandType = CommandType.StoredProcedure'
PRINT '      Try'
END
ELSE BEGIN
PRINT '    {'
PRINT '      iReturn = DatabaseReturnCode.NoErrorDetected;'
PRINT '      string sConnect = CommonData.GetConnect();'
PRINT '      SqlConnection ' + @ObPrefix
      + 'SqlConnection = new SqlConnection(sConnect);'
PRINT '      SqlCommand ' + @ObPrefix
      + 'SqlCommand = new SqlCommand("'
      + @SpName + '", ' + @ObPrefix + 'SqlConnection);'
PRINT '      ' + @ObPrefix
      + 'SqlCommand.CommandType = CommandType.StoredProcedure;'
PRINT '      try'
PRINT '      {'
END
SELECT @RowIndex = 0
DECLARE xArgs CURSOR FOR
SELECT DISTINCT Colname, Coltype, length, ColId, xprec, xscale
FROM @DocTable
WHERE Colname NOT IN
-- Begin updateable
('REPLICASTAMP','CreatePersonId','CreateDate','UpdateDate')
-- End Updateable
ORDER BY ColId, Colname
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))
IF @LANG = 'C#'
    SELECT @PrintLine=@PrintLine + ');'
ELSE
    SELECT @PrintLine=@PrintLine + ')'
PRINT @PrintLine
IF (@Type='decimal') OR (@Type='numeric') BEGIN
    IF @LANG = 'VB'
      SELECT @PrintLine='        ' + @ObPrefix
        + 'SqlCommand.Parameters("@'
        + @COL_NAME + '").Precision = '
        + CONVERT(VARCHAR(3), @xprec)
    ELSE
      SELECT @PrintLine='        ' + @ObPrefix
        + 'SqlCommand.Parameters["@'
        + @COL_NAME + '"].Precision = '
        + CONVERT(VARCHAR(3), @xprec) + ';'
    PRINT @PrintLine
    IF @LANG = 'VB'
      SELECT @PrintLine=' ' + @ObPrefix
          + 'SqlCommand.Parameters("@'
          + @COL_NAME + '").Scale = '
          + CONVERT(VARCHAR(3), @xscale)
    ELSE
      SELECT @PrintLine=' ' + @ObPrefix
           + 'SqlCommand.Parameters["@'
           + @COL_NAME + '"].Scale = '
           + CONVERT(VARCHAR(3), @xscale) + ';'
    PRINT @PrintLine
    END
SELECT @RowIndex=@RowIndex + 1
IF @RowIndex = 1
    IF @LANG = 'VB'
      SELECT @PrintLine='        ' + @ObPrefix
          + 'SqlCommand.Parameters("@'
          + @COL_NAME + '").Value=iId'
    ELSE
      SELECT @PrintLine='        ' + @ObPrefix
          + 'SqlCommand.Parameters["@'
        + @COL_NAME + '"].Value=iId;'
ELSE BEGIN
    IF @LANG = 'VB'
      SELECT @PrintLine='        ' + @ObPrefix
           + 'SqlCommand.Parameters("@'
           + @COL_NAME + '").Value = '
    ELSE
      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
IF @LANG = 'VB'
    PRINT @PrintLine
ELSE
    PRINT @PrintLine + ';'
FETCH   xArgs INTO @COL_NAME, @Type, @Length, @colid,
                     @xprec, @xscale
END
CLOSE      xArgs
DEALLOCATE xArgs
IF @LANG = 'VB' BEGIN
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()'
--         iReturn = Ctype(CommonData.NullToInteger(xSqlCommand.Parameters("@Return").Value),DatabaseReturnCode)
PRINT '        iReturn = Ctype(CommonData.NullToInteger(' + @ObPrefix
        + 'SqlCommand.Parameters("@Return").Value), DatabaseReturnCode)'
PRINT '        ' + @ObPrefix + 'SqlCommand.Dispose()'
PRINT '        ' + @ObPrefix + 'SqlConnection.Dispose()'
END
ELSE BEGIN
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)CommonData.NullToInteger(' + @ObPrefix
        + 'SqlCommand.Parameters["@Return"].Value);'
PRINT '        ' + @ObPrefix + 'SqlCommand.Dispose();'
PRINT '        ' + @ObPrefix + 'SqlConnection.Dispose();'
PRINT '      }'
END
IF @LANG = 'VB' BEGIN
PRINT '      Catch ex As System.InvalidOperationException'
PRINT '        Dim sMethod As String = '
      + 'System.Reflection.MethodInfo.GetCurrentMethod().Name.ToString()'
PRINT '        CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "")'
PRINT '        iReturn = DatabaseReturnCode.NetworkError'
PRINT '      Catch ex As Exception'
PRINT '        Dim sMethod As String = '
      + 'System.Reflection.MethodInfo.GetCurrentMethod().Name.ToString()'
PRINT '        CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "iId="'
      + ' & iId.ToString())'
PRINT '        iReturn = DatabaseReturnCode.OtherError'
PRINT '      Finally '
PRINT '        ' + @ObPrefix + 'SqlCommand.Connection.Close()'
PRINT '        ' + @ObPrefix + 'SqlCommand.Dispose()'
PRINT '        ' + @ObPrefix + 'SqlConnection.Dispose()'
PRINT '      End Try '
PRINT '    End Sub'
PRINT '    #End Region'
END
ELSE BEGIN
PRINT '      catch (System.InvalidOperationException ex)'
PRINT '      {'
PRINT '        string sMethod = '
      + 'System.Reflection.MethodInfo.GetCurrentMethod().Name;'
PRINT '        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 '        CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");'
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 '    }'
PRINT '    #endregion'
END
PRINT '*/'
PRINT 'GO'
PRINT @SET_DB
PRINT 'GO'
RETURN
-------------- this is the end ----------------
GO

Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239

Edited by TATWORTH on Mar 08, 2008 02:07 PM

Reply

TATWORTH

All-Star

72415 Points

14017 Posts

MVP

Re: filling the textbox,dropdownlist from the database??

Mar 08, 2008 11:52 AM|LINK

The execute permissions on the stored procedures are set by the following script

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:        Clive Chinery
-- Create date: 11Nov2007
-- Description:    Set Permissions
-- =============================================
CREATE PROCEDURE [dbo].[asp_dbPermissions]
(
@Execute BIT = 1, -- Default is Execute
@Print    BIT = 0 -- Default is No print
) AS
SET NOCOUNT ON;
DECLARE xArgs CURSOR FOR
    SELECT O.name
    FROM sysobjects O
    WHERE O.xtype = 'P' AND O.name LIKE 'usp_%'
    ORDER BY O.name
DECLARE @name VARCHAR(128)
DECLARE @exec VARCHAR(160)
OPEN xArgs
FETCH xArgs INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
-- Change WebRole to your desired database RoleName
SET @exec = 'GRANT EXECUTE ON ' + @name + ' TO WebRole '
IF @Print = 1 PRINT @exec
IF @Execute = 1 EXEC (@EXEC)
FETCH xArgs INTO @name
END
CLOSE xArgs
DEALLOCATE xArgs
GO
asp_dbPermissions 1,1

Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239

Reply

TATWORTH

All-Star

72415 Points

14017 Posts

MVP

Re: filling the textbox,dropdownlist from the database??

Mar 08, 2008 01:28 PM|LINK

Using the following

csp_MakeDelete 'Details', 'C#'

I generated the following stored procedure:

CREATE PROCEDURE dbo.usp_detailsDelete
(
@id   INT,
@Return         INT OUTPUT
) AS
-- Purpose:
--     Delete record on details table
-- Parameters:
--    id -
--    Return          - Zero or Error Code
-- History:
-- 08Mar2008 ACERXP\Clive Original coding
SET NOCOUNT ON
BEGIN TRY
DELETE details WHERE
id = @id
END TRY
BEGIN CATCH
SELECT @Return = @@error
END CATCH
RETURN
-------------- this is the end ----------------
GO
asp_dbPermissions
GO

Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239

Reply

TATWORTH

All-Star

72415 Points

14017 Posts

MVP

Re: filling the textbox,dropdownlist from the database??

Mar 08, 2008 01:31 PM|LINK

#region " Delete "
/// <summary>
/// Delete record on details table
/// </summary>
/// <remarks>
/// This code was autogenerated on 08Mar2008
/// </remarks>
/// <param name="iId">TBA</param>
/// <param name="iReturn">Return code to use in switch statement</param>
public void Deletedetails(int iId, ref DatabaseReturnCode iReturn)
{
    iReturn = DatabaseReturnCode.NoErrorDetected;
    string sConnect = CommonData.GetConnect();
    SqlConnection xSqlConnection = new SqlConnection(sConnect);
    SqlCommand xSqlCommand = new SqlCommand("usp_detailsDelete", xSqlConnection);
    try
    {
      xSqlCommand.CommandType = CommandType.StoredProcedure;
      xSqlCommand.Parameters.Add("@Id", SqlDbType.Int);
      xSqlCommand.Parameters["@Id"].Value = iId;
      xSqlCommand.Parameters.Add("@Return", SqlDbType.Int);
      xSqlCommand.Parameters["@Return"].Direction = ParameterDirection.Output;
      xSqlCommand.Connection.Open();
      xSqlCommand.ExecuteNonQuery();
      xSqlCommand.Connection.Close();
      iReturn = (DatabaseReturnCode)CommonData.NullToInteger(xSqlCommand.Parameters["@Return"].Value);
      xSqlCommand.Dispose();
      xSqlConnection.Dispose();
    }
    catch (System.InvalidOperationException ex)
    {
      string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;
      CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");
      iReturn = DatabaseReturnCode.NetworkError;
    }
    catch (Exception ex)
    {
      string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;
      CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");
      iReturn = DatabaseReturnCode.OtherError;
    }
    finally
    {
      xSqlCommand.Dispose();
      xSqlConnection.Dispose();
    }
}
#endregion

Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239

Reply

TATWORTH

All-Star

72415 Points

14017 Posts

MVP

Re: filling the textbox,dropdownlist from the database??

Mar 08, 2008 01:33 PM|LINK

The stored procedure that created it was

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[csp_MakeDelete]
(
@TABLENAME VARCHAR(255)= 'xyzzy',
@LANG      CHAR(2) = '' -- VB or C# (default set in configration section)
) AS
-- Purpose:
--   Create delete script from table definition
-- Copyright (C) 2000, 2003, 2004, 2005, 2006, 2007, 2008 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 database under development
--      csp_MakeDelete TABLENAME XX
--      where XX is VB or C#
--   2) The TABLENAME is case insensitive, however the table must exist
--   3) Id and TIMESTAMP columns are specially handled
--   4) Tables withan identity Id column will have a where clause generated
--      otherwise
-- Changes:
-- 11Nov2007 Clive Handle lack of TSTAMP
-- 12Jun2007 Clive Changes for static
-- 24Jun2006 Clive Make VB/C# switchable
-- 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)
DECLARE @ALLOWED    BIT
DECLARE @DefLanguage CHAR(2)
DECLARE @Separator2 VARCHAR(1)
SELECT @LANG = RTRIM(UPPER(LTRIM(@LANG)))
SELECT @ALLOWED = 0
IF @LANG = 'VB' SELECT @ALLOWED = 1
IF @LANG = 'C#' SELECT @ALLOWED = 1
-- Configuration start
SELECT @SpPrefix = 'usp_'          -- Set to desired prefix for stored procedures
SELECT @DOM_NAME = 'DALETH' + '\' -- Set to your domain name
-- Set @SET_DB to name of SP to set permissions or to blank
SELECT @SET_DB      = 'asp_dbPermissions'
SELECT @ObPrefix    = 'x'          -- Prefix for object variables
SELECT @DefLanguage = 'VB'         -- Default language C# or VB
SELECT @Separator2 = ''           -- Separator between Tablename and action ('_' or '')
-- Configuration end
IF @ALLOWED = 0 SET @LANG = @DefLanguage
IF @TABLENAME ='xyzzy'
OR (NOT EXISTS (SELECT * FROM sysobjects
    WHERE UPPER(name)=UPPER(@TABLENAME) and type='U'))
BEGIN
PRINT '--Use csp_MakeDelete TABLENAME (XX) to generate update S.P. for TABLENAME'
PRINT ' and XX is the language for the Wrapper VB or C#'
PRINT '--or csp_MakeDelete garbage   to get this help text!'
RETURN
END
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) + @Separator2 + '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
IF @HasTSTAMP > 0
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 TRY'
------------
IF @HasTSTAMP > 0 BEGIN
    PRINT 'BEGIN TRANSACTION'
    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
IF @HasTSTAMP > 0 PRINT 'COMMIT'
END
PRINT 'END TRY'
PRINT 'BEGIN CATCH'
PRINT ' SELECT @Return = @@error'
PRINT 'END CATCH'
PRINT 'RETURN'
PRINT '-------------- this is the end ----------------'
PRINT '/* Data Access code follows:'
IF @LANG = 'VB' BEGIN
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>'
IF @HasTSTAMP > 0
    PRINT '    '''''' <param name="iUpdatePersonId">Id of user from GetUserPersonId</param>'
PRINT '    '''''' <param name="iReturn">Return code to use in switch statement</param>'
PRINT '    Public Sub Delete' + @TN + '(iId As Integer, iUpdatePersonId As Int, ByRef iReturn As DatabaseReturnCode)'
PRINT '      iReturn = DatabaseReturnCode.NoErrorDetected'
PRINT '      Dim sConnect As String = CommonData.GetConnect()'
PRINT '      Dim ' + @ObPrefix + 'SqlConnection As SqlConnection = new SqlConnection(sConnect)'
PRINT '      Dim ' + @ObPrefix + 'SqlCommand As SqlCommand = new SqlCommand("'
               + @SpName + '", ' + @ObPrefix + 'SqlConnection)'
PRINT '      Try'
PRINT '        ' + @ObPrefix + 'SqlCommand.CommandType = CommandType.StoredProcedure'
PRINT '        ' + @ObPrefix + 'SqlCommand.Parameters.Add("@Id", SqlDbType.Int)'
PRINT '        ' + @ObPrefix + 'SqlCommand.Parameters("@Id").Value = iId'
IF @HasTSTAMP > 0 BEGIN
    PRINT '        ' + @ObPrefix + 'SqlCommand.Parameters.Add("@UpdatePersonId", SqlDbType.Int)'
    PRINT '        ' + @ObPrefix + 'SqlCommand.Parameters("@UpdatePersonId").Value = iUpdatePersonId'
END
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)'
                  + 'CommonData.NullToInteger(' + @ObPrefix + 'SqlCommand.Parameters["@Return"].Value)'
END
ELSE BEGIN
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>'
IF @HasTSTAMP > 0
    PRINT '    /// <param name="iUpdatePersonId">Id of user from GetUserPersonId</param>'
PRINT '    /// <param name="iReturn">Return code to use in switch statement</param>'
IF @HasTSTAMP > 0
    PRINT '    public void Delete' + @TN + '(int iId, int iUpdatePersonId, ref DatabaseReturnCode iReturn)'
ELSE
    PRINT '    public void Delete' + @TN + '(int iId, ref DatabaseReturnCode iReturn)'
PRINT '    {'
PRINT '      iReturn = DatabaseReturnCode.NoErrorDetected;'
PRINT '      string sConnect = CommonData.GetConnect();'
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;'
PRINT '        ' + @ObPrefix + 'SqlCommand.Parameters.Add("@Id", SqlDbType.Int);'
PRINT '        ' + @ObPrefix + 'SqlCommand.Parameters["@Id"].Value = iId;'
IF @HasTSTAMP > 0 BEGIN
    PRINT '        ' + @ObPrefix + 'SqlCommand.Parameters.Add("@UpdatePersonId", SqlDbType.Int);'
    PRINT '        ' + @ObPrefix + 'SqlCommand.Parameters["@UpdatePersonId"].Value = iUpdatePersonId;'
END
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)'
                  + 'CommonData.NullToInteger(' + @ObPrefix + 'SqlCommand.Parameters["@Return"].Value);'
PRINT '        ' + @ObPrefix + 'SqlCommand.Dispose();'
PRINT '        ' + @ObPrefix + 'SqlConnection.Dispose();'
PRINT '      }'
END
IF @LANG = 'VB' BEGIN
PRINT '        iReturn = DatabaseReturnCode.NoErrorDetected'
PRINT '      Catch ex As System.InvalidOperationException'
PRINT '        Dim sMethod As String = System.Reflection.MethodInfo.GetCurrentMethod().Name.ToString()'
PRINT '        CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "")'
PRINT '        iReturn = DatabaseReturnCode.NetworkError'
PRINT '      Catch ex As Exception'
PRINT '        Dim sMethod As String = System.Reflection.MethodInfo.GetCurrentMethod().Name.ToString()'
PRINT '        CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "iId="
                    + iId.ToString())'
PRINT '        iReturn = DatabaseReturnCode.OtherError'
PRINT '      Finally '
PRINT '        ' + @ObPrefix + 'SqlCommand.Dispose()'
PRINT '        ' + @ObPrefix + 'SqlConnection.Dispose()'
PRINT '      End Try'
PRINT '    End Function'
PRINT '    #End Region'
END
ELSE BEGIN
PRINT '      catch (System.InvalidOperationException ex)'
PRINT '      {'
PRINT '        string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;'
PRINT '        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 '        CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");'
PRINT '        iReturn = DatabaseReturnCode.OtherError;'
PRINT '      }'
PRINT '      finally'
PRINT '      {'
PRINT '        ' + @ObPrefix + 'SqlCommand.Dispose();'
PRINT '        ' + @ObPrefix + 'SqlConnection.Dispose();'
PRINT '      }'
PRINT '    }'
PRINT '    #endregion'
END
PRINT '*/'
PRINT 'GO'
PRINT @SET_DB
PRINT 'GO'
RETURN
-------------- this is the end ----------------
GO

Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239

Edited by TATWORTH on Mar 08, 2008 02:06 PM

Reply

TATWORTH

All-Star

72415 Points

14017 Posts

MVP

Re: filling the textbox,dropdownlist from the database??

Mar 08, 2008 02:17 PM|LINK

To make a count s.p. try

csp_MakeCount 'Details', 'C#'
it produced

CREATE PROCEDURE dbo.usp_detailsCount
(
@Count         INT OUTPUT
) AS
-- Purpose:
--     Count records on details table
-- Parameters:
--    Count          - Count of records
-- History:
-- 08Mar2008 ACERXP\Clive Original coding
SET NOCOUNT ON
SELECT @COUNT= COUNT(*) FROM details
RETURN
-------------- this is the end ----------------
GO
asp_dbPermissions
GO

Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239

Reply

TATWORTH

All-Star

72415 Points

14017 Posts

MVP

Re: filling the textbox,dropdownlist from the database??

Mar 08, 2008 02:19 PM|LINK

The wrapper code produced was

#region " Count "
/// <summary>
/// Count records on details table
/// </summary>
/// <remarks>
/// This code was autogenerated on 08Mar2008
/// </remarks>
/// <param name="iCount">Count of records</param>
public int Deletedetails()
{
    int iCount; // set to 0 by runtime
    string sConnect = CommonData.GetConnect();
    SqlConnection xSqlConnection = new SqlConnection(sConnect);
    SqlCommand xSqlCommand = new SqlCommand("usp_detailsCount", xSqlConnection);
    try
    {
      xSqlCommand.CommandType = CommandType.StoredProcedure;
      xSqlCommand.Parameters.Add("@Count", SqlDbType.Int);
      xSqlCommand.Parameters["@Count"].Direction = ParameterDirection.Output;
      xSqlCommand.Connection.Open();
      xSqlCommand.ExecuteNonQuery();
      xSqlCommand.Connection.Close();
      iCount = CommonData.NullToInteger(xSqlCommand.Parameters["@Count"].Value);
      xSqlCommand.Dispose();
      xSqlConnection.Dispose();
    }
    catch (Exception ex)
    {
      string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;
      CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");
      iCount = 0;
    }
    finally
    {
      xSqlCommand.Dispose();
      xSqlConnection.Dispose();
    }
    return iCount;
}
#endregion

Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239

filling the textbox,dropdownlist from the database?? RSS

72 replies

TATWORTH

Re: filling the textbox,dropdownlist from the database??

TATWORTH

Re: filling the textbox,dropdownlist from the database??

TATWORTH

Re: filling the textbox,dropdownlist from the database??

TATWORTH

Re: filling the textbox,dropdownlist from the database??

TATWORTH

Re: filling the textbox,dropdownlist from the database??

TATWORTH

Re: filling the textbox,dropdownlist from the database??

TATWORTH

Re: filling the textbox,dropdownlist from the database??

TATWORTH

Re: filling the textbox,dropdownlist from the database??

TATWORTH

Re: filling the textbox,dropdownlist from the database??

TATWORTH

Re: filling the textbox,dropdownlist from the database??

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft