Accessing one Record from Database

Home ›

ASP.NET Forums » Data Access » Access Databases and AccessDataSource Control » Accessing one Record from Database

Previous Next

Accessing one Record from Database

Last post 03-08-2008 1:43 PM by r_nassabeh. 10 replies.

Sort Posts:

Accessing one Record from Database

03-04-2008, 11:41 PM

Contact

Loading...
Anemix
Joined on 03-04-2008, 3:49 PM
Posts 36

Hi,

I am new on this, I am trying to understand how to retrieve 1 record from a database,

I am using SQLDataSource, FormView and DetailsView controls to display data, very simple.

But what I need is to retrieve just 1 record and either display it in a label or textbox or variable so I can manipulate that data

I am using VWD Express 2008 and SQL Express, so what's the easiest way to accomplish this in .NET? Using classic ASP i used to do it easily like this:

<%dim SQLStatement

SQLStatement = "SELECT Count(*) AS intTotal FROM DataTable where Name='"&Search_Name&"' "

RecordSet.Open SQLStatement, Connection, 3, 3

' Display result

Response.Write RecordSet("intTotal")

RecordSet.Close

----------- OpenDB.asp -----------------

Dim Connection

Dim RecordSet

Dim ConnectionString

ConnectionString ="Provider=SQLOLEDB; Data Source = (local); Initial Catalog = myDB; User Id =myUser; Password=myPassword"

Set Connection = Server.CreateObject("ADODB.Connection")

Set RecordSet = Server.CreateObject("ADODB.Recordset")

RecordSet.CursorType = 3

RecordSet.CursorLocation = 3

Connection.Open ConnectionString

--------------------------------------------

I guess that now in VWD Express 2008 using SQLDataSource or something must be much easier than before, but I don't know how. any suggestions?

Thanks.

PS: sorry for the format

Filed under: label control texbox ExceuteScalar Data Binding Dataset datareader

Re: Accessing one Record from Database

03-05-2008, 1:55 AM

Contact

Answer

Loading...
r_nassabeh
Joined on 01-11-2008, 5:14 AM
Shiraz, Iran
Posts 490

It's fairly simple in .Net, just add a using System.Data.SqlClient; line on top of your code behind and then you can execute the command in your code:

SqlConnection cn = new SqlConnection("Some Connection String");
SqlCommand cmd = new SqlCommand("SELECT Count(*) FROM DataTable", cn);
cn.Open();
int count = Convert.ToInt32(cmd.ExecuteScalar());
cn.Close();

Reza Nassabeh
www.professionalcsharp.com

Don't forget to click "Mark as Answer" on the post that helped you. That way future readers will know which post solved your issue.

Re: Accessing one Record from Database

03-05-2008, 2:03 AM

Contact

Loading...
TATWORTH
Joined on 02-04-2003, 8:34 AM
England
Posts 6,474

I use stored procedures to write both the stored procedures for insert, update, listall, count rows and delete together with the necessary wrapper code in either vb.net or c#.

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.

Re: Accessing one Record from Database

03-05-2008, 2:08 AM

Contact

Loading...
TATWORTH
Joined on 02-04-2003, 8:34 AM
England
Posts 6,474

The required library (in both C# and Vb.net) is available on thread http://forums.asp.net/p/1207886/2155318.aspx#2155318

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.

Re: Accessing one Record from Database

03-05-2008, 2:56 AM

Contact

Loading...
TATWORTH
Joined on 02-04-2003, 8:34 AM
England
Posts 6,474

The current set follows:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[asp_dbPermissions]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- 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
PRINT @name
FETCH xArgs INTO @name
END
CLOSE xArgs
DEALLOCATE xArgs'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[csp_MakeUpdate]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
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.GetConnection()''
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.GetConnection();''
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 ----------------'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[csp_MakeSelect]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE Procedure [dbo].[csp_MakeSelect]
(
@TABLENAME NVARCHAR(255)= ''xyzzy'',
@LANG      CHAR(2) = '''' -- VB or C# (default set in configration section)
) AS
-- Purpose:
--   Create Select script from table definition
-- Copyright (C) 2000, 2003, 2004, 2005, 2006, 2007 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 current database
--      by entering csp_MakeSelect TABLENAME XX
--      where XX is either VB or C# (can be defaulted)
--   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
-- 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
SET NOCOUNT ON
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)
DECLARE @VarPrefix VARCHAR(10)
DECLARE @ALLOWED    BIT
DECLARE @DefLanguage CHAR(2)
DECLARE @Separator2 VARCHAR(1)
DECLARE @ArgName    VARCHAR(80)
DECLARE @COL_NAME   VARCHAR(80)
DECLARE @HAS_LAST   BIT
DECLARE @Type       VARCHAR(32)
DECLARE @Length     INT
DECLARE @SqlDbType VARCHAR(20)
DECLARE @COMMENT    VARCHAR(132)
DECLARE @PREFIX     VARCHAR(1)
DECLARE @ModPrefix VARCHAR(10)
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
SELECT @ModPrefix   = ''m_''         -- Module level prefix
-- 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_MakeSelect TABLENAME (XX) to generate select S.P. ''
PRINT '' for TABLENAME and XX is the language for Wrapper VB or C#''
PRINT ''--or csp_MakeSelect garbage to get this help text!''
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=0
DECLARE xArgs CURSOR FOR
SELECT Colname FROM @DocTable
ORDER BY 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 + ''Select''
SELECT @HelperName = RTRIM(@TN) + ''Select''
SELECT @ColLength= MAX(DATALENGTH(CONVERT(VARCHAR(64),Colname)))
FROM @DocTable
WHERE Cstatus<>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 @DocTable
WHERE Cstatus=128
SELECT @HAS_LAST = 0
SELECT @RowIndex = 0
IF      @ColLength < 16 SELECT @ColLength = 16
DECLARE xArgs CURSOR FOR
SELECT ''@'' + Colname, Coltype, length, xprec, xscale
FROM @DocTable
ORDER BY 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 Colname FROM @DocTable
WHERE Cstatus = 128
ORDER BY 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 ''-- 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 @DocTable
WHERE Colname NOT IN (''Id'')
DECLARE xArgs CURSOR FOR
SELECT Colname FROM @DocTable
WHERE Colname NOT IN (''Id'')
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)
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 @DocTable
-- Where clause missing
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 @DocTable
     WHERE Cstatus = 128
    IF @RowCount = 0 BEGIN
      PRINT ''-- THERE IS NO Identity ROW - assuming first row''
      DECLARE xArgs CURSOR FOR
      SELECT Colname + ''=@'' + Colname
      FROM @DocTable
      ORDER BY ColId
      OPEN xArgs
      FETCH xArgs INTO @SetValue
      PRINT '' '' + @TN + ''.'' + @SetValue
      CLOSE      xArgs
      DEALLOCATE xArgs
      END
    ELSE BEGIN
      DECLARE xArgs CURSOR FOR
      SELECT Colname + ''=@'' + Colname
      FROM @DocTable
      WHERE Cstatus = 128
      ORDER BY ColId
      SELECT @RowIndex=0
      OPEN xArgs
      FETCH xArgs INTO @SetValue