Hi

I build computers and have a small asp.net webpage that I put in all the system components and their serial numbers which is then inserted into a SQL Database.

Currently I just manually input the system serial number into the form I have.

What I would like to do is everytime I go to input data for a new system, it displays the next serial number for that system on the page, that I fill in the information.

My Serial numbers are like this DLX07-3421

I also would like to have a form that I can change it as for example next year I would change it to DLX08-3422 etc. but keep the same numbers

 What would be the best way to go about this

 Thanks

 Matthew

 The simplest way that I can think of to use some parameter tables within your SQL Server database. One table would hold string values and in particular one row would hold the DLX-07 prefix. The other table would hold integer values and in particular one holding the last serial number e.g. 3421

All that is then required is a GetNextSerialNumber function which increment the last serial number by 1 and assemble the prefix and serial number (cast to varchar) returning the serial number as a varchar.

Hi

Thanks for your suggestion would you be able to help me with the coding or does anyone know of tutorials and or examples I can work from.

 Thanks

Matthew

 Which version of SQL Server are you using?
Are you using VB.NET or C#?

Please let me know and I will put some scripts together for you. 

 The tables required are created by this SQL2005 script

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParamInteger]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParamInteger]
(
    [Id] [int] NOT NULL,
    [ParamDescription] [nvarchar](100) NOT NULL CONSTRAINT [DF_ParamInteger_ParamDescription]  DEFAULT (''),
    [ParamValue] [int] NOT NULL CONSTRAINT [DF_ParamInteger_ParamValue]  DEFAULT ((0)),
 CONSTRAINT [PK_ParamInteger] PRIMARY KEY CLUSTERED ([Id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Non-identity primary key' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ParamInteger', @level2type=N'COLUMN', @level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Description of parameter' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ParamInteger', @level2type=N'COLUMN', @level2name=N'ParamDescription'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Value of parameter' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ParamInteger', @level2type=N'COLUMN', @level2name=N'ParamValue'
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].[ParamString]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParamString](
    [Id] [int] NOT NULL,
    [ParamDescription] [nvarchar](100) NOT NULL CONSTRAINT [DF_ParamString_ParamDescription]  DEFAULT (''),
    [ParamValue] [nvarchar](max) NOT NULL CONSTRAINT [DF_ParamString_ParamValue]  DEFAULT (''),
 CONSTRAINT [PK_ParamString] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Non-identity primary key' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ParamString', @level2type=N'COLUMN', @level2name=N'Id'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Description of parameter' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ParamString', @level2type=N'COLUMN', @level2name=N'ParamDescription'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Value of parameter' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ParamString', @level2type=N'COLUMN', @level2name=N'ParamValue'

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].[ParamBool]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParamBool](
    [Id] [int] NOT NULL,
    [ParamDescription] [nvarchar](100) NOT NULL CONSTRAINT [DF_ParamBool_ParamDescription]  DEFAULT (''),
    [ParamValue] [bit] NOT NULL CONSTRAINT [DF_ParamBool_ParamValue]  DEFAULT ((0)),
 CONSTRAINT [PK_ParamBool] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Non-identity primary key' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ParamBool', @level2type=N'COLUMN', @level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Description of parameter' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ParamBool', @level2type=N'COLUMN', @level2name=N'ParamDescription'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Value of parameter' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ParamBool', @level2type=N'COLUMN', @level2name=N'ParamValue'
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].[ParamDate]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParamDate]
(
    [Id] [int] NOT NULL,
    [ParamDescription] [nvarchar](100) NOT NULL CONSTRAINT [DF_ParamDate_ParamDescription]  DEFAULT (''),
    [ParamValue] [datetime] NOT NULL CONSTRAINT [DF_ParamDate_ParamValue]  DEFAULT (getutcdate()),
 CONSTRAINT [PK_ParamDate] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Non-identity primary key' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ParamDate', @level2type=N'COLUMN', @level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Description of parameter' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ParamDate', @level2type=N'COLUMN', @level2name=N'ParamDescription'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Value of parameter' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ParamDate', @level2type=N'COLUMN', @level2name=N'ParamValue'

 

 The data to populate the tables is:

INSERT INTO ParamString(Id, ParamDescription, ParamValue)
VALUES (1, 'Serial number prefix', 'DLX07-')
INSERT INTO ParamInteger(Id, ParamDescription, ParamValue)
VALUES (1, 'Serial number prefix', 3421)

 The required stored procedure is:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:          Clive Chinery
-- Create date: 27Nov2007
-- Description:    Get next serial number
-- =============================================
CREATE PROCEDURE dbo.GetNextSerialNumber
    -- Add the parameters for the stored procedure here
  @SERIAL VARCHAR(20) OUTPUT
AS
SET NOCOUNT ON
SELECT @SERIAL = ParamValue FROM ParamString WHERE Id = 1
SELECT @SERIAL = @SERIAL + CONVERT(VARCHAR(10), ParamValue) FROM ParamInteger WHERE Id = 1
UPDATE ParamInteger SET ParamValue = ParamValue + 1  WHERE Id = 1
GO

 Excercising the s.p. by

DECLARE @SERIAL VARCHAR(20)
EXEC dbo.GetNextSerialNumber @SERIAL OUTPUT
PRINT @SERIAL
EXEC dbo.GetNextSerialNumber @SERIAL OUTPUT
PRINT @SERIAL
GO

gives

DLX07-3421
DLX07-3422

Please let me know which of VB.NET or C#, you need the wrapper code in. 

Hi

 Thanks for your help, I really appricate it. I will need the code in VB.NET

Thanks

 Matthew

 Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class Class1
#Region " Update "
  ''' <summary>
  ''' Get Next Serial Number
  ''' </summary>
  ''' <remarks>
  '''
  ''' </remarks>
  ''' <returns>Next serial number</returns>
  Public Shared Function GetNextSerialNumber() As String
    Dim sConnect As String = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=XXXXX"
    Dim xSqlConnection As SqlConnection = New SqlConnection(sConnect)
    Dim xSqlCommand As SqlCommand = New SqlCommand("GetNextSerialNumber", xSqlConnection)
    xSqlCommand.CommandType = CommandType.StoredProcedure
    Dim sReturn As String = String.Empty
    Try
      xSqlCommand.Parameters.Add("@SERIAL", SqlDbType.VarChar, 20)
      xSqlCommand.Parameters("@SERIAL").Direction = ParameterDirection.Output
      xSqlCommand.Connection.Open()
      xSqlCommand.ExecuteNonQuery()
      xSqlCommand.Connection.Close()
      sReturn = "" & xSqlCommand.Parameters("@SERIAL").Value
      xSqlCommand.Dispose()
      xSqlConnection.Dispose()
    Catch ex As Exception
    Finally
      xSqlCommand.Connection.Close()
      xSqlCommand.Dispose()
      xSqlConnection.Dispose()
    End Try
    Return sReturn
  End Function
#End Region
End Class

Hi

Is their anything special I need to do with the code to display the serial numbers on an asp.net page.

 As I have copied the code into an asp.net page changed the Catalog Name and DataSource location and I can't get it to work

Matthew 

 I should have posted my Console program that I used to test it with.

Assuming the class1 is part of the asp.net project

Dim sNextSerial As String = Class1.GetNextSerialNumber() 

Which version of SQL Server are you using? Did you try the test TSQL in SSMS or Query Analyser? 

Hi

 Here is how I have got the code set out in the asp.net page to display the serial number on the page, but I can't get it to work, I know the Database is setup correctly as I have tested it and it generates the serial numbers fine using the Query Analyiser.

 Below is the code,

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="Imports.System" %>

<script runat="server">

Dim sNextSerial As String = Class1.GetNextSerialNumber()

Public Class Class1
#Region " Update "
  ''' <summary>
  ''' Get Next Serial Number
  ''' </summary>
  ''' <remarks>
  '''
  ''' </remarks>
  ''' <returns>Next serial number</returns>
  Public Shared Function GetNextSerialNumber() As String
            Dim sConnect As String = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CompBuild;Data Source=SERVER01\SQLEXPRESS"
    Dim xSqlConnection As SqlConnection = New SqlConnection(sConnect)
    Dim xSqlCommand As SqlCommand = New SqlCommand("GetNextSerialNumber", xSqlConnection)
    xSqlCommand.CommandType = CommandType.StoredProcedure
    Dim sReturn As String = String.Empty
    Try
      xSqlCommand.Parameters.Add("@SERIAL", SqlDbType.VarChar, 20)
      xSqlCommand.Parameters("@SERIAL").Direction = ParameterDirection.Output
      xSqlCommand.Connection.Open()
      xSqlCommand.ExecuteNonQuery()
      xSqlCommand.Connection.Close()
      sReturn = "" & xSqlCommand.Parameters("@SERIAL").Value
      xSqlCommand.Dispose()
      xSqlConnection.Dispose()
    Catch ex As Exception
    Finally
      xSqlCommand.Connection.Close()
      xSqlCommand.Dispose()
      xSqlConnection.Dispose()
    End Try
    Return sReturn
  End Function
#End Region
    End Class
        </script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
    </div>
    </form>
</body>
</html>

 Thanks

Matthew

The command program I used to test the function was

Imports System.IO
Imports VbTest '

Module Module1
  Sub Main()
    Console.WriteLine(Class1.GetNextSerialNumber())
  End Sub
End Module

Using the aspx

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default2.aspx.vb" Inherits="Default2" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
  <head id="Head1" runat="server">
    <title>Test Get Next Serial</title>
  </head>
  <body>
    <form id="form1" runat="server">
      <table>
        <tr>
          <td><asp:Label ID="lblSerial" runat="server" Text="_____"></asp:Label></td>
          <td>&nbsp;</td>
        </tr>
        <tr>
          <td>&nbsp;</td>
          <td><asp:Button ID="cmdGet"  runat="server" Text="Get Next"  /></td>
        </tr>
      </table>     
    </form>
  </body>
</html>

and the code behind file

 Imports System
Imports System.Data
Imports System.Data.SqlClient

Partial Class Default2
    Inherits System.Web.UI.Page
#Region " GetNextSerialNumber "
  ''' <summary>
  ''' Get Next Serial Number
  ''' </summary>
  ''' <remarks>
  '''
  ''' </remarks>
  ''' <returns>Next serial number</returns>
  Private Function GetNextSerialNumber() As String
    Dim sConnect As String = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=ACERXP"
    Dim xSqlConnection As SqlConnection = New SqlConnection(sConnect)
    Dim xSqlCommand As SqlCommand = New SqlCommand("GetNextSerialNumber", xSqlConnection)
    xSqlCommand.CommandType = CommandType.StoredProcedure
    Dim sReturn As String = String.Empty
    Try
      xSqlCommand.Parameters.Add("@SERIAL", SqlDbType.VarChar, 20)
      xSqlCommand.Parameters("@SERIAL").Direction = ParameterDirection.Output
      xSqlCommand.Connection.Open()
      xSqlCommand.ExecuteNonQuery()
      xSqlCommand.Connection.Close()
      sReturn = "" & xSqlCommand.Parameters("@SERIAL").Value
      xSqlCommand.Dispose()
      xSqlConnection.Dispose()
    Catch ex As Exception
    Finally
      xSqlCommand.Dispose()
      xSqlConnection.Dispose()
    End Try
    Return sReturn
  End Function
#End Region

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not Me.IsPostBack() Then
      lblSerial.Text = " Click button for next number "
    End If
  End Sub
  Protected Sub cmdGet_Command(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.CommandEventArgs) Handles cmdGet.Command
    lblSerial.Text = GetNextSerialNumber()
  End Sub
End Class

It gets the next serial number 

Hi

Thanks for the code, I do have a problem, when I copy and paste the code into the code behind file, and upload it to my local dev box it brings up a Internal Error 500 Message.

When I go to open the file, alot of the code is missing, and can't get it to work.

Is their any chance of getting the files created and I download them as i have never had this problem before.

Thanks

Matthew 

Please post the full test of the error message.

Also what O/S is your local development box? 

Hi

I have attached the error message I am getting

Line 14:         <tr>
Line 15:           <td>&nbsp;</td>
Line 16:           <td><asp:Button ID="cmdGet"  runat="server" Text="Get Next" OnClick="cmdGet_Click" /></td>
Line 17:         </tr>
Line 18:       </table>      

Please recopy line 16 - the OnClick="cmdGet_Click" should not have been there - I have corrected the line concerned.

  I have created a CodePlex project http://www.codeplex.com/CommonParam to defines table, stored procedures, data layer and sample ASP.NET pages to hold parameter information for boolean, integer, string and other data types.